matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.|||Regarding about the issue of subtotal.I am encountering a problem,wherein,when i use the aggregate sum in the details expression,the value changed from the actual value in my database?Can you have any suggestions about it?Thanks!!

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.|||Regarding about the issue of subtotal.I am encountering a problem,wherein,when i use the aggregate sum in the details expression,the value changed from the actual value in my database?Can you have any suggestions about it?Thanks!!

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.

Matrix grouping

I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this:

Jan Feb May Jun Jul Aug Sep Oct Nov Dec Mar Total

Why is it doing this?

Here's a view of my matrix in layout view...

Month_Name

Fatal Crashes =sum(fatal_crashes.value)

Injury Crashes =sum(injury_crashes.value)

Property Damage =sum(Prop_Damage.value)

Total Crashes =sum(Total_crashes.value)

Chicago Crashes =sum(Chicago_crashes.value)

Crashes Located =sum(located_crashes.value)

% Located =sum(percent_located.value)

any help would be greatly appreciated!! THANKS!

Try changing the sorting of the matrix to use the month number if available.

http://msdn2.microsoft.com/en-us/library/aa179319(SQL.80).aspx

If it's not available, you may be able to build an ugly IIF statement to provide the row numbers, or use a function to return them.

cheers,

Andrew

|||

I tried to hardcode in a switch statement such as this one :

=Switch((Fields!Month_Name.Value) = "Jan", 1, Fields!Month_Name.Value = "Feb", 2, Fields!Month_Name.Value = "Mar", 3, Fields!Month_Name.Value = "April", 4, Fields!Month_Name.Value= "May",5, Fields!Month_Name.Value= "Jun",6, Fields!Month_Name.Value= "Jul",7, Fields!Month_Name.Value= "Aug",8, Fields!Month_Name.Value="Sep",9, Fields!Month_Name.Value="Oct",10, Fields!Month_Name.Value="Nov",11, Fields!Month_Name.Value="Dec",12)

But it didnt change anything. I also changed the sort to Fields!Month_Name.key and Fields!Month_Name.Level and nothing changed as well. I wonder if its because i added extra rows to the matrix ...but still it should work, i have added extra columns before and never had this problem.