Hi-- I am relatively new to SQL Reporting Services and have a couple basic questions (I think they are basic). First, lets say that I have the following fields in the body of my report: Sales, Cost, Profit, and Percent Profit. Percent Profit is a calculated field of profit/sales. Then, I have a grouping by customer, with a footer with the sum of sales, sum of cost, sum of profit. And I also want percent profit, but I do not want a sum or average of percent profit. Instead I want percent profit calculated as sum of profit/sum of sales. I cannot seem to get this to work with a calculated field. I must have the syntax wrong (as I was just taking sum(fields!profit.value)/sum(fields!sales.value)*100. Also, I understand the format of #,# can be used to print a blank instead of zero. But this format also suppresses any decimals. So if I have a field that needs two decimals when there is a value, but need it to be blank when 0.00 is returned, what is the appropriate format? Any help that can be provided wouldbe much appreciated. As books are great, but sometimes do not explain everything. Thanks! -Christina

For the formating something like this may work, put an expression into the cell.... =IIF(fields!profit.value=0,"", format(fields!profit.value=0,"#,#")) On the calculated field, what error message do you get? What exactly are you putting the field?

Thanks! I think the issue on the formatting might be that the number has four decimal digits, and I want it formatted to two, but if 0.00 then blank. So your format above might work but would need to accomodate the formatting of two decimal places right?As for the sum field, here is what I was trying... =sum(fields!profit.value)/sum(fields!sales.value)*100 This is where the profit and sales are sums in the group footer, and again in the report footer. Thanks, Christina

So for the format all you have to do is. .... =IIF(fields!profit.value=0,"", format(fields!profit.value=0,"#,#.##")) That should result in a blank value on the reportif the value is 0, otherwise it formats the value to 2 decimal places. You should read up on this in the Reporting Services documentation. And what is wrong with the sum(fields!profit.value)/sum(fields!sales.value)*100 expression? Is this a format issue again? Is the value being displayed correct and you just want to format it as a percentage?

When I use that calculation as the format, it still prints with all four decimal places when there is a value. Instead of printing with two. The only change I made was to add sum... =IIF(sum(fields!profit.value)=0,"", format(sum(fields!profit.value)=0,"#,#.##")) As the total I want to show blank if zero is on a footer in a subreport. I have not tried the sum percent calc yet, but I will. It was just giving me a general syntax error. I will check it out again, though. Thanks!

So, when I try the following for my calculated field, an internal server error occurs during the build: =sum(fields!profit.value)/sum(fields!sales.value)*100 What am I doing wrong?

Possible Zero Divide! Try: =IIF(sum(fields!sales.value)=0, 0,sum(fields!profit.value)/sum(fields!sales.value)*100 )

I find this works fine for me and doesn't cause any divide by zero errors: =Iif(Count(Fields!Smoker.Value)>0,Count(Fields!Smoker.Value)/Count(Fields!Smoker.Value, "matrix1_Product"), NOTHING)