Solved

SSRS 2005 Summing Issue

Posted on 2010-09-16
17
545 Views
Last Modified: 2012-05-10
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

Open in new window

0
Comment
Question by:DowntownIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 11

Expert Comment

by:aelliso3
ID: 33694398
Using the part above, can you show an example of what the expected out come is?
0
 
LVL 27

Expert Comment

by:planocz
ID: 33706672
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.
0
 
LVL 2

Author Comment

by:DowntownIT
ID: 33734822
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.



Category  
    Product    Sale  Budget Cnt.    Budget Amt.
   1
        123    47    1500           10,000

   2
        151    107    2600          50,000

Total          154   4100           60,000

Open in new window

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 27

Expert Comment

by:planocz
ID: 33736157
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)
0
 
LVL 2

Author Comment

by:DowntownIT
ID: 33737074
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.value) 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.value)

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.value)) -- "This does not work"




Thanks for the help.
0
 
LVL 27

Expert Comment

by:planocz
ID: 33737208
You have to use group footers, not table footer.
0
 
LVL 2

Author Comment

by:DowntownIT
ID: 33737306
Say I move it to this format,


Grp_1 Header        =(Fields!Category_Desc.value)   -- 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.value))


How would you handle the budget value in the Group 1 footer?



Thanks,
0
 
LVL 27

Expert Comment

by:planocz
ID: 33737473
the detail row will be between the grp1 header and grp2 footer should work with the setup you have above
0
 
LVL 2

Author Comment

by:DowntownIT
ID: 33737860
I guess I am not following. It is the grp_1 footer that I am have the issue. The expression =Sum(First(Fields!Budget.value))
 is not working for me.

Thanks
0
 
LVL 27

Expert Comment

by:planocz
ID: 33737933
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.
 
0
 
LVL 2

Author Comment

by:DowntownIT
ID: 33738030
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.
0
 
LVL 27

Expert Comment

by:planocz
ID: 33738136
I think you  are wanting...
=First(Fields!Budget.value)  -- Sum(Fields!Prod_Desc.value)
0
 
LVL 2

Author Comment

by:DowntownIT
ID: 33743463
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.
0
 
LVL 11

Accepted Solution

by:
thman earned 500 total points
ID: 33775089
DowntownIT,
Since your details rows do not list all the rows for the same product and only list the summary, instead of using 2 groups in your layout, you should just use 1 group (category) in the layout and place the product grouping in your command text of the query. When grouping in the query, you sum on sale and counts first and then join with the table that contains budget.
0
 
LVL 2

Author Closing Comment

by:DowntownIT
ID: 33780998
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,
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question