SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cascading Parameter of type DateTime does not work?


Cascading Parameter of type DateTime does not work?

Author
Message
sotn
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 469
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
Scott Murray-240410
Scott Murray-240410
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 3143
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?
sotn
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 469
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.
Scott Murray-240410
Scott Murray-240410
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 3143
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search