Blog Stats
  • Posts - 19
  • Articles - 0
  • Comments - 40
  • Trackbacks - 258

 

<< Developing Without Admin Privileges is Most Frustrating! | Home | Blogging From SauceReader to This Site >>

Reporting Services Report Parameters

After spending some time with Reporting Services over the past couple weeks, I have noticed a few issues that I find rather odd, at least in my opinion.

If you create a report and use a parameterized query, report services will nicely add the parameters to the report for you as well.  This is a great feature and I really like that aspect.

What is different however, is when you want to call that report from within an ASP.NET web application.  Ideally, you would like to be able to pass the values for the parameters in the URL. For the most part, this should not create any problems, but it does.

If you were to go into reporting services and view the report, you will not get any data returned until you enter the values required for the parameters in the SQL SELECT statement in your datasource.  This is by design as you have not told reporting services what you want returned yet.

The way that Reporting Services works in this case is to present you with two text boxes with appropriate labels allowing you to enter the parameters required by the SQL string.  Once you enter the values and view the report, your data displays.  You can also do this from within Visual Studio by selecting the Preview option in the report designer.  All seems to work well until you notice that when you run the report in the browser, you see the prompts at the top of the form for the parameters.  If you have passed in the values in the URL, the report should display properly but the values will also be displayed in the text boxes.  This is not desirable most times.

By going into Reporting Services directly in the browser, http://localhost/reports/Pages/Folder.aspx, you can set various properties of the report, one of which is to not prompt the user.  This is found under the Parameters link for the report you wish to work with.  I caution you, DO NOT do this.  Once you have set the prompt check boxes to cleared, your reports will now issue this infamous error;

Reporting Services Error

  • The report parameter 'paramnamehere' is read-only and cannot be modified. (rsReadOnlyReportParameter) Get Online Help

Microsoft Reporting Services

Click the Get Online Help link to find out that there is very little help available for this error message other than to say;

Explanation

This error occurs when you attempt to set a value for a parameter that is read-only.

   
User Action

Search Books Online for more information about the parameter. There are conditions that you can control that determine whether the parameter is read-only.

Currently there are no Microsoft Knowledge Base articles available for this specific error or event message. For information about other support options you can use to find answers online, see http://support.microsoft.com/default.aspx.

Nice?  NOT!

Here is what I have found to be the solution.  Go back into the report manager on your computer and set the Prompt User check boxes to checked.  Now, you still don't want the prompts to be displayed on the page because you are passing them in the URL. 

Go back to your project and select the report viewer control, if you are using it, and set the Parameters property to false.  If you are not using the viewer, you can set the property in code.  Once you set the parameter property to false, the prompts will not display at the top of the report in the browser anymore.

BTW, you can supposedly also suppress parameters in the URL by adding a query string parameter of rs:parameters=false.

 

Gerry

Print posted @ Friday, July 30, 2004 3:57 PM

Feedback

# re: Reporting Services Report Parameters

Gravatar I ran into the same problem, but as I am writing a form based reporting services managenment tool, I need to have control over the Promp User check boxes. eg I need a way for my users to check/uncheck the Prompt User within my app, which I just cannot figure out how to do. And when I create a new subscription I cannot pass new parameter values, if the Promp User check boxes are not checked in the report manager.

Another problem I have got is that I just cannot find a way do display some parameter values. They appear in the reoport manger but when I try to display them their state is "DynamicValuesUnavailable" although they are visible in the report manager.

So the abouve workaroud is not good enough for me, and I have not found any sites dealing with that problem.

Gash 8/2/2004 6:28 AM | Gash

# re: Reporting Services Report Parameters

Gravatar Sounds like you have some more serious issues than I was having. I haven't gone to that extent with RS yet. I do know that the prompt check boxes really threw me for awhile. It just doesn't make sense.

I will keep your issues in mind as I work more with RS and if I come across a solution, I'll be sure to let you know.

Gerry 8/2/2004 8:23 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar You say to "set the Parameters property to false". How/where exactly do you do that?

Thanks,

Leon 8/4/2004 12:53 PM | Leon

# re: Reporting Services Report Parameters

Gravatar You can do this using two possible methods.

1) If you are using the reportviewer control in your application, you can set the parameter property in the property window to False. NOTE: This is only available in the reportviewer control.

2) You can set the parameters to false in the URL of the request for your report if you are going to view it as a report relying on report manager rather than the control. Add this to your URL;

rc:Parameters=false

This belongs in the URL query string portion.

Gerry

8/4/2004 1:24 PM | Gerry

# Length of parameter to a report

Gravatar Hi ,
Help is greatly appreciated.
I am rendering the reports thru code.
I want to pass as parameter a huge xml string.The report is just hanging. Is there any limitation to the size of the parameter i can send to the report.
8/24/2004 11:45 AM | Raj

# re: Reporting Services Report Parameters

Gravatar I haven't run across that yet nor have I searched the documentation but there is a limit on the whole URL length anyway of 2048 characters.

I'm assuming there is a length limit on the parameters as well.

Gerry 8/24/2004 11:50 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar Your limitation is an ASP problem. The url cannot be longer that something like the 2048 characters 9/20/2004 10:41 AM | Hennie van Dyk

