Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cascading Parameter of type DateTime does not work? Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 125, Visits: 415
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

Post #1366424
Posted Tuesday, October 2, 2012 3:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:11 AM
Points: 243, Visits: 2,717
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?
Post #1366929
Posted Tuesday, October 2, 2012 4:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 125, Visits: 415
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.
Post #1366938
Posted Tuesday, October 2, 2012 4:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:11 AM
Points: 243, Visits: 2,717
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.
Post #1366955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse