Wednesday, December 16, 2009

How to upload a rdl-file for SQL Server Reporting Services?

Got this question from a regular visitor of my blog. He found a rdl-file containing an interesting Report for OpsMgr. His question is how to get this file into SQL Server Reporting Services (SSRS).

Remark:
Be aware that a direct import into SSRS introduces some risks:
1. Report upgrade will be difficult manual task.
2. You can not quickly move such a report from a test environment to production.
3. You'll be forced to restore the ReportServer DB in case of recovering the OpsMgr Management Group from a catastrophic failure.

Got this from Alexey Zhuravlev. Thanks Alexey!

Before I show the procedure, lets take a closer look at the rdl-file itself. A bit more knowledge on that topic won’t hurt at all. This is what differentiate us from a robot. Yes, they can built cars or whatever. But they haven’t got a clue of WHAT/WHY they are doing something.

What does RDL mean?
RDL stands for Report Definition Language which is a XML Schema which describes the report. Like a Management Pack which is also XML based. MPs follow a certain XML Schema required by OpsMgr. The same goes for RDL files which follows a specific XML schema required by SSRS.

How are RDL files created?
Not manually but by using graphical tools like Report Designer, Report Builder or Visual Studio.

So I can open a RDL File in Internet Explorer?
Yes, you can. And certainly do so. There you will see the contents of a rdl-file. However, do not alter anything of it, unless you know exactly what you are doing. But taking a closer look at it is a good thing to do.

Why Internet Explorer and not Notepad? Well, IE shows the file in a more structured way compared to Notepad. Also is the file less easier to modify then opening it in Notepad. So less change to wreck it. Lets take a closer look at a RDL file and the most important components:
image

Schema Definition
The first line defines the XML Schema to be followed:
image `

Datasources
Defines the source(s) of information to be accessed by the report.

Report Parameters
Defines the parameters of the report itself, like what fields must be filled in before the report can be run. The more parameters, the more customizable a report becomes.

Datasets
Where as DataSources define where the report must get its data from, the Datasets define what data to use from the defined Datasources. Also the needed queries will be found under this header:
image 

Body
Defines the main layout of the report itself. Not what data is to be displayed, but how.

Pagefooter
As the name states. This header defines what information will be shown in the footer of the report.

Let’s move on to importing a rdl-file into SSRS. The attached file from this blog posting is being used.

  1. Open IE on the SQL Server and type: http://localhost/Reports. Since the rdl file to be uploaded depends on the Microsoft Generic Report Library, you need to open that folder (Microsoft.SystemCenter.DataWarehouse.Report.Library):
    image

  2. Click Upload File, Browse and select the rdl-file. Click Open > OK. The file will be uploaded tot SSRS.
     image

  3. When you’re back in the screen as described in step 1, select the Detail View. Select the properties (click on the yellow highlighted hand symbol) for this new report:
    image
    Go to the third option,Data Sources
    image
    A warning will be shown. Click Browse > select the symbol Data Warehouse Main. Its name will be filled into the Location box.
    image
    Click OK. You’re back in the Data Sources screen. This must be shown:
     image
    Click Apply.

  4. Report is uploaded successfully:
     image
    Close SSRS. 

  5. Open the OpsMgr Console > Reporting > Microsoft Generic Report Library:
    image
    The new report will be shown. Double click it and run  it.

Additional Advise:
Normally step 3 is not needed. For this particular file however it was.

2 comments:

John Bradshaw said...

Worked a charm Marnix. Don't know how u do it, but thankyou once again!!
John Bradshaw

John Bradshaw said...

Hi Marnix,
Worked great!
Don't know how u do it, but pls keep up the excellent work!!
John Bradshaw