Unable to link report parameters to DataSet

11 posts, 1 answers
  1. alanford01
    alanford01 avatar
    6 posts
    Member since:
    Dec 2012

    Posted 19 Dec 2012 Link to this post

    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.

  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 19 Dec 2012 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  3. DevCraft banner
  4. alanford01
    alanford01 avatar
    6 posts
    Member since:
    Dec 2012

    Posted 19 Dec 2012 Link to this post

    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".

     
  5. Answer
    Steve
    Admin
    Steve avatar
    10941 posts

    Posted 19 Dec 2012 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  6. alanford01
    alanford01 avatar
    6 posts
    Member since:
    Dec 2012

    Posted 21 Dec 2012 Link to this post

    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 :( 

  7. Elian
    Admin
    Elian avatar
    435 posts

    Posted 27 Dec 2012 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  8. alanford01
    alanford01 avatar
    6 posts
    Member since:
    Dec 2012

    Posted 30 Dec 2012 Link to this post

    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
  9. Elian
    Admin
    Elian avatar
    435 posts

    Posted 02 Jan 2013 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  10. alanford01
    alanford01 avatar
    6 posts
    Member since:
    Dec 2012

    Posted 06 Jan 2013 Link to this post

    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). 

  11. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 09 Jan 2013 Link to this post

    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

    HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!

  12. alanford01
    alanford01 avatar
    6 posts
    Member since:
    Dec 2012

    Posted 09 Jan 2013 Link to this post

    Hello Steve,

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