Oh, the joys of Reporting Services… I think this is the product I'm mostly looking forward to seeing on November 7th….
Today I've been working on using a stored procedure in a report. I'm currently working on creating a standardized set of reports based on an OLAP cube, which is based on data from Microsoft Axapta. Some data comes from the SQL Server database too. A couple of days ago, my manager decided that adding the name of the company running the reports would be good. Branding, sort of. This name has to come from the SQL Server database, not from the OLAP cube.
Now, due to Axapta being Axapta, all the different tables are prefixed with the customer's name. So, to get the name of the company, you need to query like this:
select name from customer1.companyinfo.
And as there can be more than one dataset for Axapta, you also need to add what DataAreaID you want to use. So the full query is
select name from customer1.companyinfo where dataareaid like 'cu1'
This means 2 parameters, the DatabaseName and the DataAreaID.
Where do you want to set these two parameters?
I've had some success before, using dynamic SQL, so I decided to try creating a stored procedure with a bit of dynamic SQL. I know there are some severe issues with this, but I did it anyway. I wanted all the parameters to be set in the reports, and have as little as possible to do with the SQL server database. Just creating a stored procedure in an Axapta database is almost out of bounds… On the other hand, I had discovered that I couldn't just do a normal, non-parameterized text-based select in a report - that would mean I'd have to change this dataset in all the standardized reports when I wanted to deploy them in a new environment.
Creating the dynamic SQL was fairly easy. When I tested it in the Query Analyzer, it returned what I wanted. When I tested it in Report Designer, as Text instead of StoredProcedure in the data tab, it returned what I wanted. When I tested it as a StoredProcedure, it returned what I wanted - but the Fields didn't show up. Usually, when you do a query, and hit the ! to test it, the Field pane will be filled with the columns returned. And this is what you base you reports on. With my SP, I never got any fields as long as I was using dynamic SQL in a stored procedure. Oh, great.
It IS possible to add these fields, if you want to. But adding 4 fields manually to 9 reports? Somehow, I think using a normal text-based select statement would be a better idea… So, having the parameters in the report didn't seem like the best choice after all. So I decided that "What the heck". I need to add this StoredProcedure to the customer's database anyway, might as well just leave it as a non-parameterized basic stored procedure, and just remember to add the DatabaseName and the DataareaID in the query.
Tested it. The dataset returns the right data. The fields pop up in the field panel. Fairly easy to use too. Now I just need to remember to 1) add the stored procedure to other solutions using these reports and 2) change the parameters in the stored procedure.
And what did I learn today? Basically, don't expect fancy SQL queries to work with Reporting Services. Stored procedures that require parameters are sort of tricky. And it's probably better to put the parameters in a semi-hard-coded stored procedure than trying to send them from each of several reports.
Couple of resources about RS and Stored Procedures: