Row group 'footers' in matrices

I am trying to create a report that will append a calculated row of
percentages to the end of a group of test results. The results are
grouped by 'Category A' and have different subcategories. But
overall, I want to generate percentages for each column in 'Category
A'. The columns of Category A consist of Pass, Fail, and In
Progress. I want to be able to have a percentage of all the passes in
category A, but still have that distinction between a subcategory in
category A.
I have designed the matrix to look like such:
Static
Column
Category A | Sub Category
and I want it to produce a result like such:
Pass Fail In Progress
Category A Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
Category B Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
It is easy to format within Crystal Reports, but I have been mashing
my brain all day and I haven't found how to do it within Reporting
Services. HELP.Here are the layouts again. I didn't realize that it was going to get
ruined when it posts.
.Static Column
Category A | SubCategory
.................................P .F .IP
Category A Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%
Category B Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%

Round() function doesn't seem to work properly in SQL 2k5

Hi,
I have been using the round function in SQL 2000 for quite a while, and when doing sums of percentages have used the Round(99.99,0) function to great avail to get a value of 100.00.

But, when I migrated to SQL 2K5, i used the same function and it gave me an arithmetic overflow error; more specifically:
"An error occurred while executing batch. Error message is: Arithmetic Overflow."

Is anyone aware of any bugs / changes to this function in 2k5 that I am not aware of?

Thanks,
Saurabh

You might also try using CEILING() in conjuction with ROUND(). But be careful if you do...

|||

Yeah, ceiling will work for 99.99, but for the likes of 100.01, then floor is better.

Anyway, does anyone know why the round doesnt work properly in sql 2k5?

|||

I'm not aware of any changes in the underlaying functionality of ROUND().

Could it be that something about the data is causing the overflow?

|||

Hmm...not sure

It was just a simple select round(99.99,-2) that yielded 100.00 in sql 2k and arithmetic overflow error in 2k5

|||

You got me on that one. I definitely see that the results are the same for SLQ 2000 and SQL 2005.

Hopefully, someone from MS will chime in and let us know what is happening...

|||That seems to be a problem with implicit casting.

This works:
select round(CAST(99.99 AS DECIMAL(10,2)),0)

This errors with "An error occurred while executing batch. Error message is: Arithmetic Overflow":

select round(99.99,0)

It looks like a bug to me.

Running 2005 9.0.2153. Anyone running SP2 to see if this fails under it also?|||

Cool, mate!

That works.

I am running 2005 9.0.2047 at the moment, and have that error.