Solved

Replacing the Hard-Coded Date Field with an SSRS Parameter

Posted on 2011-03-24
9
412 Views
Last Modified: 2012-05-11
I have a very lengthy query that I have been testing in SQL 2005 Management Studio.  Now that I have finished testing it, I was going to place this query into the "Data" tab of SQL Reporting Services (SSRS).  But, as I just found, the "Data" tab has a character limitation.  So, not all of my query gets pasted in.

OK.  I'm going to have to take this query, then, and make it into a view in SQL and pull that view into SSRS.  I have done that before many times and need no help with that.

Here's the problem, though.  In about 400 spots throughout the query, I have a hard-coded date that I have been testing my query with.  My intent with all of this is to create a Date parameter in SQL Reporting Services that will prompt the user with a date to run the report on.  I need for that "Run Date" to replacte the hard-coded date in my query.

That's why I simply wanted to copy and paste the query into the "Data" tab, so that I could do a simple "find and replace" and replace this hard-coded date with the SQL parameter.  But, siince there is this really wonderful character limitation, I cannot do so.

I don't know that I can create a view, either, though.  I mean, if I do that, I guess that's fine.  But, how in the world do I tell SQL Reporting Services to replace the hard-coded date in the query (i.e. the view) with the Run Date parameter?
0
Comment
Question by:apitech
  • 5
  • 4
9 Comments
 
LVL 4

Expert Comment

by:RGBDart
ID: 35207324
Try to use Stored Procedure instead of view. Something like

create proc dbo.ReportData
	@TargetDate date
as
begin
	--your huge select statement
end

Open in new window


Replace your hardcoded date with @TargetDate
Use this procedure as data source in SSRS
0
 
LVL 1

Author Comment

by:apitech
ID: 35207847
I created this proc.  Now, in SSRS BIDS, I get an error message during the Report Wizard saying "invalid object name" when I do a "select * from [the name of my proc]".

Help!
0
 
LVL 4

Expert Comment

by:RGBDart
ID: 35207893
Try to change the command type of the query from Text to Stored Procedure.

You don't need to write "select * from [the name of my proc]", you'll need just put the name of procedure in SSRS.

0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 1

Author Comment

by:apitech
ID: 35208008
After cancelling out of the wizard, I decided to manually create the report using the Dataset "feature" or whatever on the Data tab.

Attached is a screenshot.  Did I do this right?  When I click the "Fields" tab, I do not see my fields.

I'm a real novice at this.  I don't usually work with stored procs.
Help.docx
0
 
LVL 4

Expert Comment

by:RGBDart
ID: 35208087
hm, that looks fine =(

try to open Query Analyser and run query

exec dbo.TRXHATB GETDATE()

Open in new window


does it returns any data? (you may replace GETDATA() with any datetime value, according to your query logic)
0
 
LVL 4

Accepted Solution

by:
RGBDart earned 500 total points
ID: 35208272
one more hint.
Try to remove "dbo." prefix in that window in your screenshot.

If it not helped - try to read this article  from the title "Add a new report" - there is a step-by-step tutorial for creating report with stored proc.
0
 
LVL 1

Author Comment

by:apitech
ID: 35208295
Indeed, I do get data.  So, the stored proc works.

By the way, I did create the dataset successfully after eliminating the space in the name of it.  And, I was able to hit the red exclamation point in the "Data" tab and pull my data in!  Yay!

So, I presume that the next time that I open or run this report, the fields and the data will "be there"?

Thanks, for your excellent assistance by the way!!!!!!!!!
0
 
LVL 1

Author Closing Comment

by:apitech
ID: 35209608
RGBDart is awesome!!!!  He's one of the best experts on this site!
0
 
LVL 4

Expert Comment

by:RGBDart
ID: 35213075
Thank you very much for your approval =)
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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