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.