Table Item That Contains Unique List

7 posts, 1 answers
  1. Logan
    Logan avatar
    17 posts
    Member since:
    Jul 2018

    Posted 03 Feb Link to this post

    Hello! I need some general guidance. I will try to upload an example with CSV files and example report tomorrow morning to elaborate, but wanted to get this explanation out tonight. Been a hectic weekend.
     
    I am generating what's called an ASSESSMENT report. I have a datasource that queries an Oracle DB to get an ID number, called an ASSESSMENT_ID, from a table in our DB. Through several inner joins it gathers information from several DB tables to fill out numerous Telerik Table items. One Table item is supposed to be duplicated for three records, which are based on TASKS_IDs associated with this ASSESSMENT_ID, that this query returns.

    One of the tables in our query, though, returns a column called EXPOSUE_CODES which has numerous records per TASK_ID and cannot be inner joined. I have tried setting up a report parameter to make a list of TASK_IDs and tried to pass this list into a second query to grab the EXPOSURE_CODES separately, but it duplicates all EXPOSURE_CODES from all three TASK_ID records in EACH TASK_ID table.

    How do I get each TASK_IDs list of EXPOSURE_CODES into their respective table without duplication in other tables?

    ASSESSMENT_ID ->

    TASK_ID1, TASK_ID2, TASK_ID3 ->

    [TASK_ID1:EXPOSURE_CODE1, EXPOSURE_CODE2, EXPOSURE_CODE3],

    [TASK_ID2:EXPOSURE_CODE2, EXPOSURE_CODE5, EXPOSURE_CODE6],

    [TASK_ID3:<NO EXPOSURE_CODES>]

  2. Logan
    Logan avatar
    17 posts
    Member since:
    Jul 2018

    Posted 04 Feb in reply to Logan Link to this post

    Attached is a picture of what I'd like to accomplish. Below, I have listed two CSV data sources that emulate what is getting returned from each query.

    TASK_TABLE:

    TASK_TITLE,ASSESSMENT_ID,ASSESSMENT_TASK_ID,DESCR
    TASK_TITLE1,5,19,Some additional field that gets pulled from TASK_TABLE. It is TASK_ID specific, but is not a list like EXPOSURE_CODE.
    TASK_TITLE2,5,20,Some additional field that gets pulled from TASK_TABLE. It is TASK_ID specific, but is not a list like EXPOSURE_CODE.
    TASK_TITLE3,5,21,Some additional field that gets pulled from TASK_TABLE. It is TASK_ID specific, but is not a list like EXPOSURE_CODE.

    SUB_TASK_TABLE:

    TASK_ID,EXPOSURE_CODES
    19,EXPOSURE_CODE1
    19,EXPOSURE_CODE2
    19,EXPOSURE_CODE3
    20,EXPOSURE_CODE2
    20,EXPOSURE_CODE5
    20,EXPOSURE_CODE6


  3. Logan
    Logan avatar
    17 posts
    Member since:
    Jul 2018

    Posted 04 Feb in reply to Logan Link to this post

    Note:Formatting for the DESCR field cuts off at the comma.
  4. Answer
    Neli
    Admin
    Neli avatar
    59 posts

    Posted 06 Feb Link to this post

    Hello Logan,

    Thanks for the provided expected layout and sample CSV data sources.

    If I understand correctly, the main problem is how to obtain the data from the second table that will display with the EXPOSURE CODES based on a passed ASSESSMENT_TASK_ID. It can be done via SQLDataSource parameter or by using Filtering on the data item. I have prepared a small example based on your description that demonstrates the latter approach. The example uses SQL DataSources that return static data rather than fetching data from a database tables - you just need to fix their connection strings to make the example run.

    The layout is a bit complicated because each table must have only one row per TASK_TITLE. So I suggest to use a Table item in a report's detail section. The report's data source is set to TASK_TABLE, which means it will generate a new table instance for each row in its data set. We need to filter the Table rows so each table will show only one row. This can be done via Filtering, checking the current report item's DataObject property:
    = Fields.TASK_TITLE = =ReportItem.DataObject.TASK_TITLE

    This will ensure that each Table will have only one row per each TASK_TITLE.

    The list with the EXPOSURE CODES can be displayed in a nested Table. This table will filter its contents based on the current ASSESSMENT_TASK_ID. The tricky moment here is that current ASSESSMENT_TASK_ID should be obtained via the parent data context. So the filtering expression looks like this:
    = Fields.TASK_ID = =ReportItem.DataObject.ASSESSMENT_TASK_ID

    This will filter out all the rows from the SUB_TASK table that have TASK_ID different than the current ASSESSMENT_TASK_ID.
    Instead of client-side filtering, the current ASSESSMENT_TASK_ID can be passed as a SqlDataSource parameter to the second data source - this way the returned data will contain only the rows that correspond to the passed ASSESSMENT_TASK_ID, thus improving the performance.

    As a result, the report displays one table with one row per each TASK_TITLE and its middle cell contains a nested table that shows the TASK's EXPOSURE CODES.
    Please examine the attached report. I hope you will find it useful and in case you need further assistance, please let us know.

    You can also check the following documentation article that elaborate on the subject:
      How to use the ReportItem.DataObject property in expressions

    Regards,
    Neli
    Progress Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  5. Logan
    Logan avatar
    17 posts
    Member since:
    Jul 2018

    Posted 08 Feb in reply to Neli Link to this post

    Hello!

    Thank you so much for getting back to me. There appears to be a difference in the version of Telerik Report Designer you are using and the one I am using. I am using Version 12.1.18.620. You provided very clear instructions, and I'm going to see if I can replicate what you did on that, but if there is anyway you could resend your example, I would be grateful. Right now this version is all we're allowed to use, so it's a little bit of a constraint on our end.

    Thank you again for getting back to me. If there is any more information at all I can provide on my Telerik version number, please, let me know.
  6. Logan
    Logan avatar
    17 posts
    Member since:
    Jul 2018

    Posted 08 Feb Link to this post

    Your instructions were spot on! Thank you so much! Based on your explanation, I have the correct codes displaying and if I could give you some kind of recommendation through Telerik, I absolutely would! Thanks again, you're a life saver!
  7. Neli
    Admin
    Neli avatar
    59 posts

    Posted 08 Feb Link to this post

    Hi Logan,

    Thank you for the warm words! I am really glad that my support was useful.

    Regards,
    Neli
    Progress Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top