Solved

How pass parameters to reportviewer using c# code behind

Posted on 2010-09-10
21
28,583 Views
Last Modified: 2014-04-23
I am able to render a remote ssrs report inside an aspx page using the ReportVIewer Control.

I would like to pass two variables from code behind to the report parameters as the page loads. Here are some more details:

Parameter 1 Name: CustID
Parameter 1 Value: 001984

Parameter 2 Name: ShipDate
Parameter 2 Value: 2010/10/09 00:00:00

I need help understanding what references to use as well as how to code the entire routine in C#. Running Visual Studio 2008 with .NET 2.0
Running Site Locally as well as deploying the Site to a public web server.
SQL Report Server is on another machine within the same network.

I have been banging on this most of today without success. I just can't seem to get things to work...
See code below for more details and please ask me if there is any additional information i can provide.
// ========================================================

// aspx report viewer control

// ========================================================



<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" ShowParameterPrompts="false"  

            Font-Size="8pt" Height="1000px" ProcessingMode="Remote" Width="100%">

                <ServerReport ReportPath="Folder/Report Name" 

                ReportServerUrl="http://ServerName/ReportServer" />

        </rsweb:ReportViewer>



// ========================================================

// Code Behind Attempt

// ========================================================



            Microsoft.Reporting.WinForms.ReportParameter[] Param = new Microsoft.Reporting.WinForms.ReportParameter[1];

            

           Param[0] = new Microsoft.Reporting.WinForms.ReportParameter("CustID", "001984");

Open in new window

0
Comment
Question by:jsvb1977
  • 13
  • 7
21 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 33650226
0
 

Author Comment

by:jsvb1977
ID: 33650376
Thanks for the link -- I get this error:

Error      32      The type 'System.Windows.Forms.UserControl' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.      C:\WIP\PartnerWeb\Partner\Partner\PrintPOG.aspx.cs      27      22      Partner

Line 27 is this:
private void SetReportParameters(ReportViewer viewer)

I am sure this is basic .NET stuff -- but I could use some help...
0
 

Author Comment

by:jsvb1977
ID: 33650428
Sorry to jump in on my own posts -- but I added the references and added this:

using Microsoft.ReportingServices;
using Microsoft.Reporting.WinForms;

Now, on rebuild i get the following errors:

Error      32      Non-invocable member 'System.Web.HttpRequest.QueryString' cannot be used like a method.      C:\WIP\PartnerWeb\Partner\Partner\PrintPOG.aspx.cs      30      78      Partner
Error      33      Non-invocable member 'System.Web.HttpRequest.QueryString' cannot be used like a method.      C:\WIP\PartnerWeb\Partner\Partner\PrintPOG.aspx.cs      31      82      Partner


.NET is complaining about Request.QueryString where QueryString is underlined in BLUE.

Any thoughts?
0
 

Author Comment

by:jsvb1977
ID: 33650449
Sorry again, looks like I needed Brackets...

            ReportParameter myCustID = new ReportParameter("CustID", Request.QueryString["001984"]);
            ReportParameter myShipDate = new ReportParameter("ShipDate", Request.QueryString["2010/10/09 00:00:00"]);

I will rebuild and post my results...
Jason
0
 

Author Comment

by:jsvb1977
ID: 33650488
Well, the page loads, but the parameters are not passed on...

Could it have something to do with the fact that I am not referencing the ReportViewer ID in the code behind? The ID is

<rsweb:ReportViewer ID="ReportViewer1" runat="server" ...

In the code behind, no reference to that ID is made, so how will the code behind link to the reportviewer in the aspx page?

Thank you!
Jason
0
 
LVL 5

Accepted Solution

by:
milindsaraswala earned 500 total points
ID: 33658557
You have to write following code in page load event. Here I am assuming that ReportViewer1 is ID of the Report Viewer control in your WinForm


ReportParameter[] para = new ReportParameter[1];
para(0) = new ReportParameter("NameOfParamater", Value);
para(1) = new ReportParameter("NameOfParamater", Value);

this.ReportViewer1.LocalReport.SetParameters(para);
0
 

Author Comment

by:jsvb1977
ID: 33661421
milindsaraswala,

Thank you for the reply. When I use the code posted above I get the following error:

Error      10      'para' is a 'variable' but is used like a 'method'      C:\WIP\PartnerWeb\Partner\Partner\PrintPOG.aspx.cs      48      13      Partner

Error      11      'para' is a 'variable' but is used like a 'method'      C:\WIP\PartnerWeb\Partner\Partner\PrintPOG.aspx.cs      49      13      Partner

==================================================================
Also, I want to mention that I have a default value set for the parameter in the report on sql services. Not sure if this will cause a problem, but I think I should get the code working in C# first.  Let me know if you have any ideas what the errors above mean. I did some googling, but nothing stood out to me in the SERP.

Jason

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.IO;

using System.Drawing;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Web.Util;

using FSPI.Security;

using FSPI.Utility;

using Ajax;

using Microsoft.ReportingServices;

using Microsoft.Reporting.WinForms;



namespace extranet {



    public class PrintPOG : System.Web.UI.Page {



        protected C_Header Header1;

        ReportViewer ReportViewer1;



        protected void Page_Load(object sender, EventArgs e)

        {



            ReportParameter[] para = new ReportParameter[1];

            para(0) = new ReportParameter("CustID", "123456");

            para(1) = new ReportParameter("ShipDate", "2010/13/09 00:00:00");



            this.ReportViewer1.LocalReport.SetParameters(para);

        }



    }

}

Open in new window

0
 

Author Comment

by:jsvb1977
ID: 33661452
oh, yeah. needed square brackets like this para[0] instead of para(0) --

But still I am not seeing the report update with the parameters. Any thoughts? I will design a new report without the default values populated in the parameters and see if that helps any.

I will report back with my results -- let me know if you think of anything until then -- and thank you!

Jason
0
 

Author Comment

by:jsvb1977
ID: 33661899
No Joy. Is it even possible to pass variables to a server report? Or does the report have to be local to the web server using an .rdl?

I attempted to remove the default values of the parameters on the sql reporting server report, then reloaded the web page we are working on. The report still did not load.

My current code is listed below.
Any thoughts?
namespace extranet {



    public class PrintPOG : System.Web.UI.Page {



        protected C_Header Header1;

        ReportViewer ReportViewer1;



        public void Page_Load(object sender, EventArgs e)

        {



            ReportParameter[] para = new ReportParameter[1];

            para[0] = new ReportParameter("CustID", "126456");

            para[1] = new ReportParameter("ShipDate", "9/13/2010"); // 2010/09/13 00:00:00



            //this.ReportViewer1.LocalReport.SetParameters(para);

            this.ReportViewer1.ServerReport.SetParameters(para);

        }



    }

}

Open in new window

0
 
LVL 5

Expert Comment

by:milindsaraswala
ID: 33662381
Actually when u run the report with reportviewer control it will run at local side. And as per the MSDN

When to Use Local Processing
Local processing mode is recommended for applications that include small to medium-sized reports and datasets. Because all data and report processing is performed on the client, you might encounter performance degradation or memory limitations if you attempt to process large or complex reports and queries.

Local processing mode is less scalable than remote processing and is intended for standalone applications that do not require a report server. Users who are familiar with server reports that run on a remote SQL Server Reporting Services report server should note the following differences:

In remote processing mode, the report server both retrieves the data and processes the report, whereas in local processing mode, the ReportViewer control processes the report only, and the the host application must retrieve the data and supply it to the ReportViewer control.

Client report definitions do not include query information.

Report parameters in client report definitions (.rdlc) do not map to data source query parameters.

There is no parameter input area for a client report definition that accepts values that are subsequently used in a query.

As per above you have to pass data source also.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jsvb1977
ID: 33662555
OK, Cool -- thanks for the feedback. So, what could be the problem here? Here is a repeat of the background of the information posted above:

We have an SSRS Report Server on one server on our network. It has many reports. one of which I am attempting to display in a web application.

I am able to get the Report to render in the web app using ReportViewer, but am unable to pass in the Parameter Values as Variables from Code Behind.

It is like the code behind is not properly 'communicating' with the ReportViewer Control on the aspx page. I get the following message when attempting to load the page:

The 'CustID' parameter is missing a value

I have a feeling that their is a small piece missing from the either the code behind as has been kindly provided in this post or maybe in the ReportViewer Control on the aspx page. Both are posted below for review.

