Can I total "distinct" subtotals?

Suppose the dataset comes back looking like (the counts are
arbitrarilly chosen)
PO #___Dept___PO Total___PO Line__Colour___ Item Count
1 Dept1 $100 1 Red 5
1 Dept1 $100 2 Green 10
1 Dept1 $100 3 Blue 15
2 Dept2 $110 1 Red 1
2 Dept2 $110 2 Green 2
2 Dept2 $110 3 Blue 3
3 Dept1 $200 1 Red 50
4 Dept2 $150 1 Green 65
How would I get a report that gives group footers line
Red 55
Green 10
Blue 15
Dept1 $300
Red 1
Green 67
Blue 3
Dept2 $260
I did not use PO Line $ bcause in my real world the field corresponding
to the PO Total is not an argregate of the lines but rather another
attribute of the higher level file altogether.You need to get a subtotal on your groups. Edit Group and then subtotal on
"rows"..
"BruceJ" wrote:
> Suppose the dataset comes back looking like (the counts are
> arbitrarilly chosen)
> PO #___Dept___PO Total___PO Line__Colour___ Item Count
> 1 Dept1 $100 1 Red 5
> 1 Dept1 $100 2 Green 10
> 1 Dept1 $100 3 Blue 15
> 2 Dept2 $110 1 Red 1
> 2 Dept2 $110 2 Green 2
> 2 Dept2 $110 3 Blue 3
> 3 Dept1 $200 1 Red 50
> 4 Dept2 $150 1 Green 65
> How would I get a report that gives group footers line
> Red 55
> Green 10
> Blue 15
> Dept1 $300
> Red 1
> Green 67
> Blue 3
> Dept2 $260
> I did not use PO Line $ bcause in my real world the field corresponding
> to the PO Total is not an argregate of the lines but rather another
> attribute of the higher level file altogether.
>|||Thanks Asim. That gets me the subtotals by colour quite readily.
How would I then get the subtotals by Department 1 $300 and 2 $260
instead of $500 (3 x $100 + $200) and $480 (3 x $110 + $150) for 1 and
2, respectively if I use regular sum approaches.
The closest that I've come is to define two functions in my customer
code. The first maintains a string array and will return 1 if it is
able to insert a new concatenation of PO # + Line # 1 in to that array
and zero otherwise. The second will maintain and return a running
total of the PO Line Amounts by receiving the current line amount
multiplied by success or failure of the insertion attempt. To enquire
as to the current value of the running total I simply pass it a zero
which is added to the current value and then the new current value =old current value + zero is returned.
http://www.toomuchcode.net/2005/11/sql_server_2000.html gives more
details on the procedure.
Unfortunately I've run in to a problem with the order that reporting
services actually builds the footers prior to rendering them. It
appears that the lower numbered, higher level or outer footer levels
are processed before the deeper ones. So, in my example above my
running value at the Department 1 footer would be zero and then the
detail lines would shown it as $100 and then $300 and the $300 value
would be placed on the Department 2 footer.|||Asim, a colleague and I have have just confirmed that, for the
following report structure
Group1 Header
Group2 Header
Group3 Header
Detail Line1
Detail Line2
...
Detail Line n
Group3 Footer
Group2 Footer
Group1 Footer
(with "=Code.TrackLine(1)" placed in a cell once per Header, Footer or
Detail), the order of execution of TrackLine will be
=1 Group1 Header
=2 Group1 Footer
=3 Group2 Header
=4 Group2 Footer
=5 Group3 Header
=6 Group3 Footer
=7 Detail Line1
=8 Detail Line2
...
=(n+6) Detail Line n
Which concurs with my experience that I was shows a value of zero in my
Group 3 Footer before my detail lines got 'round to adding anything to
my running total.
The Custom Code looks like
...
Function TrackLine(ByVal c_line as integer) as integer
Static Dim n_line as integer
n_line = n_line + c_line
return n_line
End Function
...
I find this rather puzzling since aggregate functions work on the
footer levels. Either Bill's wandering through the data twice or
there's a deeper mystery.
Regardless, does anyone know of a way to force a morenatural order of
execution that corresponds with the way the report would appear once it
has been rendered? e.g. top-to-bottom, left-to-right.