March 4, 2007 at 9:44 pm
Hi. I want to create a subtotal for a report, but cannot see how to do it. I don't find the Reporting Services help very helpful at all. I see the aggregate functions can be used with a scope parameter, but don't know how to apply that to my example.
I downloaded the RS2000 financial report pack sample reports and have used the Fabrikam income statement as the basis for my report. The following gives a small sample of data to illustrate the problem.
CREATE TABLE #TestData (
normal_bal [smallint] NULL ,
AccountDesc [varchar] (50) NULL ,
AccountCode [char] (4) NULL ,
GrossMarginGroup [varchar] (30) NULL ,
IncomeFromOperationsGroup [varchar] (30) NULL ,
IncomeBeforeIncomeTaxesGroup [varchar] (30) NULL ,
AccountGroup [varchar] (30) NULL ,
per_net_amount [money] NULL
) ON [PRIMARY]
GO
INSERT INTO #TestData VALUES(2,'Sales','4100','Gross Margin','Income From Operations','Income Before Income Taxes','Revenue',-229811.36)
INSERT INTO #TestData VALUES(1,'Sales Returns','4110','Gross Margin','Income From Operations','Income Before Income Taxes','Revenue',6819.71)
INSERT INTO #TestData VALUES(1,'Sales Discounts','4250','Gross Margin','Income From Operations','Income Before Income Taxes','Revenue',22726.69)
INSERT INTO #TestData VALUES(1,'COGS','4500','Gross Margin','Income From Operations','Income Before Income Taxes','Cost Of Goods Sold',166843.90)
INSERT INTO #TestData VALUES(1,'Salary Expense','5000','Other','Income From Operations','Income Before Income Taxes','Operating Expenses',27700.88)
INSERT INTO #TestData VALUES(1,'Office Expense','5200','Other','Income From Operations','Income Before Income Taxes','Operating Expenses',1237.21)
INSERT INTO #TestData VALUES(1,'Rent','5250','Other','Income From Operations','Income Before Income Taxes','Operating Expenses',2304.22)
Select * from #TestData
In the example reports they have grouped the accounts by AccountGroup as the innermost group, and then by GrossMarginGroup, IncomeFromOperationsGroup, and IncomeBeforeIncomeTaxesGroup. Finally the report footer contains the net income amount. They have used the visibility property to hide the group footers where necessary. For example, the visibility property for the group footer for GrossMarginGroup is set to the following:
= Fields!GrossMarginGroup.Value = "Other"
In this way you can easily construct a table report that gives the following output:
Total Revenue -200,264.96
Total Cost of Goods Sold 166,843.90
Gross Margin -33,421.06
Total Operating Expenses 31,242.31
Income from Operations -2,178.75
I have hidden the details for simplicity, and am not worried about the signs as I can fix that. What I need is to be able to create a new subtotal which is not simply the sum of all amounts so far (like the profit figure). Say for example I need a total of Cost of Goods Sold + Operating Expenses ($166,843.90 + $31,242.31 = $198,086.21). How can this be done?
I already have around 15 columns in the report, so I don't really want to add a heap more if it can be avoided. The only I know to do this is to create more hidden columns but it makes things very messy both for the dataset and the report. Is there a better way?
If I was still using Crystal Reports I might use a formula with variables e.g. if AccountGroup = Cost of Goods Sold or Operating Expenses then amount else zero, and then show the variable total in the report. Can variables be used like this in Reporting Services?
Thanks in advance,
Mark.
March 7, 2007 at 8:00 am
This was removed by the editor as SPAM
March 12, 2007 at 4:31 am
Try this expression in a table footer:
=Sum(IIF(Fields!Accountgroup = "Cost Of Goods Sold" , per_net_amount, 0)
- Sum(IIF(Fields!Accountgroup = "Operating expenses" , per_net_amount, 0)
March 12, 2007 at 1:49 pm
Thanks for the suggestion. However I get an error message with that method. I also tried just using the first part, i.e. "=Sum(IIF(Fields!Accountgroup = "Cost Of Goods Sold" , per_net_amount, 0))", in either the group footer or the table footer. Both give the following error message:
The value expression for the textbox ... refers to the field 'AccountGroup'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Mark.
March 13, 2007 at 12:00 pm
Seems like your report is candidate for a Subreport where the dataset is the result of your grouping and sum of the those values you need.
If not, add different grouping levels to your report
March 14, 2007 at 8:06 am
Sorry, I made an error in the expressions. Try these:
=Sum(IIF(Fields!Accountgroup.Value = "Cost Of Goods Sold" , per_net_amount, 0)
- Sum(IIF(Fields!Accountgroup.Value = "Operating expenses" , per_net_amount, 0)
March 14, 2007 at 4:15 pm
There is also a closing bracket missing for each part, but it still doesn't make any difference. I get the same error message as before regarding the scope. In addition I need to be able to put this in a group footer rather than the table footer.
Can anyone tell me if there is a way of performing calculations on different group footers, or using variables as in Crystal Reports?
March 20, 2007 at 11:27 pm
No-one has come up with a solution for this. Do I take it that it can't be done?
Is there any way of relating different totals together? For example, if there is only one grouping with totals in the footer (GF1), the report may look like this:
Details
GF1 - total A
Details
GF1 - total B
Details
GF1 - total C
Can you then calculate a value say Total B + Total C? Instead of using a table report do I need to use a list report creating three different data sets, or will I strike the same problem as my current scope errors?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply