Question about date parameter and calendar control

Hi Experts,

I have a report in SSRS (SQL Server 2005) and one of the parameters is a date. My question is a two-parter:

1. Cascading parameters: How do I set the date of the date/calendar parameter with a value from a dataset that uses the value of a different parameter? In other words, the users sees the date parameter calendar go to a date automatically after picking one of the values from another parameter's drop down selection.

2. Is is possible to attach date values to the date calendar parameter so that unavailable dates (those not in the dataset) are greyed out and available ones are available to the user to select?

thank you. Mucho urgento!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I dont think what you are asking for in question 2 is possible with the calendar but you can create a dropdown list of available dates.

As to cascading the parameters so that the dates are limited to what was chosen in the previous parameter, this should be possible but depends on your how your working with your dataset.

will you be passing the selected date parameter as well as the first parameter back to the same dataset or will you be using more than one dataset?

One way of creating your date dropdown would be to create a dataset with a query such as

select date from table
where criteria=paramater1

you would then create a report parameter representing the date and use this datset as the source. Select the date field as the default value also.

in the preview.. the date parameter will remain greyed until the first paremeter has been selected and then will list all the available dates.

Let me know if you want more details.

rss2Author Commented:
Hi shorak,

I am getting the date from a separate dataset already. That dataset is the result of a stored procedure, which takes one parameter. The parameter is populated by the first report parameter.

Confusing, so I'll outline it:
2 parameters - 1 drop down select, 1 date (calendar)

3 datasets: - 1 to provide the repot drop down select with data, 1 to provide the report date (calendar) with a date, and 1 to provide the report with data based on those 2 report parameters

So I want the date in the calendar to default on a date that comes from the dataset, which depends on the drop down value.

It's just not working. Could you tell me, for each of the 2 parameters, what settings I should have for each? (AVailable values: non-queries | from query and Default values: Non-queries | From query | Null )?

Thank you,
thanks for the additional info.

As an example, lets call the dataset that provides the details for parameter 1 'OptionList',
the datset that produces the dates 'DateList' and your main dataset that will produce your final data 'Main'

I Assume you know how to create report parameters.

First of all create the first dropdown parameter. Go to the parameters screen, give it a name, a datatype (interger or string) and the prompt text.

In the next section (Available Values), select 'From Query' and you will get 3 fields to complete.
In the dataset field, use the drop down to select the dataset that gives you the list that will appear for parameter1 (ie 'OptionList' in my example).
In the Value field, select the field that contains the values that you will send as criteria for dataset 'DateList'.
In the Label field select the field that the user will see in their drop down. This can be the same as the value field if there is only one field returned by dataset 'OptionList'

In the next section (Default Values), If you want to have a fixed default value then you  can select non-queried  and enter the value in the box. If you want it to automatically pick a value from the query then select from query and it will ask you for the dataset name and the value field. It will automatically select the first value from the list returned.
Click on okay and you have created the first dropdown parameter.

Now, how to get the available date parameter to work..

I'm assuming you have already set up your stored procedure with a variable in the where clause that takes a value and that your date dataset (DateList in my example) has the commandline 'exec sp_yourstoredprocedure @parameter1'  in the query string window where @parameter1 is the name of the parameter you just created (preceeding with a @).

Create the date parameter in exaclty the same way as the first one but select the 'DateList' dataset when you get to the Available Values and Default Values sections in the parameter window and also select 'from query'  and not non-queried in the Default Values section.

In your final datset 'Main', refer to the two parameters and hopefully your report will work.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.