IBM Business Analytics

How can a stand alone calculation be added to a rows list report, as a separate column, without necessarily being automatically filtered by the data of the row or other parts of filters?

------------------------------
Joseph Galvin
------------------------------

Hi Joseph,

I can think of 2 ways to do this

1. Calculation Filters
   1. Put the filter(s) on the columns/calcs in the list and not the list itself.  So, column 1 could be if([Year] = ?pYear? and [Month] = ?pMonth?) then ([Value]) else (0). 
   2. Add a column at the end could where the value is unfiltered or uses another set of filters similar to above.
2. Union 2 queries. 
   1. Leave the first query (Query 1) alone and add an extra data item as an identifier, maybe something like 'Filtered Query'. 
   2. Copy Query 1 and remove/modify the filters and change the identifier to 'Unfiltered Data' or something else to differentiate between the data in Query 1
   3. Union the queries together and use logic like if([Query Identifier] = 'Unfiltered Data') then ([Value]) else (0) and the opposite for the columns that use Query 1.

Hope this help!
Todd

------------------------------
Todd Schuman
------------------------------

Original Message:
Sent: Sun June 13, 2021 05:02 PM
From: Joseph Galvin
Subject: Cognos Business Intelligence Standalone Calculation

How can a stand alone calculation be added to a rows list report, as a separate column, without necessarily being automatically filtered by the data of the row or other parts of filters?

------------------------------
Joseph Galvin
------------------------------

Thank you.  For item 1, do you mean to place a filter in the query instead of the report body?  Is this possible?  I am extremely new to this reporting tool.
Item 1.2, you note adding a column at the end unfiltered.  My understanding in trying to figure out the reporting tool that filters of the report or query apply to the entire report attempted to being written.  Thank you.

------------------------------
Joseph Galvin
------------------------------

Original Message:
Sent: Mon June 14, 2021 08:17 AM
From: Todd Schuman
Subject: Cognos Business Intelligence Standalone Calculation

Hi Joseph,

I can think of 2 ways to do this

1. Calculation Filters
   1. Put the filter(s) on the columns/calcs in the list and not the list itself.  So, column 1 could be if([Year] = ?pYear? and [Month] = ?pMonth?) then ([Value]) else (0). 
   2. Add a column at the end could where the value is unfiltered or uses another set of filters similar to above.
2. Union 2 queries. 
   1. Leave the first query (Query 1) alone and add an extra data item as an identifier, maybe something like 'Filtered Query'. 
   2. Copy Query 1 and remove/modify the filters and change the identifier to 'Unfiltered Data' or something else to differentiate between the data in Query 1
   3. Union the queries together and use logic like if([Query Identifier] = 'Unfiltered Data') then ([Value]) else (0) and the opposite for the columns that use Query 1.

Hope this help!
Todd

------------------------------
Todd Schuman

Original Message:
Sent: Sun June 13, 2021 05:02 PM
From: Joseph Galvin
Subject: Cognos Business Intelligence Standalone Calculation

How can a stand alone calculation be added to a rows list report, as a separate column, without necessarily being automatically filtered by the data of the row or other parts of filters?

------------------------------
Joseph Galvin
------------------------------

Hi Joseph,

So each report object (List, Crosstab, Visualization, etc.) has a single query associated with it.  Those queries can have detail or summary filters applied that will filter the results of all data items in the query.  For your requirement, I would avoid using the Detail Filter shown below and focus on putting filters in the Data Items using if/then/else logic.  I put together a sample below which I hope better demonstrates.

-Todd




------------------------------
Todd Schuman
------------------------------

Original Message:
Sent: Mon June 14, 2021 09:22 AM
From: Joseph Galvin
Subject: Cognos Business Intelligence Standalone Calculation

Thank you.  For item 1, do you mean to place a filter in the query instead of the report body?  Is this possible?  I am extremely new to this reporting tool.
Item 1.2, you note adding a column at the end unfiltered.  My understanding in trying to figure out the reporting tool that filters of the report or query apply to the entire report attempted to being written.  Thank you.

