SSRS 2005 - Divide by 0 Matrix and Custom quarters
I created this matrix that has 3 columns: the first one is received appl, 2nd completed appl and 3rd is percentage of completed, the problem is in the last column, if the Received value is not null then I get 0% completed but if both columns are Null then I get #Error, I am not sure how to write expression to account for both cases, this is my code. =round(IIF(count(Fields!PK_ZEDaCTN.Value) = 0,"N/A",SUM(code.Completed(Fields!CompDate.Value))) *100 /IIF(count(Fields!PK_ZEDaCTN.Value) = 0,1,count(Fields!PK_ZEDaCTN.Value)),0) My second problem is that the Quarters of our budget are: Q1 7/1/2010 through 9/30/2010; Q2 10/1/2010 through 12/31/2010, Q3 1/1/2011 through 3/31/2011, Q4 4/1/2011 through 6/30/2011. In my query I created a column Quarters: DATEPART(q, DateRecd), this is grouping the quarters in the normal way, so in the header of the column I create this expression: ="Q" & iif(Fields!Quarter.Value = 1, 3,iif(Fields!Quarter.Value = 2, 4,iif(Fields!Quarter.Value = 3, 1,iif(Fields!Quarter.Value = 4, 2,"")))) and is displaying the data Q4, Q3, Q1, Q2. My questions how can display the data as Q1, q2, Q3, Q4? Please I need you help, AngieAngie Rivera
March 14th, 2011 7:20pm

Hi, for showing quarter use this ="Q" & DATEPART(q, DateRecd) for avoid divide by 0 error =iif(Sum(Fields!A.Value)=0 or isnothing(Sum(Fields!A.Value)),"-", iif(sum(Fields!B.Value)=0 or isnothing(Sum(Fields!B.Value)),"-", Sum(Fields!A.Value)/iif(Sum(Fields!B.Value)=0 or isnothing(Sum(Fields!B.Value)),1,Sum(Fields!B.Value)))) Best Regards, C.Gnanadurai ----------------------- Please mark the post as answer if it is helpfull to you
Free Windows Admin Tool Kit Click here and download it now
March 15th, 2011 2:00am

Your formula for Avoid to divide by 0 worked!!! But the Quarters problem is not solved.Angie Rivera
March 15th, 2011 10:03am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics