Displaying data in parameter dynamically

Hi All,

I want to set the default value of the parameter based on the query result, when the current_status parameter is set as "opened" I want a certain date value to be displayed in the next parameter (start date), if it is set as closed I want a different date value to be displayed in the next parameter (start date), , so I created the dataset with the query given below and assigned the default value to the query fields start and end date, however my Report parameters are not changing as expected, kindly suggest where I am wrong and what needs to be done for this.

dataset query:

IF @current_status = 'Opened'
SELECT DISTINCT cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(MAX(estcompletion_date) AS DATE) AS End_date
FROM  <Table Name>

ELSE IF @current_status = 'Closed'
SELECT      distinct  cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(getdate() AS DATE) AS End_date FROM <Table Name>

April 20th, 2015 5:45am

Hi All,

There are 2 parameters in my report current status and start date. I want to set the default value of the parameter based on the query result, when the current_status parameter is set as "opened" I want a certain date value to be displayed in the next parameter (start date), if it is set as closed I want a different date value to be displayed in the next parameter (start date), , so I created the dataset with the query given below and assigned the default value to the query fields start and end date, however my Report parameters are not changing as expected, kindly suggest where I am wrong and what needs to be done for this.

dataset query:

IF @current_status = 'Opened'
SELECT DISTINCT cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(MAX(estcompletion_date) AS DATE) AS End_date
FROM  <Table Name>

ELSE IF @current_status = 'Closed'
SELECT      distinct  cast(DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS date) AS Start_Date1, cast(getdate() AS DATE) AS End_date FROM <Table Name>

Free Windows Admin Tool Kit Click here and download it now
April 20th, 2015 6:17am

Hi Ramasubramanian S,

Per my understanding that you have one parameter current_status which values contain "Opened" and "Closed", another two parameters "StartDate","EndDate" which values display depend on the current_status parameter, right?

I have tested on my local environment and all the parameters works fine as expected, from the query you have provided that the parameter "StartDate1" will always display the same value "01/01/2015" and the "EndDate" will change depend on the parameter "current_status" as below. You issue can be caused by you haven't specify the default values for the Start_Date Parameter.

Details information about how to add the three parameters for your reference:

  1. Create an dataset2 which the parameter "Current_Status" to get the values from and you can also specify values for this parameter, remember to specify both the "Available values" and default values
  2. Create an dataset3 using the query you have provided and create the parameter "Start_date" and "EndDate" to get the values from and remember to specify both the "Available values" and default values.

If you still have any problem, please try to provide more details information about the parameter settings.

Regards,
Vicky Liu

If you have any feedback on our support, please click here.

April 20th, 2015 10:52pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics