Productive reports are always welcome. Management always appreciates you to build up a good informative report. In SharePoint, you can found a large area where organization can play with their business data and transformed the raw data to a informative and intelligent report.

From the ancient stage, we are following the process to transformed business data to generate report. Latter stage we have got many tools like SQL DTS and then we have got another feature, cubical concept. There are many product available in the market for generate business intelligent report and one of them from SQL Server Analysis Service suite from Microsoft.

In Microsoft SQL Analysis service, you can build up cube by executing the raw SQL and you can buildup cube. Next phase is to build up report using VS.NET report server project template and using those cube and build up the .rdl compiled file. I’m not going to drill down that much of detailed describing how to build up the .rdl file, instead of, I’m assuming those files already has been deployed in the SharePoint Document Library and we just need to configure properly those files into SharePoint.

How to configure SQL Server Reporting Services report Reviewer

To configure this functionality in SharePoint first thing is, there should be compiled SQL report (with .rdl extension) in any of the document library in the SharePoint environment.

Go to any SharePoint page where web part zone exist or better to say, you have the option to add OTB web part inside the page. Click on Add Web Part link and select the SQL Server Reporting Services report Reviewer from the Miscellaneous section.

SQL Server Reporting Services report Reviewer Web part

Once added, you can check the Report Viewer web part is added into the system. Click on Modify web part link and select the .RDL file under Report section. Below the .rdl selection text box and under Hyperlink Target, you can specify the window target location (i.e. using _top, _blank etc..). Under this, there is view configuration. You can specify there whether the report tool bar, Document Map, Document Map area width etc.
Now click on apply.

SQL Server Reporting Services report Reviewer

Now, probably you will get the following error message and no report is displaying inside the web part.
Error: The Report is missing a parameter value but prompting for it has been disabled.

This is because the report parameter is not configured properly. Click on Parameter drop down arrow and click on Load Parameter button. The report parameter will be displayed on the drop down control. Click on option ‘Override Report Default’ and select the correct parameter.

SQL Server Reporting Services report Reviewer Configuration

Next the option is required to configure is the report width and height under appearance section. Under the appearance, you need to specify the report width and height if you know about it. Otherwise select the adjust height/width to fit zone.

SSRS Report Height Width Setup

Few things need to clarify here. Report width you can control, so you can overcome the horizontal bar. Most of the cases this need to be controlled from the generation of the report i.e. VS.NET reporting services. Changing the height and width inside this web part will take a lot of time to fix the horizontal bar issue. Report vertical bar is not something you can control totally. This is also depends on how many records are in the CUBE/table.

We are yet to be finished. To implement the viewer condition i.e. only specific people can view this report, there is target audience feature available in this report viewer web part. Click on Target Audience at the bottom of the configuration settings and select the appropriate groups.

SSRS Target Audience

It’s done. Click Apply and Ok. Now, the report will be displayed on the associated SharePoint page. Here it goes…

SSRS