Rporting Service and Sharepoint...simple solution to a colossal problem

Working with Sharepoint and Reporting Service is not something someone is going to enjoy. I can make a list of troubles you will face but that may scare you enough to make different plans for your career(as it did for me), so I am not being a wailer here. Rather I would talk about a common problem, discuss the weird things came into my mind and guide you to a elegant solution that caused me almost 2 days.


At the beginning of the day the requirement is very simple. You have to make a SSRS report which takes 5 parameters and load the report. It's so easy man, you can do it. So you make a report and upload it to reporting service server. And in Sharepoint you do the following

1. Add a Report Viewer web part to your page. By default this web part is not available, so to use this web part copy RSWebParts.cab from C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\ RSWebParts.cab

Then run the following command
STSADM.EXE -o addwppack -filename "RSWebParts.cab" -globalinstall

2. Change the report viewer web part's report server and report path value in edit mode.

So you are done with adding the report to Sharepoint. The client will change the parameter values form the parameter panel of the report and view the report.

Now as the noon comes the requirements get changed. The client does not like the ugly parameter panel anymore and they want some better looking controls for selecting parameter values. So what you have to do is make a custom parameter panel. What I could come up with is a custom Filter web part. My plan was

1. Make a filter web part which will have some server controls(one control per parameter).
2. Connect the report viewer web part to the filter web part using connection. The report viewer web part can take parameter values from connections and get refreshed with the new parameter values.

As I have not mentioned all detail about how to do this, by the time you have done this you will be thanking Google and may be cursing Microsoft!

Your client is still not happy(as they are never). To make your life hell now they want different reports to be displayed in the same place depending on a drop down or button(you are dead!). Moreover they again wants to change the parameter panel. For example they have added a Javascript tree and some other html controls from which you have to take the parameter values.

The first problem with the new requirements is multiple reports in the same report viewer control. Report viewer controls can take both report path and report parameter values using connections. But they can not take them both at the same time. As the custom Filter web part is providing the parameter values the report viewer control can not take report pathusing connection.

The second problem is your HTML controls.How the filter web part is going to provide values taking form a non server control?

After couple of frustrating hours(smoke,tea,coffee and career change plans...) I came up with the following ideas.

1. The parameter panel will be now in HTML and JS. There will be a "Show Report" HTML button.
2. There will be an Iframe in the page.
3. There will be an aspx page stored in sharpeoint which will have a report viewer control(read again, it is not report viewer web part!). The page in its page load will parse it's Query string and find the report name and parameters required. It will then set the report viewer's report path and parameters.
4. When the "Show Report" button is clicked it will set the iframe's source too the aspx page and pass the parameters as Query string.

So this way if you are lucky enough you can make it work. But there is a very good chance of getting stuck at some point. Luckily while struggling with these issues I found something totally unexpected.

The reporting service server manager URL actuallly has an aspx page. And the well kept secret is this page takes query string parameters and load the reports accordingly. So you only pass the report name and parameters as querystring and set the url as source of your iframe. And voila! your report is shown out of no where :) So finally I came up with the following javascript code.

Few important power shell commands

Before trying any of the following commands in powershell make sure that service SharePoint 2010 Administrationis started. if it is not started then start it.

Go to Start -> All Programmers -> Microsoft SharePoint 2010 Products -> SharePoint 2010 Management Shell


To install a solution we use the Add-SPSolution command. If you are using a Sandboxed solution you would use Add-SPUserSolution instead. But for Sandbox solution I would prefer adding the wsp file using GUI in Central Admin.

PS C:\Users\user1> Add-SPSolution C:\mySolution.wsp

Now to deploy the solution to your Sharepoint site we shall use Install-SPSolution


PS C:\Users\user1> Install-SPSolution -Identity mySolution.wsp -WebApplication https://mySiteURL –GACDeployment

To upgrade solution use Update-SPSolution

PS C:\Users\user1>Update-SPSolution –Identity mySolution.wsp –LiteralPath C:\mySolution.wsp –GACDeployment

To retract a solution we use the Uninstall-SPSolution

PS C:\Users\user1>Uninstall-SPSolution –Identity mySolution.wsp –WebApplication https://mySiteURL

And to remove it from solution store use Remove-SPSolution

PS C:\Users\user1>Remove-SPSolution –Identity mySolution.wsp

Few little troubleshooting.
1. You may sometime see the following error when trying Install-SPSolution

Install-SPSolution : This solution contains no resources scoped for a Web appli
cation and cannot be deployed to a particular Web application.

Then try this instead
PS C:\Users\user1> Install-SPSolution -Identity mySolution.wsp –GACDeployment

2. If any of your deploy command fails you will not be able to deploy it again. If you try again you will see the following error

A deployment or retraction is already under way for the so
lution "yourSolution", and only one deployment or retraction at a time is supported.....