------------------------------
Joseph Galvin

Original Message:
Sent: Mon June 14, 2021 08:17 AM
From: Todd Schuman
Subject: Cognos Business Intelligence Standalone Calculation

Hi Joseph,

I can think of 2 ways to do this

1. Calculation Filters
   1. Put the filter(s) on the columns/calcs in the list and not the list itself.  So, column 1 could be if([Year] = ?pYear? and [Month] = ?pMonth?) then ([Value]) else (0). 
   2. Add a column at the end could where the value is unfiltered or uses another set of filters similar to above.
2. Union 2 queries. 
   1. Leave the first query (Query 1) alone and add an extra data item as an identifier, maybe something like 'Filtered Query'. 
   2. Copy Query 1 and remove/modify the filters and change the identifier to 'Unfiltered Data' or something else to differentiate between the data in Query 1
   3. Union the queries together and use logic like if([Query Identifier] = 'Unfiltered Data') then ([Value]) else (0) and the opposite for the columns that use Query 1.

Hope this help!
Todd

------------------------------
Todd Schuman

Original Message:
Sent: Sun June 13, 2021 05:02 PM
From: Joseph Galvin
Subject: Cognos Business Intelligence Standalone Calculation

How can a stand alone calculation be added to a rows list report, as a separate column, without necessarily being automatically filtered by the data of the row or other parts of filters?

------------------------------
Joseph Galvin
------------------------------

Thank you.  Unfortunately, this is not working for our data.  Thank you for your input.

------------------------------
Joseph Galvin
------------------------------

Original Message:
Sent: Mon June 14, 2021 09:39 AM
From: Todd Schuman
Subject: Cognos Business Intelligence Standalone Calculation

Hi Joseph,

So each report object (List, Crosstab, Visualization, etc.) has a single query associated with it.  Those queries can have detail or summary filters applied that will filter the results of all data items in the query.  For your requirement, I would avoid using the Detail Filter shown below and focus on putting filters in the Data Items using if/then/else logic.  I put together a sample below which I hope better demonstrates.

-Todd




------------------------------
Todd Schuman

Original Message:
Sent: Mon June 14, 2021 09:22 AM
From: Joseph Galvin
Subject: Cognos Business Intelligence Standalone Calculation

Thank you.  For item 1, do you mean to place a filter in the query instead of the report body?  Is this possible?  I am extremely new to this reporting tool.
Item 1.2, you note adding a column at the end unfiltered.  My understanding in trying to figure out the reporting tool that filters of the report or query apply to the entire report attempted to being written.  Thank you.

------------------------------
Joseph Galvin

Original Message:
Sent: Mon June 14, 2021 08:17 AM
From: Todd Schuman
Subject: Cognos Business Intelligence Standalone Calculation

Hi Joseph,

I can think of 2 ways to do this

1. Calculation Filters
   1. Put the filter(s) on the columns/calcs in the list and not the list itself.  So, column 1 could be if([Year] = ?pYear? and [Month] = ?pMonth?) then ([Value]) else (0). 
   2. Add a column at the end could where the value is unfiltered or uses another set of filters similar to above.
2. Union 2 queries. 
   1. Leave the first query (Query 1) alone and add an extra data item as an identifier, maybe something like 'Filtered Query'. 
   2. Copy Query 1 and remove/modify the filters and change the identifier to 'Unfiltered Data' or something else to differentiate between the data in Query 1
   3. Union the queries together and use logic like if([Query Identifier] = 'Unfiltered Data') then ([Value]) else (0) and the opposite for the columns that use Query 1.

Hope this help!
Todd

------------------------------
Todd Schuman

Original Message:
Sent: Sun June 13, 2021 05:02 PM
From: Joseph Galvin
Subject: Cognos Business Intelligence Standalone Calculation

How can a stand alone calculation be added to a rows list report, as a separate column, without necessarily being automatically filtered by the data of the row or other parts of filters?

------------------------------
Joseph Galvin
------------------------------