# re: Reporting Services Report Parameters

Gravatar Hmm I've run into the same problem. Unfortuantely I need more flexbility to. I'd like some parameters to be passed without interaction, and some where interaction would be nice. This is a really horrible bug! Is there anything in the RDL code that can be modified to sort this out? 10/11/2004 11:32 AM | David Boyd

# re: Reporting Services Report Parameters

Gravatar Actually if you edit the RDL file and put in the prompt, but put prompt in blank for the parameter i.e. <Prompt></Prompt>
seems to work
10/11/2004 11:42 AM | David Boyd

# re: Reporting Services Report Parameters

Gravatar This was informative, and answers a different question I was getting ready to investigate. However, I have a more central question that I'm really hoping you can answer.

I have a query with a parameter being passed in. Something like:

select myvalue from mytable where myparam = @param

This query needs to be used multiple times in the report, and the only difference is the value of @param. I have researched extensively, but have not found a way to pass in the parameter from the report (not from the url). I.e., I would like to be able to do something like:

= SUM(Field!myvalue.value, "mydataset?param=1") (or something analogous) for the equation, where I am passing in a value for @param. There are multiple situations (dozens) where, if I can't do this, then I literally have to have datasets where the only difference is the value of @param. So I would end up with something like:

dataset1: select myvalue from mytable where myparam = 1

dataset2: select myvalue from mytable where myparam = 2

etc, etc.

This is, to say the least, immensely cumbersome.

Any ideas? Does this make sense? I'm on a massive deadline, so any hints would be most deeply appreciated. 10/22/2004 10:45 PM | Gregory S. Hill

# re: Reporting Services Report Parameters

Gravatar hi

Is there a way to control the toolbar Of the Parameters
For example to enter a calander For date Parameters?
tanks 11/17/2004 9:38 AM | ofir shoval

# re: Reporting Services Report Parameters

Gravatar Eu quero saber se tem algum parametro para exibir o botão de imprimir... 1/6/2005 2:33 PM | Nice

# re: Reporting Services Report Parameters

Gravatar Hello All,
Ques: I have a three parameter on my reports. Out of which One I am passing as a quesrystring and rest two I need on the Report pae to do Filter. Now setting "rc:Parameters=false" removes all the paramters from the report page. Where as i need only one to be removed and rest two to be there. Any Suggesion? 2/21/2005 1:52 AM | HEMAL RAVAL

# re: Reporting Services Report Parameters

Gravatar I labored for hours trying to hide parameters. I kept getting errors such as the parameter you are trying to set is read only. Finally I realized that the designer could not help me. So I told the designer to have the Prompts... such as 'UserID' for the parameter UserID.

Then I went into the XML code, by right clicking on the {report}.rdl and edited the XML.

I made the <Prompt> have an empty set. If you just go into the designer and make the Prompt: blank, it takes that element out of the XML which seems to make the parameter the dreaded readonly.

<ReportParameter Name="UserID">
<DataType>Integer</DataType>
<Prompt></Prompt>
</ReportParameter>
<ReportParameter Name="PlanID">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<Prompt></Prompt>
</ReportParameter>

Then I rebuilt, and deployed... and walla.. I could set the parameters in the query string. 2/24/2005 4:55 PM | Robert Koernke

# re: Reporting Services Report Parameters

Gravatar I'm using Report Services with a ReportViewer on my WebForm. Is there a way to change the reports datasource (connectionstring, query..) from my ReportViewer? 3/2/2005 8:11 AM | Marinus

# re: Reporting Services Report Parameters

Gravatar I facing problem in passing using large number of parameters(say around 500 which hold the key to the filter criteria of my application )to URL. Can some one tell me what is the solution to it or at least what is the maximum no. of parameters that can be passed....
Reply me at alkesh.patel@patni.com 3/4/2005 8:51 AM | Alkesh

# re: Reporting Services Report Parameters

Gravatar I can't recall the max parameters right off but I think you will find that you are running into a problem with the length of the URL.

THe parameters are being passed using GET which appends the parameters to the URL.

Check the length of your URL with all of your parameters.

Gerry 3/4/2005 9:01 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar What exactly are you trying to do with changing the datasource?

Can you provide a little more information?

Thanks

Gerry 3/4/2005 9:04 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar Has anyone answered this question above:

Another problem I have got is that I just cannot find a way do display some parameter values. They appear in the reoport manger but when I try to display them their state is "DynamicValuesUnavailable" although they are visible in the report manager.

I'm having a similar problem in the dropdown list, and can not find anything that would provide possible reason for this error 3/8/2005 6:43 PM | RG

# re: Reporting Services Report Parameters

Gravatar Hi,
I am running into an issue , any time the length of the url and parameters combination are greater than 2048 characters. It brings up the report page and sits on the "Report is being generated" screen forever.
i am trying to solve this problem for real long time now , can some one plz help !!!
i have tried using the render method (web service),but the major problem is, i cant use the toolbar with this method... which is my requirement :-)
could u plz tell me any method to view the report after rendering the report, instead of saving it as a file and then displaying it.... is display the rendered report in image format the only way to do it ?
it would be seriously kind of u if u could give me a solution for the URL length problem ! 3/9/2005 1:51 PM | Vivek

# Reporting Services Change connectionstring

Gravatar I'm using Report Services with a ReportViewer on my WebForm. Is there a way to change the reports datasource (connectionstring, query..) from my ReportViewer?

When my ASP.NET application is used, it depends on the user where the ReportViewer/Report S. will get the data. So, in runtime I would need a possibility to change the name of the Database (from "Northwind" to "Southwind").
I also wunder how I could change the SQL for the report. Example: change from "where User=123" to "where User = 456".
3/11/2005 9:34 AM | Marinus

# re: Reporting Services Report Parameters

Gravatar I believe the max length of the URL is 1024 characters. That means your URL with all params is twice that length.

Try shortening your URL and see if that helps.

Gerry 3/11/2005 9:37 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar For your first issue, you might try changing the connection string in code when the user logs in. You would have to write that yourself as I don't think you can do it by default in the Reportviewer.

As for issue #2, change your report to a parameterized report and provide the user id on the request.

Gerry 3/11/2005 9:40 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar Help, I have tried everything to be able to do the following in reporting services:

I need to have one value passed in the url that will not appear as a prompt and that will be hidden from the user. I have three other parameters that the user will choose.

In the URL I set the string as follows:
rc: parameter=true&hiddenparam=102

It all works but I cannot hide this parameter (hiddenparam) from the user since I have to set prompt user in the Report Manager to prevent the dreaded read only error. I have also edited out the prompt in the xml but that does not seem to work. I always get the prompt.

3/13/2005 5:07 PM | Mark

# re: Reporting Services Report Parameters

Gravatar Marinus,

The way I handle using connections is through linked servers. I fire a dynamic stored procedure that lives on one server. That stored procedure dyanmically builds based on some params that I pass in. I can determine what database to hit based on this. And to tell you the truth, this works great. I have the majority of my reports setup this way. 3/14/2005 4:19 PM | Bob

# re: Reporting Services Report Parameters

Gravatar Bob,
That sounds great! But I can not imagine how this stored procedure should look like... Could you give a sort of an example of that. If you please... 3/15/2005 8:59 AM | Marinus

# re: Reporting Services Report Parameters

Gravatar you guys who figured out how to do the <prompt></prompt> thing in the XML rock. Just what I needed. Hopefully they don't break this in a future version. 3/22/2005 7:41 PM | Andy

# re: Reporting Services Report Parameters

Gravatar If, like me, you are trying to hide something like ... say ... a user ID, you should use the user collection's User!UserID instead of trying to pass it. you can put =User!UserID as a stored procedure parameter value, for example. Here it is from the "horse's mouth":

http://blogs.msdn.com/tudortr/archive/2004/07/20/189398.aspx

Of note, I _am_ using a custom security extension. 3/24/2005 1:01 AM | Andy

# re: Reporting Services Report Parameters

Gravatar Regarding Mark's trouble with the hidden parameters not working (3/13/2005 5:07), I found that if you have a default value on your hidden param that it will not work. Also make sure that you save your xml specifically and verify the changes. Also, this worked for me on SP1; I haven't tested without SP1. 3/29/2005 1:43 PM | Johnny D.

# re: Reporting Services Report Parameters

Gravatar I wonder if Mr.HEMAL RAVAL already has the answer of his posted question, because I'm facing the same problem as his. I want to hide/remove 2 of 5 parameters, does anyone has the answer? 3/29/2005 9:47 PM | Liany

# re: Reporting Services Report Parameters

Gravatar Hi ,
I am new to RS - I want to know if we want to call another report from current report-passing values to the new one-and displaying the value-how to do that? 3/30/2005 2:21 AM | Vrushali Chaudhary

# re: Reporting Services Report Parameters

Gravatar Now that is scenario I haven't thought of before. I'll have to look into it.

Gerry 3/30/2005 8:37 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar I have a question. I've a query that i want to limit the results based on a report parameter. How do i represent that parameter in the SQL for the query. for example

select employer, state from table where employer = ???

If i add a query parameter, and then try and associate it with a report parameter, i always get no results in the preview pane. At this point i can't figure out how to represent the value in the Report Parameter to take effect on the query. Thanks in advance.

Daryl 3/30/2005 4:04 PM | Daryl

# ReportViewer

Gravatar Is there a way to select the windows user in ReportViewer ? 4/1/2005 12:30 PM | Beauma2

# re: Reporting Services Report Parameters

Gravatar HOW CAN I ET A REPORT PARAMETER THAT IS A LISTBOX TO BE MULTIPLE SELECT ENABLED? 4/21/2005 8:41 AM | sanhel

# re: Reporting Services Report Parameters

Gravatar HOW CAN I ET A REPORT PARAMETER THAT IS A LISTBOX TO BE MULTIPLE SELECT ENABLED? 4/21/2005 8:41 AM | sanhel

# re: Reporting Services Report Parameters

Gravatar HOW CAN I SET THE REPORT PARAMETER THAT IS A LISTBOX TO BE MULTI SELECT ENABLED???? 4/21/2005 8:42 AM | sanhel

# re: Reporting Services Report Parameters

Gravatar Are you referring to the parameter drop down that is provide by RS or one that you add to a form?

Gerry 4/23/2005 3:56 PM | Gerry

# Q: Reporting Services Report Parameters

Gravatar i have 2 questions:
1- how can i add a calender to a parameter from type datetime ?
2- how can i change the format of date from
mm/dd/yyyy TO dd/mm/yyyy and use it in parameter ?
can anyone help me quickly .. 4/24/2005 7:20 AM | Nezar

# re: Reporting Services Report Parameters

Gravatar For the first question, add a calendar control to the page you are taking parameters from. Get the date from that calendar in the click event and pass it as a parameter to the report.

For question #2, the date format is controlled by regional settings on the computer that is executing it.

Gerry 4/24/2005 12:27 PM | Gerry

# re: Reporting Services Report Parameters

Gravatar Dear Gerry:
there is no Calender Control in RS.
its a bug in RS.
could u provide me with alternative solution plz.

for the second question, thank u very much 4/25/2005 8:03 AM | Nezar

# re: Reporting Services Report Parameters

Gravatar You're right, sorry. I thought you were trying to get the date from another form to pass as a parameter to the report.

I wouldn't call it a bug necessarily as perhaps a feature that should be added. You have no control over dates in RS because of this. You must use the dates in the format that computer's cultureinfo specifies.

Gerry 4/25/2005 8:46 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar I'm not certain that is possible. Reporting Services provides the controls that you use for parameters from basic .NET controls but doesn't implement the entire functionality behind those controls. I have not found a way yet to enable multi-select on a list box.

This would require you to do some parsing prior to the parameters getting passed to the report as you will have more than one parameter coming from the control. RS expects one parameter per control.

Gerry 4/25/2005 8:50 AM | Gerry

# Reporting Services Report Parameters (rendring through web form)

Gravatar Hi,

I have been playing with Reporting Services for sometime now and it seems to work great so far. I am now trying to render these reports into a web application. I am using the web service to render them. However, when I try to pass my paramters, it gives an "System.NullReferenceException: Object reference not set to an instance of an object.
" exception. The following is the code I am trying to use.

ParameterValue[] parameters = new ParameterValue[3];
parameters[0].Value = "20055";
parameters[1].Value = "000047";
parameters[2].Value = "83";

Can someone please help. It seems like I am not defining my paramters correctly.

Regards. 4/25/2005 5:58 PM | Moosa

# re: Reporting Services Report Parameters

Gravatar Actually i got it fixed. it should be like this.

ParameterValue p1 = new ParameterValue();
p1.Name = "<parameter name>";
p1.Value = "20055";

ParameterValue p2 = new ParameterValue();
p2.Name = "<parameter name>";
p2.Value = "000047";

ParameterValue p3 = new ParameterValue();
p3.Name = "<parameter name>";
p3.Value = "83";

ParameterValue[] parameters = new ParameterValue[3];
parameters[0] = p1;
parameters[1] = p2;
parameters[2] = p3; 4/25/2005 6:49 PM | Moosa

# re: Reporting Services Report Parameters

Gravatar Re post from Gerry on 4/25 8:50 a.m.
Hi. I am just starting to learn RS and need to rewrite a large number of reports that currently have a web front end to let the user select report criteria prior to running the (Crystal) report.

I want to eliminate the web front end and instead have an RS form collect these user choices. Making an RS form listbox multiselect enabled is critical since almost every report has one or more listboxes where the user can optionally select multiples. How could MS omit something this basic? Since MS RS is supposed to be extensible by adding custom program code, is there anyway that you could add code that would assemble the multiple selected codes into a single string that can then be the 'single' parameter for that control? Any ideas?

4/29/2005 6:44 PM | Bob Fitz

# re: Reporting Services Report Parameters

Gravatar Hi Moosa I'm rendering my RS by the web service and adding the paramethers like u've shown here , but a error is being threw "The report parameter 'parameterName' is read-only and cannot be modified" Can you help me?! hhehehe Thanks 6/3/2005 3:06 PM | Diego

# re: Reporting Services Report Parameters

Gravatar HOW CAN I SET THE REPORT PARAMETER THAT IS A LISTBOX TO BE MULTI SELECT ENABLED 6/14/2005 1:33 PM | kate

# re: Reporting Services Report Parameters

Gravatar HOW CAN I SET THE REPORT PARAMETER THAT IS A LISTBOX TO BE MULTI SELECT ENABLED ..
Can you help me ASAP ?! Thanks 6/14/2005 1:35 PM | kate

# re: Reporting Services Report Parameters

Gravatar How do you pass a parameter from Reporting Services to SQL query (Dataset) for ORDER BY clause 6/22/2005 11:56 AM | RP

# re: Reporting Services Report Parameters

Gravatar rc:parameters=false....it works....thank you so much, Gerry for solving my problem. 7/8/2005 4:50 AM | smy

# re: Reporting Services Report Parameters

Gravatar I desperately need to find a solution to a parameter issue. I've read through all of the above posts and tried to implement some of the suggestions to no avail.

I have an HTML page where I'm sending the a parameter in the URL string. I have rc:Parameters=False and have edited the xml data to a have a blank prompt.

