Hope you all are doing good. Would like to start with praying for all those who have been affected by a deadly earthquake in Nepal. Just when it seems that life is good something like this happens, to remind us of that we are not permanent here. Death is a bitter truth.
Moving on this week I again ran into one of the bugs in reporting services 2005 i.e. we cannot have aggregates of aggregates in a situation where we have a parent group and a child group.
Requirement - See the resultset below
pid pdesc cid cdesc
1 harish 1 ninu
1 harish 2 lovely
2 asha 3 ricky
2 asha 4 hina
We have 2 groups one is parent having pid has the identifier and second group is child having cid as the identifier
We want to calculate the sum of cids accross parent and child group (off-course at child level it would be the individual cid itself)
Implementation - so I have written the expression sum(Fields!cid.Value,"details") adjacent to column cdesc inside child group
After this I have written the expression sum(sum(Fields!cid.Value,"details"),"pdesc") adjacent to column pdesc inside parent group.
details and pdesc are the name of child and parent groups respectively.
The above scenario works fine in sql 2012 reporting services, however in sql 2005 we cannot implement sum of sum.
So as a workaround, try to implement this logic inside the sql query by adding group by clause,
I hope it is clear.
Have a safe life.