Pre-defined lists of values as report parameter input

Has anyone come up with a solution for the following scenario yet?
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>

POSTing report parameters

We are having an issue POSTing report parameters to reporting services. One
of our report parameters can be very long (hundreds or even thousands of
characters) and is far to long to pass on a URL. Our solution has been to
POST the parameter, which does work, but not quite as we want it to. We only
need to provide a single parameter to the reprt as all the others have
default values.
The report generates and displays results as expected, however the parameter
selection and report navigation section is not visible on the report that is
rendered.
If we POST the parameters, is it possible to get the parameter selection and
report navigation sections to show? and, if so, how? The report is being
called from a custom app which passes the parameter to the report.
This is how we call our report:
http://sidev/ReportServer?/tbi/development/Report200_GIS
The POST data is:
XMLFarmIDs=<TbI>....</TbI>I've done this in the past by putting a hidden element in the form I'm
posting equivalent to the HTML Viewer command.
In my post form, I'll put this:
<input type=hidden name="rc:Parameters" value="false">
Andy Potter

PostBack while selecting a parameter

Hi,

I'm working on a report having 2 date parameters(which uses calendar control) and a dropdownlist. But on selecting each of these parameters, the page refreshes. For eg On selecting a date from the calendar control results in a postback. The same is the case with the dropdownlist. Could you please help to resolve this issue? We need the postback to happen only on clicking the 'View Report' button.

Also, is there any way to customize the 'View Report' button. It always appears in the right hand side. Can we set the position of this button so that it appears just below the paging button?

Thanks in advance,

Sonu.

1. No, it's not possible to avoid postbacks when you enter parameters one by one.

2. There is no way to customize the position of the button but you can change the style of the button in your report manager by using the ReportingServices.css file in the following folder (probably):

C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager\Styles

Please refer to more details in the following link:

http://msdn2.microsoft.com/en-us/library/ms345247.aspx

Shyam

sql

Post method in ASP stops working

I am having an issue on a windows 2003 box where I run a report to a new
window, and I do a request.form to get all my parameters. The first time I
run a report to a new window I am prompted for the windows user name and
password. Once I enter in the login info my report runs fine. Then when I try
to run another report to a new window the request.form doesn't work to pull
things in from my form. Everything is blank, so I get error messages. Another
report will not run until I shutdown IE restart IE log back into my website
and run the report.
My redirect string is
http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
"&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
Can anyone tell me why the post method or Request.form will only work once
and then I have to restart IE. Again I am opening the report in a new window.
--
Thanks,
CraigI fixed this by going into IIS and going into the properties on the Reports
and ReportServer Virtual directories and under Directory security enabling
anonymous access.
"Craig" wrote:
> I am having an issue on a windows 2003 box where I run a report to a new
> window, and I do a request.form to get all my parameters. The first time I
> run a report to a new window I am prompted for the windows user name and
> password. Once I enter in the login info my report runs fine. Then when I try
> to run another report to a new window the request.form doesn't work to pull
> things in from my form. Everything is blank, so I get error messages. Another
> report will not run until I shutdown IE restart IE log back into my website
> and run the report.
> My redirect string is
> http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
> "&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
> Can anyone tell me why the post method or Request.form will only work once
> and then I have to restart IE. Again I am opening the report in a new window.
> --
> Thanks,
> Craig

POST BACK TO SERVER

hi all,

i want to filter data from a database using parameters supplied by the user via textboxes. i've been able to write the select statement. my problem now is, the code behind for the "view data" button. do i do "sqldatasource1.select" orpost the databack to theserver? if i'm topostback to theserver, whats the code i should use?

protected void button1_Click(object sender, Eventargs e)

{

????

}

---

I guess it depends. Are you simply displaying data within something like a GridView? If so, then just use GridView.DataBind() and set your Parameters within the SqlDataSource.Selecting event. You could also set up your Parameters to be ControlParameters and point them directly to your TextBoxes.

|||

well i had done that already. it was just the code behind i needed. i didnt put any code and at runtime i clicked the button and it posted to the server. so i guess thats all i need. thanks for the input though

Possible?: Count(*) returned by EXEC

Hi all,

I have a stored procdure which does a select and returns the records
directly -i.e. Not in output parameters e.g:

CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS

SELECT *
FROM MyTable
WHERE [Name]=@.ProductName

In another stored procedure I need to do the following:

SELECT COUNT(*)
FROM MyTable
WHERE [Name]=@.ProductName

As the select queries are actually a lot more complex that this, I'd
rather not duplicate the select code in 2 sp's to save the maintenance
effort - I'm looking for a way to execute the first procedure from the
second and just count the records returned - something like:

SELECT Count(*)
FROM EXEC up_SelectRecs @.ProductName

