SQL Server Reporting Services - Report Builder 3.0 - Somewhat Dynamic Parameters (Date Options)

[kjv1611]

I have this thought of a better method of reporting parameters, and I already thought through how to set them up in a table, and some thought about how to use in Excel for that type of report. For Reporting Services, using Report Builder 3.0, I'm a little more unsure of how to make it work.

So here's what I would like to setup. A table (Report Builder DataSet) something like this:

Option FromDate ToDate
LastMonth 5/1/2015 5/31/2015
MTD 5/1/2015 6/9/2015
Previous90 3/1/2015 5/31/2015
Custom 5/1/2015 5/31/2015


My idea is to set it up to where the user selects the first option, so they can oftentimes use a one-click change to select different time periods, but then if they want more custom options, they update the From/To dates. But what I'd like to do is say: If user selects anything other than "Custom", run the report upon selection change, otherwise, they have to click the "Run" button in Reporting Services.

Any thoughts? Is this a far fetched idea? It's not anything required, but just an idea I think that would go over really well if I am able to implement it.

Thanks for any thoughts.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Here's the SQL Code I've setup for now for an initial stab at the table that would drive these values:

DECLARE	@Options AS Table (
		OptID int IDENTITY(1,1)
	,	Descrip varchar(50)
	,	StartDate datetime
	,	EndDate datetime
	,	MenuOrder int
)

INSERT INTO @Options 
	SELECT	  'Last Month'
			, DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-1, 0)
			, DATEADD(MS,0-3,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))
			, 1

INSERT INTO @Options 
	SELECT	  'Last 90 Days'
			, DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-4, 0)
			, DATEADD(MS,0-3,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))
			, 2

INSERT INTO @Options 
	SELECT	  'Current Month'
			, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)
			, DATEADD(MS,-3,DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
			, 3

INSERT INTO @Options 
	SELECT	  'This Week'
			, DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0)
			, DATEADD(MS,-3,DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
			, 4

INSERT INTO @Options 
	SELECT	  'Yesterday'
			, DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0)
			, DATEADD(MS,-3,DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
			, 5

INSERT INTO @Options 
	SELECT	  'Custom'
			, NULL
			, NULL
			, 6
			
SELECT * FROM @Options order by MenuOrder

And here's one reference that sounds like it may be what I'm after:

https://msdn.microsoft.com/en-us/library/dd255197.aspx

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[philhege]

Have you posted this question to the Reporting Services forum?

-----------
With business clients like mine, you'd be better off herding cats.