INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SSRS - Report Builder 3.0 - Somewhat Dynamic Parameters (Date Options)

SSRS - Report Builder 3.0 - Somewhat Dynamic Parameters (Date Options)

(OP)
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.

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

CODE

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....

And then I saw the discussion here:
hread1462-1749103

I think what I'm after is the same, but the suggestion of a stored procedure won't work on the server where I'm placing this, as I won't have permissions to create procedures. However, if I proved the worth, I could get those with permissions to add it I'm sure.

Thanks for any thoughts.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close