none
How to determine a report parameter based upon selection of other report parameter

    Question

  • Hi there,

    I have two parameters called Start Date and End Date which are to be populated based upon another parameter Perid (This parameter has default values as Weekely/BiWeekly/Monthly with default : Weekly) Based upon the selection made in Period dropdown, I need to populate Start Date and End Date accordingly.

    The Start Date and End Date Values are calculated using a custom code.

    I have already assigned StartDate =Code.MyFunction((Parameters!Period.Value).

    But when I change the Period value to anything other than default value, the start date and end date are not updated. They still show the dates for default period value i.e., Weekly.

    Please suggest.

    Thanks.

    Tuesday, December 03, 2013 6:19 AM

Answers

All replies

  • Hi Manisha,

    According to your description, you want to StartDate and EndDate parameters refresh after change the values on Perid parameter, right? In this case, the parameters are called cascading parameters in Reporting Services. You need create a dataset to return the values of StartDate and EndDate. Here is a sample query for your reference.
    CREATE TABLE #test(Perid nvarchar(20), DateValue date)
    INSERT INTO #test VALUES 
    (' Weekely', CONVERT(date, DateAdd(day, -6 - DatePart(WeekDay, getdate()), GETDATE()))),
    (' BiWeekly', CONVERT(date, DateAdd(day, -13 - DatePart(WeekDay, getdate()), GETDATE()))),
    (' Monthly', CONVERT(date, DateAdd(day, - 29 - DatePart(WeekDay, getdate()), GETDATE())))
    SELECT * FROM #test where Type=@ Perid

    Reference:
    Adding Cascading Parameters to a SSRS Report
    Lesson 4: Adding Cascading Parameters (SSRS)

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Manisha J P Friday, December 06, 2013 5:55 AM
    • Unmarked as answer by Manisha J P Friday, December 06, 2013 5:56 AM
    Wednesday, December 04, 2013 9:00 AM
  • This scenario is coming up a lot. I have done this in several reports. See this thread for more details:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a6defdd2-113b-49ac-a2bb-c4bc20b02d42/can-we-show-between-date-parameters-displayed-like-below-scenario?forum=sqlreportingservices

    In my response you will see a TSQL query that returns various start and end dates based on the selection of the parent parameter. The key is that both start and end date must have their default value set to the appropriate field from the query and have allowed values set the same. The query is designed to return a single row. When you change period, the query is run and appropriate values are returned. If the values from the query change then the value of the cascaded parameter will also change because their allowed values change. Unfortunately add available values removes the ability for end user input.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Wednesday, December 04, 2013 3:24 PM
  • Hi team,

    I have implemented cascading of parameters and I could see values of parameters are changed accordingly.

    But the new problem that I am facing is, this update is not happening every time I change the Period value.

    Sometimes, one of the parameters do not get populated and if both are populated then on click of view report button, one of the parameters looses its value. Please find the attachment.

    Start Date Current value is not populated when Period value is changed from Monthly to Weekly


    • Edited by Manisha J P Friday, December 06, 2013 6:05 AM
    Friday, December 06, 2013 6:04 AM