pass multiple filter parameters to custom report viewer web part

Introduction:

Here I will explain that how to display ssrs report on sharepoint site which can accept custom multiple filter parameters.
I will highlights on requirement details, different approaches which does not work for me, Approach which work for me and Then I will explain the code for working approach.

Requirement Detail:

We require to display SSRS report (use cube to make ssrs report) on SharePpoint 2010 application using custom multiple filter which will be provided in custom user control or custom webpart.

That means we have 2 zones on our page:-

1. Custom panel to display parameter with viewreport button.
2. Result pane where report will display based on selected parameters in custom panel.

Issue:

How to display report viewer page in sharepoint page based on our custom parameters which are more than one?

Applied approaches which does not work for me:

1. OOB Report Viewer web part

It does not allow to take parameter. So this solution does not work.

2. Customize SharePoint Report Viewer web part

I have created normal webpart class which use “ReportViewerWebpart” Class and pass parameter. But this solution also does not works.

See below code:-

1
2
3
4
5
6
7
8
9
10
ReportViewerWebPart wp = new ReportViewerWebPart();
wp.ReportPath = "http://localhost/ReportServer/myproj/Report_one";
ReportParameterDefaultCollection parame = wp.OverrideParameters;
 
//Add all report parameters here.
parame.Add(new Microsoft.Reporting.WebForms.ReportParameter("DimFirstLocation", "IND"));
parame.Add(new Microsoft.Reporting.WebForms.ReportParameter("StartDimTimeMonthYear", "MAY - 2011"));
parame.Add(new Microsoft.Reporting.WebForms.ReportParameter("EndDimTimeMonthYear", "April - 2012"));
 
this.Controls.Add(wp);

3. Create Custom Filter web part to supply parameter for SharePoint Report Viewer web part.

I have tried ITransformableFilterValues and Ifilter interface but it only support to pass 1 parameter at a time to report. So this solution does not work since I need to pass multiple parameters into report.

4. .Net Report viewer web part

I have created .Net report viewer control and passed parameter which works great in .Net Application but some how it doesn’t not work on sharepoint 2010 site.

Working solution:

I have created IFrame in user control and assign url with proper parameters which result me report.

Challenging issue:-

When I assign url parameter it was throwing error “missing parameter or values”
After searching lot I found out that we need to convert our parameter in Unicode url.
then assign into iframe.

Here is sample:-

A). Non working url:-

http://localhost/ReportServer/myproj/Report_one?&rs:Command=Render& DimFirstLocation=[DimFirst].[Location].[Country].&[IND]

This url when I assign to iframe it throws error where as its correct.

B) Working url:-

I convert my url into below format

http://localhost/ReportServer/myproj/Report_one?&rs:Command=Render& DimFirstLocation=[DimFirst].[Location].[Country].%26[IND]

Here I just replaced & to %26.

That’s all and it works great.

 

Detail Code:

1. Create user control , add iframe in result section with visible=false, src=””.

You create a user control with required multiple filter parameter. Place below Iframe code in search result place.

iframe_passparam

2. Create c# event for button

Generate event for button and write below code:-
(this will find iframe control and assign url into it.)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
protected void btnViewReport1_Click(object sender, EventArgs e)
        {
            try
            {
                    string reportUrl = GenerateReportViewerPath();
                    HtmlControl contentPanel1 = (HtmlControl)this.FindControl("iframeViewerReport");
                    contentPanel1.Visible = true;
                    contentPanel1.Attributes["src"] = reportUrl;
            }
            catch (Exception ex)
            {
                ExceptionHandler(ex, "btnViewReport_Click", "ReportViewerControl");
            }
        }

3. Generate report viewer path function

1
2
3
4
5
6
7
8
9
10
11
12
13
private string GenerateReportPath()
        {
string strReportServerPath =
"http://localhost/ReportServer/Pages/ReportViewer.aspx?/myproject
/Report_1&rs:Command=Render&rc:Toolbar=true&rc:parameters=false";
 
string param1 = "&DimFirstLocation=[DimFirst].[Location].[Country].%26[IND]"
string param2 = "&DimSecondAmount=[Dimsecond].[Amount].[USD].%26[20]"
 
string reportUrl = strReportServerPath + param1 + param12;
 
return reportUrl;
}

Hope it helps.
Thanks!
Avinash

calendarAugust 25, 2012 · cardInfoyen · comments10 Comments
tagTags: ,  · Posted in: SharePoint, SharePoint 2010, WebPart

