URL access to SSRS with parameters

Passing a parameter to a SSRS report that is hosted in SharePoint 2010 can be done using the following URL (assuming that the report parameter is a string) :

http://bisite/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Reports/MyReport.rdl&rv:ParamMode=Hidden&rp:city=Perth

rv:RelativeReportUrl is path to the report
rv:ParamMode is the parameters panel setting
rp:city is the report’s parameter (Please note the prefix ‘rp‘)

For more information about other URL options, please refer to this link msdn

If the report is based on Analysis Service (SSAS), the parameter would be slightly different. Instead of just the value, the parameter needs to be in MDX format, e.g. [CityInfo].[City].&[Perth]

Using the example above, the URL to pass the SSAS parameter would be:

http://bisite/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Reports/MyReport.rdl&rv:ParamMode=Hidden&rp:city=%5BCityInfo%5D.%5BCity%5D.%26%5BPerth%5D

*Please note that the ampersand (&) should be replaced with %26

Lesson learned about PerformancePoint and SSRS

Today I have learned a few things about PerformancePoint web part, SSRS and report parameters. First thing first, the requirement was to build a SSRS report with SSAS as the data source and the report should be displayed on a Dashboard page.

I started with creating the report using ReportBuilder 3.0. I defined the data source and decided to use Query Designer to generate the MDX query. So I selected some fields and defined the parameters. In addition, I also let Query Designer to generate the parameters automatically which generates hidden datasets

Saved the report, created the performance point report using Dashboard Designer and deployed to SharePoint. I also added a query string url filter web part to read a querystring and pass it to my PerformancePoint webpart. Great, all done!!! But….when I opened the page, I had to wait 2-3 minutes just to see SSRS loading image 😦 .

Long story short, did some research and found some comments about removing the generated hidden datasets if I don’t need them. So I went to Report builder, removed the parameter datasets (which can be found in the Datasets folder, right click and select ‘Show hidden Datasets’) and saved the report. It did improve the rendering time of the report (by a lot!!!).

So lessons learned for today were:

  • Generated queries/parameters are great but need to use them with caution
  • Try to manually create the MDX query (if you can)

SQL : Get date part from datetime

I have been doing a bit of SQL stuff lately for my current project and did a lot of datetime conversion. I have always used the following query to get the date part of a datetime (which works just fine for me)

Select Convert(datetime, Convert(varchar(10), getdate(), 101))

I stumbled upon the following query which returns the same result and I find it quite interesting, that is no conversion required… 🙂

Select Dateadd(dd, 0, DateDiff(dd, 0, getdate()))

I have not seen any difference in terms of performance and it works the same with the first query. Thoughts anyone?

SSMS – problem when modifying an existing table

When making changes to an existing table using SSMS i.e adding a column or changing data type of a column, SSMS will create a temporary table and copy all the data from the original table to the temporary table. Imagine if the table has one million rows 🙂 . Another problem that could occur:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created…”.

The latter can be solved by going to Tools menu – Options – Designers – Table and Database Designer and un-check the check box button that says “Prevent making changes that require table re-creation”.

Another way to solve it (and also avoiding the creation of temp table) is by using Alter Table statement and run it in query editor.

ALTER TABLE <table name> ADD <column name>  int NULL;