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.