RS 2005 - totals arrived at by subtracting one group from another
-
I've used this forum quite a bit to learn some of the basic concepts - this is "giving back" as it were...
The task: produce a profit and loss statement that can be run dynamically from the financial package's database.
The formula is Revenue - Cost of Goods Sold - Overhead = Net
I tried using a matrix with the raw data and that got me a very raw version of it. However it was desired to combine the live data with the budget and produce both year-to-date and variances. That wasn't going to happen in a matrix.
I did my own PIVOT query within a view. That view is joined together with the budget as well as the table in the accounting software that labels all of the GL accounts that are being reported on.
There were some challenges. First of all - how to intelligently group together the three classes of numbers?
When I imported the budget into an SQL table I added a field identifying Rev, COGS, and Overhead. I chose coded values that would sort correctly in ascending order. That was brought forward into the resulting dataset fed to SSRS. In the table object that produces the whole thing I grouped first by category (department) with a page break at the end of that group. I then grouped by that coded field that put my values into the correct layout on the page.
Coming up with subtotals for the groups is no problem - but how to subtract one group from another?
I tried all sorts of things that don't work. Formulas like
=ReportItems!Textbox32.Value-ReportItems!Textbox64.Value
... variations on the same in the footer...
... variations on the same inside and outside the table...
All of these produced pretty much the same error message about scope.
Formulas that include Me.Value will produce very long error messages indeed!
Then it occurred to me: if all this
thing knows how to do is straight-line sums... let's give it one. I created "shadow" fields for each value. They are never displayed in the table but they're used in doing those straight-line sums SSRS is so good at.
Going back to the formula:
Revenue - Cost of Goods Sold - Overhead = Net
I already "know" what class of field I'm using at calculation time, it's the way I'm getting the grouping.
So for all 36 financial fields, I added 36 more calculated fields into the SQL statement with formulas that all look pretty much like this:
CASE WHEN SGT.Account_Type <> N'I' THEN SGT.M1 * - 1 ELSE SGT.M1 END AS SM1
'I' was an arbitrarily-chosen letter of the alphabet that corresponded to Revenue accounts. COGS and Overhead accounts had their own letters later in the alphabet. This way all the Revenue accounts remained untouched while COGS and Overhead values all got their sign switched.
I added one more row below my final grouping and put in the exact same formulas as above - I consistently just added a letter S to the field name. So my last subtotal was above my new "total :), " looking like this:
=Sum(Fields!M1.Value, "table1_Group1")
=Sum(Fields!SM1.Value, "table1_Group1")
That got repeated all the way across the table for the other months of the year, other budget months, other variances. Success.
If this concept is of interest to anyone I can try to post samples of how it functions/appears - I'd attach something now but due to the nature of the information it works with it takes awhile to figure out what to redact!
This is a great forum... I hope someone finds this post and finds this workaround to be useful.
-
Hi
I tried to follow your posting and got a little lost with the use of the hidden fields. I then built a similar report which has an output that looks like this:
I used a formula in the SQL query to make the CostOfSales and Overheads values negative so that on a grand total level the formula would work out to Revenue - COS - Overheads. I think this is what you were doing in your post?
There is another expression that you could use on the total level (to build the Net Value):
=SUM( IIF( TRIM(Fields!AccountType.Value) = "Revenue", Cint(Fields!Value.Value),0), "GetData")
-
SUM( IIF( TRIM(Fields!AccountType.Value) = "COS", Cint(Fields!Value.Value),0), "GetData")
-
SUM( IIF( TRIM(Fields!AccountType.Value) = "Overheads", Cint(Fields!Value.Value),0), "GetData")
("GetData" is the name of my dataset). This work similarly to Excels SUMIF function. The only strange thing is that SSRS wants the values it is summing to be converted to integers! That is what the Cint is doing in the expression above - if someone else knows how to get around this please post here. The "green" value below the P/L total line is the output of this expression.
If you'd like to see my SQL and "test" data then let me know.
-
We're doing pretty much the same thing here - it just depends on what the user of the information will accept. On my first pass through this I also showed negative numbers in the spreadsheet but it wasn't "acceptable" 🙂
Also - how are you building your report? Mine is done as one table with a couple of groupings inside it. Are you using multiple table/list objects?
Just curious... I did something similar to that approach for a formula on a different project where the calculated field in report server was 10% of the length/complexity of the T-sql to do the calculation. I know to total it I had to do either CInt or CDbl, it wouldn't work otherwise.
-T
-
Hi
Built the report using a single table with one grouping inside. I usually have to swap the signs of the values too as the negatives are unacceptable.
I will fiddle later today with using a list with multiple tables. That approach should look similar to this but will allow us to use ReportItems!Field.value for the Net P/L calculation. The single table design is probably easier to maintain, but might require more work from the dataset side. I think the multiple tables approach could be far too complex as the designer would have to apply different filters for each table (or use different Datasets).
I have to build some similar reports to this soon and I might initially be using the multiple datasets/multiple tables approach as the queries I would have to build to put it all together are going to be very nasty to maintain. I would then simplify the report once its been approved and data audited.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply