Passing complex parameters

Deepak-
"Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
(1) Permissions (The data provider is probably using integrated security)
(2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
(3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
(4) Set IgnoreNamespaces="true" in the query
Hope this helps.
Jon Heide
SQL Server Reporting Services
--Original Message--
From: Deepak@.discussions.microsoft.com
Posted At: Wednesday, October 05, 2005 2:06 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Passing complex parameters
Subject: RE: Passing complex parameters
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
> >Jon,
Thanks for those tips. I've got a SharePoint Lists Web Service (using the
"GetListCollection" method without parameters) source working, but still
can't get results from other methods with parameters. Next I'm going to try
this sample "Report Manager" report from Brian Welcker's blog, which should
be similar to the SQL Server 2005 BOL XML sample:
http://home.ripway.com/2005-6/332074/ReportManager.rdl
"Jon Heide" wrote:
> Deepak-
> "Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
> (1) Permissions (The data provider is probably using integrated security)
> (2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
> (3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
> (4) Set IgnoreNamespaces="true" in the query
> Hope this helps.
> Jon Heide
> SQL Server Reporting Services
>
> --Original Message--
> From: Deepak@.discussions.microsoft.com
> Posted At: Wednesday, October 05, 2005 2:06 PM
> Posted To: microsoft.public.sqlserver.reportingsvcs
> Conversation: Passing complex parameters
> Subject: RE: Passing complex parameters
>
> 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
> > >
>

Pass XML string as parameter for report data source?

Hello,
Is it possible to pass an XML document as a string parameter to a Reporting
Services 2005 report and use this report as a data source for the report?
I know you can source from a web service, the problem is that the effort
needed to convert what I have into a web service will be more than we would
like to take on. Instead, I am trying to pass an XML structure as a string
parameter to a report and then setting the Command Text like so:
<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
TIA!!!I figure out how to do what I am looking to do.
1. Create a new report and specify a sample XML file as your data source.
This way you get all the DataSet <Field> elements you need automatically.
2. Create a new blank report (without the wizard using Add > New Item >
Report).
3. Create a string report parameter to hold your XML data structure for the
new report. I give it a default value of my sample XML used to create the
report in step 1 for testing purposes.
4. Create a new DataSet (Data tab > <New DataSet...> from DataSet dropdown).
5. Name the DataSet (like "XmlSource").
6. Select XML from the Type dropdown.
7. Specify your connection string like so (use the appropriate parameter):
="<Query><XmlSource>" & Parameters!XmlSource.Value & "</XmlSource></Query>"
8. Go to your report you created in Step 1 in "Code View" and copy the
<Fields> element from your <DataSet> element. Now paste this <Fields>
element into the new report's <DataSet> element.
9. Using the fields, drag and drop to complete the layour of your report.
This is sort of a hack to specifying a web service as the datasource, but it
seems to work! I wouldn't recommend this if you have a really large data
structure, but might be useful if you don't have the resources (i.e. web
servers, developers, TIME) to create a web service out of some existing XML
structure.
Hope this helps!!
"brianpmccullough" wrote:
> Hello,
> Is it possible to pass an XML document as a string parameter to a Reporting
> Services 2005 report and use this report as a data source for the report?
> I know you can source from a web service, the problem is that the effort
> needed to convert what I have into a web service will be more than we would
> like to take on. Instead, I am trying to pass an XML structure as a string
> parameter to a report and then setting the Command Text like so:
> <Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
> TIA!!!
>|||Sorry...Correction...In step 7, do not specify a connection string...instead
leave it empty and instead this value should be used in the Query String!
"brianpmccullough" wrote:
> I figure out how to do what I am looking to do.
> 1. Create a new report and specify a sample XML file as your data source.
> This way you get all the DataSet <Field> elements you need automatically.
> 2. Create a new blank report (without the wizard using Add > New Item >
> Report).
> 3. Create a string report parameter to hold your XML data structure for the
> new report. I give it a default value of my sample XML used to create the
> report in step 1 for testing purposes.
> 4. Create a new DataSet (Data tab > <New DataSet...> from DataSet dropdown).
> 5. Name the DataSet (like "XmlSource").
> 6. Select XML from the Type dropdown.
> 7. Specify your connection string like so (use the appropriate parameter):
> ="<Query><XmlSource>" & Parameters!XmlSource.Value & "</XmlSource></Query>"
> 8. Go to your report you created in Step 1 in "Code View" and copy the
> <Fields> element from your <DataSet> element. Now paste this <Fields>
> element into the new report's <DataSet> element.
> 9. Using the fields, drag and drop to complete the layour of your report.
> This is sort of a hack to specifying a web service as the datasource, but it
> seems to work! I wouldn't recommend this if you have a really large data
> structure, but might be useful if you don't have the resources (i.e. web
> servers, developers, TIME) to create a web service out of some existing XML
> structure.
> Hope this helps!!
> "brianpmccullough" wrote:
> > Hello,
> >
> > Is it possible to pass an XML document as a string parameter to a Reporting
> > Services 2005 report and use this report as a data source for the report?
> >
> > I know you can source from a web service, the problem is that the effort
> > needed to convert what I have into a web service will be more than we would
> > like to take on. Instead, I am trying to pass an XML structure as a string
> > parameter to a report and then setting the Command Text like so:
> >
> > <Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
> >
> > TIA!!!
> >

Pass XML Data to use as Data Source?

Hello,

Is it possible to pass an XML document as a string parameter to a Reporting Services 2005 report and use this report as a data source for the report?

I know you can source from a web service, the problem is that the effort needed to convert what I have into a web service will be more than we would like to take on. Instead, I am trying to pass an XML structure as a string parameter to a report and then setting the Command Text like so:

<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>

TIA!!!

Hi Brian-

Yes, it is possible to do this. You will need to make the entire query string an expression, so that report processing will construct the query string first, then pass it to the data provider. For your example it would look like this:

="<Query><XmlData>" & Parameters!XmlData.Value & "</XmlData><ElementPath>MyRootElement</ElementPath></Query>"

Note that this will not execute in the designer data tab (It will show an error loading the query string). Ignore this error and manually add the data set values in the layout tab. This can be done by right clicking the your xml dataset in the DataSets pane and selecting add. The report will execute successfully through the preview pane and after publishing on the server.

Thanks, Jon

|||Why does this not run in the designer? Why do you have to publish it to get it to work?|||

It will run in the designer preview or a published report. However, the report designer query window/data tab does not have the ability to parse report parameters and will thus execute an invalid query.

-JonHP

Pass XML Data to use as Data Source?

Hello,

Is it possible to pass an XML document as a string parameter to a Reporting Services 2005 report and use this report as a data source for the report?

I know you can source from a web service, the problem is that the effort needed to convert what I have into a web service will be more than we would like to take on. Instead, I am trying to pass an XML structure as a string parameter to a report and then setting the Command Text like so:

<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>

TIA!!!

Hi Brian-

Yes, it is possible to do this. You will need to make the entire query string an expression, so that report processing will construct the query string first, then pass it to the data provider. For your example it would look like this:

="<Query><XmlData>" & Parameters!XmlData.Value & "</XmlData><ElementPath>MyRootElement</ElementPath></Query>"

Note that this will not execute in the designer data tab (It will show an error loading the query string). Ignore this error and manually add the data set values in the layout tab. This can be done by right clicking the your xml dataset in the DataSets pane and selecting add. The report will execute successfully through the preview pane and after publishing on the server.

Thanks, Jon

|||Why does this not run in the designer? Why do you have to publish it to get it to work?|||

It will run in the designer preview or a published report. However, the report designer query window/data tab does not have the ability to parse report parameters and will thus execute an invalid query.

-JonHP

Pass XML between SPROCs possible in SQL 2000? 2005?

A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hello Dave,

> Now I want to execute my stored procedure using the XML result of part
> A as the parameter for the sproc in part B. Is that possible? The
> only way I know how to do it right now is to return the result of A
> using ADO.NET, insert a root element (to make the XML valid), and then
> call the sproc created in part B with this xml as the parameter.
In 2005, you'd pass the XML as string to a SQLCLR stored proc. That stored
proc could parse the XML using a XmlReader, extact the command text and para
meter
values and have that proc execute other ones.
This is like the second time today that is idea has come up. Seems like it
time for some demo code... ;)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello me, meet the real me,

> This is like the second time today that is idea has come up. Seems
> like it time for some demo code... ;)
Okay, here's the CLR-less version:
http://www.sqljunkies.com/WebLog/kt...er.aspx

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/