Formula woes

[Guest_imported]

I have a table

Team Agent received approved

Team = substr(<Agent>,1,3)
received is supposed to be Count(<AppID>) In (<Agent>)

Where <AppID> is a dimension with unique values.
I applied a break for Team.

Somehow, I can't get the formulas right.. tried every permutation I could think of and I am still stuck (it will always display 1)... help!

 

[blom0344]

Hello Lan,

Not using <AppID> in the table and then setting a break will not work, i think. Use sections instead of breaks or use <AppID> in the table as well. You can always hide it with : format table --> pivot --> hide T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Guest_imported]

Thanks blom0344,
I kinda solved it.. Scanning through the msg board, I noticed a post saying select Avoid Row Aggregration... did that, and the formula kinda works.. but then, I faced the same problem as the posted problem.. ie

Team Agent Approved Rejected
A B 3 3
A B 3 3
A B 3 3

The number of rows is the same as the # approved. Anyone care to enlighten me on this?? Thanks

 

[blom0344]

Hello Lan,

With default settings BO will attempt do show a 'distinct'representation of rows retrieved. So, If your query gives you 100 identical rows, BO will only show one line in a table. If you mark 'Avoid duplicate rows aggregation' BO will show you the 100 rows.

This setting is default, because if you retrieve dimensions and measures and the measures are set to aggregating behaviour (you set this with the properties of a measure in the universe) you'll want BO to do an automatic aggregation at report level. It is the basis for multi-dimensional analysis (drilling and such) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com