We are using SSRS in which we have following Query Result
LOC PD SM PG Product BUDGET Amount Month Date
DL PD1 Anil RR SC 125000 1000.30 April 2015-04-03
DL PD1 Anil RR SC 125000 2500.30 April 2015-04-03
DL PD1 Anil RD SC 130000 1580.01 April 2015-04-03
DL PD2 Anil PCH SC 150000 3611.00 April 2015-04-03
DL PD2 Sanjay AG AH 225000 1566.67 May 2015-05-04
DL PD2 Sanjay AG IW 225000 3380.48 May 2015-05-04
DL PD2 Sanjay MG IW 75000 2237.62 May 2015-05-04
DL Dist Sunil UP AH 300000 523.33 May 2015-05-04
DL Dist Sunil UP AH 300000 1258.17 April 2015-04-06
While Implementing this in SSRS in following Herarchy we get following result
Apr'15 - Mar'16 September 2015
Loc PD SM PG Budget Amount MthBdt SC AH IW % 07-09-15 08-09-15
DL PD1 Anil RR 1,25,000 3,501 10416.67 0 0 0 0 0 0
RD 1,30,000 1,580 10833.33 0 0 0 0 0 0
Anil Total 3,80,000 5,081 31,667 0 0 0 0.00 % 0 0
PD1 Total 3,80,000 5,081 31,667 0 0 0.00 % 0 0
PD2 Sanjay AG 2,25,000 4,947 18,750
MG 75,000 2,238 6,250
Sanjay Total 5,25,000 7,185 43,750 0 0 0.00 % 0 0
Anil PCH 1,50,000 3,611 12,500 0 0 0 0.00 % 0 0
Anil Total 1,50,000 3,611 12,500 0 0 0 0.00 % 0 0
PD2 Total 6,75,000 10,796 56,250 0 0 0 0.00 % 0 0
dist Sunil 3,00,000 1802 25000 0 0 0 0.00 % 0 0
Sunil Total 6,00,000 1,782 50000 0 0.00 %
Dist Total 6,00,000 1,782 50000 0 0.00 %
DL TOTAL 16,55,000.00 17,135 1,37,917 0 0 0 0.00 % 0 0
Raw total of Amount is correct but is not correct in terms of Budget as Budget in query is link with PG, so should not get calculated transactions wise it should calculate column wise
it should display 2,55,000 insted of 3,80,000 in Anil Total , we have tried (Sum(Field!Budgey.value)) its giving this result and if we put only (Field!Budgey.value) its giving only 1,25,000
please guide is there any way where we can calculate the total of this value ?
I didn't found any solutions through SSRS, but following changes in Query is worked for me, what i added is count of the PG
PGCount = COUNT(*) OVER (PARTITION BY PG)
and created new BUDGET by dividing Budget to Count, and the same field value set in SSRS has resolve the Totaling Issue without any error
Thank