THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

SQL Server Reporting Services with Oracle Data

I have been spending a lot of time of late getting a new reporting solution in place for a customer whose data is in Oracle on Unix. I'm happy to report that all is going well, and the biggest issues I am running into involve the use of SQL*Plus Worksheet - enough to make anyone shutter. My workaround is to use Notepad as the editor and SQL*Plus Worksheet for execution only.

I digress, as I have something to report from the field.

Using the Generic Query Designer, I had made a change to the SQL and because of a typo, well, more of an oversight being that I thought all the inequality comparison operator was <> instead of the correct !=. Well, assuming I did the right thing, I saved the report and then attempted to run it. Well, wouldn't you know, but the report designer in VS removed all my parameters that I had set up. Upon correcting the SQL, it put the parameters back, but to their original values. Here are two of the formulas I had setup:

:FiscalYear = Year(Parameters!ReportDate.Value)
:FromDatePrev = Format(DateSerial(Year(Parameters!ReportDate.Value)-1,
                                  Month(Parameters!ReportDate.Value),
                                  1),
                       "yyyyMMdd")

These were converted to (without my knowledge):

:FiscalYear = Year(Parameters!FiscalYear.Value)
:FromDatePrev = Year(Parameters!FromDatePrev.Value)

So, my 6 nice formulas were hosed and I had saved the changes, and well, all that was left to do was to recreate them. And since I hadn't deployed the changes yet, the server still had my correct parameter formulas. That's when I went to Google and found an nice little piece of software written by Jasper Smith called "Reporting Services Scripter", which can be found at http://www.sqldbatips.com/showarticle.asp?ID=62. This app can script RDL files back out from the database server. I have been dabbling with some code of my own, but it wasn't ready for prime time. Jasper provided me a quick and useful solution. I ran the app, copied the <QueryParameters> section from the scripted RDL file, pasted into my local RDL file, and wham-o, I had my parameters back!

Now, about Oracle and parameters: You may have noticed the colon prefix for the parameter names. Using the Oracle provider, this is how you create parameters into your SQL. For example:

SELECT ...
FROM   ...
WHERE  POSTING_DATE 
         BETWEEN TO_DATE(:FromDatePrev, 'yyyymmdd') 
         AND TO_DATE(:ToDatePrev, 'yyyymmdd')

Then, as you saw above, I tie the report parameter (ReportDate) to the query parameters. It's really quite nice, except of course, when it clears out your parameter settings.

N.B. As some of you may know, I am a big advocate of using stored procedures. When these reports are ready for production, we will be using stored procedures, but for the report development phase, we are dynamic SQL until we can iron out the exact data we need.

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Julie said:

short and sweet.

March 26, 2007 2:47 PM
 

Grant H said:

Hi Peter - your article saved me from a whole lot of head-banging. I've been trying to create a report (with parameters) based on an Oracle data source and did not know to use : instead of @

Thank you very much...Grant

April 17, 2007 9:14 PM
 

Mitch said:

Cha-ching.  Nice post.  Colons?  I wouldn't have thought of that on my own.

May 31, 2007 11:47 AM
 

David said:

Not sure why you needed a 3rd party tool or code to script the RDL from the server for this restore purpose. Next time, just go to the reports folder, find the report and click Edit.

Or in Management Studio, connect to the Report Server, find your report, right click and click Edit Report...

September 26, 2007 2:19 PM
 

RK said:

THANK U SOO MUCH SAVED ME HEAPS OF TIME

June 30, 2008 1:51 AM
 

Peter W. DeBetta said:

I'm glad this post still serves a good purpose. :-)

June 30, 2008 3:18 AM
 

Andrew said:

I have been trying to figure it out for a year, and finally ran across this post. I can say goodbye to SSIS now !!!!! Thanks

August 11, 2008 6:05 PM
 

Huriya M. said:

Peter,

  We are also trying to implement reporting services against Oracle running on Sun Solaris.The two consulting firms we consulted told us that the Oracle Client that comes with reporting services cannot support large amount of data and concurrent users. They both recommended that we should move our data to SQL Server DB.

Can you please give me some information about your experience with this task?

Thanks

August 19, 2008 11:59 AM
 

ed said:

I'm late to this party, but you ended 2 days frustration with this issue.  Thanks!

March 23, 2011 3:49 PM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement