How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian|||

Ian Roof - MSFT wrote:

The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

Oooh... good to know. Well, okay then.

Thanks for the clarification, Ian.

So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

--Pete

|||One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian|||

Ian Roof - MSFT wrote:

The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

Oooh... good to know. Well, okay then.

Thanks for the clarification, Ian.

So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

--Pete

|||One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian|||

Ian Roof - MSFT wrote:

The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

Oooh... good to know. Well, okay then.

Thanks for the clarification, Ian.

So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

--Pete

|||One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

Ian

How to do SUM of Average in subtotal cell for the entire matrix

Hi,

I have a matrix as shown below:

Head Count Jan-07 Feb-07 Average Dept1 59.00 62.00 60.50 Dept2 21.00 21.00 21.00 Total 80.00 83.00 81.50

I am having trouble figuring out how to ADD the "Average" column to get the 81.50 (red). I tried SUM(AVG(Fields!....)) but it didn't work.

Any help is appreacited!

Thanks,

Tabbey

Don't try to SUM it, just leave the formula as is and it should give you the correct result in the total row, the average of 80 and 83 is 81.50.|||

Can you explain me what you did in report ?

How do you populate data into the Avg and Total i.e subtotal of rows and columns ?

|||

Sluggy,

I apologize I did not explain that correctly. The Dept has a nested group of Divisions as shown in the new example below. The RED highlited cells give the "WRONG" AVERAGE (using function AVG). That's the reason I think I need the AVG(SUM(...)).

Jan 07 Feb 07 Average Dept1 Div 1A 345.00 345.00 345.00 Div 1B 41.00 41.00 41.00 Div 1C 283.00 283.00 283.00 Total 669.00 669.00 223.00 Dept2 Div 2A 8.00 8.00 8.00 Div 2B 63.00 63.00 63.00 Div 2C 2.00 2.00 2.00 Total 73.00 73.00 24.33

As the result, I get the wrong average when the Divisions are collapsed as shown below (in RED highlite):

Jan 07 Feb 07 Average Dept1 669.00 669.00 223.00 Dept2 73.00 73.00 24.33

What is the best solution for situation like this?

Thanks,

Tabbey

|||What you are looking to accomplish requires nested aggregates, the average of a subtotal. Nested aggregates are not currently supported. There is a workaround, however. The workaround is to use a custom function that uses the appropriate expression, based on the current scope. In this example, there are four different scopes in which the cell is calculated. One for the cell, the subtotal, average, and the average of the subtotals. The custom function for the first two cases simply returns the aggregate value passed in. The third case will take the subtotal passed in and add it to a running total, as well as keep a count values added to the total, and then return the subtotal. The fourth case will calculate and return the average, and reset the running total and count.

The custom function is called using the following expression, assuming that the column group is named "Date" and the inner row grouping is named "Division."

=Code.CalculateSumSubtotalOrAverage(Sum(Fields!FieldName.Value),AVG(Fields!FieldName.Value), InScope("Date"), InScope("Division"))

Here is the custom function.

Code Snippet

Private m_total As Double
Private m_count As Integer

Public Function CalculateSumSubtotalOrAverage(subtotal As Double, average As Double, inDateScope as Boolean, inDivisionScope As Boolean) As Double

If inDateScope And inDivisionScope Then
' Regular cell
Return subtotal

Else If Not inDateScope And inDivisionScope Then
' Average of Division

Return average

Else If inDateScope And Not inDivisionScope Then
' Subtotal of Date
m_total = m_total + subtotal
m_count = m_count + 1

Return subtotal

Else
' Average of Subtotal
Dim avg as Double
avg = m_total / m_count

m_count = 0
m_total = 0

Return avg

End If

End Function

|||

Ian, would this code also help resolve my issue here?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515409&SiteID=1|||Yes, it should, but you will need to pass in the results of the InScope function for Year and Quarter, and the only the result of the SUM aggregate are needed.

In the function, if Year is in scope and Quarter is not, then update the variance calculation, and return the subtotal. If both Year and Quarter are not in scope, then calculate and return the variance. Otherwise, just return the sum. This should produce the variance over the year subtotals for the column grand total and regular subtotals for the Customer and Year subtotals.|||

Hi Ian,

I don't know visual basic and I don't know how to calculate the variance using a visual basic code. Could you write a visual basic code that calculates the variance in scope just like the one you wrote above?

Thanks, Susan

|||

Ian,

I have a report project in VS2005 with a matrix on the report. I have added a rowgroup on date and I want to average the values for each column at the bottom of the report. I cannot find anything within the Matrix Properties dialog that lets me specify Averaging as opposed to Summing the totals.

I see what you are doing with this function, but where do I put this code? VS will not let me add a code module or anything other than another report.

Thanks,

Russ.

|||

Hi Ian,

Could your code be adapted to give me the max value of a specific column of a table ?

For more details please check out this : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1

If you could help me with some hints this would be great.

Greetings

Vinnie

|||Hi Vinnie,

Yes, the above can be modified to accomplish this. However, you don't really need anything this complicated.

You can use the first function below in the body of the table, and use the second one in the table footer.

Code Snippet

Private m_max As Integer = -1

Public Function StoreMaxValueForHour(maxValueForHour as Integer) As Integer

m_max = Math.Max(m_max, maxValueForHour)
Return maxValueForHour

End Function

Public Function GetMaxHourSubtotal() As Integer

Return m_max

End Function

The first one is called like this:

=Code.StoreMaxValueForHour(CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100)))))))

Actually, you can simplifiy this a little using ReportItem references:

=Code.StoreMaxValueForHour(math.Max(ReportItems!TableBodyCountTest1.Value, math.Max(ReportItems!TableBodyCountTest2.Value, math.Max(ReportItems!TableBodyCountTest3.Value, math.Max(ReportItems!TableBodyCountTest4.Value, ReportItems!TableBodyCountTest5.Value)))))

The second in the footer is called like:

=Code.GetMaxHourSubtotal()

I hope this helps.

Ian|||

I've been reading this discussion thread as I'm running into a similar problem wherein I have a table that performs a Count(# of support cases) per Month. The table groups on month. I'm trying to get the max(count(#support cases)).

I tried to implement the code suggested from above; the first function works fine "StoreMaxValueForHour", but the second function just returns -1. I've placed the second function in the table footer.

What's the secret to making it work?

|||The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian|||

Ian Roof - MSFT wrote:

The reason this is not working is that the Table headers and footers are calculated before the inner groups and details. The code discussed here is really only appropriate for Matrix report items.

Ian

Oooh... good to know. Well, okay then.

Thanks for the clarification, Ian.

So, I'm guessing this means that a table data region just doesn't offer much in the way of getting to a solution for an aggregate of an aggregate. Your suggestion (relevant to a matrix) seems like the closest thing to a simple solution of a nested aggregate that I've been able to find.

--Pete

|||One thing you may be able to try is to create a two Tables. One table that is hidden and uses the first function that does not have headers and footers, and your current table that only uses the second function. Make sure that the new, hidden table is above and to the left of your current table, so that it will be executed first.

Ian

How to do such a job ?

Hi, everyone,

In my database , i create 2 table to store all the table description and all table fields description.

So I want the user to use these 2 table to define the report .

How to do such a job ?

Thank u .

Jeffers

If there is any relation between the tables, you could either write a Select query or create a view based on a select query which combines the two tables to a dataset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to do something like: SqlDataSource[nRow][nCol].Value ??

How do you get a record-set or something like it. I do not want to bind to any grids or display the data. I just need access to SQL Server tables.

What is the best way and where can I get some documentation and example code?

Thank's for any help

Then you do not need SqlDataSource. You can use SqlCommand/SqlDataReader/SqlDataAdapter etc. You can find lots of example from MSDN:

http://msdn2.microsoft.com/en-us/library/ms254937.aspx

How to do sizing on Memory requirement of SQL Server 2000 ?

Hi All,
We have custom application running on SQL Server 2000, about 100
users, on WIn Server 2003. The RAM installed is 1GB, very often that
the server the client getting kicked when trying to connect to the
server from custom app. Then we add to 2GB, now its getting better.
SO, I need a formula about how to determine the memory requirement of
our SQL Server 2000 on Windows Server 2003 ?
Thank you for your info,
xtantokrislioe@.gmail.com wrote:
> Hi All,
> We have custom application running on SQL Server 2000, about 100
> users, on WIn Server 2003. The RAM installed is 1GB, very often that
> the server the client getting kicked when trying to connect to the
> server from custom app. Then we add to 2GB, now its getting better.
> SO, I need a formula about how to determine the memory requirement of
> our SQL Server 2000 on Windows Server 2003 ?
> Thank you for your info,
> xtanto
You get an error when trying to connect for the first time?
I would start by addressing your connection timeout in the application
and if you are using 1GB-2GB of RAM look at the design of your database
and make sure you have good indexes. If you need to do table scans and
load everything into memory every time you do a select you will be in
trouble RAM and CPU wise.|||Marc S wrote:
> krislioe@.gmail.com wrote:
>
> You get an error when trying to connect for the first time?
> I would start by addressing your connection timeout in the application
> and if you are using 1GB-2GB of RAM look at the design of your database
> and make sure you have good indexes. If you need to do table scans and
> load everything into memory every time you do a select you will be in
> trouble RAM and CPU wise.
Looking at the design will help. But you should run performance
monitor. One thing you could do is look at Buffer Cache and Cache hit
ratios. If they are not adequate, then you could have poor written
queries and missing or improper indexes. Possible unnecessary
voluminous pages could be filling up the Buffer Cache causing it to
flush necessary pages. Then run profiler to identify processes and sql
statements unless you have a good idea what this could already be. What
I'm getting at here is there is no formula. Environments differ. That's
why real-world multi-user database applications typically need real
DBAs. Also, what I've stated above is not all you can look at. I would
do a google search on SQL Memory and start reading. Sorry there is no
easy formula.
Good Luck!!!|||Under SQL 2000 the most SQL will be able to see is 2 Gigs when running on
Win 2003 Standard. On Windows 2003 Enterprise Edition it can see more (3
Gigs with the /3 Gig switch, and the data cache can access more memory with
AWE enabled).
SQL will utilize all available memory for its caches.
There is no formula to predict how much memory your load will require as
there are many variables.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<krislioe@.gmail.com> wrote in message
news:1157559010.101762.108420@.e3g2000cwe.googlegroups.com...
> Hi All,
> We have custom application running on SQL Server 2000, about 100
> users, on WIn Server 2003. The RAM installed is 1GB, very often that
> the server the client getting kicked when trying to connect to the
> server from custom app. Then we add to 2GB, now its getting better.
> SO, I need a formula about how to determine the memory requirement of
> our SQL Server 2000 on Windows Server 2003 ?
> Thank you for your info,
> xtanto
>|||Hi Hillary,
is there any official URLS / docs about this limitation ?
Thank you,
xtanto
Hilary Cotter menuliskan:[vbcol=seagreen]
> Under SQL 2000 the most SQL will be able to see is 2 Gigs when running on
> Win 2003 Standard. On Windows 2003 Enterprise Edition it can see more (3
> Gigs with the /3 Gig switch, and the data cache can access more memory wit
h
> AWE enabled).
> SQL will utilize all available memory for its caches.
> There is no formula to predict how much memory your load will require as
> there are many variables.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <krislioe@.gmail.com> wrote in message
> news:1157559010.101762.108420@.e3g2000cwe.googlegroups.com...