10 Responses

  1. gurmeet - January 4, 2013

    Hi avinash,
    I need your help.
    The requirement in my project is i need to pass a multiple parameter to my SSRS report using a nice interface i.e i need to pass the parameter using a webpart made in dot net having listbox control in my webpart from which user can select multiple values and that selected values should be the search criteria of my SSRS report.
    I am stuck in this issue for a while now.
    any help will be appricated.
    thank you,
    gurmeet

  2. Infoyen - January 4, 2013

    Hi Gurmeet,

    This is what adjectly i explained in my article.
    Read my article step by step. I am sure you will get solution.

    Key point for your web part should be:-
    1. Create filters as much as you want in your web part. no issues.
    2. place i-frame in your webpart; as i mentioned in my article (Detail code: point no 1).
    3. On click of submit or view report; generate report path using your web part parameter selection. (Refer: Detail code topic: point no 3)
    4. Assign this url to i-frame. (Refer: Detail code topic: point no 2)

    Hope you get my idea.

    Regards,
    Avinash

  3. gurmeet - January 7, 2013

    hi avinash,
    thank you for your reply.
    I studied your article. I have two questions in particular with your 3 point.
    a)
    string strReportServerPath =
    “http://localhost/ReportServer/Pages/ReportViewer.aspx?/myproject
    /Report_1&rs:Command=Render&rc:Toolbar=true&rc:parameters=false”;

    Here are you calling the report which is deployed on reporting server ? or you have created a new report in visual studio 2010?

    b)
    string param1 = “&DimFirstLocation=[DimFirst].[Location].[Country].%26[IND]”
    string param2 = “&DimSecondAmount=[Dimsecond].[Amount].[USD].%26[20]”

    I didnt understand this how you are passing the multiple values in the parameter.

    thank you,
    gurmeet

  4. Infoyen - January 8, 2013

    Here are answers:-

    1. Report path would be report server url. So it can be like this
    http://localhost/ReportServer/Pages/ReportViewer.aspx?/myproject/Report_1&rs:Command=Render
    Here my report name is “Report_1”
    2. Create a SSRS report with 3-4 parameter. Deploy on server. Now render your report.
    Here you filter your report with 2 parameter. Then copy your browser url.

    In this way you will understand that how report server build url for multiple parameter selection.

    Now your target is to build same string from your webpart.

    Hope you get my idea..

  5. Krishna - February 19, 2013

    Hi Avinash,

    I have one question for you. My name is Krishna. I’m working as SSRS developer. Recently as per my project requirement I’m publishing reports in Sharepoint 2010 library. Basically my report has 2 parameters that i need provide before generating report.

    When i ran my report in report server i’m good and i can pass two integer parameters before initiating report( i meant after clicking report.rdl file)and i’m able click View Report button to generate report.

    When it comes to sharepoint i was not able to pass parameters like report server. I’m getting message like” Specify parameter values…..Report Parameter values must specified before the report can be displayed. choose parameter values in parameters area and click APPLY button”

    I can pass some default values to display report with out above message.

    I’m wondering why there is no “view report” button when it comes to sharepoint?. I see only Apply.

    In report server i can pass parameters at report server header location.( i meant from top left corner to right)

    But in sharepoint why i see parameters option at right side of the screen along with Apply button?

    Is there any approach to over come this?

    Appreciate your inputs on this issue.

    Thanks,
    Krisha V

  6. Infoyen - June 9, 2013

    Its problem with parameter.
    When it throws error on sharepoint webpage, please take report url and directly hit on web browser.
    You will find same error.

    This will help you to find the solution.

  7. Vikram - January 28, 2014

    Hi avinash,

    I am following the steps mentioned by you and its working.
    However, I have a small issue, I want to hide the controls in the ToolBar(Ex: print, save …) i just wast the user to see the Navigation. I am trying to user “&Toolbar=Navigation” but its not wotking, can you help me out. thanks

    Regards,
    Vikram

  8. Infoyen - June 18, 2014

    I hope you might have solve the issue already.
    However below is my idea
    – mention &Toolbar=false in your parameter.

    Thanks!!

  9. Abdul Rahman - October 31, 2014

    Hi Avinash,
    Thanks for your post as this was what exactly i was looking for, i have a small doubt in the working approach you proposed are you using the OOB report viewer WP or the .Net report viewer webpart, and where are we placing the iframe tag ?(before or after the webpart?)
    Thanks in advance.

  10. Infoyen - November 20, 2014

    I tried to use several webpart but it does not work.

    So here i have created .net user control which have iframe. Loaded this control using webpart.
    Please see my other article if you want to know that “How to load user control from webpart” (http://sharepoint.infoyen.com/2012/04/11/create-and-deploy-user-control-in-sharepoint-using-feature/)

Leave a Reply

Spam Protection: , required

myworldmaps infoyen