Performance Point Report Web Part & Unterminated String Constant

Recently I had this weird javascript error when I added performance point report web parts to a page that uses a custom master page. The error on the page was ‘Unterminated string constant’ and I could not see any of the report web parts. After a bit of google I found this post by Martin Hatch which described exactly the same issue. Apparently the issue was because separate lines in the element.

What I had was

<title id="onetidTitle">
<asp:ContentPlaceHolder ID="PlaceHolderPageTitle" runat="server" />
</title>

And the fix :

<title id="onetidTitle"><asp:ContentPlaceHolder ID="PlaceHolderPageTitle" runat="server" /></title>

Using ListData service in SharePoint 2010

For the past few weeks, I have been using ListData.svc, SharePoint Client object model and Jquery to create custom web parts which display some information from a list. First things first, ListData service reside in http:///_vti_bin/ListData.svc. So as an example, Let’s say that I have an employee list with First Name and Last Name columns. In order for me to get the list of employees from Employee list, the url would be http://samplesite/_vti_bin/ListData.svc/Employee.


var listUrl = 'http://samplesite/_vti_bin/ListData.svc/Employee';
$.getJSON(listUrl, function(data) {
   for (var i=0; i<data.d.results.length; i++)
   {
       var employee = data.d.results[i];
       alert(employee.FirstName);
   }
});

We can also filter the results that we would like to retrieve from the list by using the $filter query string or sort the results by using the $orderby. The example below filters the list by using the list item Id. For more information on what query strings that can be used, please have a look at this page in MSDN.

var listUrl = 'http://samplesite/_vti_bin/ListData.svc/Employee?$filter=Id eq 15';
$.getJSON(listUrl, function(data) {
   for (var i=0; i<data.d.results.length; i++)
   {       
   }
});

We can also use ListData service to create a new list item or update an existing item. Credits go to Anatoly Mironov for explaining on how to perform save and update operations. Following our example above, let’s say that we want to create an entry for a new employee

var newEmp = {};
newEmp.FirstName = "Joe";
newEmp.LastName = "Blogg";

var body = Sys.Serialization.JavaScriptSerializer.serialize(newEmp);

$.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            processData: false,
            url: listUrl,
            data: body,
            dataType: "json",
            success: function () { alert('succeeded');},
            error: function (xhr, status, error) { alert(xhr.responseText); }
        });
    }

Assuming that the create operation above succeeded and the new employee’s id is 20. We can update the employee list item as such

var emp = {};
emp.FirstName = "Joe";
emp.LastName = "B";

var body = Sys.Serialization.JavaScriptSerializer.serialize(emp);
$.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            processData: false,
            headers: {
                "If-Match": "*",
                "X-HTTP-Method": "MERGE"
            },
            url: listUrl + "(20)",
            data: body,
            dataType: "json",
            success: function () { alert('succeeded');},
            error: function (xhr, status, error) { alert(xhr.responseText); }
        });

Notice the url and the headers for the AJAX call. The url that I specified points to the record that I just created and then I specified ‘MERGE’ to indicate that this is an update operation.

Retrieving SP user info using Client Object Model

Recently I built a visual web part to show current user’s information (Picture, Name, etc). Instead of using SharePoint server object model, this time I choose to use client OM. First I need to get the id of the logged-in user, which can be retrieved from _spPageContextInfo.userId (Ted Pattison has posted an excellent article explaining this variable). Next step is to get the client context.

var context = new SP.ClientContext.get_current();
    var web = context.get_web();

    var userInfoList = web.get_siteUserInfoList();

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml('<View><Query><Where><Eq><FieldRef Name=\'ID\' />' +
                                '<Value Type=\'Number\'>' + userId + '</Value></Eq>' +
                                '</Where></Query><RowLimit>1</RowLimit></View>');

    this.userListItem = userInfoList.getItems(camlQuery);

    context.load(this.userListItem);

    context.executeQueryAsync(
            Function.createDelegate(this, onSucceeded),
            Function.createDelegate(this, onFailed));

The onSucceeded function is called when the query runs successfully, otherwise the onFailed function will be executed.


function onSuceeded(sender, eventArgs)
{
        var item = this.userListItem.itemAt(0);
        var name = item.get_item('Name');
        var userName = "Name";

        if (name) {
            userName = name;
        }

        alert(userName);
}

One last thing to remember is to run the code after SP.js is loaded

SP.SOD.executeOrDelayUntilScriptLoaded(LoadUserInfo, 'SP.js');

Programmatically Connect SharePoint Web Parts

In this post I am adding two web parts into a page programmatically and then connect them by using SPLimitedWebPartManager.SPConnectWebParts. Lately, I have been using Performance point web part and QueryStringFilter web part so I am going to use them for this example.

First step is to create an instance of the two web parts :


//Create an instance of the PerformancePoint web part 
ReportViewWebPart wp = new ReportViewWebPart();
wp.LocationUrl = "/TestBI/Lists/PerformancePoint Content/200_.000";

//Create an instance of the query string filter web part
QueryStringFilterWebPart filterwebpart = new QueryStringFilterWebPart();
filterwebpart.ZoneID = "Zone 1";
filterwebpart.DefaultValue = "Perth";
filterwebpart.QueryStringParameterName = "City";
filterwebpart.ID = "g_" + Guid.NewGuid();
filterwebpart.FilterName = "MyFilter";
filterwebpart.Title = "Hello";

Once we have created the web parts, we can add those web parts into a page by using SPLimitedWebPartManager


using (SPLimitedWebPartManager wpm = web.GetLimitedWebPartManager("/TestBI/Dashboards/MyTest/TestPage.aspx",System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared))                                                                     
            {
                wpm.AddWebPart(filterwebpart, "Zone 1", 0);
                wpm.AddWebPart(wp, "Zone 1", 1);                

                var cc = wpm.GetConsumerConnectionPoints(wp);
                var pp = wpm.GetProviderConnectionPoints(filterwebpart);

                var transformer = = new TransformableFilterValuesToParametersTransformer();

            filter.ConsumerFieldNames = new string[] {"SqlReportViewUniqueParameterIdSI1"};
            filter.ProviderFieldNames = new string[] { "MyFilter" };

ConsumerConnectionPoint consumerConnection = null;

                foreach (ConsumerConnectionPoint cpoint in cc)
                {
                    if (cpoint.InterfaceType == typeof(IWebPartParameters))
                        consumerConnection = cpoint;
                }

                ProviderConnectionPoint providerConnection = null;

                foreach (ProviderConnectionPoint ppoint in pp)
                {
                    if (ppoint.InterfaceType == typeof(ITransformableFilterValues))
                        providerConnection = ppoint;

                }

                var conn = wpm.SPConnectWebParts(filterwebpart, providerConnection , wp, consumerConnection, transformer);

                wpm.SPWebPartConnections.Add(conn);
            }

The QueryStringFilterWebPart resides in Microsoft.Office.Server.FilterControls.dll which resides in GAC.

As for the ReportViewWebPart, it resides in Microsoft.PerformancePoint.Scorecards.WebControls.dll which can found in GAC.

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)

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 🙂