July 15, 2011 at 9:48 am
Please forgive me if this is an easy solution. I'm a avid Crystal Reports user who is learning the guts of SSRS.
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
SpecialistTeamTotal SalesBudgetDifference
DICK MATCHINSKY"$35,174.00""$63,530.00""($28,356.00)"
NATHAN MATCHINSKY"$16,193.00""$40,000.00""($23,807.00)"
PAUL MATCHINSKY"$52,096.00""$55,000.00" "($2,904.00)"
STEVE MATCHINSKY"$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
SpecialistTeamTotal SalesBudgetDifference
DICK MATCHINSKY"$35,174.00""$63,530.00""($28,356.00)"
NATHAN MATCHINSKY"$16,193.00""$40,000.00""($23,807.00)"
PAUL MATCHINSKY"$52,096.00""$55,000.00" "($2,904.00)"
STEVE MATCHINSKY"$31,185.44""$66,700.00""($35,514.56)"
TOTALS: "$134,648.44" "$225,230.00" "$71,118.44"
And here is my expression to get the total for the Difference and it's also used to get the grand total for every Specialist:
=Sum(Fields!TotalSales.Value)-Fields!Budget.Value
As you can see in the above data, I have correct data and incorrect data. The incorrect data is the data that is showing in my report and the correct data is when I export the data into excel and use excel's sum functions.
I would post the query, but I don't think it will assist anyone with this problem. Please let me know if it is needed.
Thanks in advance.
July 15, 2011 at 10:04 am
Looks to me like a formula error.
What about
=Sum(Fields!TotalSales.Value)-Sum(Fields!Budget.Value)
?
Your formula is doing this:
134,648.44 - 63,530.00 = 71,118.44
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 15, 2011 at 10:18 am
That's a good catch (seeing the connection 134,648.44 - 63,530.00 = 71,118.44) , but that actually didn't work out.
Here's what it did:
Before Your Suggestion
SpecialistTeamTotal SalesBudgetDifference
DICK MATCHINSKY"$35,174.00""$63,530.00""($28,356.00)"
NATHAN MATCHINSKY"$16,193.00""$40,000.00""($23,807.00)"
PAUL MATCHINSKY"$52,096.00""$55,000.00" "($2,904.00)"
STEVE MATCHINSKY"$31,185.44""$66,700.00""($35,514.56)"
TOTALS: "$134,648.44" "$225,230.00" "$71,118.44"
After Your Suggestion
SpecialistTeamTotal SalesBudgetDifference
DICK MATCHINSKY"$35,174.00""$63,530.00""($28,356.00)"
NATHAN MATCHINSKY"$16,193.00""$40,000.00""($23,807.00)"
PAUL MATCHINSKY"$52,096.00""$55,000.00" "($2,904.00)"
STEVE MATCHINSKY"$31,185.44""$66,700.00""($35,514.56)"
TOTALS: "$134,648.44" "$225,230.00" "($1,603,301.56)"
I should mention that there is grouping on the Specialist field. I doubt that would have anything to do with it, though because it is calculating the Difference correctly with the exception of the Grand Total.
What I initially did to get the group totals was right click on Difference, and selected "Add Total" like I would do to get any other Grand Total in a report.
Hm......weird.
July 15, 2011 at 11:33 am
Have you tried utilizing the ReportItems collection instead of using the fields from the dataset. Put something like the following for your Grand Total expression:
=ReportItems!textbox14.Value - ReportItems!textbox15.Value
Where you replace textbox14 and textbox15 with the names of your respective textboxes. The textbox name can be found by hovering over the textbox or viewing the properties of the textbox
July 15, 2011 at 12:56 pm
I appreciate all of the help on this sdvoranchik and Phil.
Unfortunately your suggestion gives me the same values that my previous expression did. Your suggestion also wouldn't let me add in a Total or a Sum. Weird...
July 15, 2011 at 3:16 pm
Maybe this might spark something....
I realized that I need to rethink my query
What I'm trying to do is make sure that the Budget result shows only once in each result set and have the remaining results display 0. So when I run my query in SQL Server, I get these results:
Team_IDSales_IDTeam_NameSubTotalBudget
4SCOTTVikings"202""25000.00"
4SCOTTVikings"1890""25000.00"
4SCOTTVikings"5167.22""25000.00"
4SCOTTVikings"20256.74""25000.00"
4SCOTTVikings"0""25000.00"
4SCOTTVikings"0""25000.00"
...where the results I'm looking to get are:
Team_IDSales_IDTeam_NameSubTotalBudget
4SCOTTVikings"202""25000.00"
4SCOTTVikings"1890""0.00"
4SCOTTVikings"5167.22""0.00"
4SCOTTVikings"20256.74""0.00"
4SCOTTVikings"0""0.00"
4SCOTTVikings"0""0.00"
I have duplicate data for the subtotal, which I am turning the value to '0'. I am doing this based off of a suggested solution using a WITH AS statement with ROW_NUMBER() OVER:
ROW_NUMBER() OVER (PARTITION BY DocID ORDER BY DocID) AS RowNumber
CASE WHEN RowNumber = 1 THEN SubTotal ELSE 0 END as SubTotal
This duplicate data is the result of one order that is shipped out multiple times (if an order is shipped 3 times, the subtotal will repeat 3 times).
I've tried doing the ROW_NUMBER() and a WITH AS statement but the source table wasn't liking it.
Here's my query if this helps out. It's not the cleanest code, but it grabs data that spans over 3 databases that don't exaclty interact with each other:
WITH InvoicedAndPaid
AS
(
SELECT DocNo
,SoldToPONumber
,TotalTax
,SubTotal
,KEY1
,CustomText01
,SoldToContact
,SoldToCompany
,ROW_NUMBER() OVER (PARTITION BY DocID ORDER BY DocID) AS RowNumber
,InvoiceDate
,[MonthInvBalZero]
,[YearInvBalZero]
,Source
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 != 'PARTS'
AND KEY1 IN ('QA')
AND LEN(INVOICE) > 0
AND ((CUSTOMER = 'QAA' AND (InvoiceDate IS NOT NULL)
AND (DATEPART(MM,InvoiceDate) = DATEPART(MM,'2011-5-1'))
AND (DATEPART(YYYY,InvoiceDate) = DATEPART(YYYY,'2011-5-31')))
OR
(CUSTOMER != 'QAA' AND (YearInvBalZero > 0)
AND (YearInvBalZero = DATEPART(YYYY,'2011-5-1'))
AND (MonthInvBalZero = DATEPART(MM,'2011-5-1'))
AND (YearInvBalZero = DATEPART(YYYY,'2011-5-31'))
AND (MonthInvBalZero = DATEPART(MM,'2011-5-31'))))),
TotalSales AS
(
SELECT DocNo
,Created
,CreatedBy
,Terms
,SoldToPONumber
,KEY1
,CustomText01
,InvoiceDate
,Source
,Salesperson
,Customer
,CUSTOMTEXT03
,CUSTOMTEXT04
FROM InvoicedAndPaid
)
SELECT bb.team_id
,bb.specialist_id
,Team_Name
,CASE WHEN RowNumber = 1 THEN SubTotal ELSE 0 END as SubTotal
,Specialist
,dd.Budget
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.Org = aa.KEY1
AND dd.period_no = DATEPART(MM,'2011-5-31')
GROUP BY bb.team_id
,bb.specialist_id
,dd.Budget
,cc.team_name
,KEY1
,CustomText01
,RowNumber
,SubTotal
ORDER BY team_name, CustomText01
July 15, 2011 at 3:27 pm
And it's not working if you use the same case statement for both the SubTotal and Budget?
Replace dd.Budget
With CASE WHEN RowNumber = 1 THEN dd.Budget ELSE 0 END as Budget
July 15, 2011 at 3:32 pm
Another random possibility would to be to divide out the count for your grand total, this would effectively create the average of the duplicate records which would be the ovalue you are looking for and displaying:
=Sum(Fields!TotalSales.Value)-(Sum(Fields!Budget.Value)/Count(Fields!Budget.Value))
July 15, 2011 at 3:47 pm
That's correct.
That's my dilemma....here's the result set when I add in CASE WHEN RowNumber = 1 THEN dd.Budget ELSE 0 END AS Budget I've included the DocNo and the RowNumber:
team_idspecialist_idTeam_NameRowNumberDocNoSubTotalBudget
4SCOTTVikings1AAAQ5790920225000.00
4SCOTTVikings1AAAQ571015167.2225000.00
4SCOTTVikings1AAAQ5675720256.7425000.00
4SCOTTVikings1AAAQ57509189025000.00
4SCOTTVikings2AAAQ5675700.00
4SCOTTVikings3AAAQ5675700.00
Since the DocNo is repeated 3 times, we two row numbers that aren't = 1 and the subtotal is set to 0.
Maybe this might help. This is the table from which the budget data is coming from:
yearperiod_noorgspecialist_idBudget
20111QASCOTT15000.00
20112QASCOTT13000.00
20113QASCOTT12000.00
20114QASCOTT20000.00
20115QASCOTT25000.00
Hopefully that helps
July 18, 2011 at 2:15 pm
It's tough to replicate the issue because of the complexity of the query and they way you are displaying the budget, but depending on how the groups are set up you may be able to get away with this:
=Sum(Fields!TotalSales.Value)-FIRST(Fields!Budget.Value)
From a report layout perspective, how many groups are there, one or two? It looks like the first level of aggregation (for person) may be happening in the query. If that is displayed in the details row, then you have one level of grouping for Team and the GrandTotal ReportFooter? Does that sound about right?
July 18, 2011 at 3:01 pm
sdvoranchik,
Here's what the report looks like: http://i55.tinypic.com/124jw2u.png
The white area is hidden
I'm not sure how it worked, but now I have the totals working. I had to put them in a footer and reference them to the actual text boxes (like I believe you mentioned). I know I did this before, but maybe I was referencing the wrong cells? Who knows...
This is what it looks like now: http://i52.tinypic.com/2dwdd8z.jpg%5B/url%5D
I appreciate all of the help!
July 19, 2011 at 4:50 am
I'm glad you finallly got it working. I didn't think what you were trying to do was that far out of the norm, but I was beginning to lose hope. When referencing the ReportItems you do have to be careful in terms of where you put it and making sure you have the right item. Good luck.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
