Solved

Replacing the Hard-Coded Date Field with an SSRS Parameter

Posted on 2011-03-24
9
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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