Exception aggregation _Count in Calculation view.

[former_member184624]

Dear HANA Experts,

In one of the report, I am displaying the Purchase order number with "Number of Purchase orders"

But if i include PO Line number, below is the output.

But my client wants to display as below.

But if i remove PO Line number, the number of POs, should not count. It should be

P001 - 1

P002 -1

How to achieve this in semantics of the calculation view. In Bex report, the system itself is taking care (by exception aggregation -> Count) and it is working fine.

Pls suggest, how to achieve this in Sematics of the calculation view, Sematics.

Thanks.

[Abhishek_Hazra · Accepted solution]

Hi jelina.masilamani,

I think you can achieve the output you need by building a calculation view somewhat like below, I just recreated the sample you have provided.

Table data :

Calclation view : Create with default node Projection.

Aggregation_1 node selects just the dimensions : PO & PO_ITEM, & Aggregation_2 node selects only PO & aggregated column NO_OF_PO from the same table.

Aggregation_2 :

Now simply join between Aggregation_1 & 2 on column PO. Since your default node is projection, it would consider NO_OF_PO as a dimension & you should not change it to measure if you want the output as you mentioned.

Now, you should be ready with it. Below are 2 sql scripts, one with selecting PO_ITEM & another without selecting PO_ITEM.

SQL1 (with PO_ITEM)

SQL2 (without PO_ITEM)

Note : If you are only looking for counter, remember that you can always create a counter based on count distinct exception aggregation if your calculation view's default node is aggregation.

In that case you need to select PO for the exception aggregation base column.

But this will only return the distinct counts, not total number of POs.

Hope this helps.

Best Regards,
Abhi

[former_member184624 · Accepted solution]

Dear Hazra,

Thank you for the detailed steps.

Right now, my Customer is testing the report in Sematics by slice and dicing in the calculation view. And client wants to use one field "NO_OF_POs". If they use Purchase order line number, then it should display 1 for all Line items. And if they remove puchase Line item, then the same field should not sum up all the line items. It should display 1. Just want to know, how to achieve this functionality by using one field ? please suggest. And also, please let me know, where can write SQL in HANA Modeling.

Thanks.

[Abhishek_Hazra · Accepted solution]

Hi jelina.masilamani ,

That's what I explained in the graphical calculation view in my above comment. Can you try that approach?

And you can write sql in the modeller perspective from SQL console.

Best Regards,
Abhi

[former_member184624 · Accepted solution]

Thank you very much.