Any way to achieve this?

Thanks all

--James"James" <Jamesmitchard@.yahoo.co.uk> wrote in message
news:19d01a84.0501261535.1d7c6dd7@.posting.google.c om...
> Hi all,
> I have a stored procdure which does a select and returns the records
> directly -i.e. Not in output parameters e.g:
> CREATE PROCEDURE up_SelectRecs(@.ProductName nvarchar(30)) AS
> SELECT *
> FROM MyTable
> WHERE [Name]=@.ProductName
> In another stored procedure I need to do the following:
> SELECT COUNT(*)
> FROM MyTable
> WHERE [Name]=@.ProductName
> As the select queries are actually a lot more complex that this, I'd
> rather not duplicate the select code in 2 sp's to save the maintenance
> effort - I'm looking for a way to execute the first procedure from the
> second and just count the records returned - something like:
> SELECT Count(*)
> FROM EXEC up_SelectRecs @.ProductName
> Any way to achieve this?
> Thanks all
> --James

See here:

http://www.sommarskog.se/share_data.html

If you have SQL 2000 (you didn't mention which version you have), a
table-valued UDF would probably work well in your case:

select * from dbo.MyFunc(@.ProductName)
select count(*) from dbo.MyFunc(@.ProductName)

Simon

possible to spoof report parameters?

Sorry if this has been asked before, but the searches I tried didn't turn anything up.

My question is, is it possible for a user to spoof report parameters in order to get a report to run for values that would normally be unavailable to that user?

For example, let's say we have a report that has a drop-down parameter containing a list of account numbers that the user is allowed to see data for. (The query that populates the dropdown itself uses the reporting services login to appropriately filter the list of accounts.) Would it be theoretically possible to write a custom http client that injected its own value for the account # parameter and have the report run for an account that was not intended for that user? Or is this somehow prevented in some way (perhaps the viewstate?).

Note, I am not the potential hacker, I would like to know if I need to add some other security mechanism to all of our reports.

By the way, we are using RS 2005.

ThanksUse stored procedures to prevent SQL injection attacks.|||I'm sorry, I shouldn't have used the word "inject" in my original question. I am not speaking about a traditional SQL injection attack, I'm only talking about spoofing an http client to post arbirtrary http-parameter values in runing the report. Thanks for the reply tho.

possible to spoof report parameters?

Sorry if this has been asked before, but the searches I tried didn't turn anything up.

My question is, is it possible for a user to spoof report parameters in order to get a report to run for values that would normally be unavailable to that user?

For example, let's say we have a report that has a drop-down parameter containing a list of account numbers that the user is allowed to see data for. (The query that populates the dropdown itself uses the reporting services login to appropriately filter the list of accounts.) Would it be theoretically possible to write a custom http client that injected its own value for the account # parameter and have the report run for an account that was not intended for that user? Or is this somehow prevented in some way (perhaps the viewstate?).

Note, I am not the potential hacker, I would like to know if I need to add some other security mechanism to all of our reports.

By the way, we are using RS 2005.

ThanksUse stored procedures to prevent SQL injection attacks.|||I'm sorry, I shouldn't have used the word "inject" in my original question. I am not speaking about a traditional SQL injection attack, I'm only talking about spoofing an http client to post arbirtrary http-parameter values in runing the report. Thanks for the reply tho.

Possible to change parameter layout?

Is it possible to force the parameters to line up differently? When I have 3
parameters in a report, they will show up with 2 on the top line and 1 on a
new line. I'd rather have all 3 on the same line. Can I change the layout,
without having to code a new display form?
Kaisa M. LindahlNo, this is not possible in RS 2000. You would need to write your own
parameter front-end to achieve full control over the layout.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:emNGccY9EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Is it possible to force the parameters to line up differently? When I have
3
> parameters in a report, they will show up with 2 on the top line and 1 on
a
> new line. I'd rather have all 3 on the same line. Can I change the layout,
> without having to code a new display form?
> Kaisa M. Lindahl
>

possible setup.exe exit codes?

As part of our application deployment we have wrapped and automated the SQL Server Express install by launching the setup.exe with our parameters and waiting around for the process to exit.

I had been checking for a Exit Code != 0 to detect errors returned by setup.exe but this does not appear to be foolproof. Several customers' deployments have returned non-zero exit codes, but SQL Express seems to be successfully installed.

I have not found a reference of possible codes returned and their meaning that I could use to fine tune our checking. Does one exist? Or am I going about this the wrong way and should just check for the existence of our instance after setup is complete?

thank you for any input.

I *think* the only other success error code you need to code around is error code 3010, which signifies success, but a reboot is necessary.

Here is a list of other possible Windows Installer error codes:

http://msdn2.microsoft.com/en-us/library/aa368542.aspx

Thanks,
Sam Lester (MSFT)

Possible pass parameters on "Jump to URL" properties?

Hi,

I use the " JUMP to URL" properties and the code as follow:

=SWITCH(Fields!KpiCode.Value=Fields!KpiCode.Value,void(window.open('"+First(Fields!ServerString.Value,
"HostInfo")+"/L3/"+Fields!KpiCode.Value+".aspx','"+First(Fields!PropertyString.Value,
"HostInfo")+"'))")

I want to know is any way or any possible way could pass the report
parameter on this kind of porperties or this code?
For some requirement, I just can use Jump to URL, but hope can pass the
parameters on "jump to url" properties.

Thanks for any advice!
Angi

Similar to Fields, you can pass parameters.

Parameters!ParameterName.Value

|||

Brad,

Thanks for reply!

It's my mistake didn't descript clearly.

This code on IE status bar will generate as

=void(window.open('http://anginb:8022/L3/Bu01301200.aspx','_blank','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbar=yes,resizable=yes,copyhistory=no'))"

And what I want is pass the report parameter throght this .aspx url like this:

=void(window.open('http://anginb:8022/L3/Bu01301200.aspx?puog=01','_blank','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbar=yes,resizable=yes,copyhistory=no'))"

puog is a report parameter!

Could I use report parameter on url as a url parameter?

Thanks for advice!

|||Sure, this is possible, look in the BOL qor the online version [1], there are samples about passing URL parameters.

[1] http://msdn2.microsoft.com/en-us/library/ms153586.aspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

you can pass report parameter in the URL if you are using URL Access of Reporting Services.

Basically, when using URL Access to visit the report, you need to first specify the report server virtual directory An example looks like:

http://localhost/ReportServer?

After that, you need to append the path of the report to the URL. Assume you have installed the sample report and wants to view the Employee Sales Summary report:

http://localhost/ReportServer?/SampleReports/Employee Sales Summary

And then, you need to append a report command so that the Report Server knows that you want to render the report:

http://localhost/ReportServer?/SampleReports/Employee Sales Summary&rs:command=render

Finally, you can append the value of the parameter to the URL:

http://localhost/ReportServer?/SampleReports/Employee Sales Summary&rs:command=render&EmpID=20&ReportMonth=12&ReportYear=2003

|||

Jens, Yicong,

Thanks a lot!

Simply!

Could I use any parameter after http://localhost:8022/L3/Bu01301200.aspx ? and how to do ?

Thanks!

|||If this is a external (non reporting services) page you have to grab those paramters through the QueryString collection and pass it again to the reporting services parameter like mentioned above.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for Jens.|||

Can anybody plz tell me how to pass multiple values in open() method to next page,

Actually I want to know the seperator between the different parameter passed

the code i wrote is somewhat this kind

function addr()
{
open("edoc_add.asp?Docno=<%=DaNo%> Address=<%=Daln%> Pin=<%=DaPin%> Tel=<%=DaTel%> Mob=<%=DaMob%> Mark=<%=DaRm%> mail=<%=Damail%> url=<%=DaUrl%>","AddressInfo","toolbar=No,StatusBar=No,Resize=Yes,Scrollbars=Yes,width=600,height=400")
}

I m calling it on click event of anchor tag.

plz reply soon.............

thnkx in advanced.

|||

Its alway the paramter name:

ParamName=1&ParamName=2 ...

Jens.

Possible pass parameters on "Jump to URL" properties?

Hi,

I use the " JUMP to URL" properties and the code as follow:

=SWITCH(Fields!KpiCode.Value=Fields!KpiCode.Value,void(window.open('"+First(Fields!ServerString.Value,
"HostInfo")+"/L3/"+Fields!KpiCode.Value+".aspx','"+First(Fields!PropertyString.Value,
"HostInfo")+"'))")

I want to know is any way or any possible way could pass the report
parameter on this kind of porperties or this code?
For some requirement, I just can use Jump to URL, but hope can pass the
parameters on "jump to url" properties.

Thanks for any advice!
Angi

Similar to Fields, you can pass parameters.

Parameters!ParameterName.Value

|||

Brad,

Thanks for reply!

It's my mistake didn't descript clearly.

This code on IE status bar will generate as

=void(window.open('http://anginb:8022/L3/Bu01301200.aspx','_blank','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbar=yes,resizable=yes,copyhistory=no'))"

And what I want is pass the report parameter throght this .aspx url like this:

=void(window.open('http://anginb:8022/L3/Bu01301200.aspx?puog=01','_blank','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbar=yes,resizable=yes,copyhistory=no'))"

puog is a report parameter!

Could I use report parameter on url as a url parameter?

Thanks for advice!

|||Sure, this is possible, look in the BOL qor the online version [1], there are samples about passing URL parameters.

[1] http://msdn2.microsoft.com/en-us/library/ms153586.aspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

you can pass report parameter in the URL if you are using URL Access of Reporting Services.

Basically, when using URL Access to visit the report, you need to first specify the report server virtual directory An example looks like:

http://localhost/ReportServer?

After that, you need to append the path of the report to the URL. Assume you have installed the sample report and wants to view the Employee Sales Summary report:

http://localhost/ReportServer?/SampleReports/Employee Sales Summary

And then, you need to append a report command so that the Report Server knows that you want to render the report:

http://localhost/ReportServer?/SampleReports/Employee Sales Summary&rs:command=render

Finally, you can append the value of the parameter to the URL:

http://localhost/ReportServer?/SampleReports/Employee Sales Summary&rs:command=render&EmpID=20&ReportMonth=12&ReportYear=2003

|||

Jens, Yicong,

Thanks a lot!

Simply!

Could I use any parameter after http://localhost:8022/L3/Bu01301200.aspx ? and how to do ?

Thanks!

|||If this is a external (non reporting services) page you have to grab those paramters through the QueryString collection and pass it again to the reporting services parameter like mentioned above.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for Jens.|||

Can anybody plz tell me how to pass multiple values in open() method to next page,

Actually I want to know the seperator between the different parameter passed

the code i wrote is somewhat this kind

function addr()
{
open("edoc_add.asp?Docno=<%=DaNo%> Address=<%=Daln%> Pin=<%=DaPin%> Tel=<%=DaTel%> Mob=<%=DaMob%> Mark=<%=DaRm%> mail=<%=Damail%> url=<%=DaUrl%>","AddressInfo","toolbar=No,StatusBar=No,Resize=Yes,Scrollbars=Yes,width=600,height=400")
}

I m calling it on click event of anchor tag.

plz reply soon.............

thnkx in advanced.

|||

Its alway the paramter name:

ParamName=1&ParamName=2 ...

Jens.

Positioning parameters in header

I have three parameters for a financial rpt. Division name, "From" date, and
"To" date. I want the "From" and "to" date parameters to be on the same line,
with the division name on top. How do I make this happen?
Thanks in advance!
carlYou can create two text boxes, one to show the first parameter and 1 to show
the from/to values:
The value property of each:
1) = Fields!division_name.Value
2) = "From " + Fields!from_date.Value.ToString("d") + " to " + Fields!to_date.Value.ToString("d")
Date/Time format strings:
http://msdn2.microsoft.com/en-us/library/az4se3k1.aspx
> I have three parameters for a financial rpt. Division name, "From"
> date, and "To" date. I want the "From" and "to" date parameters to be
> on the same line, with the division name on top. How do I make this
> happen?
> Thanks in advance!
> car|||I apologize, after reading my own post I realize I was not clear on what I
wanted.
I want to be able to position the actual drop down boxes in the report tool
bar so that when the user goes to enter/select data in the drop downs, the
placement of the drop downs makes more sense.
I realize that this makes no difference to the functionality of the report,
but I have a business user who doesnt like the "feel" of where they line up
now, with the division name and "from date on the top line, then the "to"
date below by itself. I want to get the from and to date parameters on the
same "line" so that its more obvious as to what the purpose of these
parameters are.
thanks!!
"Andrew Backer" wrote:
> You can create two text boxes, one to show the first parameter and 1 to show
> the from/to values:
> The value property of each:
> 1) = Fields!division_name.Value
> 2) = "From " + Fields!from_date.Value.ToString("d") + " to " + Fields!to_date.Value.ToString("d")
> Date/Time format strings:
> http://msdn2.microsoft.com/en-us/library/az4se3k1.aspx
>
>
> > I have three parameters for a financial rpt. Division name, "From"
> > date, and "To" date. I want the "From" and "to" date parameters to be
> > on the same line, with the division name on top. How do I make this
> > happen?
> >
> > Thanks in advance!
> > carl
>
>

positioning of report parameters when diaplayed in report manager

hi all

i have a report with set of parametrers.

by default when we create parameters they r displayed adjacent to each other.

is there any way by which we can specify position of parameter to be displayed in parameter area?

for eg:- suppose i have two parameters say age & name

then i want to display them one below the other.

is this possible?

if yes how?

plz help me.

waiting for reply.

You cannot customize report manager in this area.

The closest you can get is to e.g. with VS 2005 build your own frontend application (Winforms or Webforms) that deals with the parameters and then use RS Winforms/Webforms ReportViewer controls to display the report based on the selected parameters. More information on the ReportViewer controls: http://www.gotreportviewer.com/

-- Robert