July 20, 2009 at 12:22 pm
Hi everyone,
I have a stored procedure that pulls in data similar to this:
Location SalesGoal RevenueGroup DollarsSold ItemsSold
Florida 72000.00 Booths 4000.00 400
Florida 72000.00 Sponsorships 5000.00 500
Florida 72000.00 Upgrades 6000.00 600
Alabama 65000.00 Booths 1000.00 100
Alabama 65000.00 Sponsorships 2000.00 200
Alabama 65000.00 Upgrades 3000.00 300
There are multiple locations, each having only one goal. Each location has multiple revenue groups, each with sales $ and quantities.
At the Location level, I need to show the goal and sum the dollars and items. Easy enough to do - I'm summing the dollars and qty sold, and for the goal I am using the First() function to grab the first goal per location.
My problem comes in the grand total. I am unable to figure out how to summarize the goals for each location without overstating them. In the above example, the goal total should be $137,000, but of course a straight sum returns $411,000.
I tried summing the value in the textbox containing the first value.
I've also tried using RunningValue() to capture the value at the group level. I've tried summing the first value. Everything I do returns an error.
Can anyone help? Thanks!
July 20, 2009 at 5:12 pm
Cross join a subquery which returns you the total of the revenue goals. Then display this column with a First() clause in the total.
July 21, 2009 at 7:30 am
I was hoping for a way to do this in the report rather than in an already-lengthy stored procedure. Is there a way?
July 21, 2009 at 10:27 am
If your SP returns ALL the revenue groups for each location every time, you can use the follwoing expression for the total:
=Sum(Fields!SalesGoal.Value)/CountDistinct(Fields!RevenueGroup.Value)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply