Total Count (aggregation) without showing Characteristic in Row

[Former Member]

Gurus -

I have a report where I want to show #1 - total customers who have sales > 0 and also include the #2 -total number of customers who dont have sales. How can I get a total of customers (#2) without bring the field that I am doing the aggregation on? I dont want to bring the Customer Number (this is what is used to count customers) into the row of the report (leave it as a free characterstic).

Sales Customers who have sales Customers who dont have sales

-

---

-

---

-

---

10,000 1000 50000

Edited by: Patrick Nowak on Apr 2, 2009 7:12 PM

[former_member184494 · Accepted solution]

Use one CKF with count of all values and base the exception aggregation on customer

Have another ckf with count all values > 0 and exception aggregation on customer

and have another which is a difference between the two.

0customer shouold be in your freecharacteristics and you should get what you want...

[Former Member]

Set from Answered to Not Answered

[Former Member]

Dear Mr.Patrick,

i am facing requirement to show key figure "order counts" without including characteristic "order No." in the cube

any clue how to resolve this

regards

Girish

[Former Member]

Sorry I didnt respond earlier. If you havent solved this yet, what you need to do is create a formula variable with replacement path processing, using 0doc_num as the reference characteristic, On the replacement path tab, Replace with "Attrib. Value", Attribute is character reference (Constant 1). Then assign this formula variable to a Calculated Key Figure (detail view) and multiplying it by 1. There are others who have solved this issue too so searching sdn you will find it.

Hope this helps.

[Former Member]

Set from Answered to Not Answered

[Former Member]

HI,

Use calculated key figures, that will solve the issue, if I have understood your query correctly.

Do post back if you have any doubts.

Regards,

Ray

[Former Member]

Yes, I am using a Calculated Key figure with Exception Aggregation. The field that I am doing the aggregation on should not be shown in the Rows of the report (leave as a Free Characteristic). Only when I bring the Ref Characterstic in the report, the total is correct. ...but I dont want to show the Ref Characterstic in the report.

Edited by: Patrick Nowak on Apr 2, 2009 7:39 PM

[Former Member]

You can bring that characteristic into the rows, but set it's display property to None (instead of key, text, etc.) if you don't want to display it. Then your formula variables or calc KFs will still work.