October 1, 2012 at 3:34 am
I have a normal integer parameter to allow the user to select a preset date range like this
value, label
1, This Month
2, Last Month
3, This quarter
etc.
I have a table which for an DateRange of 1 has 2012-10-01 00:00:00.000 and 2012-10-31 00:00:00.000 as datefrom/dateto
The DateRange of 2 returns 2012-09-01 00:00:00.000 and 2012-09-30 00:00:00.000
The query for the dateFrom and dateTo is very basic
SELECT DateFrom, DateTo
FROM PresetDateRanges
WHERE DateRange = @DateRange
When I run the report, (no default for the preset choice parameter) and choose on of the preset values, then the date from and date to are populated correctly, but if I then change the preset value the date from/to do not change.
This technique works fine for normal varchar parameters, such as country with region but it seems either their is a bug in 2008R2 SSRS to do with DateTime parameters or I am getting something wrong.
Any ideas?
TIA
October 2, 2012 at 3:59 am
I would start by displaying the parameters on the report to see exactly what values are being passed when you select an alternate value. That may point you in the direction of the problem. Are the query values and lables based on a query or are they hard coded?
October 2, 2012 at 4:16 am
Good idea, I have notw tried that and have displayed the parameters onto the report and they match the paremeter section, which means that after the initial selection they are not getting changed.
To recap:
I have a table with Date From and Date To fields, and an ID field which equates to the Prest Option
A parameter with options 1) This Month or 2) Last Month etc. These id's match the preset date table.
Upon selecting this ID then the date from date to paramters should change to use these preset values, so If I choose 'Last Month' then a value of 2 is used like this
The query for the dateFrom and dateTo is very basic
SELECT DateFrom, DateTo
FROM PresetDateRanges
WHERE DateRange = @DateRange --@DateRange is 2
As there is no default value, this works fine for the initial user selection, but if they want to change their mind and change the drop down list, then this does not perform the query again. This seems to only happen with date time parameters
In BIDS, the parameter section is re-drawn but no DB activity is performed.
October 2, 2012 at 4:54 am
I believe I understand better. You are wanting a query to run after the parameter has been selected. That is not quite how the parameters work as they query before in order to populate the list boxes. However, you can actually handle your situation in one of 2 ways. Create a second dependent parameter (which can be hidden) which takes the value of the selected date type parameter and runs your query to get the dates or you could add the date query to your main query and set the variables for your dates there based on the selected date type. I would prefer the first method.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply