Unable to link report parameters to DataSet

[alanford01]

Please help!
I'm new to the reporting and I have been trying to create a report which will show data for selected year. I've made connections to database, retrieved data but I'm unable to make the parameters work. I've created one parameter but when I preview report I doesn't filter my data. Is there any where any tutorial on this matter. 

In Report Parameter Collection Editor I've inserted following values:
DataSource -> MyDataSource[Telerik.Reporting.SqlDataSource]
DisplayMember -> =Fields.SalesYear
ValueMember -> =Fields.SalesYear

I've added Name, Text, switched Type to DateTime and Visible to True 
for Value -> =Fields.SalesYear

On my DataSet I've also added parameters. In Edit Parmeters window I've inserted a new parameter with following vlaues:
name -> myName
Dbtype -> DateTime
Value -> =Parameters.SalesYear.Vale

When I try to preview the report I get error: "Missing or invalid parameter value. Please input valid data for all parameters."
I've tried to change type and DbType to "String" and I don't get the error message, but filtering isn't working either.

[Steve]

Hi Alan,

Your explanations seem correct and the only thing that brings suspicion is that you talk about DataSet. If your data is coming from database server, please use the SqlDataSource Component to bind to it and follow the instructions in Using Parameters with the SqlDataSource component to achieve the desired result.

If you already have a DataSet you want to utilize, then you should use ObjectDataSource Component to bind the report to it. In that case the Using Parameters with the ObjectDataSource Component help article shows how to proceed.

All the best,
Steve
the Telerik team

[alanford01]

Thanks Steve,
I managed to realize that I was missing a part in my SQL query in where condition (WHERE column = @column). But now I have problems in "Configure Design Time Parameters" window, I'm getting error trying to convert "String" to "DateTime".

 

[Steve]

Hello Alan,

The error is straight-forward - either the SqlDataSource parameter or the report parameter is of type string. By design, the wizard would show the actual dbtype of the datasource parameter, so if it is a string, changing it manually in the wizard to DateTime would not convert it and is wrong. Check the type of your SalesYear column in SQL Management Studio and make sure you have the correct type for the datasource parameter and for the report parameter as well.

All the best,
Steve
the Telerik team

[alanford01]

Hello Steve,

Your information is helpful but I still don't get it what am I supposed to put in for the value in the "Configure Design Time Parameters". 
I've attached image below. Is there any tutorial for this?

I.E. I'm fetching column that contains ID's and when I put "true" in values I get only values that have ID "1". How do I get all ID's? I've tried putting =>0 but that doesn't help :( I found on forums that I should put more equals signs (===) and I did but that didn't help either :( 

[Elian]

Hello Igor,

The design time parameters are in order to retrieve the schema of the data-source at design-time. So, at run time, their values will make no difference. As to the filtering problems you are experiencing, it sounds like the SQL Query is not exactly the one you need, if you want to have all the ID-s, then you don't need to include a WHERE clause for the ID-s (filter by ID). If you share SQL query and the desired result we may be able to give you a piece of advice. 
 
Regards,
Elian
the Telerik team

[alanford01]

What I want to accomplish is to have one report with parameters so that I can view report result for all employees or for selected employee. Here below is the SQL query that I'm currently using. Maybe I'm barking at the wrong tree here but I have no other idea how to do that. The query that I have right now gives me only selected employee (that is because of the 'AND E.IDEmployee = @Employee' line).

SELECT E.Name,
ROUND(CAST(SUM(T.ticket_price) AS decimal(10,2)),2) AS total,
COUNT(*) as num_tickets,
ROUND(CAST((SUM(T.ticket_price) / COUNT(*)) AS decimal(5,2)),2) AS avr_price
FROM Ticket T
JOIN Locations L ON L.IDLocations = T.IDLocations
JOIN Employee E ON E.IDEmployee = L.IDEmployee
WHERE E.Name != 'test'
AND E.IDEmployee = @Employee
GROUP BY E.Name
ORDER BY num_tickets DESC, total DESC

[Elian]

Hi Igor,

Please, consider the following:

...
WHERE  @Employee IS NULL
    OR E.IDEmployee = @Employee
...

with this condition, when the parameter passed has a value of NULL, then all the rows will be selected, otherwise only one employee will be selected (with the corresponding id). You can also make the default value something different than NULL for example -1, it depends on your preference and specifics. 
 
Regards,
Elian
the Telerik team

[alanford01]

Hi Elian,

I've already tired that option, and still can't get all the rows :(  When I tried running that query directly on SQL Server I get results, and this solution you've suggested works perfectly. Problems start when I use this query with Telerik, I just can not get NULL value and can not get all rows. When I set default value in report parameter to be NULL I get "error" (Missing or invalid parameter value). 

[Steve]

Hello Igor,

Have you set the AllowNull property of the report parameter to True? This property determines if null value is acceptable, otherwise it is not acceptable and the error "Missing or invalid parameter value" is expected.

All the best,
Steve
the Telerik team

[alanford01]

Hello Steve,

This one is the problem solver! Thanks a million. I totally forgot to set the "AllowNull" property.