sum not working after turning report to table

[Guest_imported]

Hi guys, I am a new BO user, migrating from SAS. The tech guys set up the universe such that its just a raw translation of our oracle tables, ie a field becomes a dimension.
(1) I managed to do some basic data retrieval, eg
Agent Sales ID
----- --------
A abc100
A def300
B ghi200
C jkl600
C mno799

Now, I want the report to look something like

Agent # Sales
----- -------
A 2
B 1
C 2

What I did first was let the agent be the master, and then use Sum(<Sales ID>), which returned the correct results. But when I turned it into a table, everything becomes 1. How do I get the correct results. Any help would be appreciated!

John

 

[blom0344]

Hello John,

There is much to say about setting up universe objects that do aggregates on measures and dimensions. If you lack these , you would normally do a Sum operation on measures (or Avg .....), while you do a Count on dimensions.
So in this case #sales should be defined as Count(SalesID). However this does not take the correct context into account, as you will probably find out. Adding the correct context will give you:

=Count(<SalesID>) in <Agent>

as the formula for you report variable.

Please note that BO represents the distinct view at reportlevel of data you retrieve. Retrieve a list consisting of 1000 records with the exact same records and BO will only show 1 record in a table. This is why universe aggregates are a must in many cases. Set the tech guys at work !!! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[Guest_imported]

Thanks,
I was browsing the forum the whole day trying to get the answers.. Unbelivably.. My tech guys didn't install the tutorial pdf files.. I was only left with the BO User's Guide which is not comprehensive enough!! I'll get them to install those straight away!!

Does anyone know a good book out there??