Thank you for engaging in this with me, when it is solved, it will be a major improvement for the web app and your efforts are truly appreciated.

Let me know if you can suggest anything to assist.

FYI -- I have attempted to add a local report without success -- and now that you have clarified it's limitations above, I will not continue to attempt it. The report being rendered in the aspx page calls a stored procedure and is also a pretty big report.

Thank you,
Jason
in PrintPOG aspx page:

==================================

        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" PromptAreaCollapsed="true"   

            Font-Size="8pt" Height="1000px" ProcessingMode="Remote" Width="100%">

                <ServerReport ReportPath="/Sales/Online Ordering/Product Order Guide" 

                ReportServerUrl="http://sql01.fsproduce.com/ReportServer" />

        </rsweb:ReportViewer>



====================================

in PrintPOG.aspx.cs:

====================================



namespace extranet {



    public class PrintPOG : System.Web.UI.Page {



        protected C_Header Header1;

        ReportViewer ReportViewer1;



        public void Page_Load(object sender, EventArgs e)

        {



            ReportParameter[] para = new ReportParameter[1];

            para[0] = new ReportParameter("CustID", "126456");

            para[1] = new ReportParameter("ShipDate", "9/13/2010"); // 2010/09/13 00:00:00



            //this.ReportViewer1.LocalReport.SetParameters(para);

            this.ReportViewer1.ServerReport.SetParameters(para);

        }



    }

}



==============================================

When I disable the default value for the CustID Parameter on SQL Server Reporting Services and then render the page I get the following message:



The 'CustID' parameter is missing a value 

Open in new window

0
 

Author Comment

by:jsvb1977
ID: 33662818
UPDATE:

I placed breakpoints at the page load event in code behind, ran the app in debug, and the code never entered the page load event?!!?

No clue what is happening. I thought every page had to enter the page load event in code behind when the page loads?

hmmm...

Jason
0
 
LVL 5

Expert Comment

by:milindsaraswala
ID: 33662965
We need to find the name of the parameters expected by the your Report. To do this click on Report --> Report Parameters and we find that the Parameter names. We now have all the information we need to proceed. We now need to specify the ServerURL and the Report name to the Report Viewer component.

ReportViewer1.ServerUrl=@"http://localhost/ReportServer";
ReportViewer1.ReportPath=@"/Report URL";

To pass the parameters to the report, we need to use the SetQueryParameter method that we added to the ReportViewer component

ReportViewer1.SetQueryParameter("ParameterNme",Value);

Our next step is to hide the parameters part of the toolbar created in the Report. Since our web page now accepts user inputs, we don’t need Reporting Services to do this.

ReportViewer1.Parameters=Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

If you want to hide the rest of the toolbar as well (the area from where you can export to another format), set the Toolbar to false.

ReportViewer1.Toolbar=Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;
0
 
LVL 5

Expert Comment

by:milindsaraswala
ID: 33663127
Sorry I forget to mention  that use SetQueryParameter  you have to add code in

C# and VB versions of the source code are available, and if you used all the default installation options, they are installed under in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\vb and C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer \cs respectively.

Open up the solution file ReportViewer.sln then add this

public void SetQueryParameter(string ParamName, string ParamValue)
    {
  SetParameter(ParamName,ParamValue);
    }
0
 

Author Comment

by:jsvb1977
ID: 33666133
well, thank you again for all of your help. The thing is, i got so frustrated that i moved the code into a whole new website to see if it would run on its own without all the other pre-existing classes and what not.

and guess what? it ran just fine. this means that the first few lines of code you posted were working correctly.

so i started to experiment -- didn't really get me anywhere, though. so i scrapped everything and started again. I believe I am close -- but could use some assistance.

Never mind the fact that I did get the code you originally posted to run in its own website. Here is where I am now:

The error I am getting is that the "Object reference not set to an instance of an object. "
The line that errors in debug is "this.ReportViewer1.ServerReport.SetParameters(para);"

My current code is posted below. Can you please take a look at the code behind and let me know if you see anything glaringly obvious that would cause this generic error message?

In addition, I have included a screen shot of the error message captured during debug as well as the stack trace of the error which is located at the bottom of the code snippet I posted below.

If possible, i would like to muscle through this error -- since I believe that once we get past this error, the original code you posted will likely work [as it did in the side project i created earlier today].

