Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS - Date Parameter - Is it possible to have a multi select date range?

Posted on 2014-01-04
9
Medium Priority
?
3,249 Views
Last Modified: 2014-01-06
Hi Experts, I have a problem creating a parameter for a date range which now needs to be a changed to a multi select parameter.
I am using Report Builder 2008 R2. I am using the Filter tab in the report Builder.

I have a date field which indicates how overdue the payments are.

I need to have 4 date ranges between (today() and -30days), (-30days and -60days), (-60days and -90 days) and lastly (more than -90 days). I have this part working now but I now have to change this so that users can select a combination of the 4 date ranges. Is this possible??

If so how would I do this?
0
Comment
Question by:Papa1NZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 39757047
So, I think I understand what you want.

you want a drop down of the 4 values showing the values "4/1/2014 - 3/12/2013", "3/12/2013 - 2/11/2013", "2/11/2013 - 1/10/2013", "older than 1/10/2013"?

you say the user can select a combination of the 4 date ranges.
I'd day this may be hard to do.

Anyway,

I think you can go this way...

For the dataset for the parameter I think you should do this.

Select Convert(Date,Getdate()) as StartDate,  
Convert(Date,DATEADD(D,-30,GetDate())) AS EndDate,
CONVERT(VARCHAR(12),Convert(Date,Getdate())) + ' - ' + CONVERT(Varchar(12),Convert(Date,DATEADD(D,-30,GetDate()))) AS Label

UNION

Select Convert(Date,DATEADD(D,-30,GetDate())) as StartDate,  
Convert(Date,DATEADD(D,-60,GetDate())) AS EndDate,
CONVERT(VARCHAR(12),Convert(Date,DATEADD(D,-30,GetDate()))) + ' - ' + CONVERT(Varchar(12),Convert(Date,DATEADD(D,-60,GetDate()))) As Label

UNION

Select Convert(Date,DATEADD(D,-60,GetDate())) as StartDate,  
Convert(Date,DATEADD(D,-90,GetDate())) AS EndDate,
CONVERT(VARCHAR(12),Convert(Date,DATEADD(D,-60,GetDate()))) + ' - ' + CONVERT(Varchar(12),Convert(Date,DATEADD(D,-90,GetDate()))) As Label

UNION

Select Convert(Date,DATEADD(D,-90,GetDate())) as StartDate,  
'1900-01-01' AS EndDate,
CONVERT(VARCHAR(12),Convert(Date,DATEADD(D,-90,GetDate()))) + ' - ' + '1900-01-01' As Label

Order by Startdate desc

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39757561
Looks like the above answer is correct as far as populating the parameter list.

SQL expert ValentinoV wrote an article on SSRS Passing multi-value parameters that I'm about to do a deep dive into.  Good luck.
0
 

Author Comment

by:Papa1NZ
ID: 39758223
Hi Tony / Jim.

Thank you for your responses. I should have mentioned this earlier but the application driver I am using to access the database does not allow me to use stored procedures and it has limited query syntax for the dataset. So I have to try and find a work around by using SSRS report features.

I would need to do this using the expression in the filter/parameter of the report builder / BIDS. Or/And use the 'Code' section. Is this possible?

What I want to do is have 4 labels.
(1) 'Overdue<30 days', (2) '-30days and -60days', (3) '-60days and -90 days',  (4)'>90 days'.

What I did previously (Note: I didn't need the Overdue<30 in the earlier version) is create a Parameter which would create the end date required for the date range. When the users selected the label with e.g. '-30days and -60days' the system date minus 60 days would be passed as a parameter.
Parameter Label Expression:
=DateAdd("d",-30,Today())
=DateAdd("d",-60,Today())
=DateAdd("d",-90,Today())

With the date filter I used end date as the passed Parameter. For the start date I used the following expression:
=IIf(Parameters!OverDueDate.Value=DateAdd("d",-60,Today()),
DateAdd("d",-90,Today()),
IIf( Parameters!OverDueDate.Value=DateAdd("d",-90,Today()),
DateAdd("d",-100000,Today()), DateAdd("d",-60,Today()) )    )

This worked fine. However it now needs to be a multi select which will not work with the way I have created this filter.

Can you tell me other ways I might be able to create a multi-select range of dates?

Many thanks
Parameter-list.PNG
2FilterTab.PNG
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 12

Accepted Solution

by:
Tony303 earned 2000 total points
ID: 39758357
Hey Papa,

Are you able to actually add a column to the underlying query? Please say yes!!

In the past I have added a column called Age lets say and then populated each line with 0 for Current, 1 for 30Days, 2 for 60Days, 3 for 90 days and 4 for 90+.

This then makes the selection the user chooses really easy.
They want Current and 90+, you have in your filter Age in (1,4).

T
0
 

Author Comment

by:Papa1NZ
ID: 39758423
Hi Tony,

Thanks I am now using 1, 2, 3, 4 instead of the dates. This works but is it possible do a multi-select version of this? My idea now is to find the Min value out of (1,2,3,4) and use that as the start date period and the Max value to use the end date for the 'between' date filter.  I am just trying to do this now but I am unsure if this will be accepted as a work around?
Do you think it is possible to get a multi-select date range? E.g. Period '0-30', and '>-90' when a user selects 1 and 4 missing 2,3?

Currently using single selection I use the following in the DataSet Filter.

Start Date
=IIf(Parameters!OverDueDate.Value=1, DateAdd("d",-30,Today()), IIf( Parameters!OverDueDate.Value=2, DateAdd("d",-60,Today()), IIf( Parameters!OverDueDate.Value=3, DateAdd("d",-90,Today()), DateAdd("d",-1,"01/01/1900"))))

End Date
=IIf(Parameters!OverDueDate.Value=1, Today(), IIf( Parameters!OverDueDate.Value=2, DateAdd("d",-30,Today()), IIf( Parameters!OverDueDate.Value=3, DateAdd("d",-60,Today()), DateAdd("d",-90,Today()))))

This works fine for single selection but how would I change this to now use multi-selection?

Many thanks
values-Parameter-Instead-Of-Date.PNG
0
 

Author Comment

by:Papa1NZ
ID: 39760857
Hi Tony,
Sorry I have just clicked about what you said above. So I should create a column in the query? Would I use Case in the select statement? Or do you mean adding the column somewhere else?
0
 

Author Comment

by:Papa1NZ
ID: 39760960
Tony just set it up. It was simple in the end like you said. I just created a calculated column and used that in my multi-selection list.
Thanks.
0
 

Author Closing Comment

by:Papa1NZ
ID: 39760963
This is a good solution. Creating a calculated field instead where you use a switch command and DateDiff and assigning the string to the appropriate ranges.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39761156
Hi Papa,

Sorry I haven't got back to you again. For some reason the pre Christmas work rush hasn't abated...

Anyway, I am pleased you are now fully on track.

Thank you for your points, I really appreciate it.

Cheers now.

T
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 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