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 🙂

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?

Building custom OData service on SharePoint 2010

Recently I had a task to build a custom OData service for SharePoint, so here are the steps:

First, need to make sure that ADO.Net data service framework is installed on SharePoint server:
ADO.Net for Windows 2008
Or
ADO.Net for Windows 2008 R2

After that, add the following references on the project:

  • System.ServiceModel
  • System.ServiceModel.Web
  • System.ServiceModel
  • System.Runtime.Serialization
  • System.Data.Services
  • System.Data.Services.Client
  • Microsoft.SharePoint.Client.ServerRuntime

The next step is to create a SharePoint Mapped Folder and then select ISAPI. This is the folder where we are going to create our .svc file. Open the svc file and make sure it uses MultipleBaseAddressDataServiceHostFactory.

<%@ ServiceHost Language="C#" Debug="true" Factory="Microsoft.SharePoint.Client.Services.MultipleBaseAddressDataServiceHostFactory, Microsoft.SharePoint.Client.ServerRuntime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
    Service="TestODataService.EmployeeService,$SharePoint.Project.AssemblyFullName$" %>

In my project, I have created the following classes

[Serializable]
    [DataContractAttribute(IsReference = true)]
    [DataServiceKey("EmployeeId")]
    public class Employee
    {
        [DataMemberAttribute]
        public int EmployeeId { get; set; }

        [DataMemberAttribute]
        public string FirstName { get; set; }

        [DataMemberAttribute]
        public string LastName { get; set; }
    }
public class DataContext
    {
        public IQueryable<Employee> Employees
        {
            get
            {
                List<Employee> employeeList = EmployeeStore.GetEmployees();

                return employeeList.AsQueryable();
            }
        }

        
    }

In this case the DataContext class will be used as the data source type.

The last one is the EmployeeService itself, which inherits from DataService class

[BasicHttpBindingServiceMetadataExchangeEndpoint]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class EmployeeService : DataService<DataContext>
    {
        [WebGet]
        public IQueryable<Employee> GetEmployeeByName(string name)
        {
            var searchResult = EmployeeStore.GetEmployees()
                                        .Where(e => e.FirstName.Contains(name) || e.LastName.Contains(name));

            return searchResult.AsQueryable();
        }

        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            config.SetServiceOperationAccessRule("*", ServiceOperationRights.AllRead);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }

Please note that I have set access to all entities and service operations to AllRead since I only need to read the data.

To consume the above service, we can add a service reference in our test project and use the url to the service, e.g.: http://localhost/_vti_bin/EmployeeService.svc

To get access to the entities, we can use the data context as below:

var ctx =
              new ServiceReference1.DataContext(new Uri("http://localhost/_vti_bin/employeeService.svc"));
            ctx.Credentials = System.Net.CredentialCache.DefaultCredentials;

            ctx.Employees.ToList().ForEach(e => Console.WriteLine(e.FirstName + " " + e.LastName));

To use the service operations:

var ctx =
              new ServiceReference1.DataContext(new Uri("http://localhost/_vti_bin/employeeService.svc"));
            ctx.Credentials = System.Net.CredentialCache.DefaultCredentials;
var query = ctx.CreateQuery<Employee>("GetEmployeeByName").AddQueryOption("name", "'Blogg'");
            var result = query.Execute();

            result.ToList().ForEach(e => Console.WriteLine(e.FirstName + " " + e.LastName));

Using SharePoint PeopleEditor user control

I had the chance to play with PeopleEditor user control on my previous project and just thought would be good to put another blog about it out there, so here you go:

Started with creating a visual web part and the next step is adding the control to the ascx page

<SharePoint:PeopleEditor ID="peUser" ValidatorEnabled="true" runat="server"
                        SelectionSet="User" Width="350px" IsValid="true" AllowTypeIn="true"
                        MultiSelect="true"></SharePoint:PeopleEditor>
<SharePoint:InputFormRequiredFieldValidator ID="rfvUser" 
                        SetFocusOnError="true" runat="server" ControlToValidate="peUser" BreakBefore="true"
                        ErrorMessage="Required field." EnableClientScript="false"
                        Display="Dynamic"></SharePoint:InputFormRequiredFieldValidator>

To get the selected users we can iterate through its resovedEntities property

for (index = 0; index <= peControl.ResolvedEntities.Count - 1; ++index)
{
    PickerEntity entity = (PickerEntity)peopleEdControl.ResolvedEntities[index];
    SelectedUser selectedUser = new SelectedUser(){ Id = Convert.ToInt32(entity.EntityData["SPUserID"])
                                                    , Name = entity.DisplayText
                                                    , LoginName = entity.Key };
    selectedUserList.Add(selectedUser);
}

To assign a value to PeopleEditor user control (in my case I use a string of login name separated by a semicolon)

string[] delimiter = { ";" };
string[] loginNames = loginNameString.Split(delimiter, StringSplitOptions.RemoveEmptyEntries);

foreach(string loginName in loginNames)
{
     PickerEntity pe = new PickerEntity();
     pe.Key = loginName;

     pe = peUser.ValidateEntity(pe);
     ArrayList entityList = new ArrayList() { pe };

     peUser.UpdateEntities(entityList);
}

Hope that helps 🙂

SharePoint URL Field

One way to assign value to URL field is by joining the url string and its description e.g. https://akurniaga.wordpress.com, My Blog (Not so nice but it works).

Another way is to use SPFieldURLValue:

To get value from a URL Field:

SPFieldUrlValue urlField = new SPFieldUrlValue(item["MyUrl"].ToString());
string description = urlField.Description;
string url = urlField.Url;

To assign to a URL Field:

SPFieldUrlValue urlField = new SPFieldUrlValue();
urlField.Url = "https://akurniaga.wordpress.com";
urlField.Description = "My Blog";
item["MyUrl"] = urlField;

Simple right? 🙂