Exceptional Aggregation in Query Designer

[Former Member]

Hello Gurus,

we have an existing  Report at the Material Level on the InfoCube. 
Each Material may have multiple POs and we are having multiple 
records for each PO varying the date. Now the user want to view 
only the record latest changed Record at the PO level 
and Sum of all the records at the Material Level.

For this i have created a CKF using the Exception Aggregation
 as Maximum for the 0calday. But client want the Report at Material Level 
he dont want the PO's details. When i remove the PO from the report 
it gives the result for only the latest PO not all the PO for that Material.

For Example we are having below records in the cube. 

PO             Material     Calday       Qty         Price
101            ABC          1/12           100         10
101            ABC          2/12            100         10
101            ABC          3/12            100         11
102            ABC          4/12           100         10
104            ABC          5/12            100         10
103            ABC          7/12            100         11

I am getting the result with PO in Rows as Below

PO             Material     Calday       Qty         Price
101            ABC          3/12            100         11
102            ABC          4/12           100         10
104            ABC          5/12            100         10
103            ABC          7/12            100         11

When I remove the PO's From the Rows then it will display 
as below i am placing the calday for under standing the 
question actually it is not present in the  output of the below report only.

        Material     Calday       Qty         Price
          ABC         7/12          100         11

Which is not currect I have to get the Result as

Material            Qty         Price
    ABC              500         42


My Concerns:

!. Kindly let me know if it is possible and what are the 
other things that i need to consider.
2. Did it will impact the performance.

Waiting for your quick replies

Thanks & Regards

KK

Edited by: KK on Dec 23, 2009 11:08 AM

Edited by: KK on Dec 23, 2009 11:15 AM

Edited by: KK on Dec 23, 2009 11:22 AM

[Former Member · Accepted solution]

Hi KK

Confirmation:

First I need to confirm one thing,

as you mentioned expected output is,

Material Qty Price

ABC 500 42

But I think, it should be

Material Qty Price

ABC 400 42

right?

Answer:

Can you please try below steps and let me know whether it is working as expected or not

Create a basic Key figure for calday and include it in your InfoCube. In the transformation, assign it to same fileld which is a source of calday.

In query, Create a calculated key figure based on calday key figure with exception aggr as last value and reference characteristics as PO.

Regards

Rohit

[Former Member · Accepted solution]

Thanks Rohit,

Output which i am getting is

Material  Qty  Price
ABC        100   11

But I think, it should be 

Material  Qty  Price
ABC      400    42
 
For ths latest PO record i have created one CKF but need to Create nested 
which is possible and i also created for testing its working fine. 
But my query is in BW 3.5 version. 
how could i get the nested Exceptional Aggregation in 3.x version. 
i tried it by creating the CKF 
pls check my previous Comments in Procedure which i followed.

Regards
KK

Edited by: KK on Dec 28, 2009 6:59 AM

[Former Member · Accepted solution]

Hi,

I think it might be the limitations of 3x .Sorry I did not have 3x version,earlier i tried in the 3x view of the 7.0 version. If CKF is not working, could you try to use local formula for both the cases? ie Create first formula with the aggregation properties for the first CKF and then create a second formula using the first formula with a different aggregation in the column.

Please let me know if the system allows this.

Thanks.

[Former Member]

Hello Experts

Please let me know if there are any other procedure at the query level only.

as i cant changed the Modeling designed as per the instruction from the client.

Regards

KK

Edited by: KK on Dec 24, 2009 7:10 AM

[Former Member]

Hi,

Nested Exception aggregation will solve your issue i.e You may need to create one more exception aggregation keyfigure. Create another formula using your current display keyfigure and use Exception aggregation as 'Total' and reference characteristics as 'Material'. Hide the previous key figure and display the final key figure.

Now the total of the qty and price will be displayed regardless of the PO item and it will never affect your performance of the report.

Thanks & Regards,

Raja

[Former Member]

Hello Raja,

Thanks a lot for ur response. But i think this is possible in BI 7.Query Designer and our Queries has been built on 3.5 and rest of the modelling is done in BI 7.0. So kindly let me know if there is any alternate solution. I had already assigned points to your answer

Regards

KK

[Former Member]

Hi,

I think it is possible in BW 3x as well. I tried here in 3x and I am able to create new formula based on the existing one & I can able to change the exception aggregation properties of the new formula . I hope this properties is same in 3x.

You have already created one keyfigure(CKF) with aggregation properties . Now you may need to create one more keyfigure (if you face any issue in creating new CKF,try to create a local formula keyfigure in the column) using the existing CKF with the different aggregation properties. Hide the first CKF and display the new keyfigure.This process is similar to the first one . Please let me know if you still face any issue with this.

There might be another alternate solution .i.e.adding extra fields and storing the values in the InfoProviders itself .In this case ,you may need to delete the data and reload it again.

Hence I would suggest you to choose the aggregation, which surely solve your issue without any additional work.

Thanks.

[Former Member]

Hello Raja,

Thanks for ur reply i had tried with nested Exceptional Aggration in BI 7.0 its working fine. But when i try to make the same in the 3.x version i am unable to get the exact results in the report. its working same as the first CKF which i am using to create the second CKF but adding the differet PO.

Procedure which i had adapted.

1. Created one CKF with Exception Aggregation as Last with respect to 0calday

2. Created another CKF but i am unable to get the Exception Aggreagation option over here.instead i will get the option time of calculation here i am having 2 option before Aggregation and After Aggregation.

or

I had also tried as you mention by creating the local formula but getting the same CKF value ie first one for getting the last 0calday value, where i have to get the summation of all the last 0calday PO's which are created using the Material as i had explain in my question.

Thanks kindly let me know if i am missing any thing

Regards

KK