Problem with Subtotal on SQL Report!!!

[liorlankril]

I have a SQL report that contain a Matrix (Example of Matrix fields)

CallStatusType
NumberOfCalls %

HandlingOrg SubjectName
SUBTOTAL

Explanation:
"HandlingOrg" and "SubjectName" are Row Groups in the matrix (the result is dynamic according to the dataset)
"CallStatus" is Column Group in the matrix (the result is dynamic according to the dataset - the matrix will create column for each StatusType) that contain 2 sub columns : "NumberOfCalls" - number of calls for each status type and "%" - The percent of calles in this status from total of all statuses.

I also defined SUBTOTAL under the "SubjectName" group

Example for output:

Open Close Watting UnClosed

Org1 Sub1 10 25% 10 25% 10 25% 10 25%

Sub2 2 20% 4 40% 0 0% 4 40%

Sub3 0 0% 0 0% 1 100% 0 0%

SubTotal 12 45% 14 65% 11 125% 14 65%

Org2 Sub1 ...
Sub4 ...


As you can see, my problem is in the SubTotal line!!
It's correct to write 12 (total of 10+2) BUT!!! it's also operate totals of the percents (25% + 20% = 45%) And it's not good!!! I want it to be the percent of 12 from the total calls for Org1 (In this example I want it to be the percent of 12 from 51)

1) Is there any option to handle value of this subTotal?
2) If no, How can I at least to make this value not visibale only in the SubTotal cell of the percents?

 

[Catadmin]

You could manually code the subtotal using the Report - Report Properties - Code menu option, but you have to know VB to do it.

Or, you could highlight that cell, look at the properties explorer and change Visibility to False (I think that's the property). That should hide it for you.

Let me know if this didn't help. I'm in a SQL 2k5 Development class right now and can't look at my VS 2003 for tips.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[liorlankril]

Hi Catadmin!
Thank tou foe your reply

1) I know how to write code for report. But I dont know how to operate for cell which is type SubTotal

2) The SubTotal automatic operate subtotal for each column in the matrix. I dont think I have control of what to calce in each column, because the subtotal cell represent bu only one cell.
How can I control it?
As you can see in my example, I want it to operate SubTotal for the first column in the matrix but not for the second column.