April 27, 2008 at 11:39 pm
I have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group.
Can anybody give the suggestions how to resolve the problem..
I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..
location invoice no. date quantity item
delhi inv/del/1 1/1/2008 3 item-1
item-2
item-3
inv/del/2 2/1/2008 2 item-4
item-5
Location Total 5
This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.
April 29, 2008 at 1:34 am
This is just the Logic
Try it out in your query
Select MainTable.PrimaryColumn,..... .... from MainTable
Inner Join (Select MainTable.PrimaryColumn,Sum(column) from Maintable with Group by MainTable.PrimaryColumn ) Dummy On Dummy.PrimaryColumn = MainTable.PrimaryColumn
Remember I am sub querying the same table in the join. So this will also ease your rendering logic a lot
and with some hope solve your problem as well.
PS. I dont have an SQL server Installed else could have responded with an example
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply