Using The Rollup operator in Sql Server 2000

Guys, I need some advice on displaying Rollup results

this is the query (SP).

select Org_lvl_2,level_one,doe,sum(RegHours),sum(OtHours)
from ZT where Ppe= @PERIOD
GROUP BY level_one,org_lvl_2,doe WITH ROLLUP
HAVING sum(RegHours) >0 or sum(OtHours) >0
ORDER BY level_one,Org_lvl_2,Doe

the Results:

NULL NULL 53.0 0.0
NULL CANM 53.0 0.0
HQJ CANM 53.0 0.0
HQJ CANM 32.0 0.0
HQJ CANM 13.0 0.0
HQJ CANM 8.0 0.0

what i'd like to display in a datagrid is:(with the Grand Total @ the bottom & leaving out the Nulls)

HQJ CANM 32.0 0.0
HQJ CANM 13.0 0.0
HQJ CANM 8.0 0.0
HQJ CANM 53.0 0.0

i've been able to gain a little control putting the returned dataset into a dataRow
and using IsDBNull(myRow.Item(0)) -[like that] - to weed out those null values. but,i've only had success if i then use a Listbox.item.add(myRow.Item(0) & myRow.Item(1)) - like that and i do have control, except for the Grand Total appears at the top
of the list, not the bottom.

i'm a little lost how to do this same thing in a datagrid, but a repeater or datalist control would be fine too if that's possible.

i've worked the sql to death trying to get subTotals & then Grand Total, so if anyone knows another way in sql, i'd appreciate some help on that.

other wise any advice on a good Control to use and maybe the method to display this would be very helpful.
i've tried using templates in the datagrid but it doesnt seem to recognize the aggregate columns.

thanks again

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!