passing parameter from asp.net application to reporting services report

Hi,

There is a .net application which has a screen with four options(text boxes)

Each of these should take to reports generated by SQL REp Services

This is the requirment

The School selected in the App should be passed on to the report

How should I pass the parameter in my report

so that when user selects a link or text box report for that particular school number is displayed

Thanks

sowree

Are you displaying the report in a report viewer control, or opening up a new browser window with the report URL?

BobP

|||

hi

the report is being viewed using report manager, so the report I have deployed to report server must be displayed when the user selects the menu selecting a school number in the asp app.

I need to know how i pass the parameter in my report rdl 's stored procedure to select the school number selected in the ASP application

Thanks

|||

You would create a parameter in the report rdl to accept the school number, and then use that parameter in the call to the stored proc.

For example: You create a parameter called @.SchoolNumber

exec spgSchoolInfo @.SchooNumber

To pass the parameter to the report in report manager, you would put the school number in the URL: (http://msdn2.microsoft.com/en-US/library/ms153586.aspx)

Example: http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&SchoolNumber=1234

HTH

BobP

Passing OLAP Parameters Within a URL

Hi there!

My report uses an OLAP query with a (multi-valued) parameter, the MDX generated query is something like "...ON ROWS FROM ( SELECT ( STRTOSET(@.Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".

The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:

https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]

But I cannot get Reporting Services to accept the parameter instead of complaining about a missing parameter value. I've already tried quoting the braces, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.

How do I quote this parameter correctly?

I do this befor but so it was as follow

