Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Replacing the Hard-Coded Date Field with an SSRS Parameter

Posted on 2011-03-24
9
Medium Priority
?
454 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 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