I have unchecked the Prompt checkbox on the report and also cleared the Prompt String in the report server.

Here is the error I receive.
Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError)

ANY help is appreciated as this report must not give the user the option to enter a parameter 7/9/2005 10:10 AM | yrmeyer

# re: Reporting Services Report Parameters

Gravatar I have problems with parameter DateTime format on Reporting services 2005 beta. Date format on my PC is dd.mm.yyyy and same on IIS server.
Its ok if I use Reporting portal URL:
http://<My Server>/Reports/Pages/Report.aspx?ItemPath=%2fTimeSheet%2fDailyReport
but if I use URL:
http://<My Server>/ReportServer/Pages/ReportViewer.aspx?%2fTimeSheet%2fDailyReport&rs%3aCommand=Render
its not ok, date format is mm/dd/yyyy.

Thx for any idea
7/12/2005 6:17 AM | Tomaz

# re: Reporting Services Report Parameters

Gravatar Hello All!
I have a problem with hiding 2 of 3 report parameters. How could I send 2 report parameters in URL and provide user with possibility to select remaining one?

I've tried to realize this, but after selecting remaining parameter and clicking 'View report' I just see nothing, no errors and no report. 7/14/2005 6:37 AM | Timur

# rc:Parameters:false Not Working!?

Gravatar I have a form that calls a report, and passes several parameters to it. I simply want to hide the parameters when the report is displayed.

I've added &rc:Parameters:false to the end of the URL and it has no effect. Everything else with the report works great.

I have NO default values on the parameters.

Any help is appreciated.

-Scott 8/4/2005 9:36 AM | Scott

# re: rc:Parameters:false Not Working!? 8/4/2005 9:36 AM Scott

Gravatar OOps I mean I have &rc:Parameters=false in my URL. 8/4/2005 9:37 AM | Scott

# re: Reporting Services Report Parameters

Gravatar I am trying to call a report using URL method by doing form post method. I have some document map in my report. I am running into the issue where document map does not show in the report output. Also, I want to keep the toolbar visible but I don't see the toolbar (with all the exporting options etc) either. Can someone please help?

8/8/2005 7:17 AM | Khan

# re: Reporting Services Report Parameters

Gravatar I am trying to hide 1 parameters out of 4 in the reports. This paremeter will come from url while the other parameters will be calculated depending on the first parameter value. Any ideas would be highly appreciated.... 8/8/2005 3:29 PM | Aditya

# re: Reporting Services Report Parameters

Gravatar Hi,

Is it possible to embed a fillable web form in the body of a report. I would like to collect information from the subscribers of the report.

Thank You,

Roman 8/16/2005 1:07 PM | Roman

# re: Reporting Services Report Parameters

Gravatar I found when rendering reports via the webservice I would run into the rsReadOnlyReportParameter error when a param was set to not prompt the user. I use this code to check the report parameters for this condition prior to calling the render method via the webservice. It basically looks for this condition and changes the PromptUser property = True and the Prompt="" (an empty string) so it doesn't display in the report manager.

Dim reportParams() As ReportParameter
reportParams = rs.GetReportParameters(ReportPath, Nothing, True, Nothing, Nothing)

' to get around the rsReadOnlyReportParameter exception that gets thrown when
' the 'PromptUser' property of a parameter is set to false, loop through all the
' parameters and if they PromptUser is false set it to true but then set the
' Prompt property = to an emptry string "" so that the parameter does not
' display in the report manager
Dim rp As ReportParameter
For Each rp In reportParams
If rp.PromptUser = False Then
rp.PromptUser = True
rp.Prompt = ""
End If
Next
rs.SetReportParameters(ReportPath, reportParams) 8/24/2005 9:42 AM | Nick @ INDATA

# re: Reporting Services Report Parameters

Gravatar THanks Robert Koernke for tip to turn off individual report parameters from showing
The DEsigner bites back if, after typing <Prompt></Prompt> in the RDL Code, you dare just to CLick Report>REport Parameters in the Designer: as you Click OK it wipes out your <Prompt></Prompt> from the RDL anf you're back to square 1.

So: Switching off the prompts in the RDL is the last thing you do to report parameters - a bit like setting your burglar alarm before leaving home! 9/2/2005 3:33 AM | Dan

# re: Reporting Services Report Parameters

Gravatar Is there any way to have multiple select like a listbox in the parameter dropdown. 9/12/2005 6:55 AM | Topgun

# re: Reporting Services Report Parameters

Gravatar I have a similar issue like the one posted by Gregory S. Hill

I'm creating a report that does some calculation for all the months. I wrote a Stored Proc that does it and takes Month as a Parameter and created a dataset for it. But i have to execute the same SP for all 12 months, means 12 times in the same report with different month as a Parameter. Does it mean i have to create 12 datasets with different Report Parameters ? Is there any other better way to do it like automating Parameters in a single dataset ?

Thanks. 10/3/2005 12:31 PM | Girija

# Hiding Report Parameters

Gravatar Hiding the report parameters by deleting the prompt string in the RDL will not work unless you have service pack 1 or 2 installed. 10/14/2005 9:17 AM | Mark Fruhling

# re: Reporting Services Report Parameters

Gravatar Girija,
Perhaps you could build a report with your report as 12 separate subreports. Each subreport can supply a different parameter. When you run the main report, you will run all 12 subreports. Or, maybe, can your stored proc accept the entire year, then RS can page the resulting data by month. 11/3/2005 8:43 AM | Jusme

# re: Reporting Services Report Parameters

Gravatar The report parameters can be hidden by having a space(do not have it empty , press space bar) in prompt string 11/11/2005 1:03 PM | Jack

# re: Reporting Services Report Parameters

Gravatar In response to Gregory S. Hill and others asking about using a single stored procedure accessed by multiple datasets on a report, passing a dynamic parameter can be done by going to the report menu and adding new report parameters. Make one for each dataset that you've created that requires different parameter values. For each new report parameter, make the parameter "non-queried" and set the expression value to what you want.

Then, go back to your datasets, go the the parameters tab and in the value field, you'll notice your newly-created parameters in your available drop-down parameters. At this point, select the parameter specific to your dataset (I would name my parameters similar to my dataset to avoid confusion).

The report should run and set the parameters individually, now.

I had this same problem, and with no help from anyone, figured this out after long hours of "playing." I hope this helps someone!

(Note: if the parameter is based off another query within the report, this method can not be done unless the queried parameter is in a subreport. You can also choose a "queried" parameter in the report parameter properties for you new parameter to get a dynamic parameter from the report.)
1/24/2006 11:37 AM | Stuart

# size Limit on Parameters

Gravatar I would like to know for sure,
whether there is a size limit on parameters or not?


Any help is greatly appeciated.

Anil 1/26/2006 4:29 AM | Anil

#  Sql Injection in Reporting Services Report Parameters

Gravatar Hello,
I have web based application which used sql reporting services to generate web based reports. My application has dynamic columns reports. Entire application get displayed in a pop up window ,hence there is no problem with sql injection. But while I do import to excel or any other format it opens another b
rowser where I found the Sql injection (sql clause).This can be achieved if there is any fascility to use form post method. If this is possible then please let me know the configuration or any other solution to do not display parameteres value in querystring.
Help me. Thanks in advance. 1/26/2006 8:25 PM | Pramod Pawar (email: pramod3681@

# re: Reporting Services Report Parameters

Gravatar One of the reports I'm working with in SRS 2005 (RTM) currently
produces:

--
w3wp!library!1!01/27/2006-14:24:28:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. ---> System.NullReferenceException: Object reference not
set to an instance of an object.

--

when trying to run this specific report only,
while running from the reporting server.

This report works correctly about 50% of the time, but the other
times, generates the null reference error as shown. Interestingly
enough, running the exact same .rdl report file in 2 other SRS 2005
environments produces no errors.

Also, on the server that does produce errors, this is the only report
that does so - other reports generate correctly when run. We're not
using any subreports.

The config file for reporting services is rather default, and has the
same memory/timeout/etc values as other SRS deployments in our test
environment.

Any help would be very much appreciated.!!!! 1/31/2006 3:01 PM | Joshua (email: joshuaga@avanade.

# re: Reporting Services Report Parameters

Gravatar Hello everyone,

I have a web application in VBScript/ASP. I need to pass parameters to Reporting Services 2005 report. I also need to have the parameters hidden in the URL so the users can not change their value, or use any other method of passing the parameter to the report.

I am new to Reporting Services and just can not find any help about it.

Thank you.
2/22/2006 3:53 PM | Asad

# re: Reporting Services Report Parameters

Gravatar When you pass parameters to RS and you don't want them to be seen by the users, you can implement the POST method rather than the GET method for your form action.

This will still not restrict a knowledgeable user from changing them. If they are familiar with HTML, they can simply to a View/Source from the browser and see what the query string looks like. They can then generate their own request in the address bar by typing in a URL with the parameter names and values of their own choosing.

Not sure if anyone else has found a way to hide the parameters but I haven't found one yet.

Gerry 2/22/2006 4:02 PM | Gerry

# re: Reporting Services Report Parameters

Gravatar Need some help desperatly.
I have several drop down lists but an issue has come to light where they would like to pick more than one item from each list how do i do this???? Please help.

regards Jason 2/24/2006 3:08 AM | Jason

# re: Reporting Services Report Parameters

Gravatar I don't monitor this board, but thought that this method may help. I've implemented the reports behind ASP.Net webpages, using a ReportViewer control. I then:

Dim params As New System.Collections.Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
Dim param As Microsoft.Reporting.WebForms.ReportParameter

' Begin Repeat

param = SignalServerSide.GetReportParameter(params, "NumberDays")
param.Values.Clear()
param.Values.Add(90)

' Repeat Above for ALL parameters

Me.ReportViewer1.ServerReport.SetParameters(params)

Public Shared Function GetReportParameter(ByRef paramList As Collections.Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter), ByVal paramName As String) As Microsoft.Reporting.WebForms.ReportParameter
Dim param As Microsoft.Reporting.WebForms.ReportParameter
For Each param In paramList
If param.Name = paramName Then Return param
Next
' if we got here then it's not in there yet
param = New Microsoft.Reporting.WebForms.ReportParameter(paramName)
paramList.Add(param)
Return param
End Function

This system works well for me - only took me most of the day to figure this out! MS should consider putting out a book on this kind of thing, I know that others are using RS for extranet reporting yet can't/won't add everyone to AD who uses the website... 3/3/2006 12:56 PM | Chris Wiegand

# Re: Reporting Services Change connectionstring

Gravatar Has anyone had any luck changing a report's connection string through code? (using the ReportViewer control)

Gerry's advice of changing the connection string when the user logs in isn't good because the application's connection string and the report's connection string may not be the same.

I thought the SetDataSourceCredentials() method would do it, but it doesn't, or at least it doesn't for me.

Scott. 3/6/2006 10:45 AM | Scott

# heterogeneous reporting

Gravatar I'm trying to use ALL the results from a dataset in the query of another dataset and I can't seem to figure it out.

similar to select * from table where ("values from other dataset")

Because the other dataset is consider a parameter I get a drop down to select from and plug into my query. I would like to see all the results.

Can anyone help? Thanks. 3/10/2006 5:23 AM | Phil

# re: Reporting Services Report Parameters

Gravatar a parameter as DateTime returns always mm/dd/yyyy hh:mm:ss AM or PM, i just want to get the mm/dd/yyyy, i need this type of format cause im using oracle's to_char(<date>,'mm/dd/yyyy')

thanks
4/17/2006 6:25 AM | Oz

# re: Reporting Services Report Parameters

Gravatar Please, advise!!! Using Reporting Service() Render method I pass five parameters and the report produces: --->System.Web.Services.Protocols.SoapException: Error during processing one of the report parameters. It complains of the only parameter. Additional info: it does not accept a parameter that originally(in the report) is chosen from a data set using usp_ and there is no a default value.
I have a combobox with the same data on the form I am working with; so, I get a value and a label from there and then pass it. Here is how I pass the params:
ParameterValue[] parameters=new ParameterValue[5];
parameters[0]=new ParameterValue();
parameters[0].Name="pDateOOS";
parameters[0].Value= this.datDateOOS.Value.ToString();
parameters[1].Name="pPortfolioID";
parameters[1].Value=this.cmbDealPortfolio.SelectedValue.ToString();
this.PortfolioID=Convert.ToInt32(this.cmbDealPortfolio.SelectedValue.ToString());
DataRow SelectedRow=(DataRow)this.dsDealPortfolioList.Tables["DealPortfolioList"].Select(String.Format("portfolioid={0}",this.PortfolioID.ToString())).GetValue(0);
parameters[1].Label= SelectedRow[1].ToString(); ........
Please,help! 5/3/2006 10:44 AM | msu

# Creating Reports using Analysis Services Data Source

Gravatar I'm currently using RS 2005 with an analysis services data source. Now, what I'm trying to do is a simple parameter pass in the URL after I have built and deployed my report. Listed below are things I've already tried:

1) Adding the parameter to the report, leaving the default value NULL and unselecting the multiple value option. I then go to http://localhost/ReportServer?/Folder/ReportName&rs:Command=render&paramName=SomeValue. After the page loads, the drop down box for the parameter appears, but without "SomeValue" being selected. In other words, my passed value in the URL was ignored, but no error message was received.
2) Repeated step #1 but kept the prompt for the parameter hidden.
3) Repeated step #1 but made the parameter internal.

