Question about date parameter and calendar control

Posted on 2008-11-11
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
    LVL 11

    Expert Comment

    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

    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

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now