Help with formula

building reports webintelligence
1

I am trying to get a formula based on two conditions to work:

=Sum(If ([Meeting Status] = “Active” And [Paid Meeting Expense Amount] + [Paid Speaker Expense Amount] = 0;[Planned Speaker Expense];0))

=Sum([Planned Speaker Expense]) ForEach ([Meeting Id]) Where ([Meeting Status] = “Active” And [Paid Meeting Expense Amount] + [Paid Speaker Expense Amount] = 0)

Basically, what I want to be added up are meetings with an active status with paid amounts that equal zero. Even though I know a few records pass this test, no matter what I do the result comes out to be zero. Can anybody help with this formula?

bkaporch :us: (BOB member since 2008-12-02)

2

The 2 formulae listed are the ones which you have tried?

See if your Universe has a restriction on the number of rows returned. Probably those rows arent fetched from the Database even.

nithya_raj (BOB member since 2007-02-03)

3

Put filter [Meeting Status] = “Active” and [Paid Meeting Expense Amount] + [Paid Speaker Expense Amount] = 0 on the result set in the report and then use the formulas.
= SUM([Planned Speaker Expense])
and
=Sum([Planned Speaker Expense]) ForEach ([Meeting Id])

Rakesh_K :india: (BOB member since 2007-12-11)

4

I can’t use that filter for every record because I don’t want to exclude the records with 0 compensation completely from the report.

The filter I have for the block is (sum actuals greater than 0 and meeting status is cancelled) or meeting status is approved.

bkaporch :us: (BOB member since 2008-12-02)

5

Apply Block Filter.

Rakesh_K :india: (BOB member since 2007-12-11)

6

It’s strange, because I add one field to the report (meeting id), and the total changes from $34,348 to $66,819 WITHOUT making any other changes to formulas. It seems that BI is not interpreting my report correctly. (And these totals are changing even though I’ve selected meeting id for both queries, so literally no other changes.)

The formula I’m using is:

=Sum([Planned Speaker Expense]) ForEach ([Meeting Id]) Where ([Meeting Status] = "Cancelled" Or ([Meeting Status] = "Approved" And [Sum Actuals]=0))

I should add that I can’t keep the meeting id in this report, because I’m only suppossed to show totals by speaker. The meeting id breaks down the totals too much, so simply hiding the column won’t work. If you need further details, I’ll show you what’s happening in one report for the same speaker. (Totals for estimates go from $0 without meeting id to $1770 with meeting id.)

Also, Sum Actuals is defined as:

=If (IsNull(Sum([Paid Speaker Expense Amount])+Sum([Paid Meeting Expense Amount]));0;Sum([Paid Speaker Expense Amount])+Sum([Paid Meeting Expense Amount]))

I believe that since Sum Actuals should be a total on a meeting by meeting basis, that business objects doesn’t know how to break down the totals unless the meeting id is in the report. I even tried adding a foreach onto the formula:

=Sum([Paid Speaker Expense Amount]) ForEach ([Meeting Id])+Sum([Paid Meeting Expense Amount]) ForEach([Meeting Id])

This results in a multi-value expression, even when prefixed with a sum. Even anybody knows any way that business objects can handle this formula, please let me know.

bkaporch :us: (BOB member since 2008-12-02)

7

I had to change how the report was querying the data to use sub-queries instead of the formula. As I said, the formula seems to be correct with meeting id in the report, but incorrect when the meeting id was removed. The report seems to be working now.

bkaporch :us: (BOB member since 2008-12-02)