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

Advertisements

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)

SharePoint: Custom provider web part

Got a requirement to read a querystring and pass it to some of the web parts on a page. I thought that this would be easy since I can use the OOTB query string url filter web part to read and then pass the value after setting up the connection.

However, apparently some of the consumer web parts need the value to be in different format. My first thought was to append another querystring with the same value (just different format) but that would not be very nice (especially when the url length is restricted). This is where ITransformableFilterValues comes in handy. The interface is used for web part to web part connections.

Below are an example on how this interface can be implemented.


public class CustomQueryStringFilterWebPart : System.Web.UI.WebControls.WebParts.WebPart, ITransformableFilterValues
    {        
        string _queryStringKey = "category";
		string _filterValue = "";
		
        [WebPartStorage(Storage.Shared)]
        [WebBrowsable(true)]
        [WebDisplayName("Query String Key")]
        [Personalizable(PersonalizationScope.Shared)]
        [Category("Settings")]
        public string QueryStringKey
        {
            get { return _queryStringKey; }
            set { _queryStringKey = value; }
        }      

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);

            _filterValue = HttpContext.Current.Request.QueryString[_queryStringKey];
        }

        public bool AllowAllValue
        {
            get { return true; }
        }

        public bool AllowEmptyValue
        {
            get { return false; }
        }

        public bool AllowMultipleValues
        {
            get { return false; }
        }

        public string ParameterName
        {
            get { return "Query String Filter"; }
        }

        public System.Collections.ObjectModel.ReadOnlyCollection<string> ParameterValues
        {
            get
            {
                string value = string.Format("[{0}]", _filterValue.ToUpper());
                string[] values = new string[] { value };
                return new ReadOnlyCollection<string>(values);
            }
        }

        [ConnectionProvider("Query String Filter", "ITransformableFilterValues", AllowsMultipleConnections = true)]
        public ITransformableFilterValues SetConnectionInterface()
        {
            return this;
        }        
    }

The code above is a custom web part that implements ITransformableFilterValues. It gets the querystring value and convert it to uppercase.

Add the above web part to a page and to pass the transformed value, connections to the consumer web parts need to be created. Once the connection is setup, everything should just work 🙂

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?