Needless to say after trying all these methods, nothing has worked. Any ideas? 6/7/2006 10:08 AM | Joel

# re: Reporting Services Report Parameters

Gravatar Gerry:

Create a Linked Report (based on your original called report) in the browser - then edit this and Hide the parameter(s) (rather than uncheck Prompt User). That's using RS2005, not sure about 2000 though. This should do the trick. You can hide the original and/or linked report too of course.

Hope that helps!
6/8/2006 1:20 AM | Chris

# re: Reporting Services Report Parameters

Gravatar Actually, it doesn't have to be a linked report, but for my purposes it suits because I want to run a report from another report as well as independantly. It's nice not to have to display the parameters when running the second report through the link. 6/8/2006 11:02 PM | Chris

# re: Reporting Services Report Parameters

Gravatar Hi! About setting parameter type datetime from format mm/dd/yyyy to dd/mm/yyyy, i have searched a lot but cannot do in RS. There is just one way to do that is : set CultureInfo in aspx (Me.Culture = "en-AU" example of Australia Culture written in aspx when Page_Load) . I think this suggest could help you!
I got a problem, I want to remove the default value of RS Parameter (Value:"<Select a Value>") but don't know how. Is anyone here can help me out? If you can, please send an email to me: vt3hiepbee@yahoo.com . Thank a lot
6/23/2006 1:19 AM | Henry

# re: Reporting Services Report Parameters

Gravatar I have a problem too? I have a web app, which a user can use an advanced search, enter a keyworkd the can select from year and to year, ect. the user is then presensented with a results page, from there they click on generate report which gives the an option to group by the report by e.g., program, fiscal year, state etc. How, can I generate a report that passes in those parameters and will reflect the search criteria and they entered along with the grouping selection they chose. I'm usining RS 2005 and VS .net 1.4, The advance search and group by was done using C#. I have no clue and have been baning my head agains a brick wall. 7/12/2006 3:02 AM | MIZOD

