Case statement splits line into multiple

[Skykit]

Hi all,

I was hoping for a kick up the backside to remind me how this should be done as I seem to have had what seems like a mind-block on a simple issue (or maybe not)

With no influence on the database side, I have a scenario whereby a field called amount contains a value.  That value is determined by another field called amount type which tells you whether the amount is a net amount or gross amount (and there are a few others such as tax amount etc)

So to make life easier, in framework, I have assigned a couple of data items:
CASE WHEN [SALE].[AMOUNT_TYPE].[SALE_AMOUNT_TYPE_KEY]  = 0
THEN [SALE].[SALE_AMOUNT].[AMOUNT]
END

And I've named this Sale Amount

Another data item is the same as above but the key is 1, and I've named that Gross Amount
Another data item is the same as above but the key is 2, and I've named this Net Amount and so on

This works fine however when the columns are dragged into the report I get
Line 1:
Reference= 001
Sale Amount = 7.00
Gross Amount = blank
Net Amount = blank

Line 2:
Reference= 001
Sale Amount = blank
Gross Amount = 5.00
Net Amount = blank

Line 3:
Reference= 001
Sale Amount = blank
Gross Amount = blank
Net Amount = 3.00

(values are fictitious!)

Obviously the values are correct but split into 3 lines where as really it should be just one line
Line 1:
Reference= 001
Sale Amount = 7.00
Gross Amount = 5.00
Net Amount = 3.00


So what am I doing wrong here?
Should I just do a TOTAL([sale/gross/net amount]) for [Reference]) type thing or is there a better way of doing this?

Package/source both relational

Thanks in advance

[Lynn]

What do you have as usage and aggregation property settings for these? I'd expect you want them as facts that total. You might also consider and "ELSE 0" clause on each one.

[BigChris]

Would it suit your purposes to create a crosstab? You could create a Category field which you could use as your columns:

Code Select Expand

Case [SALE].[AMOUNT_TYPE].[SALE_AMOUNT_TYPE_KEY]
  When 0 then 'Sales Amount'
  When 1 then 'Gross Amount'
  When 2 then 'Net Amount'
Else NULL
End

Then you could just bring the Amount in as the measure.

[Skykit]

Thanks Both
BigChris - valid point and certainly something I will raise.  It seems this recent place of employment love their list reports and really do shun away from cross-tabs but it's something worth exploring again

Lynn: all items are indeed facts however initially I left it to unsupported simply because they do not always want a total ie- when they want to see the split should they bring in something like line number in...otherwise totalling is fine to get it all to line up.
Having said that, I've had discussions with the end-user and it might be the case of having two of the same data items - one totally, the other not.  Messy but I think it would then answer both sides of the query

[Lynn]

Thanks Both
BigChris - valid point and certainly something I will raise.  It seems this recent place of employment love their list reports and really do shun away from cross-tabs but it's something worth exploring again

Lynn: all items are indeed facts however initially I left it to unsupported simply because they do not always want a total ie- when they want to see the split should they bring in something like line number in...otherwise totalling is fine to get it all to line up.
Having said that, I've had discussions with the end-user and it might be the case of having two of the same data items - one totally, the other not.  Messy but I think it would then answer both sides of the query

When you consider the aggregation property in your framework model, remember it is the default method of aggregation. There is nothing that would stop an author from changing that to something else on a report-by-report basis. Therefore, choosing the method that is most typically going to be used is a good approach. I don't quite follow what you mean about seeing the split by bringing the line number in. Cognos likes to have star schemas.

[Skykit]

Hi Lynn
Totally accept the aggregation in framework - to be honest, the end-users are not too bad with report writing so that was never a worry

I have I think finally managed to solve this one -
What I ended up doing is bringing into framework the same table multiple times but filtering each one - essentially a filtered dimension

So one table is filtered in FW for sales, another for gross and another for net.  As the main table was already joined, it was just a case of cleaning up joins that were not needed and it seems to have done the trick - also it feels faster in performance

thanks again for your help + others