Issue with using variable in a List report

[asmfloyd]

Hello,

  I have a list report with a few dimension columns (Year/IndexCode/IndexName) and a measure column. The measure I am calculating inside a variable as below:

  IF ( FUND_CODE='AA') THEN (1) ELSE (0) where I am keeping my measure's Detail Aggregation Property as "Total"
My goal is to count the number of fund_codes of value 'AA'

When I check the generated sql, it creates an additional column "FUND_CODE_u" (as below- due to which only one row will have value but others will be null ) and hence the total is not working.
Any inputs why this is occurring and how to fix?


Generated SQL:
"FUND_CODE",
        CASE
            WHEN
                ROW_NUMBER()
                    OVER(
                        PARTITION BY
                            "ACADEMIC_YEAR_DIM0"."ACADEMIC_YEAR",
                            "IDX_DIM"."IndexCode",
                            "IDX_DIM"."IndexName",
                            "FDX_DIM"."FUND_CODE"
                        ORDER BY
                            "ACADEMIC_YEAR_DIM0"."ACADEMIC_YEAR" ASC,
                            "IDX_DIM"."IndexCode" ASC,
                             "IDX_DIM"."IndexName" ASC,
                            "FDX_DIM"."FUND_CODE" ASC
                    ) = 1
                THEN
                    "FDX_DIM"."FUND_CODE"
            ELSE NULL
        END AS "FUND_CODE_u"

Thanks,

[MFGF]

Hello,

  I have a list report with a few dimension columns (Year/IndexCode/IndexName) and a measure column. The measure I am calculating inside a variable as below:

  IF ( FUND_CODE='AA') THEN (1) ELSE (0) where I am keeping my measure's Detail Aggregation Property as "Total"
My goal is to count the number of fund_codes of value 'AA'

When I check the generated sql, it creates an additional column "FUND_CODE_u" (as below- due to which only one row will have value but others will be null ) and hence the total is not working.
Any inputs why this is occurring and how to fix?


Generated SQL:
"FUND_CODE",
        CASE
            WHEN
                ROW_NUMBER()
                    OVER(
                        PARTITION BY
                            "ACADEMIC_YEAR_DIM0"."ACADEMIC_YEAR",
                            "IDX_DIM"."IndexCode",
                            "IDX_DIM"."IndexName",
                            "FDX_DIM"."FUND_CODE"
                        ORDER BY
                            "ACADEMIC_YEAR_DIM0"."ACADEMIC_YEAR" ASC,
                            "IDX_DIM"."IndexCode" ASC,
                             "IDX_DIM"."IndexName" ASC,
                            "FDX_DIM"."FUND_CODE" ASC
                    ) = 1
                THEN
                    "FDX_DIM"."FUND_CODE"
            ELSE NULL
        END AS "FUND_CODE_u"

Thanks,

Hi,

When you create a calculation, the timing is controlled based on whether you use item names from the existing query or item names from the source pane in your expression. If you use item names from the query, the calculation happens after the default aggregation has been done (aggregate then calculate), whereas using item names from the source pane makes the calculation happen before the default aggregation (calculate then aggregate). I'm assuming you need the latter?

If so, change the expression of your calculation so that [FUND_CODE] (from the query) is replaced by [Your package].[Your Query Subject].[FUND_CODE] (from the source pane). You can just drag it in to get the fully qualified name.

Cheers!

MF.

[asmfloyd]

Thank you MF for the reply.

I tried. But that alone is not taking out the partition condition. Instead I tried in another way which removed the partitioned extra column in the query.
Instead of hardcoded 1, I used a column "DUMMY_COUNT"; which is a dummy column in database having a value of '1' for all rows.
 Not sure why it is behaving this way. Probably a bug?
 
IF ( [Your package].[Your Query Subject].[FUND_CODE]='AA') THEN
([Your package].[Your Query Subject].DUMMY_COUNT)
ELSE (0)