Solved

Replacing the Hard-Coded Date Field with an SSRS Parameter

Posted on 2011-03-24
9
396 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 URL 11 47
SQL Encryption question 2 42
Parsing the XML data to SQL Server 4 51
Pivot Query Problem 9 31
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now