SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouping the same transaction type in multiple ways


Grouping the same transaction type in multiple ways

Author
Message
aaa121
aaa121
SSC-Addicted
SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)SSC-Addicted (499 reputation)

Group: General Forum Members
Points: 499 Visits: 783
Hello all,

Could you suggest the best way to structure the following, which I am sure is a common situation.

Ok so I have a monetary value (fact) posted on a day (dim) against an account (dim). This account has a grouping level associated with it e.g. (Revenue, Cost of Sales, Supervision Costs, Overheads).

I need to be able to group these transactions by a derived type, so:

Revenue & Cost of sales = 'Gross Profit Before Supervision'
Revenue, Cost of sales, Supervision Costs = 'Gross Profit'
Revenue, Cost of sales, Supervision Costs, Overheads = 'EBIT'

So my problem comes about as the figure that gets posted against and account will only appear once in the fact table under say Cost of Sales. But then I need to include this figure in all 3 of the transaction types dervied above.

Any ideas on how best to design for this?
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2488 Visits: 487
Are you trying to create a conditional sum?
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4742 Visits: 3218
aaa121 (2/18/2014)
Hello all,

Could you suggest the best way to structure the following, which I am sure is a common situation.

Ok so I have a monetary value (fact) posted on a day (dim) against an account (dim). This account has a grouping level associated with it e.g. (Revenue, Cost of Sales, Supervision Costs, Overheads).

I need to be able to group these transactions by a derived type, so:

Revenue & Cost of sales = 'Gross Profit Before Supervision'
Revenue, Cost of sales, Supervision Costs = 'Gross Profit'
Revenue, Cost of sales, Supervision Costs, Overheads = 'EBIT'

So my problem comes about as the figure that gets posted against and account will only appear once in the fact table under say Cost of Sales. But then I need to include this figure in all 3 of the transaction types dervied above.

Any ideas on how best to design for this?



This looks like the normal way I would expect a chart of accounts to role up.

It is common (in accounting systems) for the values to be entered as positive numbers and for the reporting software to know that (for example) revenue accounts add as positive numbers and expense accounts get subtracted from the total.

Analysis Services eats this sort of things for breakfast. If you can define your chart of accounts with an account type SSAS will do a lot of the work for you. Alternatively, you can define a rollup operator (e.g. + for the values for this account are added to the total and "-" for the value for this account are subtracted from the total).

Have a look at "Account Intelligence" and/or rollup operator in your favourite search engine.



sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2488 Visits: 487
Agreed (if that is what the question represents--a rollup of a chart of accounts).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search