Question:
I've searched through the site and have not come across how to accomplish what I need to do. I'm new to BEX, so I still talk a lot in SQL terms.
We are replicating reports from another system and we have several sitautions where the Key Figure within BW was included in a 'Group By' clause in the old SQL. How do we replicate this in BEX?
Data:
Part (CHAR), LCL Price (KF), Quantity (KF)
A , .20 , 100
A , .20 , 200
A , .30 , 100
Needed to dispaly:
Part , LCL Price , Qty , Qty Value (using before aggregation)
A , .2 , 300 , 60
A , .3 , 100 , 30
What we currenlty have is:
Part , LCL Price , Qty , Qty Value (using before aggregation)
A , .7 , 400 , 90
In other situations, we might want to group by the Qty field to display the distinct values of the qty, so we would still need to have the capability of summing that KF. We started to create duplicate Characteristics in order to include in the group by, but that doesn't seem to be a logical solution when we started looking at all the KFs that we would need to do this for. It's probably a simple solution, but I just haven't been able to find it in different documentation out there.
Thanks!
Answer:
Hi, schets
I'd the same problem to solve here, and achieved to do this only in coding Visual Basic (and not a little program).
The problem is the following : you want a KF to act as a characteristic, and BW cannot do this, to my knowledge.
My solution was to display in the query the smallest level of characteristic which is relevant for your analysis (that's to say, for example, invoice line, if you want to analyse medium sales price and quantities sold), and, after, use the cross table tool in Excel to transform the KF in analysis axis.
The other solution is to create another cube where the price is a characteristic (which will be the case on our project, the previous solution not working with Web Reporting )
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.