Issue with the Key figure

[Former Member]

Hi All,

I have scenario, where there are 3 characteristics and one key figure in a info cube.

Now I'm performing reporting on these and displaying the data of Key figure in the normal Bex report.

Below is the sample data:

ticket id	category	status	duration
7000	sr1	1	100
7000	sr1	2	100
7000	sr1	3	0
7000	sr2	12	0
7000	sr2	10	50
7000	sr2	3	50

now when i display the data in the report for Duration basically should show me one record with 7000 and duration as 300 irrespective of Category and it should be 200 if category is sr1 and for sr2 it should be 100.

My requirement is irrespective of Status, no.of records, based on Category i need to display duration as 100 for Sr1 and 50 for Sr2. Is there any way to do this please?

Thanks & Regards,

Nazeer

[Former Member]

Hi Seshu,

Can you please explain how I can apply it?

Thanks,

Nazeer

[former_member199945]

Hi,

Can you refer this :

Constant Selection and Overall Result - SAP NetWeaver Business Warehouse - SCN Wiki

Create new selction  with  Duration Kfg  and take status  as constant selction for that same as above .

Thanks.

[Former Member]

Sorry Seshu, that doesn't help..

[KodandaPani_KV]

Hi Nazeer,

other wise create the one KF ZCOUNT give the constant value 1 at transformation form source to target.

after loading the data form source to target check the data in cube - it will give the each records count.

use the same KF count use in the for count purpose.

[former_member199945]

Hi,

Can you try like this create new formula which contains kfg duration and apply exception aggregation as summation with reference char as Category.

Thanks.

[Former Member]

Hi Phani,

This solution works but with small change, we need to populate count with 1 only if the duration value is <>0 because if you see my sample data there are 3 records with duration as 100, 100, and 0 so as per you the counter will be 3 and the final kf would be (100+100+0)/3 which is 66.66 which is not correct.

If we populate the counter with 1 if the duration is <>0 then the counter would be 2 and the final kf would be (100+100+0)/2 = 100 which is correct.

Is there any other way where we can do this in the query level instead of in the Cube level.

Thanks,

Nazeer

[former_member199945]

Hi,

Apply constant selection for  Duration kfg on char status.

Thanks.

[KodandaPani_KV]

Hi,

it is the possible .

create the new selection under column panel -> drag the object category and status objects -> right click -> restrict -> select category sr1 and status 1 ->

duration i KF.

same do like this other selections ->create the new selections -> restrict -> category and status ->category sr1 and status 10.

duration KF.

Thanks,

Phani.

[Former Member]

Hi Phani,

Thanks for your reply.

I have tried it earlier but the problem is we are not sure how many status would be coming it and as per your suggestions we need to create new selections for every status right which is tough.

Another thing, if we do like that also finally it will get aggregated and then show the final value as 200.

Thank,

Nazeer