Table Item That Contains Unique List

[Logan]

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>]

[Logan]

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

[Logan]

Note:Formatting for the DESCR field cuts off at the comma.

[Neli]

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

[Logan]

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.

[Logan]

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!

[Neli]

Hi Logan,

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

Regards,
Neli
Progress Telerik