Report server URL(http://Machinename/reportserver/) + Reports Folder (MyReports/)+Reportname (My report) +"&Firstparametername"+Value+"&secondparametername"+Value

it will be like this

http://Machinename/reportserver/(MyReports/My repor&Firstparametername=Value1&secondparametername=Value

|||

Thanks for your reply.

Normal query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. ReportServer won't accept them, I think it doesn't like the way I'm trying to quote them...

Thanks and best regards,
Thomas

|||

Hi Tamer,

I was reading your question and i have the same problem.

Please, tell me, Have you resolved this issue ?

email me to :megch00@.hotmail.com ormanuelgo@.cr-dss.com

Thanks a lot !!

|||

Hi there!

It was indeed quoting related, I must have confused hex with decimal notation in my earlier attempts.

All you have to do is urlencode the parameter names and values. Here is how to quote correctly:

InputEncoded[%5B]%5D&%26spaces+ or%20

Or you let ASP.Net do the job:

1string encodedParam = HttpContext.Current.Server.UrlEncode(myParam);

So ...&Region=[foo].[bar 123].&[baz]... becomes...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D...

If you want this parameter to have multiple values, just use it multiple times in your URL: ...&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bbaz%5D&Region=%5Bfoo%5D.%5Bbar+123%5D.%26%5Bheureka%5D...

HTH and best regards,
Thomas

sql

Passing complex parameters

Is it possible to pass a .net DataSet to a Report, and have that used as
data within the report?
I have a dynamically generated list of items in a .net DataSet that I
need to pass to a top-level report which then calls subreports for each
item.
I'm not sure how you can process such a parameter in the report -
obviously its just an xml string, but how can it be re-rendered to data.
Alternatively I could pass a comma separated string, but the same
question arises. An example would be great (he said hopefully).
Also, is there a limit to the size of a string parameter?
brian smithI saw someone doing something similar at the PASS conference last week...
I'll tell you what I remember and hope it helps... They were using a Web
Service as a data source.
They chose XML as the data provider, then in the connect string pointed to
the Web service.. asmx
Then in the query for the data source, they did an XML query, passing in the
SOAP query, which was copied from the web services description...It worked
for him, but I suspect it would take me a while to duplicate what he had
done...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Brian Smith" wrote:
> Is it possible to pass a .net DataSet to a Report, and have that used as
> data within the report?
> I have a dynamically generated list of items in a .net DataSet that I
> need to pass to a top-level report which then calls subreports for each
> item.
> I'm not sure how you can process such a parameter in the report -
> obviously its just an xml string, but how can it be re-rendered to data.
> Alternatively I could pass a comma separated string, but the same
> question arises. An example would be great (he said hopefully).
> Also, is there a limit to the size of a string parameter?
> brian smith
>|||This is in 2005. The only way in 2000 is to write a data extension.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D9FA7760-2834-4F09-A4D1-2649FFCB89F5@.microsoft.com...
>I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
> Then in the query for the data source, they did an XML query, passing in
> the
> SOAP query, which was copied from the web services description...It
> worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Brian Smith" wrote:
>> Is it possible to pass a .net DataSet to a Report, and have that used as
>> data within the report?
>> I have a dynamically generated list of items in a .net DataSet that I
>> need to pass to a top-level report which then calls subreports for each
>> item.
>> I'm not sure how you can process such a parameter in the report -
>> obviously its just an xml string, but how can it be re-rendered to data.
>> Alternatively I could pass a comma separated string, but the same
>> question arises. An example would be great (he said hopefully).
>> Also, is there a limit to the size of a string parameter?
>> brian smith|||Hi Wayne,
I was at PASS last week as well, and saw those demos. This SQL Server 2005
Books Online entry discusses XML data sources for Reporting Services, but I
can't get the example at the end (which simply lists reports from the Report
Server) to work. The dataset query returns an error like "Failed to execute
web request for the specified URL". Maybe Brian Welcker or Bruce L-C can
comment/help?
- Deepak
http://msdn2.microsoft.com/en-us/library/ms228288
SQL Server 2005 Books Online
Retrieving XML Data
XML documents are a supported data source type for reports that you build
and run in SQL Server 2005 Reporting Services. An XML data processing
extension is provided to retrieve and process the data.
...
Example: Retrieving XML Data from a Web service
To build the query, use the generic query designer. Use the following steps
to learn how to specify the connection and return XML data from a Web
service. In this example, the dataset returns items from the report server
database.
...
"Wayne Snyder" wrote:
> I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
> Then in the query for the data source, they did an XML query, passing in the
> SOAP query, which was copied from the web services description...It worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Brian Smith" wrote:
> > Is it possible to pass a .net DataSet to a Report, and have that used as
> > data within the report?
> > I have a dynamically generated list of items in a .net DataSet that I
> > need to pass to a top-level report which then calls subreports for each
> > item.
> > I'm not sure how you can process such a parameter in the report -
> > obviously its just an xml string, but how can it be re-rendered to data.
> > Alternatively I could pass a comma separated string, but the same
> > question arises. An example would be great (he said hopefully).
> >
> > Also, is there a limit to the size of a string parameter?
> >
> > brian smith
> >|||Thanks, but as other have commented, thats for SQL2005. In fact, its not
quite what I want - the datasource is SQL Server, I just want to pass a
list of values as a parameter. Seems SQL2005 will support multi-value
parameters, which will do what I want, but that's no help at present.
I've solved the problem by saving the parameter data into a work table
in the database. The report can then query this using a single passed id
parameter.
brian smith
Wayne Snyder wrote:
> I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
> Then in the query for the data source, they did an XML query, passing in the
> SOAP query, which was copied from the web services description...It worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>|||You can download the dataset extension as described belo
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp
But again the paramters are still an issue the BoL does not identifues clearly
"Brian Smith" wrote:
> Is it possible to pass a .net DataSet to a Report, and have that used as
> data within the report?
> I have a dynamically generated list of items in a .net DataSet that I
> need to pass to a top-level report which then calls subreports for each
> item.
> I'm not sure how you can process such a parameter in the report -
> obviously its just an xml string, but how can it be re-rendered to data.
> Alternatively I could pass a comma separated string, but the same
> question arises. An example would be great (he said hopefully).
> Also, is there a limit to the size of a string parameter?
> brian smith
>