Populating Dataset in SSRS Using Dynamic Query Including Declare Variables

Question

I am currently using SQL Server 2012 and Visual Studio 2012 for my projects.

My question is I have the following code below and it contains declare variables. Basically, when I use it in my SQL server I set @StartDate to the 1st of current month and the @EndDate to the last day of current month (December.)

DECLARE @StartDate date
DECLARE @EndDate date

-- SET @StartDate = '20161201'
-- SET @EndDate = '20161231'

SELECT  A.[TYPE_NAME], -- Type_Name
    A.[STRT_DTTM], --Start Date
    A.[END_DTTM], --End Date
    B.[ASSIGNED_DT], --Assigned Date
    CASE WHEN (CAST(A.[STRT_DTTM] AS DATE) <= @EndDate AND (CAST(A.[END_DTTM] as date) > @EndDate OR A.[END_DTTM] IS NULL))
        THEN 1
        ELSE 0
        END AS 'OpenIndicator',
    CASE WHEN CAST(A.[END_DTTM] as DATE) between @StartDate AND @EndDate
        THEN 1
        ELSE 0
        END AS 'ClosedIndicator',
    CASE WHEN CAST(B.[ASSIGNED_DT] as DATE) between @StartDate AND @EndDate
        THEN 1
        ELSE 0
        END AS 'AssignedIndicator',
FROM    Report AS A     
    INNER JOIN Dimension AS B 
    ON A.[ID] = B.[ID]  
WHERE   1=1
AND     A.[EXPIRE_DATE] = '12/31/9999' --Expire Date (Basically looking for active records.)
AND     (A.[TYPE_NAME] like '%Bicycle%'
    OR      A.[TYPE_NAME] like '%Car%')

So far in SSRS I have placed the above code into a dataset. I also have created the 2 parameters in SSRS StartDate and EndDate. When I try to run a simple tablix based off the dataset...nothing appears.

I commented out the SET @StartDate = '20161201' and SET @EndDate = '20161231'.

I'm very limited with what I can do at work, so I can't use stored procedures unfortunately as well, because that could make it easier I think.

But I am trying to populate a Sales like dataset by what the user inputs into the two parameters StartDate and EndDate but I am having trouble with that in SSRS. Can anyone please help me exactly on what to do, I'm very lost.

Tried to comment in the code to make it easier to understand and had to change some things around due to not trying to get in trouble at work too.

Maybe a silly question but have you actually mapped the SSRS parameters to your query parameters in SSRS? just creating the parameters is not enough in SSRS. Also if you have parameters in SSRS report , you do not need to declare them again in your query. Just make sure that the parameters are mapped to the query properly. — M.Ali

M.Ali M.Ali · Accepted Answer · 2016-12-27T17:10:20

You DO NOT need to declare variables in your query for the dataset, this is handled in the SSRS application layer. Your query should be something like..

SELECT  A.[TYPE_NAME],     -- Type_Name
        A.[STRT_DTTM],     -- Start Date
        A.[END_DTTM],      -- End Date
        B.[ASSIGNED_DT],   -- Assigned Date
    CASE WHEN (CAST(A.[STRT_DTTM] AS DATE) <= @EndDate 
                 AND (CAST(A.[END_DTTM] as date) > @EndDate OR A.[END_DTTM] IS NULL))
        THEN 1
        ELSE 0
        END AS 'OpenIndicator',
    CASE WHEN CAST(A.[END_DTTM] as DATE) between @StartDate AND @EndDate
        THEN 1
        ELSE 0
        END AS 'ClosedIndicator',
    CASE WHEN CAST(B.[ASSIGNED_DT] as DATE) between @StartDate AND @EndDate
        THEN 1
        ELSE 0
        END AS 'AssignedIndicator',
FROM    Report AS A     
    INNER JOIN Dimension AS B 
    ON A.[ID] = B.[ID]  
WHERE   1=1
AND     A.[EXPIRE_DATE] = '12/31/9999' 
AND     (A.[TYPE_NAME] like '%Bicycle%'
    OR      A.[TYPE_NAME] like '%Car%')

Since you have mentioned that you have already created the parameters in SSRS, Right-Click your DataSet and go to Properties then on the Parameters tab, make your SSRS parameters are mapped to your query parameters.

And finally if you want the default dates to be the 1 and last day of the current month you can go to Parameters, Right-Click go to Properties , go to Default Values tab and click on Add and use the following expressions.

For 1st Day of the current Month

=Today.AddDays(1-Today.Day)

For Last Day of the current Month

=DateSerial(Year(Now()), Month(Now()), "1").AddMonths(1).AddDays(-1)

Populating Dataset in SSRS Using Dynamic Query Including Declare Variables

1 Answers