Where Clause Variable Within a Value-Based Break

building reports desktopintelligence
1

I have a report which looks similar to this:

Group Area Code Area Amount
PL A Income 50
PL B Expense -15
PL C Other xxx

I want to create a sub-total after the Expense row and create a formula that adds Income to Expense to give me a Net Amount.

  1. I’ve created a variable similar to
Amount where (<Group>="PL" and <Area> = "Expense") +
Amount where (<Group>="PL" and <Area> = "Income").

  1. I’ve then created a value based break on Area for Expense, so that I get an extra line after Expense, into which I can place my variable.

  2. However when I place the variable into the footer, I only get the value for Expense. If I use it elsewhere in the report (e.g. in the Group footer), I get the correct answer.

I assume it’s something to do the Income figure being outside of the context of my value-based break, but I’m not sure how to rectify it.

Any tips? Cheers.

:sleeping:

Grantie :uk: (BOB member since 2004-07-01)

2

OK so I see your ploy. Leave it for a while and you’ll figure it out yourself.

It was indeed the conteyt that was causing the problem. I copied the formula that I had created with the specific context and could see that the output context included AREA. And because I had placed the formula in the footer of the AREA break, it could only see that AREA. Once I removed AREA from the output context, it worked a dream.

Thanks anyway.

:+1:

Grantie :uk: (BOB member since 2004-07-01)

3

Nice update :yesnod:

Andreas :de: (BOB member since 2002-06-20)

4

Well, it’s not always the case, but it can happen. As Andreas alluded to, thanks for coming back and posting your solution. It will hopefully help the next person with the same or similar question.

Here’s your reward… have a couple of cookies on me. :smiley:

:cookie: :cookie:

Dave Rathbun :us: (BOB member since 2002-06-06)

5

As Dave said – it wasn’t a ploy. :rotf: You solved it yourself while I was on the way to work – and I only now saw your message for the first time!

Remember, folks – we’re all on different time zones, and we aren’t paid tech support – we’re all just people helping each other! :wink:

Anita Craig :us: (BOB member since 2002-06-17)