Hello gurus.
We have a relational FM model as source and need to filter the result set.
The report is quite simple
||Country||Article Category||Qty sold||Revenue||No of sale orders||

The thing is that I want to se the figures for orders that have revenue value >15000
I can solve that by using a filter like:

total([Business view - Sales Payments].[Sales Payments].[Revenue Ex. VAT] for [Business view - Sales Payments].[Sales Payments].[Order Number])>15000​

The filter makes Cognos do the filtering on the result set, similar to "filter after aggregation". The issue is that the result set has around 100 million rows, meaning Cognos uses way too long to display the report.

Does anyone have an idea on how to force Cognos to send this down to the database instead? Should be self service friendly, as business users do these filters.

------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------

#CognosAnalyticswithWatson

Hi Sandeep,

You might want to use the Query Hints in the report / Query window.



Processing = Database only
Rollup Processing = Database

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company
------------------------------

Original Message:
Sent: Mon November 15, 2021 04:39 AM
From: Sandeep Dhirad
Subject: Filter result set: need advice

Hello gurus.
We have a relational FM model as source and need to filter the result set.
The report is quite simple
||Country||Article Category||Qty sold||Revenue||No of sale orders||

The thing is that I want to se the figures for orders that have revenue value >15000
I can solve that by using a filter like:

total([Business view - Sales Payments].[Sales Payments].[Revenue Ex. VAT] for [Business view - Sales Payments].[Sales Payments].[Order Number])>15000​

The filter makes Cognos do the filtering on the result set, similar to "filter after aggregation". The issue is that the result set has around 100 million rows, meaning Cognos uses way too long to display the report.

Does anyone have an idea on how to force Cognos to send this down to the database instead? Should be self service friendly, as business users do these filters.

------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------

#CognosAnalyticswithWatson

Thanks for the suggestion Patrick, but by changing query hints, as you suggested, the filter for amount isn't applied at all/is ignored - which shows in the generated query as well is in the report output. As you can see, there's no mention of ordernumber in the query:

Query sent to the database

SELECT
           "DMD_ORGANIZATION_TAB0"."COUNTRYNAME"                                    AS "Country"
         , "DMD_ORGANIZATION_TAB0"."DEPNAME"                                        AS "Store"
         , "DMD_PAYMENTTYPE_TAB"."PAYMENTTYPE"                                      AS "Payment_Type_Group"
         , "DMD_PAYMENTTYPE_TAB"."DESCRIPTION"                                      AS "Payment_Type"
         , "DMD_CHANNEL_TAB"."CHANNELNAME"                                          AS "Channel"
         , SUM("DMF_SALE_PAYMENT_TAB"."VALREVEX" * "DMD_CURRENCYRATE_TAB"."ACTUAL") AS "Payment_Amount"
FROM
           "PUBLIC"."DMD_CHANNEL_TAB" "DMD_CHANNEL_TAB"
           INNER JOIN
                      "PUBLIC"."DMF_SALE_PAYMENT_TAB" "DMF_SALE_PAYMENT_TAB"
                      ON
                                 "DMD_CHANNEL_TAB"."CHANNELID" = "DMF_SALE_PAYMENT_TAB"."CHANNELID"
           INNER JOIN
                      "PUBLIC"."DMD_CURRENCYRATE_TAB" "DMD_CURRENCYRATE_TAB"
                      ON
                                 "DMD_CURRENCYRATE_TAB"."CURRENCYID" = "DMF_SALE_PAYMENT_TAB"."CURRENCYID"
           INNER JOIN
                      "PUBLIC"."DMD_ORGANIZATION_TAB" "DMD_ORGANIZATION_TAB0"
                      ON
                                 "DMF_SALE_PAYMENT_TAB"."DEPID" = "DMD_ORGANIZATION_TAB0"."DEPID"
           INNER JOIN
                      "PUBLIC"."DMD_PAYMENTTYPE_TAB" "DMD_PAYMENTTYPE_TAB"
                      ON
                                 "DMD_PAYMENTTYPE_TAB"."PAYMENTTYPEID" = "DMF_SALE_PAYMENT_TAB"."PAYMENTTYPEID"
           INNER JOIN
                      "COGNOS"."V_DMD_TIME_TAB" "DMD_TIME_TAB"
                      ON
                                 "DMD_TIME_TAB"."PERIODID" = "DMF_SALE_PAYMENT_TAB"."PERIODID"
