Filter on fact query item not working (having clause)

[RubenvdLinden]

I have a factless fact table with incidents.
In Framework Manager I have created a fact query item 'Number of incidents' with the expression:

count([Database View].[FACT_TABLE].[DOC_GUID] auto)

This works fine in all my reports.

Now, I want to create a report of clients with 5 or more incidents (basically a HAVING clause in SQL). If I create a filter ([Number of incidents] >= 5), the report also shows clients with 4 or less incidents. I tried detail filters (before and after aggregation) and summary filters but I don't seem to get it to work.

What am I missing? We're using Cognos 8.4.

[MFGF]

If you view the generated SQL of the report, how does the filter get applied in the SQL?

[blom0344]

As an alternative define the counter within the report as:

count([Database View].[FACT_TABLE].[DOC_GUID] for [CLIENT])

and use this as a summary filter in the report:

count([Database View].[FACT_TABLE].[DOC_GUID] for [CLIENT])  > 5

In the native SQL description the filter will not appear, in the Cognos SQL it should be added directly after the entire SQL statement..

[RubenvdLinden]

@blom0344:
Soon after my post, I tried a similar solution to the one you provided and it works.
However, just to learn, I would still like to know why a filter on the query item in the framework does not.

@MFGF:
The Native SQL looks like this:

select "T0"."C0" "Weergavenaam", "T0"."C1" "Cliëntnaam", first_value("T0"."C2") over (partition by "T0"."C0", "T0"."C1") "Aantal_meldingen"
from (
select "T0"."C0" "C0", "T0"."C1" "C1", "T0"."C2" "C2", sum("T0"."C2") over () "C3"
from (
select "DIM_ORG_EENHEID"."WEERGAVENAAM" "C0", "DIM_CLIENT"."NAAM" "C1", count("FACT_MIC_FEITEN"."MIC_GUID") "C2"
from "DATAMART"."DIM_ORG_EENHEID" "DIM_ORG_EENHEID", "DATAMART"."DIM_CLIENT" "DIM_CLIENT", "DATAMART"."FACT_MIC_FEITEN" "FACT_MIC_FEITEN"
where "DIM_ORG_EENHEID"."ORG_EENH_KEY"="FACT_MIC_FEITEN"."ORG_EENH_KEY" and "DIM_CLIENT"."CLIENT_KEY"="FACT_MIC_FEITEN"."CLIENT_KEY"
group by "DIM_ORG_EENHEID"."WEERGAVENAAM", "DIM_CLIENT"."NAAM") "T0") "T0"
where "T0"."C3">10



The Cognos SQL looks like this:

select
       DIM_ORG_EENHEID.WEERGAVENAAM  as  Weergavenaam,
       DIM_CLIENT.NAAM  as  Cliëntnaam,
       XSUM(XCOUNT(FACT_MIC_FEITEN.MIC_GUID  for DIM_ORG_EENHEID.WEERGAVENAAM,DIM_CLIENT.NAAM )  at DIM_ORG_EENHEID.WEERGAVENAAM,DIM_CLIENT.NAAM  for DIM_ORG_EENHEID.WEERGAVENAAM,DIM_CLIENT.NAAM )  as  Aantal_meldingen
from
       "MiMIC datamart"..DATAMART.DIM_ORG_EENHEID DIM_ORG_EENHEID,
       "MiMIC datamart"..DATAMART.DIM_CLIENT DIM_CLIENT,
       "MiMIC datamart"..DATAMART.FACT_MIC_FEITEN FACT_MIC_FEITEN
where
       (DIM_ORG_EENHEID.ORG_EENH_KEY = FACT_MIC_FEITEN.ORG_EENH_KEY) and
       (DIM_CLIENT.CLIENT_KEY = FACT_MIC_FEITEN.CLIENT_KEY)
group by
       DIM_ORG_EENHEID.WEERGAVENAAM,
       DIM_CLIENT.NAAM
filter
       (XCOUNT(FACT_MIC_FEITEN.MIC_GUID ) > 10)

[blom0344]

I really have no conclusive answer to your question, other than the fact that hardcoding aggregates does not seem to be the 'right' way in Cognos 8. The whole idea is that you can change the aggregate at will from the report including the addition of a context. (The 'for' part)

The summary filter should AFAIK be executed by the server and not be part of the executed SQL. In your example the native SQL shows the filter as part of the where clause, which is not what you intent..

[RubenvdLinden]

As a developer I also prefer the 'for' clause, but my end users rely on the application intelligence. Cognos states that the 'auto' keyword of the expression is intended to automatically aggregate based on the grouping in a report and this clearly works. I would then expect it to work in a summary filter as well, which doesn't.

[flytrap]

Not being certain of your model, but when I have found myself with a fact-less fact table, i.e. a table of incidents, in FM I usually create a new Query Subject that is an exact copy of my actual data source table, and add a "Fact" Query Item to it such as "Incident Count" and in the Expression definition I just put the number 1, thus creating a "fact"

At this point most types of summaries and such I'd like to do on these incidents are handled automatically.  You can (I just did it b/c I am doing something similar) set a detail filter on the "count" field and set "after auto aggregation" and it will limit the results to items that have [xx] incidents associated with it.

;D

[RubenvdLinden]

That sounds plausible, I will give it a try. Thanks!

[flytrap]

Cool, I'm sure it will work for you.  A fact-less fact table generally only needs additional metadata (such as a "virtual" counter field of 1) to make it a true fact as related to your dimensional data.  Also when dealing with incidents (call record/trouble ticket/SPCR tracking apps), I will set an additional query item field that contains a date math expression, as many of these tables have some kind of start and end dates/times.

This is especially helpful when reporting both closed and open incidents, as you can build a Now() or sysdate clause into your expression to capture response times (or lack thereof  >:()on those open incidents.

[RubenvdLinden]

I have altered my framework and tested all my reports. I had to make a few minor adjustments to some of the existing reports but now it works perfectly. Thanks flytrap!