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

Matrix Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.

Thanks again for your help!

Matrix Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.

Thanks again for your help!

matrix report

Hello,

Am having this problem, and i have not been able to solve it as yet.

The requirement is to have a matrix like structure such as:

Column Header

Row Header Value1;
Value2;

I was able to create a matrix report using SQL Reporting services 2000, placed the column as well as Row headers, however, the value can be just an aggregate expression, which means I can have only one value as the data. But the requirements is to have multiple values in the data placeholder.

Does anyone have experienced the same problem as me? Any help will be highly appreciated.

Regards

Santosh

You can make like a tree in a Table control. You can add the main value in Header and when u click on it, it can display multiple values in detail row.

master with two detail views

Hello community,

I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):

In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table)

SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] =@.id)"

For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).

The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this:

SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @.person_id)"

...

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>

But now the problem: how should I declare a parameter @.task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks:

SelectCommand="SELECT [task_id], [task_name],[task_date], [task_description] FROM [TabTasks] WHERE ([task_id] =@.task_id)"

@.task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.

Thank you in advance for your help

Hello,

I found the solution on my own.

Within the properties of the gridview with the master records the property "datakeyname" must contain both keys: person_id;task_id

And in the second details datasource the task_id can be referenced as the second (index) within the selecteddatakey-collection in a select/control paramter like this:

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="task_id" PropertyName="SelectedDataKey(1)" />
</SelectParameters>

Perhaps this may help someone else here.

regars

dieter