Tuesday, March 20, 2012

"where" statement in list

I have one dataset and two lists with one table in each list. the two tables should get their data from the same dataset. but the tables need different "where" statements. in the dataset I only can define one "where" statment. it is possible to displace the "where" statment into the two lists? they should access to the dataset with their own "where" statement.

Depending on the complexity of your where clause you could remove the where clause to retrieve the data for both lists in one dataset and then use the filter tab of the table to make it display just the data you want

|||

My where statement for the first table is

WHERE (dbo.endgeraet.ansprechpartner IN (@.usernr))

when I remove it in the dataset and put it in the table as filter, the preview gives me an error

"the processing of filterexpression for the table 'table1' cannot be performed. cannot compare data of types system.int32 and system.string. please check the data type returned by the filterexpression"

I don't understand that, because dbo.endgeraet.ansprechpartner is an INT and usernr is an INT, too.

|||

Check the following:

make sure the parameter in not multivalue|||

Ok, my parameter is not multivalue and the filter in the table properties looks like this:

What else could be wrong?

|||

*deleted*

|||In the value column put Parameters!usernr.Value instead @.usernr|||Ok, I changed it, but the error is still there. what else could be wrong? |||why dont you use 2 different datasets?
one for each list

greets gerhard|||

A valid suggestion and definately the easiest to implement.

I'll persevery merely to see what the problem is with jori0001's report. Have you created a report parameter called "usernr". If so can you post a screenshot of the definition of this parameter in the parameters pane?

|||

@. Gerhard

when I use two different datasets, my report would totally have 32 datasets. if I use one dataset for two tables my report will only have 16 datasets.

@. Adam

Thank you, the parameters pane was the right hint. the parameter "usernr" had the data type "string". I changed it into "integer" and now it works. but I had to do one more change. the filter must be set in the list properties, not in the table properties.

No comments:

Post a Comment