Aggregate Question
-
I need help on calculating a subtotal. The problem is Reporting Builder keeps wanting to sum the entire column. Which in most reports I would typically want. This one is a odd cat though. I only want it to sum the previous subtotals. The subtotals in the picture are sample data I'm testing. The pink bars are similar to a average for that date. The test values are all .25 for readability. They could potentially be any value.
Is there anyway to sum based on a textbox in the table? This one has thrown me for a loop. excuse the sloppy ness its still a work in progress
***SQL born on date Spring 2013:-)
-
Hi,
What you can do is nest the aggregates and specify the scope of the inner aggregate. It would look something like this:
=Sum(Avg(Fields!GroupUnits.Value,"Service Date"))
Hope that helps!
-
Thanks for the reply @Get me?
I actually tried that before posting here. It gives me a scope error.
I may try some of the custom code and see if I can get something figured out that way.
***SQL born on date Spring 2013:-)
-
Possibly the ability to refer to the value of a specific textbox within SSRS would help here. SUM(ReportItems!TextboxName.Value).
-
You can only aggregate Report Items in the Header and Footer. At least that's what Report Builder keeps yelling at me about.
***SQL born on date Spring 2013:-)
-
I have this custom code in Report Builder, but it only returns a zero value. I don't know VB that much. Is there something obvious I'm missing?
Public Function Lookup_Sum(ByVal value As Integer) As IntegerDim Total_lookup_Sum As Integer = 0
Total_lookup_Sum = Total_lookup_Sum + value
Return Total_lookup_Sum
End Function
Value I put in the cell
=Code.Lookup_Sum(Reportitems!Textbox214.Value)***SQL born on date Spring 2013:-)
-
In the spirit of "Never let the machine win"...
Did you consider modifying the query to generate the number you need. An example
WITH cteAverage AS
(
SELECT
Date
, AVG(GroupUnits) AS DateAvg
FROM YourTable
GROUP BY Date
)
, cteGrpTotal AS
(
SELECT SUM(DateAvg) AS Total from cteAverage
)
SELECT Date
, (SELECT MAX(Total) from cteSum) AS GroupTotal
FROM YourTable
ORDER BY Date
Since the GroupTotal will be the same value for all records, you can use "First" to select a value for the total textbox.
-
Ohhh, you got the juices going. Great Idea! I am going to try that, very nice:-D
***SQL born on date Spring 2013:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply