To count the No of Purchase orders in 0PUR_C01 cube

[Former Member]

Hello All,

I have a requirement to find the "number of Purchase order". To calculate this we have a standard routine in the header level of the update rule 2LIS_02_HDR. ( and it works fine)

But when we drag the Profit Center (which is calculated at the Item level update rule 2LIS_02_ITM), all the values of the "number of Purchase order" is 0.

Question ; Is there any logic or any alternative by which we can calculate the"no of purchase order" at the item level ( as Profit center is maintained at the Item level)

Please let me know your views and ideas. I am stuck with this.

[Former Member · Accepted solution]

Hi Ram,

Check this doc: [How to.... get document count in Query|http://www.box.net/shared/b4p5oefubr]

[More Docs |http://sapbwneelam.blogspot.com/]

Hope it Helps

Srini

[Former Member · Accepted solution]

Create a formal -

---

> Go to propertis tick on "culmulated" and in flowling drop list select 2nd option "Calculate along the row"

use that formual u will get count

[Former Member · Accepted solution]

Hello All,

Sorry for replying late, I was trying all the options you guys mentioned. I solved my problem by using the Srinivas Neelam's document. In which:

1) I placed the Profit center in the rows and the Spend $ (some Key figure) in the column

2) Created a Formula variable with reference characteristic Purchase Document (0DOC_NUM) and in the properties I gave :

Replace variable with : Attribute Value

Attribute Value : Reference to Characteristic (Constant 1)

3) Kept this formula variable in a Calculated Key Figure and named it as No Of PUR Order, and kept this key figure in the column.

4) To my surprise it worked.

But could'nt understand fully how this replace Attribute value works? Can any body please provide me some idea.

[Former Member · Accepted solution]

Hello Mr. Soma Venkateshwarlu ,

I cannot able to locate the Calculate along the row in the formula variable. Can you please provide some detail view please.

[jasonmuzzy · Accepted solution]

If you have purchase order number in your cube then you can use the easiest method of all of counting -- a calculated key figure with exception aggregation.

Create a CKF and add any basic key figure to it from your cube (basic means a key figure from the cube, not another CKF or RKF). If you're using the 3.x query designer then click the Enhance button and set the exception aggregation to Counting All Values. If you're using the 7.0 query designer then click on the Aggregation tab and switch the Exception Aggregation to Counter for All Detailed Values. With either query designer set the reference characteristic to your PO number characteristic. This CKF will count the number of PO documents.

See this document for step-by-step instructions: [How to... count the occurrences of a characteristic relative to one or more other characteristics|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/7e58e690-0201-0010-fd85-a2f29a41c7af].

[jasonmuzzy]

You might be able to use constant selection to solve this, depending on your cube model. Create a restricted key figure, put your key figure into it (the one you calculate in the update rule) and then also put profit center in there. Right-click on profit center and choose constant selection. Use this RKF in your query.

Basically constant selection does a left join, so your key figure will act like profit center isn't even in the drill down. Be careful, though -- that could mean that your counts will get doubled up (or more)!