Friday, March 23, 2012

Matrix Totals

Below is part of a matrix report. Sooo close, but I have two problems I have not been able to solve. Please help as a I have several similar reports to do.

1. Row totals. I have been able to get a row total by adding a row group (i.e., $849.7 in the first row). However it does not display a group total (i.e., the sum of $849.7 and $371.3 for Economic Development) for that column.

I have not been able to add a static column outside of the dynamic groupings. I thought this might be a resolution by displaying the sum of the Estimated Costs in a static column and hoping it would show the group totals the same as with the dynamic column totals. Is there a way to do this?

2. Sorting. The report needs to display the groups based on the descending total values. I have specified the following sorts on the groups: sum(Fields!Estimated_Cost),"matrix1_Proj_Typ_Group" descending (this is the first column) and sum(Fields!Estimated_Cost),"matrix1_Proj_Typ" descending (this is the second column). Neither sort appears to be work.

City

County

Federal

Joint

Estimated Cost

% of Total

Estimated Cost

% of Total

Estimated Cost

% of Total

Estimated Cost

% of Total

Economic Development

Business District Development

$849.7

$816.0

66.83%

$27.2

2.23%

$0.0

0.0%

$6.5

0.53%

Industrial Sites & Parks

$371.3

$131.5

10.77%

$190.4

15.59%

$0.0

0.0%

$36.0

2.95%

Total

$947.6

77.61%

$217.6

17.82%

$0.0

0.0%

$42.5

3.48%

Education

K-12 New School Construction

$1,594.7

$223.0

10.05%

$1,363.5

61.43%

$0.0

0.0%

$0.0

0.0%

Non K-12 Education

$37.8

$3.1

0.14%

$33.2

1.5%

$0.0

0.0%

$0.0

0.0%

School System-wide Need

$587.1

$167.2

7.53%

$419.2

18.89%

$0.0

0.0%

$0.0

0.0%

Total

$393.2

17.72%

$1,816.0

81.82%

$0.0

0.0%

$0.0

0.0%

General Government

Other Facilities

$21.3

$17.5

4.3%

$3.9

0.95%

$0.0

0.0%

$0.0

0.0%

Property Acquisition

$8.8

$6.8

1.68%

$2.0

0.49%

$0.0

0.0%

$0.0

0.0%

Public Buildings

$375.5

$294.2

72.54%

$72.0

17.74%

$3.0

0.74%

$6.2

1.52%

Total

$318.5

78.52%

$77.8

19.19%

$3.0

0.74%

$6.2

1.52%

1. Row totals: finally ran across a reference to "subtotals" and it solved this issue perfectly.

2. Can anyone help me with the sorting issue? Is this a bug or am I specifying this incorrectly?

|||

Regarding sorting:

What exactly are you trying to sort? You have a matrix - so if you are trying to sort descending based on the totals for "Economic Development", it will impact the overall column group sort order (which is shared for all row groups!)

You cannot have every row group instance ("Economic Development", etc.) sort the column group differently. They share the same column groups. If just the cell data is re-sorted, the final matrix data would be correct for some row/column intersections but incorrect for other intersections.

-- Robert

No comments:

Post a Comment