Question about date parameter and calendar control

Posted on 2008-11-11
Medium Priority
Last Modified: 2012-05-05
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!

Question by:rss2
  • 2
LVL 11

Expert Comment

ID: 22930951
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.


Author Comment

ID: 22932655
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,
LVL 11

Accepted Solution

shorak earned 2000 total points
ID: 22939706
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.



Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Integration Management Part 2
Screencast - Getting to Know the Pipeline

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question