Universe Object - create group and sum

[Former Member]

I am fairly new to using Business Objects (using XI 3.0 Edge). I am working in Universe Designer and am trying to make a new object for users to use in Web Intelligence Reports. Here is what I want to do:

I have a field called AccountNumbers in a Table called GL Master. I also have a debit field for each month & a credit field for each month

I want to make several new fields that group AccountNumbers together so that I can sum them up and get a total for those AccountNumbers. Sum of Debits - Sum of Credits

Example: I want to make a field called Tool Room Expense. This should be the sum of AccountNumbers: 5, 6, 7, 28

I want to make a nother field called Misc Expense. This should be the sum of AccountNumbers: 15,8,32

I want to make many of these fields so that in the Web Reporting people can just drag in the newly created fields to the report.

I hae made a field that does this successfully by putting the sum(debits) - sum(credits) in the selection part of the object & in the Where portion of the object, putting the AccountNumbers are specific.

However, when i make the 2nd new object and put it's AccountNumbers in the Where clause...the reports don't work. I think b/c i have 2 fields in the report w/ separate where clauses on the same object (Account Number)

Anyway - long story longer... I want to be able to make total fields for each Group of Account Numbers and drag them all into the same report. I want to accomplish this in Universe Desinger if possible so that this doesn't need to be recreated by users in Web Intelligence.

Thanks,

Blake

[Former Member · Accepted solution]

Hi,

How about creating a Derived table for each of the different groups of AccountNumbers you want to create an object for ?

e.g. Create a derived table called Tool Room Expense with the select statement along the lines of

Select

AccountNumber

sum(Debit-Credit)

From

GL Master

Where

AccountNumber in (5, 6, 7, 28)

Join the derived table to GL Master using AccountNumber

Then create similar for Misc Expense where AccountNumbers are 15, 8, 32 then create your seperate objects from these tables.

Regards

[Former Member · Accepted solution]

Thanks Sion - I'lll give that a try and post back!

Blake