A cascading text parameter that allows you to select a DateTime Calendar or prepopulate field

Question

Well, this is fun and part of what I have done so far until I read that some people claim that this is impossible to do because of the default behavior of SSRS handling of default values. However, I got pretty far, and I'm curious if I can bring it all to the end.

Problem. I want the user to get PRE DEFINED between statements like "This Month", "LastMonth", etc ... Then he populates the DateTime variable for this string link, but the user can change it later, and he needs to update based on the parent line or let the user select another SINGLE DATETIME PARAMETER parameter.

To start working, that will work and will not:

I am. I bind the data set to this short form:

select 'Today' as Description , DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) BegDt union select 'ThisMonth' , Dateadd(month, Datediff(month, 0, getdate()), 0) 

II. Then I set the “DatesPeriod” parameter, and then add the “Description” as the field and label for the parameter when I select “get values ​​from the query”. The user can now select Today or ThisMonth from the drop-down list.

III. Now I have set up another dataset and call it "DatesSelected". For the purpose of explanation, I will keep it simple and just nest the data set from part I, but list the where clause. Essentially, I'm using the same dataset again, but forcing me to return one row at a time.

 select * from ( select 'Today' as Description , DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) BegDt union select 'ThisMonth' , Dateadd(month, Datediff(month, 0, getdate()), 0) ) as x where x.Description = @DatesPeriod 

IV. I define another “Start” parameter, which is then a DateTime and gets its values ​​from the dataset above, as well as its default values. Now, when the user changes the first text variable, the dependent variable DateTime will also change. I linked the text selection essentially to a datetime variable, which is now limited in scope to only the available value and the default value for the description. This limits the scope to unity, forcing to transmit by default.

V. This is not enough, but I want the user to be able to choose FREE DATA DATES at his discretion. The current setting DOES NOT allow this button to select the date to be used, since the scope is limited by the available values. However, this must be done in order to pass the text to the datetime variable, which will update ON DEMAND (when it will be changed at the time the report is run, and not just the first time). So I create another DateTime parameter and call it “UserStart” and should default to “[@Start]” to get its default value as a dependency on “Start”.

VI. Now I am creating a test data set called "Selection" to visually see that my data is presented in the same way as I am.

  Select isnull(@UserStart, @Start) as Choice 

VII. I create a table object and put it in my report and put it in the single cell above to see that when the user overrides the default “Start” value of his choice (from the calendar display offered by SSRS) that he will display.

So, everything works with the coven at this moment. You need to display the BOTH datetime data so that the end user can choose either the default set or choose his own. If I try to hide the second “Start” parameter, it will set the default value only at the FIRST TIME when the report is executed, I want it every time the first parameter changes. If it is visible, it works fine, but for functionality, I would not want the user to see two report parameters that, as far as they are relevant, are the same. I tried my own code to return the Start parameter, creating another dataset, but as far as I know, SSRS is created so as not to be flexible with the default update or ANY DATE you want to select. If you want to update a parameter and do it on the screen with a different variable, you need to tell it the available values ​​and the default values ​​are changed. However, this eliminates the possibility of a calendar, so you need to pass the parameter to another parameter. However, if you hide the second parameter, then it will not pass the value.

Any help would be greatly appreciated.

Answer 1

According to Microsoft Connect, the ability to update default settings "by design"

“As described, this is not an error. We do not overestimate the default value for the subsequent parameter, unless the selected value is no longer in the list of valid values. We do not know whether the user has specifically requested the current value or if it exists due to the default. You can make a case to have control over this behavior through some property, but it currently works as designed. " - Microsoft

Proposed Solution:

Playing with this for a while, the only solution that I see is to change how you intend to use the report.

Instead of displaying the date calculated by the request, UserStart uses it as an override date and allows it to accept null values ​​and set null to the default value. Then in the query use something like:

 where Table.DateStart < isnull(@UserStart, @Start) 

This allows users to select a DatePeriod to run a report or update with their own date range.

Example:

Answer 2

After much research, the above provided the final key to the solution. There are several steps, but this allows the user to select a predefined date range or enter their own dates.

• Add the text parameter "DateRange". Set AvailableValues ​​as:

Choose dates: Choose

This year: ThisYear

Last Year: LastYear

All: All

Set default value to None

2. Create the "GetDateRangeFromDesc" stored procedure as follows:

CREATE A PROCEDURE [dbo]. [GetDateRangeFromDesc]

@Range varchar (50)

As

TO BEGIN

SET NOCOUNT ON;

 SELECT CASE WHEN @Range = 'Choose' THEN NULL WHEN @Range = 'ThisYear' THEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) WHEN @Range = 'LastYear' THEN DATEADD(yy, DATEDIFF(yy,0,getdate()) - 1, 0) WHEN @Range = 'All' THEN Convert(DATE, '1/1/1900') END AS Date1, CASE WHEN @Range = 'Choose' THEN NULL WHEN @Range = 'ThisYear' THEN DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, 0) WHEN @Range = 'LastYear' THEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) WHEN @Range = 'All' THEN Convert(DATE, '12/31/2099') END AS Date2 END GO 

3. Add dataset "DateRangeValues"

Select the "GetDateRangeFRomDesc" stored procedure

Fields must be Date1 and Date2

Parameters must be @Range: [@DateRange]

4. Add Date / Time parameter "StartDate". Set AvailableValues ​​and DefaultValues ​​as:

Get values ​​from the query. Dataset: DateRangeValues; Value / Label Field: Date1

5. Add Date / Time parameter "EndDate". Set AvailableValues ​​and DefaultValues ​​as:

Get values ​​from the query. Dataset: DateRangeValues; Value / Label Field: Date2

When you run the report, the start and end dates will be populated as defined in the stored procedure. Add as many date ranges as you need for sp. You have a choice that returns NULL so that the user can enter their own values.

** Sorry, but I sent it before a full test. When you select "Select", enter the date, everything will be updated and the date will go away. :) Maybe someone can add the last part that will allow this to work.

Another update: this one works when the DateRange parameter after the last parameter causes an update!