Aggregate Requirement

[Raghuvir]

Hi All,

I have a following requirement in the report which i have attached in the below image file. The Total for PO_ID 004 should be equal to the Pending Amount which is not happening in my case. Could you please help me to achieve the same.

Regards

[navissar]

Your measure (Pending Amount) repeats for every Line Desc. That you group it doesn't mean that it is only calculated once, only that it is displayed once for each group.
So, your total row is (number of Line Descs)*(Pending Amount), in your case 14*456950.38=6,397,305.32
If you don't want the number to aggregate set its aggregate function to none, or use maximum, minimum or average as your aggregation type.

[Raghuvir]

Hi Nimod,

thanks for the reponse. changing the aggregate property to none helped me to achieve the Total for PO_ID.

What issue i am facing now is if one PO_ID has 2 ACCT_ID. I have attached the screenshot for the same.

Regards

[Raghuvir]

Hi,

i have modified the image in the screenshot so the logic of "total row is (number of Line Descs)*(Pending Amount), in your case 14*456950.38=6,397,305.32" wull give wrong result. the dada is quite big so i had to cut the image and post it to explain my requirement.

Regards

[navissar]

Try something along these lines as your summary:
total(minimum([pending amount] for [acct_id]) for [PO_ID])

[Raghuvir]

Hi,

I tried using that function but it only returns the minimum value, whereas we require the sum of values present on the Pending Amount Field.

Regards

[Raghuvir]

Hi All,

Anyone found out anything on the posted requirement.

Regards

[MFGF]

Hi All,

Anyone found out anything on the posted requirement.

Regards

Wow! You waited a whole one hour, nine minutes and eight seconds from your previous post before posting this. Do you realise how rude and ill-mannered it is to be so pushy when people are giving up time in their working days or their own personal time to provide help on these pages? Please read and digest the Forum Etiquette post here, paying particular attention to paragraph 2.

MF.

[Lynn]

Did you try changing the rollup aggregate function?

[Raghuvir]

Wow! You waited a whole one hour, nine minutes and eight seconds from your previous post before posting this. Do you realise how rude and ill-mannered it is to be so pushy when people are giving up time in their working days or their own personal time to provide help on these pages? Please read and digest the Forum Etiquette post here, paying particular attention to paragraph 2.

MF.

Apologies MFGF...will be  careful next time. Have been stuck with this for few days now...so became restless. Will be careful not to repeat this again.

Regards

[Raghuvir]

Did you try changing the rollup aggregate function?

Hi Lynn,

Yes i did try changing the rollup aggregate function, but dint achieve the desired result.

Regards

[MFGF]

Hi,

Seems to me your issue is highlighting a fundamental problem in your metadata package. My guess is that you have missing determinants in your Framework Manager model, which would control how Pending Amount values get aggregated for PO_IDs and account lines. As things stand you are seeing double-counting when you bring in account lines, and you are having to try to figure out all sorts of kludgy workarounds in your report to prevent this from happening.

Are you responsible for the FM model too, or is this provided by other parties? Do you have a feedback mechanism whereby you can report issues like this in the model and ask for them to be fixed? In the long term it would make more sense to do this than having to hack each report you write to try to work around these issues.

Cheers!

MF.

[Raghuvir]

Hi,

Seems to me your issue is highlighting a fundamental problem in your metadata package. My guess is that you have missing determinants in your Framework Manager model, which would control how Pending Amount values get aggregated for PO_IDs and account lines. As things stand you are seeing double-counting when you bring in account lines, and you are having to try to figure out all sorts of kludgy workarounds in your report to prevent this from happening.

Are you responsible for the FM model too, or is this provided by other parties? Do you have a feedback mechanism whereby you can report issues like this in the model and ask for them to be fixed? In the long term it would make more sense to do this than having to hack each report you write to try to work around these issues.

Cheers!

MF.

Hi MFGF,

We are wring SQL statements in the Query explorer to fetch most of the query items. Just the Column headings are called from the Framework Manager Package.

Regards

[MFGF]

Hi MFGF,

We are wring SQL statements in the Query explorer to fetch most of the query items. Just the Column headings are called from the Framework Manager Package.

Regards

Sigh. I'm not even going to ask why. All I will say is that doing this is analogous to buying a new Ferrari, then pushing it to every destination. It means you will get there eventually but you are making life super-difficult for yourself and missing out on all the power, comfort and pleasure the Ferrari can offer.

MF.

[Raghuvir]

Hi MFGF,

Thanks for the guidance, will see to it that we follow a proper process for building a package. Even i have the same opinion.

Anyways my issue has been resolved by changing the aggregate funtions and using total functions in the expression defination.

Regards