Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reporting services Group is not calculating by group. Expand / Collapse
Author
Message
Posted Wednesday, January 19, 2011 12:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 4:16 PM
Points: 224, Visits: 304
Hi,

Reporting services Group is not calculating by group.

=count(Fields!GMPI.Value) *900 for group column A gives the same total of $240,000 for all 3 groups, but =count(Fields!GMPI.Value) * 600 for group col B works corretly for each group having a different value.

any ideas, it is almost as if ssrs is glitching cuase the values in the field columns do not add up induvidually to 240,000.

thanks in advance
Post #1050290
Posted Wednesday, January 19, 2011 12:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:36 AM
Points: 340, Visits: 406
The COUNT() expression function used in SSRS can actually receive a second parameter that specifies the group name that defines the scope the COUNT() should be calculated in.

The syntax would be roughly, COUNT(Fields!<field_name>, "<group_name>").

Try adding that to the misbehaving count and see if that corrects the report logic.
Post #1050312
Posted Wednesday, January 19, 2011 2:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 4:16 PM
Points: 224, Visits: 304
Thanks so much. It was grouping them all by market, just that group header was choking. A fix that worked was doing the calculation at the data level in t-sql and not in the group header, although other columns were behaving fine with expressions in the group headers.



SELECT *, ISNULL(RAFScoreDataRap,0) * 900 AS RAFScoreDataRapCalc FROM @rptMatrixMetrics


SO now this above select at the end of the t-sql script in the SSRS data tab now multiplies by 900 at this level so in the layout just needs the new column alias we created above: RAFScoreDataRapCalc

Thanks
Post #1050389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse