Question:
Dear Gurus,
i had a query to be created that gives a count of a certain combination of characteristics from a cube.
Say a cube has 4 characteristics , Say A, B, C, D.
Now amongst all the records that occured in the Cube I need a report of the following A, B and be able to see the count of the number of times a combination of A B occured i.e. variations of C and D for a certain A and B.
For example say in the cube the A B C D has the following contents
a1,b1,c1,d1
a1,b1,c1,d2
a1,b1,c1,d3
a1,b1,c2,d1
a1,b2,c1,d1
a1,b2,c1,d2
a1,b2,c2,d1
a1,b2,c2,d2
a2,b1,c1,d1.
the report should show
a1,b1,4 , where 4 is the number of combination of a1 & b1.
a1,b2,4 , where 4 is the number of combination of a1 & b2.
a2,b1,1 , where 1 is the number of combination of a2 & b1.
i want a help from all the gurus regarding this query. it seems to be complicated. i request you to give the ideas for solving this problem as i didn't worked with these type of reports.
Thanking you in advance
Answer:
Create an InfoObject Counter and put it in the cube. In the update rules, set it to 1.
In the query, create a Calc KYF X with exception aggregation average and ref. char. A. Formula should be =Counter
Create a second Calc KYF Y, with formula =Counter/X
Now if you drilldown by A and B, Y will give you the number of different combinations.
Leandro
Answer:
hai Leandro,
thanks for your reply. Already cube has been built and has data. Now no key figure should be built. can we built Calculated key figure in the query 'Counter' instead of infoobject as specified by you and make the value of CFY = 1.
Now, again create a second CFY and do the rest of the things as specified by you.
I am trying by these. whether these will work or not. I will give reply whether if it has worked.
If you have an idea please tell me.
Thanks ..
Answer:
If it does not work (and I'm skeptic honestly), you can try to use any key figure you already have. You just to be sure that it always has values.
So, pick a key figure you already have ub the InfoCube (one that is never 0). Let's say it's AMOUNT.
Then,
In the query, create a Calc KYF X with exception aggregation average and ref. char. A. Formula should be =AMOUNT
Create a second Calc KYF Y, with formula =AMOUNT/X
This should work as well (but when the next developer comes after you leave, he will blow his head try to understand it).
Leandro