To handle this issue open central admin. Go to Monitoring->Review Timer Job Definitions
Then find the job which has you solution name in title. Delete it.

3. You may see the following error if SharePoint 2010 Administration service is not started.

Install-SPSolution: Admin SVC must be running in order to create deployment timer job

So all you need to do is to go to the Services section in Control Panel\System and Security\Administrative Tools and look for the service called SharePoint 2010 Administration, it was on manual start up, changed it to automatic and started it and gotten rid of this error.

Different types of application with Sharepoint Object Model

SharePoint object model is a .NET-based API to SharePoint components. Or in simpler language you can say SP OM is a way to access Sharepoint components from your custom application(console,web etc.).

Thought I don't encourage anyone to use it over Sharepoint web services but there may be situations where you will have to use it. Sharepoint web services are the best way to deal with any sort of Sharepoint issues from outside of Sharepoint. And before using SP OM keep it in mind that The object model can be used when the application will run on the server where SharePoint is installed or in assemblies that are run within a site (such as a Web Part).

Here I am showing how to use SP OM in two types of application, Console and Web.

First console.
1. Create a console application and add reference to Microsoft.Sharepoint to your project. In case of SP2010 you will find it in "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.SharePoint.dll" and in case of SP2007 you will find it in "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.SharePoint.dll"

2. Add the following code to your project.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            string siteStr = "<SP Site URL>";
            SPSite tempSite = new SPSite(siteStr);
            SPUserToken systoken = tempSite.SystemAccount.UserToken;
            using (SPSite site = new SPSite(siteStr, systoken))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    //right now, logged in as Site System Account
                    Console.WriteLine("Currently logged in as: " + web.CurrentUser.ToString());
                    Console.Read();
                }
            }
        }
    }
}

2. Now go to the "Properties" window of the project and select "Application" tab. Change the "Target Framework" to ".NET Framework 3.5". Now go to "Build" tab and change the "Platform Target" to "x64".

Now run your application. You should see something like "Currently logged in as: SHAREPOINT\system".

For Web appliaction.

1. Create a ASP.NET web site and add the following code in your .aspx page's code behind. Add Microsoft.Sharepoint like before.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string siteStr = "http://dacsvm0021/my/personal/SSO/";
        SPSite tempSite = null;
        SPSecurity.RunWithElevatedPrivileges(delegate(){
            tempSite = new SPSite(siteStr);
        });

        SPUserToken systoken = tempSite.SystemAccount.UserToken;
        using (SPSite site = new SPSite(siteStr, systoken))
        {
            using (SPWeb web = site.OpenWeb())
            {
                Label1.Text = "Currently logged in as: "+web.CurrentUser.ToString();
            }
        }

    }
}

As you can guess I have created a Label "Label1" in my page.

2. Create a web site in IIS. Now here is a small trick. While creating the site do not create a new application pool for your site, rather use the application pool of your Sharepoint site.

3. Now deploy your site to your IIS site.

Now if you browse the site you should see something like "Currently logged in as: SHAREPOINT\system".

After doing all these you still may have issues with Object Model. One popular but unauthenticated suggestion is to run all your application in "Administrator" mode. If still you have issues then beg help from Allah!

Install Sharepoint 2010 in Windows 7

If you google with the title of this post you will see many people struggling with this and everyone with their own solution. Unfortunately none of the solutions worked for me. Here I am describing the steps I followed to do this.

Step 1. As you start your installation you will soon see the following error. Pretty disheartening :(



Now to handle this issue open the config.xml file from "\Files\Setup" folder. Add the following line in the <Configuration> section of this file.

<Setting Id="AllowWindowsClientInstall" Value="True"/>

Now your installation will proceed. Except this modification installation will not start in Windows 7.

Step 2. Now as your installation go on you will see the ‘non-supported for production’ notice:



I hope I don't have to explain why this message is prompted. No one should try a production in Windows 7 fro sharepoint.

Step 3. Now you installation will be finished smoothly. But you may face the next hurdle during SharePoint Products Configuration Wizard. If "Windows identity foundation pack" is missing in your machine you will see the following error

Could not load file or assembly 'Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.IdentityModel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

To resolve this download the pack from http://www.microsoft.com/downloads/details.aspx?FamilyID=eb9c345f-e830-40b8-a5fe-ae7a864c4d76&displaylang=en and install it in your Windows 7 machine.

Step 4. If "Chart Control" is missing in your windows 7 machine then you will get the following error.

An exception of type Microsoft.SharePoint.Upgrade.SPUpgradeException was thrown. Additional exception information: Failed to call GetTypes on assembly Microsoft.Office.Server.Search, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c. Could not load file or assembly 'System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

To resolve this issue download it from http://go.microsoft.com/fwlink/?LinkID=122517 and install it.

Ok, all these should be enough. If you still are fighting with this please drop a comment, I shall try to answer with the solution.