Expression total not displaying correct data in SSRS 2008

Question

A long time reader. A great wealth of info on this site.

Hopefully this isn't a dumb question, but I've been struggling on how to get the correct totals in my report.

I'm having issues with simple totals. I have a report that takes the difference of two numbers and shows the result, with the results being both negative and positive. At the bottom of the report I want a total sum of the difference. The totals are not even close for some reason.

Here's my data:

Correct Data

Specialist  Total Sales     Budget          Difference
DICK        "$35,174.00"    "$63,530.00"    "($28,356.00)"
NATHAN      "$16,193.00"    "$40,000.00"    "($23,807.00)"
PAUL        "$52,096.00"    "$55,000.00"     "($2,904.00)"
STEVE       "$31,185.44"    "$66,700.00"    "($35,514.56)"

**Totals:  "$134,648.44"   "$225,230.00"    "($90,581.56)"**

Incorrect Data that is in my report

Specialist   Total Sales     Budget          Difference
DICK         "$35,174.00"    "$63,530.00"    "($28,356.00)"
NATHAN       "$16,193.00"    "$40,000.00"    "($23,807.00)"
PAUL         "$52,096.00"    "$55,000.00"     "($2,904.00)"
STEVE        "$31,185.44"    "$66,700.00"    "($35,514.56)"

**TOTALS:   "$134,648.44"   "$225,230.00"     "$71,118.44"**

As you can see, the data shows the total differece as $71,118.44 when it should be ($90,581.56).

Here is my expression to get the result of the Difference:

=Sum(Fields!TotalSales.Value)-Fields!Budget.Value

I right clicked on the text box and selected "Add Total" to get a Grand Total for the Difference field. I also tried this suggestion:

=Sum(Fields!TotalSales.Value)-Sum(Fields!Budget.Value)

And this:

=ReportItems!textbox14.Value - ReportItems!textbox15.Value

Another thing to mention is there is grouping on Specialist. The Total Sales field is a Group total for their sales for that particular month. The Budget field is a single field outside of the group.

This seems like a no brainer that it should work. Maybe someone can give me some guidance on this.

Thanks in advance.

EDIT:

Thanks for the help, but unfortunately the issue is more complicated than that :) I should have been more clear on that. I'm pulling data from 3-4 databases and they don't have a common field to link on. What I'm using a Row number to reduce duplicate subtotals and set them to 0. I using a rownumber on the Budget number, but the data I was getting back was bogus. Here's the data I have:

Name    Team    SubTotal Budget 
SCOTT   Vikings 202      25000.00 
SCOTT   Vikings 1890     25000.00 
SCOTT   Vikings 5167     25000.00 
SCOTT   Vikings 20256    25000.00 
SCOTT   Vikings 0        25000.00 
SCOTT   Vikings 0        25000.00

And here's the data I want to have:

Name    Team    SubTotal Budget 
SCOTT   Vikings 202      25000.00 
SCOTT   Vikings 1890         0.00 
SCOTT   Vikings 5167         0.00 
SCOTT   Vikings 20256        0.00 
SCOTT   Vikings 0            0.00 
SCOTT   Vikings 0            0.00

If the budget number repeats more than once, I want it to be 0. I think this is the reason my totals aren't showing correctly in SSRS. I'm calculating the Difference field by taking the GrandTotal of the Salesman's subtotals and subtracting them from the Budget Field that was put into the group footer (since I'm new I can't post images, sorry. Here's the hyperlink)

http://i55.tinypic.com/124jw2u.png

Here's my SQL Statement also:

WITH InvoicedAndPaid
AS

(
SELECT KEY1
  ,ltrim(rtrim(CustomText01)) as CustomText01
  ,ROW_NUMBER() OVER(PARTITION BY DocNo ORDER BY DocNo) AS RowNumber      
  ,SubTotal
  ,OrderDate     
      ,INVOICEDATE
FROM DocumentHeadersItems AS AA
JOIN SorArCombined AS BB
ON AA.DocNo = ('AA' + BB.CustomerPoNumber)
JOIN CONTACT1 AS CC
ON AA.SoldToCMAccountNo = CC.ACCOUNTNO
WHERE CreatedBy != 'NOAHDM'
  AND KEY1 IN (@org)
  AND SubTotal != 0
  AND LEN(INVOICE) > 0
  AND
     (

        (
            CUSTOMER = 'QAA'
            AND (INVOICEDATE IS NOT NULL)
            AND (DATEPART(MM,INVOICEDATE) = DATEPART(MM,@start))
            AND (DATEPART(YYYY,INVOICEDATE) = DATEPART(YYYY,@end))
         )
      OR
         (
            CUSTOMER != 'QAA'
            AND (YearInvBalZero > 0)
            AND (YearInvBalZero = DATEPART(YYYY,@start))
            AND (MonthInvBalZero = DATEPART(MM,@start))
            AND (YearInvBalZero = DATEPART(YYYY,@end))
            AND (MonthInvBalZero = DATEPART(MM,@end))               
          )
      )
)

SELECT bb.team_id
  ,bb.specialist_id
  ,dd.Budget
  ,cc.team_name
  ,KEY1
  ,CustomText01
  ,CASE WHEN RowNumber = 1 THEN SubTotal ELSE 0 END as SubTotal
FROM InvoicedAndPaid as aa
join SalesReporting.dbo.team_members as bb
on rtrim(aa.CustomText01) = rtrim(bb.specialist_name)
join SalesReporting.dbo.team_master as cc
on bb.team_id = cc.team_id
join SalesReporting.dbo.sales_goals as dd
on bb.specialist_id = dd.specialist_id
WHERE dd.time_span LIKE 'M%'
  AND dd.month = DATENAME(MONTH,@end)
  AND dd.Org = aa.KEY1
  AND cc.team_name in (@team)
  AND dd.period_no = DATEPART(MM,@end)
ORDER BY team_name, CustomText01

Does this help out a little? I'm trying to avoid doing a Stored Procedure because I'm fairly new at SSRS and SQL Server.

Unknown Unknown · Accepted Answer · 2011-07-15T20:47:24

One option would be to calculate the total sales in the SQL Server query that is used in SSRS dataset. Following example shows a sample. The sample was created based on an assumption of how I envisioned the table might be. The report was created in SSRS 2008 R2 and database used was SQL Server 2008 R2.

Assuming that the source table is as shown in screenshot #1. The table is not normalized but it is shown here just for an example.
The query for the report database would be as shown in screenshot #2. The query is also given under SSRS Dataset Query section.
Report would be designed as shown in screenshot #3. Expression in section a would =Fields!TotalSales.Value - Fields!Budget.Value and Expression in section b would be =Sum(Fields!TotalSales.Value, "SalesData") - Sum(Fields!Budget.Value, "SalesData").
Screenshot #4 shows report execution.

Hope that helps.

SSRS DataSet Query:

SELECT      Specialist
        ,   SUM(Sales) AS TotalSales
        ,   SUM(DISTINCT(Budget)) AS Budget 
FROM        dbo.SalesData 
GROUP BY    Specialist

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Expression total not displaying correct data in SSRS 2008

2 Answers