To See Only One Record When 2 or More Entries for An Incident ID

[Cognos_Jan2017]

I have a Microsoft Access background where I would Group On Fields and have a "First Of" to see only one record when 2 or more records exist for an "Incident ID" (example here from [Contributing Factor] entries).

Hope the formatting (when Posted in Cognoise) doesn't mess up badly, for 5 Fields below ...

[Incident ID]  [Date Of Incident]  [Cause Of Incident]     [Contributing Factor]           [Root Cause]
ABC                   Nov 14, 2017      Container failure     Defective tools/equipment      Work Conditions
ABC                   Nov 14, 2017      Container failure     Failed to pay attention      Work Conditions
ABC                   Nov 14, 2017      Container failure     Improper maintenance      Work Conditions
                  
Yes, I could Cut the [Contributing Factor] Field which would "merge" the other same entries in the
3 records into ONE record.

Could try to concatenate those 3   [Contributing Factor] entries which in Access would require another query
then a Joined Query.

Is there a way to accomplish this within a single Cognos query?

TIA, Bob

[Kiran P]

Hey Bob

You can either move contributing factors to the last column and group the list on all other columns or you can use a repeater control in reporting tool to generate a comma separated string of contributing factors so that the incident appears in one row.

Thanks
Kiran

Sent from my ZUK Z2132 using Tapatalk

[Cognos_Jan2017]

Thank you Kiran.

You can either move contributing factors to the last column and group the list on all other columns or you can use a repeater control in reporting tool to generate a comma separated string of contributing factors so that the incident appears in one row.

Don't want to do a bunch of Column Grouping as that tends to "merge" into a Big Cell when writing to Excel.

Have had learning Repeaters on my List of Things To Do ... the time is here.

Have a good weekend.  Thanks again, Bob

[Kiran P]

Hi Bob

There is a property Group Repeating Cells When Exporting To Excel in report properties which when set to No will in-group and show values in every cell.

Thanks
Kiran

Sent from my ZUK Z2132 using Tapatalk

[Cognos_Jan2017]

Just seeing your 11 Feb post.  Will try, and report back, tomorrow am.

Thank you Kiran, Bob

[Cognos_Jan2017]

Kiran - Thank you for the Grouping info.

We need the Field we will be concatenating to be in the middle
of the Report.

I tried a Repeater yesterday.  Apparently to work correctly,
the Repeater should be in a separate query (??) and making
a Master - Detail Relationship Join will display any values in
the Repeater.

When I tried that, I couldn't get the query for the List as the
Master Query to form a Master - Detail Relationship.

I am getting ready to watch a YouTube Repeater video, and hope it helps.

Thank you, Bob

[Cognos_Jan2017]

Am trying to set a Master - Detail Relationship for a List, and a Detail Query to
have a Field for a Repeater.

The Report has a Query (for simplicity 'ABC').

I go to the Report List Properties, select Page, then under Data, set the query 'ABC'.

Clicking below that for Master detail relationships, I click the Elipsis and see ...
"No master query context can be found for this object."

What am I doing wrong?

I need the define the Master query and then make a New Link to the other Detail query
which will show 1, or more, entries for [Contributing Factor] which will then be placed in
a Repeater on the List.

Help please.  TIA, Bob

[Cognos_Jan2017]

SOLUTION ... From a friend ...

Hi Bob, unlock your report, pull in repeater into one of the list columns and select the same query as the main list
for repeater as well. In properties for repeater, under master detail relationships you will see same query on both
sides and you can join on the ID column. You can then pull in the repeating value for ex, Incident Body Part into the
repeater control. Note that you may have to edit the expression for the element to concatenate a comma so that you
see comma separated values. You will see an additional comma at the end, there are ways to eliminate but not worth
the effort; I believe we can live with the comma at the end.