Thursday, May 17, 2007

SQL Reporting Service could get a little bit frustrated. (Invalid Object about temporary table.)


I am starting to use the SQL reporting service to generate a couple of reports for the project I am working on right now, and it could get a little bit frustrated for a newbie.


In my process, I need to generate a report based on a stored procedure.


Then, I got the following error which complains that the temporary table is invalid.

What you can do, the temporary table should be perfectly fine in the stored procedure. I just clicked OK.


Since the dataset was not generated successfully, it won’t automatically generate a list of report parameters for you.



The good thing is that unlike Crystal report which the underlying format is in binary, sql report is all xml based, which gives a lot of flexibility in editing the report manually.


You can simply manually add a list of report parameters like this in the rdl file (open it in any text editing software).


<ReportParameters>

<ReportParameter Name="countyID">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>e12b5415-de73-4751-b2cb-2e98740c4460</Value>

</Values>

</DefaultValue>

<Prompt>County ID</Prompt>

</ReportParameter>

<--- other report parameters>

</ReportParameters>


This will generate a list of report parameters


But how do you pass those reporting parameters to the sql query parameters. If you put a table control on the report, and set the underlying data source to the previous dataset, and try to preview it. It will give you another error like this:




There are two ways to address this issue:

  1. one way is that you go back to the dataset, set the Parameters manually there.

  1. The other option is that you can still edit in the xml file, which is the way I preferred. Under the DataSets, looking for the DataSet interested, and set the QueryParameters there…

<DataSets>

<DataSet Name="ComplianceMissing">

<Query>

<DataSourceName>EarlyTrack</DataSourceName>

<CommandType>StoredProcedure</CommandType>

<CommandText>rptGetComplianceMissingChild</CommandText>

<QueryParameters>

<QueryParameter Name="@countyID">

</QueryParameter>

</QueryParameters>

</Query>

</DataSet>

</DataSets>



The whole process is not that trival…

No comments: