URGENT Case condition to add a value when a row matches

[cbyrne]

Hello i have been working on this report for many months now in between other reports and i still cannot get it right, i am under a lot of pressure to get it sorted so would be very grateful for some help.

I have a list report showing Account, Group Code and Value however i have sub accounts that do not have a group code and i need a condition that enters the group code if the main account has one.  The problem is some accounts do not have a group account so they are null.

So below i need to add the value MS1 when the main account has a group.

Account   Group      Account Name
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318      MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC
CA318   MS1   MORGAN SINDALL PLC


Many Thanks in Advance

Chris

[Lynn]

Would setting the aggregate function property for group code to maximum do the trick?

[bdbits]

Can you use an expression for your group data item such that when it is null, you set it to 'MS1'?

Something like:  if ([sub_account_group_code] is null) then ('MS1') else ([sub_account_group_code])

[Lynn]

Or a coalesce function might work.

[cbyrne]

Hi Lynn,

I have tried the aggregate function without any joy because the sub account does not have a value in the group field however it is linked to the main account.

I will try the coalesce and let you know.

bdbits - I have tried this expression however we have multiple accounts and sub accounts so it would mean me entering all these codes into a case statement.  I suppose i could do this as a last resort to get the accounts department off my back lol.

Thanks for your replies.

Chris

[bdbits]

OK. I would think you will have the same thing with a coalesce, though - entering all the different cases. Sort of six of one, half a dozen of the other. :)

Just a thought... If it is more than just the one report and you are the modeler, or whoever it is will cooperate, you might consider building the expression as part of the model, perhaps in the query subject(s) you are already using.

Good luck.

[Lynn]

I was thinking of something along the lines of this:

coalesce ( [SubAcctCode], [AcctCode] )

to pick up the sub account code or else the account code if sub is null

[cbyrne]

Hi All, thanks for your replies, the coalesce doesn't seem to work however i have had to add a data item with expression of case when sub_account contains 'Morgan Sandal' then 'MS1', however i have a lot of sub account codes and when i validate the expression i get an error of too many values.

Is there anyway of getting round this too many values?

Many Thanks

Chris

[Lynn]

I really think that hard coding like that is a bad idea. Can we take a step back and better understand how your data is stored to perhaps solve the problem in a way that will be more durable over time?

Can you explain exactly how your records are stored? I'm not clear about the main account vs. the sub account. Are these different rows from different tables that get joined, or are they separate columns on the same row, or are they just different rows in the same table? Is the sub account group code actually storing a null or is it a blank character?

[cbyrne]

Hi Lynn,  sorry for the late response, yes i agree that hard coding it in this way isn't the way to go however i just need a quick fix until i can find a proper solution and the only way i can find to do this is putting the account names into a case condition, however i quite a lot of account names and now get an error for to many values.


Yes this is a cross join from Main account to group sub accounts.  They are different tables that get joined.
Basically the sub account is what we call the dot account which has a sales value which is also linked to it's main account.

The problem i have is that all the main accounts have a group account code however apparantly none of the sub accounts do so in a list report and for the same column called group code i need the account and the sub account with the same group code.

Sorry if this doesn't make much sense as i do not full understand.

Many Thanks


Chris

[Lynn]

Is the group code on the sub account table null or a blank character or something else? The coalesce function just picks up the first non-null value, so it wouldn't work if something else is stored, like a blank.

But if you are joining the records and the group code you need is on the main account, then why not just pick up that column?

[cbyrne]

Not sure about the null. 

I think i have tried linking the the two tables in the cross join but this still did nothing.  If the group code on the sub account has no value then how am i avery going to pick this up in the report.

I will have another look Lynn in the cross join links to see if i have missed anything.

Many Thanks

Chris