report server parameters

Hi!

I have a report (*.rdl) in my report server, and this report calls a dataset (by an ObjectDataSource of the reportviewer) that has query parameters. I try to run the report but the following error appears:

An error has occurred during report processing.

Query execution failed for data set 'DataSet3_generar'.

Must declare the scalar variable "@.Param1".

I read that, when you create dataset parameters that the report should inherits these parameters automatically. And when you upload the file into the report server, when you select it in the report manager and see its properties, it should appear a link of parameters.

But my report doesn't inherits the dataset parameters automatically, I tried to do it manually using the properties window of visual studio, but It does show the link of parameters in the report manager.. but doesn't link the report parameters with the dataset query parameters.

I don't know if the problem is that I am using SQL server Express, because the Report Builder doesn't work properly (with all its functions)

So, is there a way of solve this using SQL server express? Or I need SQL server 2005?

If there's nothing to do with the server.. then how can I made the link between dataset and report item using visual studio?

Thanks! Any suggestion will help me a lot!

I also did have that sometimes. There is a refresh button at the dataset icons which will populate the used paramters from the dataset to the reports automatically. I always do the following, executing the query, then refreshing the reports to have the whole thing in sync and thats it. If this is a sql exeception brought to you by the SQL Server, it could be that you are using a variable in a dynamic sql statement which is not defined in the dyna′mic sql scope. Make sure that this does not apply.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi thanks for answering....

I have a query in my dataser similar to this...

select field1, field2 from table where field3 = @.param1

is this a dynamic sql statement? and where do I have to defined it for it to be in a dynamic sql scope?

I already try what you said about refreshing the datasources and then refreshing the report, but it doesn't populate the parameters to the report....

I am using visual basic. net web development I don't know if its diferent....

|||

I already find the problem ... what I had to to was to write in the *.rdl file that the parameter was used in a query and that it was a query parameter.

the code of my report is the following... hope it help someone...

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Def_prescoConnectionString">
<ConnectionProperties>
<ConnectString />
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>fb5b58f0-f99b-4f88-9388-56107932e009</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Fecha1">

<UsedInQuery>Auto</UsedInQuery>

<DataType>DateTime</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>fecha1</Prompt>

</ReportParameter>

**** above -- the part of report parameter used in query

**** below -- the part of query parameters

<DataSets>
<DataSet Name="DataSet1_generar">
<rd:DataSetInfo>
<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
<rd:DataSetName>DataSet1</rd:DataSetName>
<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
<rd:TableAdapterName>generarTableAdapter</rd:TableAdapterName>
<rd:TableName>generar</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<QueryParameters>
<QueryParameter Name="@.fecha1">
<Value>=Parameters!Fecha1.Value</Value>
</QueryParameter>
</QueryParameters>

<CommandText>select id_form, sum(cant_latas) from generar where fecha = @.fecha1 group by id_form</CommandText>
<DataSourceName>Def_prescoConnectionString</DataSourceName>
</Query>

|||

It did help me! Thanks!

I had that same problem. I noticed in the Dataset designer you can add fields and parameters. Sometimes it fills them in automatically sometimes it doesn't.