Create report with an ADO dataset

Hello,

We are trying to convert our Crystal XI reports to SQL Reporting Services 2005. Our crystal reports get their data from ADO datasets which are populated through code at run time. Is it possible to do this in SQL Reporting Services?

The only options for a dataset seem to be query and stored procedure. When i have a blank dataset it throws an error. When i try to link my dataset to code, it throws an error.

Thanks for your help!Can you give more information on what you are trying to do? Reporting Services is based on .Net. Are you saving an ADO recordset as XML somewhere and they trying to read it from there?|||With all our crystal reports, we use an ADO dataset. We place fields on the report from the dataset, then at run time fill the dataset based off data generated in code, not a stored procedure.

We need to be able to do the same with reporting services reports. The only option available seems to be a dataset that is linked to a query or a stored procedure. We want to be able to fill the dataset by calling a method in our code.

Does that make sense?|||

Hi,

You can create a Custom Assembly and reference that in the SSRS report.

Create a data source of type "XML". Create a dataset based on this data source in the SSRS.

You can call the function in the Custom Assembly that returns an XML dataset.

The following function was written in the Custom Code window and it reads an .xml file and returns it as data to the calling function.

Public Function fGetXMLQuery() as String

dim ABC as String

Using sr As System.IO.StreamReader = New System.IO.StreamReader("C:\TestFile.xml")

' Read and display the lines from the file until the end
' of the file is reached.

ABC= "<Query> " + _
" <XmlData> " + _
sr.ReadToEnd + _
" </XmlData> " + _
"</Query>"
sr.Close()
End Using
fGetXMLQuery = ABC

End Function

The Query string for the Dataset is "=Code.fGetXMLQuery()"

HTH

Ashish

|||

I tried the above method but getting Incorrect Syntax near '<'

can any one please help me on this?

My XML is below

<Query> <XmlData> <?xml version="1.0" standalone="yes"?>
<NewDataSet>
<tblSiteType>
<iSiteTypeID>1</iSiteTypeID>
<vcSiteTypeName>Pre-Production</vcSiteTypeName>
</tblSiteType>
<tblSiteType>
<iSiteTypeID>2</iSiteTypeID>
<vcSiteTypeName>Production</vcSiteTypeName>
</tblSiteType>
<tblSiteType>
<iSiteTypeID>3</iSiteTypeID>
<vcSiteTypeName>Integration</vcSiteTypeName>
</tblSiteType>
</NewDataSet> </XmlData> </Query>

-ranga

|||I got mine working using this procedure:

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

Hi,

I was working on the MSDN Tutorial "Using XML Data in a Report", Lesson

2: Defining a Report Dataset for an ADO.NET DataSet from a Web Service

at the follwoing link

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

When i run the following query, everything goes as expected (i.e. the

displayed data is the extracted data from all XML elements and

attributes down to the leaf node on the first XML path it finds in the

dataset )

http://tempuri.org/GetEmployeeManagers

Name="GetEmployeeManagers">

But when i add the other section to specify the elements to be used in

retrieving the data

1. <ElementPath IgnoreNamespaces="True"> GetEmployeeManagersResponse {}/ GetEmployeeManagersResult/diffgram{}/ Results {}/Table </ElementPath>

the following error is dispalyed:

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

An error occurred while creating a data extension command.

The XmlDP query is invalid. (Microsoft Report Designer)

The XmlDP query is invalid.

(Microsoft.ReportingServices.DataExtensions)

There are multiple root elements. Line 9, position 2. (System.Xml)

Please help me with this...

|||So your web service works? Have you tested it through Internet Explorer?

Your query (in Visual Studio) should look something like this:

<Query>

<ElementPath

IgnoreNamespaces="True">

GetEmployeeManagersResponse

{}/

GetEmployeeManagersResult/diffgram{}/

Results {}/Table {FirstName,

LastName, Address, City}

</ElementPath>

<SoapAction>

http://tempuri.org/GetEmployeeManagers

</SoapAction>

<Method Namespace="http://tempuri.org/"

Name="GetEmployeeManagers">

</Method>

