Problem with predefined Select All in parameter

[Torbjörn Eliasson]

I have a parameter with Multivalues. I would like the parameter do default to Select all and have therefore implemented a AllValue function (as shown in another post in this forum) to set the Value property. This works very well as log as the value only has unique values. Example:

SELECT

 

fullname FROM employee

value=AllValues(fields.fullname)

This works wothout problem.But if I get the value from a table that contains the same value more than once I got problem. Example:

SELECT

 

activity.startdate,employee.fullname FROM activity INNER JOIN employee ON activity.employeesignature=employee.signature
value=AllValues(fields.fullname)

The result of this is that the parameter is shown with the unique values and all values is selected. But, if i deselect all, only some items is deselected. And if I click on the others they could not be deselected at all.
The problem occurs to be that the second example could result in several rows with the same value in employee.fullname. OPnly the rows that have only one row with the same value is handled correctly.

So, how can I solve this problem?

R
Torbjörn 

[Elian]

Hi Torbjörn,

We have verified the issue and have logged it into our bug-tracking system. Since you can't benefit from the repeating values, we would suggest that you use the DISTINCT keyword in your query, to avoid repetitions. 

Kind regards,
Elian
the Telerik team

[Torbjörn Eliasson]

It's not possible to use DISTINCT because I need a complete rowset to createthe report I need.

I have therefore tried another way. I add another sqldatasource thar returns the values that I want in the selection parameter. So now I have two datasources, one for the data in the report and one for the selection parameter. The problem is that when I run the report it doesnt recognize the fields from the report datasource, they are unknown and I get an error. It seems that only one datasource is active.

Please explain if this is a possible way to solve the problem.

[Elian]

Hello Torbjörn,

Using a separate data-source for the parameters is perfectly ok. If you look into our Product Line Demo, you will see that the report parameters have their own data-sources. 

The setup is the following:

• You add parameterSqlDataSource which retrieves the data needed to feed the parameter.
• You add a Report parameter and assign the parameterSqlDataSource  to its AvailableValues.DataSource
• You start a wizard for creating a parameterized reportSqlDataSource which retrieves the data for the report.
• The reportSqlDataSource query should look something like that: SELECT ... FROM .... WHERE Parameter in @MultISelectParam
• On the next step of the wizard you will be asked where the sqlDataSource should get it's parameter values @MultISelectParam, there you will set it to point to the ReportParameter you have previously created.

Articles that can be helpful:

• Using Multivalue Parameters 
• Using Parameters with the SqlDataSource component 

All the best,
Elian
the Telerik team

[Torbjörn Eliasson]

Hello Elian, this looks like a good approach. I have gived it a try, but I get a error when the SQL for the report is runned. The error is 'Incorrect syntax near @pstationplace1'.
@pstationplace is defined as a Multivalue pramater with a unique datasource. It has the id column as its valuemember.

Whats the problem?

The SQL looks something like this:

SELECT ..., stationplace.id, ...
FROM employee INNER JOIN stationplace ON employee.stationplaceid=stationplace.id
WHERE (stationplace.id IN @pstationplace)

[Torbjörn Eliasson]

I found the cause in another thread. The SQL should be like this:

WHERE (stationplace.id IN (@pstationplace))

Now it works!

[Steve]

Hi Torbjörn,

Yes, this is the correct syntax and this information is available in the Using Multivalue Parameters help article that my colleague has referenced.

Kind regards,
Steve
the Telerik team