WHERE
           "DMD_TIME_TAB"."YEAR" IN ( 2020 )
GROUP BY
           "DMD_ORGANIZATION_TAB0"."COUNTRYNAME"
         , "DMD_ORGANIZATION_TAB0"."DEPNAME"
         , "DMD_PAYMENTTYPE_TAB"."PAYMENTTYPE"
         , "DMD_PAYMENTTYPE_TAB"."DESCRIPTION"
         , "DMD_CHANNEL_TAB"."CHANNELNAME"​

------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------

Original Message:
Sent: Mon November 15, 2021 09:16 AM
From: Patrick Neveu
Subject: Filter result set: need advice

Hi Sandeep,

You might want to use the Query Hints in the report / Query window.



Processing = Database only
Rollup Processing = Database

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company

Original Message:
Sent: Mon November 15, 2021 04:39 AM
From: Sandeep Dhirad
Subject: Filter result set: need advice

Hello gurus.
We have a relational FM model as source and need to filter the result set.
The report is quite simple
||Country||Article Category||Qty sold||Revenue||No of sale orders||

The thing is that I want to se the figures for orders that have revenue value >15000
I can solve that by using a filter like:

total([Business view - Sales Payments].[Sales Payments].[Revenue Ex. VAT] for [Business view - Sales Payments].[Sales Payments].[Order Number])>15000​

The filter makes Cognos do the filtering on the result set, similar to "filter after aggregation". The issue is that the result set has around 100 million rows, meaning Cognos uses way too long to display the report.

Does anyone have an idea on how to force Cognos to send this down to the database instead? Should be self service friendly, as business users do these filters.

------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------

#CognosAnalyticswithWatson

Hi Sandeep,
Would you be able to show the CognosSQL as well please.

When I simulate the above, using either a Data module or FM package, the filter expression does get sent with the Native SQL.
In this case I'm using CA 11.2.1 and a SQL Server database for the data.

I've attached the CognosSQL, NativeSQL and report spec, which is basically::
item1: <expression>[gosales_8_2].[ORDER_HEADER].[RETAILER_NAME]</expression>
item 2: <expression>[gosales_8_2].[ORDER_DETAILS].[QUANTITY]</expression>
detail filter:<filterExpression>total (
[gosales_8_2].[ORDER_DETAILS].[QUANTITY]
for [gosales_8_2].[ORDER_DETAILS].[ORDER_NUMBER] ) &gt; 7000</filterExpression>

------------------------------
Kind regards,
HENK CAZEMIER
------------------------------

Original Message:
Sent: Mon November 15, 2021 02:07 PM
From: Sandeep Dhirad
Subject: Filter result set: need advice

Thanks for the suggestion Patrick, but by changing query hints, as you suggested, the filter for amount isn't applied at all/is ignored - which shows in the generated query as well is in the report output. As you can see, there's no mention of ordernumber in the query:

Query sent to the database

SELECT           "DMD_ORGANIZATION_TAB0"."COUNTRYNAME"                                    AS "Country"         , "DMD_ORGANIZATION_TAB0"."DEPNAME"                                        AS "Store"         , "DMD_PAYMENTTYPE_TAB"."PAYMENTTYPE"                                      AS "Payment_Type_Group"         , "DMD_PAYMENTTYPE_TAB"."DESCRIPTION"                                      AS "Payment_Type"         , "DMD_CHANNEL_TAB"."CHANNELNAME"                                          AS "Channel"         , SUM("DMF_SALE_PAYMENT_TAB"."VALREVEX" * "DMD_CURRENCYRATE_TAB"."ACTUAL") AS "Payment_Amount"FROM           "PUBLIC"."DMD_CHANNEL_TAB" "DMD_CHANNEL_TAB"           INNER JOIN                      "PUBLIC"."DMF_SALE_PAYMENT_TAB" "DMF_SALE_PAYMENT_TAB"                      ON                                 "DMD_CHANNEL_TAB"."CHANNELID" = "DMF_SALE_PAYMENT_TAB"."CHANNELID"           INNER JOIN                      "PUBLIC"."DMD_CURRENCYRATE_TAB" "DMD_CURRENCYRATE_TAB"                      ON                                 "DMD_CURRENCYRATE_TAB"."CURRENCYID" = "DMF_SALE_PAYMENT_TAB"."CURRENCYID"           INNER JOIN                      "PUBLIC"."DMD_ORGANIZATION_TAB" "DMD_ORGANIZATION_TAB0"                      ON                                 "DMF_SALE_PAYMENT_TAB"."DEPID" = "DMD_ORGANIZATION_TAB0"."DEPID"           INNER JOIN                      "PUBLIC"."DMD_PAYMENTTYPE_TAB" "DMD_PAYMENTTYPE_TAB"                      ON                                 "DMD_PAYMENTTYPE_TAB"."PAYMENTTYPEID" = "DMF_SALE_PAYMENT_TAB"."PAYMENTTYPEID"           INNER JOIN                      "COGNOS"."V_DMD_TIME_TAB" "DMD_TIME_TAB"                      ON                                 "DMD_TIME_TAB"."PERIODID" = "DMF_SALE_PAYMENT_TAB"."PERIODID"WHERE           "DMD_TIME_TAB"."YEAR" IN ( 2020 )GROUP BY           "DMD_ORGANIZATION_TAB0"."COUNTRYNAME"         , "DMD_ORGANIZATION_TAB0"."DEPNAME"         , "DMD_PAYMENTTYPE_TAB"."PAYMENTTYPE"         , "DMD_PAYMENTTYPE_TAB"."DESCRIPTION"         , "DMD_CHANNEL_TAB"."CHANNELNAME"​

------------------------------
Sandeep Dhirad
Elkjøp Nordic

Original Message:
Sent: Mon November 15, 2021 09:16 AM
From: Patrick Neveu
Subject: Filter result set: need advice

Hi Sandeep,

You might want to use the Query Hints in the report / Query window.



Processing = Database only
Rollup Processing = Database

Best regards,

------------------------------
Patrick Neveu
Positive Thinking Company

Original Message:
Sent: Mon November 15, 2021 04:39 AM
From: Sandeep Dhirad
Subject: Filter result set: need advice

Hello gurus.
We have a relational FM model as source and need to filter the result set.
The report is quite simple
||Country||Article Category||Qty sold||Revenue||No of sale orders||

The thing is that I want to se the figures for orders that have revenue value >15000
I can solve that by using a filter like:

total([Business view - Sales Payments].[Sales Payments].[Revenue Ex. VAT] for [Business view - Sales Payments].[Sales Payments].[Order Number])>15000​

The filter makes Cognos do the filtering on the result set, similar to "filter after aggregation". The issue is that the result set has around 100 million rows, meaning Cognos uses way too long to display the report.

Does anyone have an idea on how to force Cognos to send this down to the database instead? Should be self service friendly, as business users do these filters.

------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------

#CognosAnalyticswithWatson

Hi, I recommend to have an aggregation on database level. I.e. either by extra aggregated tables or dynamically, e.g. by views. If neither is possible you could place a Cognos Analytics Data Set in between the FM model and the report. The aggregation as well as the filter can be applied there and scheduled before the report execution. All of this only works if you don't need detail rows but only key figures on order level.

------------------------------
Philipp Hornung
------------------------------

Original Message:
Sent: Mon November 15, 2021 04:39 AM
From: Sandeep Dhirad
Subject: Filter result set: need advice

Hello gurus.
We have a relational FM model as source and need to filter the result set.
The report is quite simple
||Country||Article Category||Qty sold||Revenue||No of sale orders||

The thing is that I want to se the figures for orders that have revenue value >15000
I can solve that by using a filter like:

total([Business view - Sales Payments].[Sales Payments].[Revenue Ex. VAT] for [Business view - Sales Payments].[Sales Payments].[Order Number])>15000​

The filter makes Cognos do the filtering on the result set, similar to "filter after aggregation". The issue is that the result set has around 100 million rows, meaning Cognos uses way too long to display the report.

Does anyone have an idea on how to force Cognos to send this down to the database instead? Should be self service friendly, as business users do these filters.

------------------------------
Sandeep Dhirad
Elkjøp Nordic
------------------------------

#CognosAnalyticswithWatson