</Query>

Where i have FirstName,

LastName, Address, City this is a list of your field names you want to return. If you want to return all fields, then leave this out (also leave out the curly brackets).

Hope this helps, let me know if it does or doesn't.

Create report with an ADO dataset

Hello,

We are trying to convert our Crystal XI reports to SQL Reporting Services 2005. Our crystal reports get their data from ADO datasets which are populated through code at run time. Is it possible to do this in SQL Reporting Services?

The only options for a dataset seem to be query and stored procedure. When i have a blank dataset it throws an error. When i try to link my dataset to code, it throws an error.

Thanks for your help!Can you give more information on what you are trying to do? Reporting Services is based on .Net. Are you saving an ADO recordset as XML somewhere and they trying to read it from there?|||With all our crystal reports, we use an ADO dataset. We place fields on the report from the dataset, then at run time fill the dataset based off data generated in code, not a stored procedure.

We need to be able to do the same with reporting services reports. The only option available seems to be a dataset that is linked to a query or a stored procedure. We want to be able to fill the dataset by calling a method in our code.

Does that make sense?|||

Hi,

You can create a Custom Assembly and reference that in the SSRS report.

Create a data source of type "XML". Create a dataset based on this data source in the SSRS.

You can call the function in the Custom Assembly that returns an XML dataset.

The following function was written in the Custom Code window and it reads an .xml file and returns it as data to the calling function.

Public Function fGetXMLQuery() as String

dim ABC as String

Using sr As System.IO.StreamReader = New System.IO.StreamReader("C:\TestFile.xml")

' Read and display the lines from the file until the end
' of the file is reached.

ABC= "<Query> " + _
" <XmlData> " + _
sr.ReadToEnd + _
" </XmlData> " + _
"</Query>"
sr.Close()
End Using
fGetXMLQuery = ABC

End Function

The Query string for the Dataset is "=Code.fGetXMLQuery()"

HTH

Ashish

|||

I tried the above method but getting Incorrect Syntax near '<'

can any one please help me on this?

My XML is below

<Query> <XmlData> <?xml version="1.0" standalone="yes"?>
<NewDataSet>
<tblSiteType>
<iSiteTypeID>1</iSiteTypeID>
<vcSiteTypeName>Pre-Production</vcSiteTypeName>
</tblSiteType>
<tblSiteType>
<iSiteTypeID>2</iSiteTypeID>
<vcSiteTypeName>Production</vcSiteTypeName>
</tblSiteType>
<tblSiteType>
<iSiteTypeID>3</iSiteTypeID>
<vcSiteTypeName>Integration</vcSiteTypeName>
</tblSiteType>
</NewDataSet> </XmlData> </Query>

-ranga

|||I got mine working using this procedure:

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

Hi,

I was working on the MSDN Tutorial "Using XML Data in a Report", Lesson

2: Defining a Report Dataset for an ADO.NET DataSet from a Web Service

at the follwoing link

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

When i run the following query, everything goes as expected (i.e. the

displayed data is the extracted data from all XML elements and

attributes down to the leaf node on the first XML path it finds in the

dataset )

http://tempuri.org/GetEmployeeManagers

Name="GetEmployeeManagers">

But when i add the other section to specify the elements to be used in

retrieving the data

1. <ElementPath IgnoreNamespaces="True"> GetEmployeeManagersResponse {}/ GetEmployeeManagersResult/diffgram{}/ Results {}/Table </ElementPath>

the following error is dispalyed:

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

An error occurred while creating a data extension command.

The XmlDP query is invalid. (Microsoft Report Designer)

The XmlDP query is invalid.

(Microsoft.ReportingServices.DataExtensions)

There are multiple root elements. Line 9, position 2. (System.Xml)

Please help me with this...

|||So your web service works? Have you tested it through Internet Explorer?

Your query (in Visual Studio) should look something like this:

<Query>

<ElementPath

IgnoreNamespaces="True">

GetEmployeeManagersResponse

{}/

GetEmployeeManagersResult/diffgram{}/

Results {}/Table {FirstName,

LastName, Address, City}

