Solved

Replacing the Hard-Coded Date Field with an SSRS Parameter

Posted on 2011-03-24
9
402 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

786 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