Thanks again,
Jason
using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using Microsoft.Reporting.WebForms;

using Microsoft.ReportingServices;

using System.Windows.Forms;

using Microsoft.ApplicationBlocks.Data;

using FSPI.Security;



namespace extranet

{

    public class printpog : System.Web.UI.Page

	{

		protected System.Web.UI.HtmlControls.HtmlForm frmLogin;

		protected System.Web.UI.WebControls.Label lblRoute;

		protected System.Web.UI.WebControls.DataGrid dg;

		protected System.Web.UI.WebControls.Panel panGrid;

		protected System.Web.UI.WebControls.Label Message;

		protected System.Web.UI.WebControls.Label lblDateTime;

		protected System.Web.UI.WebControls.Label MyError;

        public C_Header Header1;

		public string selCustomer;

        ReportViewer ReportViewer1;

        

		private void Page_Load(object sender, System.EventArgs e)

		{

			// Put user code to initialize the page here

            Header1.pageTitle = "Print POG";

            

            ReportParameter[] para = new ReportParameter[1];

            para[0] = new ReportParameter("CustID", "123456");



            this.ReportViewer1.ServerReport.SetParameters(para);

                            



 		}



		#region Web Form Designer generated code

		override protected void OnInit(EventArgs e)

		{

			//

			// CODEGEN: This call is required by the ASP.NET Web Form Designer.

			//

			InitializeComponent();

			base.OnInit(e);

		}

		

		/// <summary>

		/// Required method for Designer support - do not modify

		/// the contents of this method with the code editor.

		/// </summary>

		private void InitializeComponent()

		{    

			//this.dg.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dg_ItemDataBound);

			this.Load += new System.EventHandler(this.Page_Load);

            

		}

		#endregion



	



	

	}

}



=====================================================

stack trace

=====================================================



System.NullReferenceException was unhandled by user code

  Message="Object reference not set to an instance of an object."

  Source="Partner"

  StackTrace:

       at extranet.printpog.Page_Load(Object sender, EventArgs e) in C:\WIP\PartnerWeb\Partner\Partner\PrintPOG.aspx.cs:line 41

       at System.Web.UI.Control.OnLoad(EventArgs e)

       at System.Web.UI.Control.LoadRecursive()

       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

  InnerException: 

Open in new window

errorInDebug.jpg
0
 
LVL 5

Expert Comment

by:milindsaraswala
ID: 33666288
You can do like this also

Generic.List<ReportParameter> paramList = new Generic.List<ReportParameter>();


paramList.Add(new ReportParameter("ParameterName1", Value1, false));
paramList.Add(new ReportParameter("ParameterName2", Value2, false));


ReportViewer1.ServerReport.SetParameters(paramList);
0
 

Author Comment

by:jsvb1977
ID: 33666442
ok, cool. What is "Generic?" I am unable to add a reference to it using

Add Reference >

I am using .net version 2.0 in this app.

Needless to say, I am unable to build my project as it errors on the "Generic.".

Jason

0
 

Author Comment

by:jsvb1977
ID: 33666480
ok, it's System.Collections.Generic -- but I am still getting the Object Reference Error when the page loads.

Still Googling...

Jason
0
 
LVL 5

Expert Comment

by:milindsaraswala
ID: 33666518
Replace the following code

 ReportParameter[] para = new ReportParameter[0];            
para[0] = new ReportParameter("CustID", "123456");


If you give [1], the parameter will assume that you are passing two parameters as it takes from 0. So all you need to do is to change the [1] to [0].

0
 

Author Comment

by:jsvb1977
ID: 33666650
yeah -- you might be right -- listen, this is embarrassing -- and let me preface what i am about to tell with you with the fact that i am NOT proficient in C#.

In fact, I inherited this project from people who worked here before I even started.

now that you know, here is what I did to get the thing to work properly.

I changed ReportViewer ReportViewer1;
to public ReportViewer ReportViewer1;

I guess if you do not specify "public" C# assumes private?

so, now things are working properly, the report renders, the variables are populating as values in the parameters and I have egg on my face.

Thank you for all of your help... I will award the points to you and close this thread.

Thank you,
Jason
0
 
LVL 5

Expert Comment

by:milindsaraswala
ID: 33666668
Thank you very. I am happy that I could solve your problem.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now