</ElementPath>

<SoapAction>

http://tempuri.org/GetEmployeeManagers

</SoapAction>

<Method Namespace="http://tempuri.org/"

Name="GetEmployeeManagers">

</Method>

</Query>

Where i have FirstName,

LastName, Address, City this is a list of your field names you want to return. If you want to return all fields, then leave this out (also leave out the curly brackets).

Hope this helps, let me know if it does or doesn't.

CREATE GLOBAL CUBE in dataset MDX query.

Hello,
I am attempting to use a CREATE GLOBAL CUBE command when defining a data set in MSRS. The intent is to use the MSRS security and scheduling capabilities to dump local cube files from our data warehouse to a file share, then use MSRS's delivery mechanisms to deliver the cubes to the intended recipient(s). The problem (I believe) is that the command does not return any data, so when rendering the report I bound the dataset to, the server just stops responding. I assume it is waiting for data that will never come. The report itself only contains a text box with a string literal. The good news is that when I try to render the report, the local cube file is generated...I just need the server to finish rendering the "dummy" report. Any ideas? Thanks.
-Rob HoffmanRob, did you look at DTS as an alternative solution?
Reporting Services isn't really meant to be used in the way you're trying to
use it. One area of concern is that you mention the server stops
responding. It it only this particular report that doesn't get rendered, or
does the server stop responding to any request?
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> Hello,
> I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> set in MSRS. The intent is to use the MSRS security and scheduling
> capabilities to dump local cube files from our data warehouse to a file
> share, then use MSRS's delivery mechanisms to deliver the cubes to the
> intended recipient(s). The problem (I believe) is that the command does
> not return any data, so when rendering the report I bound the dataset to,
> the server just stops responding. I assume it is waiting for data that
> will never come. The report itself only contains a text box with a string
> literal. The good news is that when I try to render the report, the local
> cube file is generated...I just need the server to finish rendering the
> "dummy" report. Any ideas? Thanks.
> -Rob Hoffman|||Lukasz,
Yes, but RS looked like a better choice out of the box because of its built-in security model, job scheduling, delivery mechanisms and extensibility. The requirements of the project I'm working on make it an expedient alternative to writing code that allows users to define, schedule and deliver local OLAP cubes from our data warehouse source.
To answer your question, when I view (view tab) my dummy report in Report Manager, the report never renders, and my aspnet_wp process consumes all processing power on the server. When I open a second browser window and try to access Report Manager, the page never loads. It looks like there is some sort of infinite loop occurring. I have never observed a timeout, but I have waited several minutes for the process to complete. I ended up having to close the first browser window (stop rendering the dummy report) to get RS to return to normal operation. When I do that, the second browser window finishes rendering my Report Manager screen almost immediately. I'd appreciate any help you or anyone else can provide in resolving this.
-Rob
"Lukasz Pawlowski [MSFT]" wrote:
> Rob, did you look at DTS as an alternative solution?
> Reporting Services isn't really meant to be used in the way you're trying to
> use it. One area of concern is that you mention the server stops
> responding. It it only this particular report that doesn't get rendered, or
> does the server stop responding to any request?
> -Lukasz
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Rob Hoffman" <Rob Hoffman@.discussions.microsoft.com> wrote in message
> news:6E2F8425-C095-4F36-9A40-C1CD6ADD73DD@.microsoft.com...
> > Hello,
> >
> > I am attempting to use a CREATE GLOBAL CUBE command when defining a data
> > set in MSRS. The intent is to use the MSRS security and scheduling
> > capabilities to dump local cube files from our data warehouse to a file
> > share, then use MSRS's delivery mechanisms to deliver the cubes to the
> > intended recipient(s). The problem (I believe) is that the command does
> > not return any data, so when rendering the report I bound the dataset to,
> > the server just stops responding. I assume it is waiting for data that
> > will never come. The report itself only contains a text box with a string
> > literal. The good news is that when I try to render the report, the local
> > cube file is generated...I just need the server to finish rendering the
> > "dummy" report. Any ideas? Thanks.
> >
> > -Rob Hoffman
>
>