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
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.
Would it suit your purposes to create a crosstab? You could create a Category field which you could use as your columns:
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.
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
Quote from: c8aj on 30 Nov 2016 07:44:36 AM
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.
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