# re: Reporting Services Report Parameters

Gravatar In your article you wrote: hiding the parameters is don by rs:parameters=false but it should be rc: parameters = false.

7/24/2006 4:24 AM | Adrian

# re: Reporting Services Report Parameters

Gravatar To Gerry and sanhel:
Passing multiple parameter values to the report viewer via the URL is possible if you just repeat the parameter key-name pair several times. Like this:
http://xxxx/ReportServer?/TestReport&Param1=A&Param1=B&Param1=C&Param2=1&Param2=2&Param2=3&rs:Command=Render&rc:Zoom=Whole Page%&rc:Toolbar=true&rc:Parameters=false

Param1 and Param2 must be defined as multi-value in the report designer (VS).

I struggled a lot with this one. I hope this helps!

Kaarew 8/11/2006 5:40 AM | Kaarew

# re: Reporting Services Report Parameters

Gravatar could any one help me, how can increase width size of parameter dropdown in SSRS 2005......upendrap@aztecsoft.com 8/15/2006 9:55 PM | upendra

# re: Reporting Services Report Parameters

Gravatar You don't have hide the parameters. If you leave the prompt checkbox checked, BUT remove the text from the prompt, RS will allow you to pass the parameter in the URL, but not display the control in the parameters bar at the top of the ReportViewer window. This will allow you to pass some parameters, but leave others for the user to select when running the report. 8/18/2006 4:10 PM | Philip

# Reporting Services(2000) Report Parameters

Gravatar I am trying to set single parameter from to fields i.e. FirstName+LastName are two fields in the table and i want to set a parameter like "Name" which joins those two fields.
Is it possible in 2000 version?
Thanks, 8/24/2006 4:27 AM | Raja

# re: Reporting Services Report Parameters

Gravatar Is it possible to increase the size of Textbox or dropdown in Report Parameter?
Thanks 8/25/2006 1:31 AM | Bab

# re: Reporting Services Report Parameters

Gravatar Has anyone knows how to chang a report's connection string through code? (using the ReportViewer control)


Please mail me if any one get the solution
Mail Id : Vinodagrawal80@hotmail.com

Vinod
10/7/2006 12:25 AM | Vinod Agrawal

# re: Reporting Services Report Parameters

Gravatar for Raja

You probably have worked this out but anyway - this does not have to be a parameter - in layout view

=Fields!Firstname.value & " " & Fields!Lastname.value
10/17/2006 10:54 PM | jewelfire

# re: Reporting Services Report Parameters

Gravatar Hi Gerry, Great info on the Reporting Services Report Parameters problem.

rc:parameters=false -solved my problem.

BR,
Frederick
10/25/2006 1:29 AM | Frederick

# re: Reporting Services Report Parameters

Gravatar Hi,
I have a web app which is using RS 2005. Now I have deployed it to production server and it's start giving error. error says.
An error occurred during rendering of the report.
Specified argument was out of the range of valid values.

Any help pls. It's urgent.

Amit
11/11/2006 9:09 AM | Amit Shrivastava

# re: Reporting Services Report Rendring

Gravatar Hi,
I have a web app which is using RS 2005. Now I have deployed it to production server and it's start giving error. error says.
An error occurred during rendering of the report.
Specified argument was out of the range of valid values.

Any help pls. It's urgent.

Amit
11/11/2006 9:11 AM | Amit Shrivastava

# re: Reporting Services Report Parameters

Gravatar Does it give you any indication as to which argument is out of range?

Can you look at the query string to validate the data that is passed to the report? 11/11/2006 9:32 AM | Gerry

# re: Reporting Services Report Parameters

Gravatar No, It doesnt give me any indication to which argument is out of range. I can look the query string which is passed via sql profiler and it is correct, as i can run the same query in query analyzer succesfully. even my reports works fine through localhost\report.
Whether running from web app or localhost\reportt, in both the cases filred query is same in sql profiler.

Amit 11/11/2006 8:27 PM | Amit

# re: Reporting Services Report Parameters

Gravatar This is the piece of code I am using -
rptReport.ServerReport.ReportServerUrl = new Uri(cm_strSIPReportServer);
rptReport.ServerReport.ReportPath = cm_strSIPReportPath;
ReportParameter[] parameters = new ReportParameter[4];
parameters[0] = new ReportParameter("dteWeek_End_Date", "11/09/2006");
parameters[1] = new ReportParameter("intEmployee_Id", "-1");
parameters[2] = new ReportParameter("intSuperintendent_Employee_Id", "-1");
parameters[3] = new ReportParameter("intOffice_Id", "-1");
rptReport.ServerReport.SetParameters(parameters);

What else is requried or what can cause the error.
11/14/2006 2:29 AM | Amit

# re: Reporting Services Report Parameters

Gravatar Hi,
This is related to hiding the parameters in RS2000.I am using 3 parameters out of which one I want to make hidden.For that I checked the prompt user check box in report manager parameter properties and making promt string textbox clear.When I call this report file from ASP.net web page using report viewer control I get to see two prompted