Business Analytics

Expand all | Collapse all

Cognos Business Intelligence Standalone Calculation

  • 1.  Cognos Business Intelligence Standalone Calculation

    Posted Sun June 13, 2021 05:02 PM
    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
    ------------------------------


  • 2.  RE: Cognos Business Intelligence Standalone Calculation

    Posted Mon June 14, 2021 08:17 AM
    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
    ------------------------------



  • 3.  RE: Cognos Business Intelligence Standalone Calculation

    Posted Mon June 14, 2021 09:23 AM
    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
    ------------------------------



  • 4.  RE: Cognos Business Intelligence Standalone Calculation

    Posted Mon June 14, 2021 09:40 AM
    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
    ------------------------------



  • 5.  RE: Cognos Business Intelligence Standalone Calculation

    Posted Mon June 14, 2021 12:00 PM
    Thank you.  Unfortunately, this is not working for our data.  Thank you for your input.

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