SSRS: Parameter population from a dataset

Question

I have a dataset with start dates, end dates, and term codes. The user will enter in an academic year. The academic year will be used to determine the three term codes for that year. I want to use those three codes to select start and end dates for each term, which will go into the main query. I can achieve this by setting up three datasets that will hold the term code, start date, and end date for each term and populating the start and end date parameters from there, but what I want to know is if there is a more dynamic way to achieve this.

Thanks!

Some clarification. I want a way to populate all six parameters from one dataset, not each start and end date getting its own dataset.

Ron Smith Ron Smith · Accepted Answer · 2014-02-20T06:00:03

I can't be sure of how your data is set up, so I'm going to make a few guesses here. You can return six separate dates by pivoting the Start and End Dates for each Term:

declare @TermCodes table(AcademicYear int,TermCode varchar(50))
insert into @TermCodes
    values
        (2014,'FL14'),
        (2014,'SP14'),
        (2014,'SM14')

declare @TermDates table(TermCode varchar(50), StartDate date,EndDate date)
insert into @TermDates
    values
        ('FL14','20140915','20141215'),
        ('SP14','20150115','20150415'),
        ('SM14','20150515','20150815')

declare @AcademicYear int
set @AcademicYear = 2014

select
    min(StartDate1) StartDate1,
    min(EndDate1) EndDate1,
    min(StartDate2) StartDate2,
    min(EndDate2) EndDate2,
    min(StartDate3) StartDate3,
    min(EndDate3) EndDate3
from    (
        select
            td.StartDate,
            td.EndDate,
            'StartDate' + cast(row_number() over(order by td.StartDate) as char(1)) StartDates,
            'EndDate' + cast(row_number() over(order by td.StartDate) as char(1)) EndDates
        from @TermCodes tc
            inner join @TermDates td
                on td.TermCode = tc.TermCode
        where tc.AcademicYear = @AcademicYear
        ) t
    pivot   (
            max(StartDate)
            for StartDates in(StartDate1,StartDate2,StartDate3)
            ) sd
    pivot   (
            max(EndDate)
            for EndDates in(EndDate1,EndDate2,EndDate3)
            ) ed

SSRS: Parameter population from a dataset

1 Answers