dynamic date range label with hidden cascading parameters

Hi,

We've got a report, inside there is a parameter @myParameter, and two hidden cascading parameters @startdate and @enddate are from the following queries. I don't have permission to create tables, so chose the approach of hidding parameters.

SELECT CAST(LEFT(@myParameter, 10) as DATE) as StartDate, CAST(RIGHT(@myParameter, 10) as DATE) as EndDate

The problem is,  the billing cycle parameter @myParameter should be dynamic(our cycle is between 21th to next month 20th), for example, today is 3/28/2012, so the last drop-down will be 3/21/2012-4/20/2012, follows up with 20 drop-downs. (2/21/2012-3/20/2012.....12/21/2012-1/20/2012........)

If when people render report, the date is 3/2/2012, the last drop-down will be 2/21/2012-3/20/2012. 

I got the first one ready shown as the expression below , but the dropdown should show 20 dropdowns following the lastest, I had some problems for dynamically render the following 20 drop-downs.

=iif(day(Today)<21, format(DateAdd("m",-1,Today),"MM")&"/21/2012-"&Format(Today,"MM")&"/20/2012",Format(Today,"MM")&"/21/2012-"&format(DateAdd("m",1,Today),"MM")&"/20/2012")

 

I've posted this before on msdn forum, but so far didn't get update. Hope I can get some help from here.

I'm having a hard time following you. Are you trying to load 20 values into a dropdown or actually create "20 dropdowns" as your post states.

I read that you don't have create table permissions, but personally, I would try declaring a table variable, load it up with [date range] for the label, [start date] and [end date] values. That is if you know ahead of time what the dates are in the range. Which it looks like it is always the 21st of the month to the 20th of the following month. Then I would set the parameter to get the values from the dataset containing the table variable.

I hope this helps,

Brian K. McDonald, SQLBIGeek

Hi Brian,

Sorry for the confusion, I need to create 20 drop-downs. Your suggestion is really good, but with no permission to create tables, I need to create 20 drop-down dynamically.

For example, today is 4/1/2012, when report is rendered, first drop-down should be 3/21/2012-4/20/2012, then 2/21/2012-3/20/2012, 1/21/2012-2/20/2012, 12/21/2011-1/20/2012....

I created ssrs expression get the first parameter, which is 3/21/2012-4/20/2012, but following that, had some hard time.  For example, the expression should capture when Dec comes, the year also change to 2011 instead of  2012.

Would this work? I only created 6 entries in the dropdown but copy/paste/modify for the other 14

Create a connection to a Microsoft SQL Server database (dummy needed for dataset)

Create a dataset with the following query and base the parameter on the this:

SELECT     1 AS DateSortOrder, RIGHT('00' + CAST(month(DateAdd([m], - 1, GETDATE())) AS varchar(2)), 2) + '/21/' + CAST(YEAR(DateAdd([m], 
                      - 1, GETDATE())) AS varchar(4)) + '-' + RIGHT('00' + CAST(month(GETDATE()) AS varchar(2)), 2) + '/20/' + CAST(YEAR(DateAdd([m], - 1, GETDATE())) AS varchar(4)) 
                       AS BetweenDates
UNION
SELECT     2 AS DateSortOrder, RIGHT('00' + CAST(month(DateAdd([m], - 2, GETDATE())) AS varchar(2)), 2) + '/21/' + CAST(YEAR(DateAdd([m], 
                      - 2, GETDATE())) AS varchar(4)) + '-' + RIGHT('00' + CAST(month(DateAdd([m], - 1, GETDATE())) AS varchar(2)), 2) + '/20/' + CAST(YEAR(DateAdd([m], - 1, GETDATE())) 
                      AS varchar(4)) AS BetweenDates        
UNION
SELECT     3 AS DateSortOrder, RIGHT('00' + CAST(month(DateAdd([m], - 3, GETDATE())) AS varchar(2)), 2) + '/21/' + CAST(YEAR(DateAdd([m], 
                      - 3, GETDATE())) AS varchar(4)) + '-' + RIGHT('00' + CAST(month(DateAdd([m], - 2, GETDATE())) AS varchar(2)), 2) + '/20/' + CAST(YEAR(DateAdd([m], - 2, GETDATE())) 
                      AS varchar(4)) AS BetweenDates       
UNION
SELECT     4 AS DateSortOrder, RIGHT('00' + CAST(month(DateAdd([m], - 4, GETDATE())) AS varchar(2)), 2) + '/21/' + CAST(YEAR(DateAdd([m], 
                      - 4, GETDATE())) AS varchar(4)) + '-' + RIGHT('00' + CAST(month(DateAdd([m], - 3, GETDATE())) AS varchar(2)), 2) + '/20/' + CAST(YEAR(DateAdd([m], - 3, GETDATE())) 
                      AS varchar(4)) AS BetweenDates       
UNION
SELECT     5 AS DateSortOrder, RIGHT('00' + CAST(month(DateAdd([m], - 5, GETDATE())) AS varchar(2)), 2) + '/21/' + CAST(YEAR(DateAdd([m], 
                      - 5, GETDATE())) AS varchar(4)) + '-' + RIGHT('00' + CAST(month(DateAdd([m], - 4, GETDATE())) AS varchar(2)), 2) + '/20/' + CAST(YEAR(DateAdd([m], - 4, GETDATE())) 
                      AS varchar(4)) AS BetweenDates       
UNION
SELECT     6 AS DateSortOrder, RIGHT('00' + CAST(month(DateAdd([m], - 6, GETDATE())) AS varchar(2)), 2) + '/21/' + CAST(YEAR(DateAdd([m], 
                      - 6, GETDATE())) AS varchar(4)) + '-' + RIGHT('00' + CAST(month(DateAdd([m], - 5, GETDATE())) AS varchar(2)), 2) + '/20/' + CAST(YEAR(DateAdd([m], - 5, GETDATE())) 
                      AS varchar(4)) AS BetweenDates  

You can create a CASE statement to manage the dates before and after 21st of the month if you need to
  
DateSortOrder    BetweenDates
1                       03/21/2012-04/20/2012
2                       02/21/2012-03/20/2012
3                       01/21/2012-02/20/2012
4                       12/21/2011-01/20/2012
5                       11/21/2011-12/20/2011
6                       10/21/2011-11/20/2011

You can also parse the values from the dropdown then you would not need the hidden parameters?

Start Date: =cdate(left(Parameters!ReportParameter1.Value,10))

End Date: =cdate(right(Parameters!ReportParameter1.Value,10))

 

 

Thank Syale, it worked.

I see that my suggestion would have worked as well. Table variables are not tables in the essence that you are describing. What I mentioned was a TABLE VARIABLE, not a physical table. Just for future clarification though, you arenot creating 20 dropdowns. You are creating 1 dropdown (parameter) with 20 options for that dropdown. I am glad you got it working.

Thank you Brian for the suggestion.