ssrs date filter expression for startdate/enddate declared variables

Question

I should create ssrs report and I have following scenario: I have sql query with median, q25, q75 functions. The query is grouped by geography. Date, which is not grouped, should be filtered in ssrs in order to calculate aggregations for certain time period and they must be grouped only by geography dimension. Bouth variables startdate and enddate are declared and implemented in to where clause and assigned to date column.

what I need in ssrs is expression for parameters startdate and enddate in the dataset filter. Since I do not select Date in the query, i do not have any generated expression in the ssrs filter.

Can I link report parameters to the query variables or does somebody know any other workaround for this?

I would really appreciate any help!

Chris Latta Chris Latta · Accepted Answer · 2014-03-02T02:06:36

Your query should look something like this:

SELECT Geography, SUM(Amount) AS Amount
FROM MyTable
WHERE DateField >= @StartDate AND DateField <= @EndDate
GROUP BY Geography

On your dataset's property page, click the Parameters tab and make sure that the date parameters are mapped to the variables you use in your query.

ssrs date filter expression for startdate/enddate declared variables

1 Answers