Setting default value for a fact column

[asmfloyd]

Hello Gurus,
  I am new to Cognos. I have an integer measure value "NETVAL" pulled from a summary table into a list report. For the report level filter I am applying, for each dimension "fundcode" value, I will have 4-5 rows getting pulled from the database (which the column will aggregate internally).

I need to give a condition that if my fundcode='MF' hardcode the result value as 200 else the "NETVAL".

   For now if I give condition as below,the result for other fundcodes, it is giving correctly. But for "MF", it is giving as 800. How can I fix this so that it will show as 200 for MF?
        IF (fundcode='MF') then (200) else (NETVAL)
   

Thanks,

[MFGF]

Hello Gurus,
  I am new to Cognos. I have an integer measure value "NETVAL" pulled from a summary table into a list report. For the report level filter I am applying, for each dimension "fundcode" value, I will have 4-5 rows getting pulled from the database (which the column will aggregate internally).

I need to give a condition that if my fundcode='MF' hardcode the result value as 200 else the "NETVAL".

   For now if I give condition as below,the result for other fundcodes, it is giving correctly. But for "MF", it is giving as 800. How can I fix this so that it will show as 200 for MF?
        IF (fundcode='MF') then (200) else (NETVAL)
   

Thanks,

Hi,

Is this expression in a calculation or in a filter? I assume a calculation?

If so, the timing of the calculation can either be before aggregation or after aggregation, based on how you code the expression. The trick is in understanding what drives this.

Normally, if you use items directly from the package in your expression, the calculation is performed before the default aggregation in your report. You can tell by observing that the item names have three parts - [namespace].[query subject].[query item]

eg in your expression above, you'd see something like

IF ([Your namespace].[Your dimension query subject].[fundcode]='MF') then (200) else ([Your namespace].[Your fact query subject].[NETVAL])

However, if you bring in items from the query rather than from the package, the calculation is performed after the default aggregation in your report. In this case, the item names in the expression have just one part - [query item]

eg in your expression above, you'd see something like

IF ([fundcode]='MF') then (200) else ([NETVAL])

Obviously, if the timing of the calculation is before aggregation, the 200 would be calculated for each underlying row of data, and these row values would then be aggregated, so if you had four rows your aggregated value would be 800. If the timing of the calculation is after aggregation, the 200 would be calculated after the four rows had been aggregated, so your displayed value would be 200.

If I'm wrong, and this expression is being used in a filter somewhere, then things are a little more intuitive in terms of the timing, as detail filters have a selection that allows you to choose before/after aggregation timing.

Cheers!

MF.

[asmfloyd]

Thanks much for giving lots of details MFGF. I understood.
   Mine is a calculation. As per your advice, I kept them in a query (rather a package) and it worked as expected.
      Till you told, I was on the assumption that both were the same and was thinking on how to do this (like the option we have in filter-before/after aggregation). :(

Thank you.