June 6, 2014 at 6:07 am
Hi,
I am working om a Profit & Loss statement in SSRS 2012 and am wondering how to fix correct calculations. My dataset has categories and subcategories and I would like to have these as row groups in a tablix (matrix). Category 1 is "Gross Margin"; category 2 "Costs" and category 3 "Interests". Subcategories are a row group that can be toggled on and off. As a Grand Total I would like to have "Gross Margin" minus "Costs" minus "Interests". My point is how to get the Costs and Interest deducted from the Gross Margin in the tablix once they are in the same data region. I don't want to show Costs and Interest with a minus sign. So, my Grand Total would be GM -/- Costs -/- Interests where the amounts for GM, Costs and Interests show up as absolute figures. How should I substract one item in a data region from another item in the same data region? I would rather not like make several adjacent groupings for the same row group. So preferably not a grouping on Category (filtered for Gross Margins), another (adjacent) group on Category (filtered for Costs) and another (adjacent) group on Category (filtered on Interests) ... I Googled around for some time but there is not a common practice for issues like this.
Example:
Sales: 1000 (= Subcategory)
Cost of sales: 600 (= Subcategory)
GROSS MARGIN: 400 (= Category)
Wages and salaries: 100 (= Sub)
Housing: 50 (= Sub)
Depreciations: 100 (= Sub)
Other expenses: 60 (= Sub)
COSTS: 310 (= Category)
Interests paid: 15 (= Sub)
Interests recieved: 5 (= Sub)
INTERESTS: 10 (= Category)
RESULT BEFORE TAXES: 80
What is the best way to accomplish this?
Thanks!
June 6, 2014 at 6:28 am
What is your source? In SSAS, you can have a dedicated column that contains the sign (+ or -). When calculating aggregations, it uses the correct signs to calculate the P&L.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 6:54 am
My source is not a SSAS cube. Just a table.
June 6, 2014 at 6:57 am
michielbijnen (6/6/2014)
My source is not a SSAS cube. Just a table.
You can include an extra column yourself with the correct sign, and multiply every value with that column when you use it in a calculation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2014 at 7:12 am
Hello Koen,
Could you be more specific? I am not sure what you mean ...
Thanks!
June 11, 2014 at 11:29 am
If you had a column in a table for the Category that indicated if it were a debit or credit. then it would be really easy:
create a calculated column, I'll call it Multiplier (one option) that's just
=IIF(GroupType="Debit",-1,1)
and then you could just multiply your values by that...
=Amount * Multiplier
and then you can sum that result.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply