DowntownIT
asked on
SSRS 2005 Summing Issue
We have products that have a budget amount and count assigned to them and I need to do the normal sums and variance by product and finally by the category that the product belongs to. The problem is that my dataset contains many rows for each product so I went ahead and put the Budget amount on each row and just use First() on the details section of the table but now I have the issue where I need the sum of the First budget amount/counts in the table footer to report at the category level.
Product	Category	 Sale Budget Cnt	 Budget Amt
123	1		12	1500		10,000	
123	1		10	1500		10,000		
151	2		45	2600		50,000		
123	1		25	1500		10,000	
151	2		62	2600		50,000
Using the part above, can you show an example of what the expected out come is?
Group by Category, then group2 by Product. Place textboxes in group2 footer and sum the values. You do not need to show a detail row, unless you need too.
ASKER
Planocz,
I am trying what you are suggesting but getting and error. "Aggregate functions can be used only on report items contained in page headers and footers."
I placed a textbox in the group 2 footer and named it sum_txtBx. Now in the group 1 footer I have this expression, =Sum(Reportitems!Sum_txtBx .Value).
Aelliso3, The final result set should be like below. Also, this should be done at the table level if possible.
I am trying what you are suggesting but getting and error. "Aggregate functions can be used only on report items contained in page headers and footers."
I placed a textbox in the group 2 footer and named it sum_txtBx. Now in the group 1 footer I have this expression, =Sum(Reportitems!Sum_txtBx
Aelliso3, The final result set should be like below. Also, this should be done at the table level if possible.
Category
Product Sale Budget Cnt. Budget Amt.
1
123 47 1500 10,000
2
151 107 2600 50,000
Total 154 4100 60,000
Each column has to look like this....
Sale Budget Ct.
=Fields!Sales.value =Fields!Budget.value
Totals =Sum(Fields!Sales.value) =Sum(Fields!Budget.value)
Sale Budget Ct.
=Fields!Sales.value =Fields!Budget.value
Totals =Sum(Fields!Sales.value) =Sum(Fields!Budget.value)
ASKER
The sales column needs to be summed and the budget Ct. not summed at the product level.
My dataset contains multiple individual sales to each product that need to be summed at the product level but since the budget is assigned at the product level I can only return the budget amt/cnt as the full value on each individual row in my dataset.
So when I am looking at the totals for a paticluar product, I can use =First(Fields!Budget_Cnt.v alue) but when I need the totals for a Category, It needs to be the the first budget value from each product in my dataset summed together.
This is what I am looking for,
Product_Name Sales Budget
Table Header =(Fields!Category_Desc.val ue)
Table Details =(Fields!Prod_Desc.value) =Sum(Fields!sales.value) =First(Fields!Budget.value ) -- The Table Details groups by Prod_Desc
Table Footer Totals: =Sum(Fields!sales.value) =Sum(First(Fields!Budget.v alue)) -- "This does not work"
Thanks for the help.
My dataset contains multiple individual sales to each product that need to be summed at the product level but since the budget is assigned at the product level I can only return the budget amt/cnt as the full value on each individual row in my dataset.
So when I am looking at the totals for a paticluar product, I can use =First(Fields!Budget_Cnt.v
This is what I am looking for,
Product_Name Sales Budget
Table Header =(Fields!Category_Desc.val
Table Details =(Fields!Prod_Desc.value) =Sum(Fields!sales.value) =First(Fields!Budget.value
Table Footer Totals: =Sum(Fields!sales.value) =Sum(First(Fields!Budget.v
Thanks for the help.
You have to use group footers, not table footer.
ASKER
Say I move it to this format,
Grp_1 Header =(Fields!Category_Desc.val ue) -- groups by Category_Desc
Grp_2 footer =(Fields!Prod_Desc.value) =Sum(Fields!sales.value) =First(Fields!Budget.value ) -- groups by Prod_Desc
Grp_1 Footer Totals: =Sum(Fields!sales.value) =Sum(First(Fields!Budget.v alue))
How would you handle the budget value in the Group 1 footer?
Thanks,
Grp_1 Header =(Fields!Category_Desc.val
Grp_2 footer =(Fields!Prod_Desc.value) =Sum(Fields!sales.value) =First(Fields!Budget.value
Grp_1 Footer Totals: =Sum(Fields!sales.value) =Sum(First(Fields!Budget.v
How would you handle the budget value in the Group 1 footer?
Thanks,
the detail row will be between the grp1 header and grp2 footer should work with the setup you have above
ASKER
I guess I am not following. It is the grp_1 footer that I am have the issue. The expression =Sum(First(Fields!Budget.v alue))
is not working for me.
Thanks
is not working for me.
Thanks
The totals have to be on the last group which is grp2 not grp1
if you want sums for grp1 you will have to add the grp1 footer for those sums.
if you want sums for grp1 you will have to add the grp1 footer for those sums.
ASKER
Yes, I do want a sum for grp 1 and my issue is that I can not add up the grp 1 footer for the budget column.
I think you are wanting...
=First(Fields!Budget.value ) -- Sum(Fields!Prod_Desc.value )
=First(Fields!Budget.value
ASKER
can not sum Fields!Prod_Desc.value, it's only a description field.
=First(Fields!Budget.value ) only works at the product level. will not work at the category level.
=First(Fields!Budget.value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually thought of going this route but was hoping that I could keep the procedure returning all details and doing the grouping at the report, this way I could re-use the procedure for other detailed reports.
Now that you mentioned it, it is not a big deal to have two different versions of the procedure.
Thanks,
Now that you mentioned it, it is not a big deal to have two different versions of the procedure.
Thanks,