Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Calculated field in SSRS

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-05-08
I have a calculated field with the following formula

There are cases where the value of C is 0; so when you preview the report, it shows #Error for those record where C=0
I don't want to have #Error for the projection value. Is there any way to validate that? I would like to show 0 instead of #Error
Question by:ITCity
  • 2
LVL 13

Expert Comment

ID: 26296642
projection=IIf(C>0, (a*12/b)/C, 0)

Open in new window

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26296646
projection= case when c>0 then (a*12/b)/C else null end

Accepted Solution

ITCity earned 0 total points
ID: 26298447
The first suggestion should work, but for some reason it does not in this case. There are two values in the formula that may be causing the problem. They are:

When you put projection =IIf(Fields!d_cy_revised_bud.Value > 0, (Fields!b_cy_memo_bal.Value*12/Fields!d_cy_revised_bud.Value, 0) In some cases, #Error are returned.

What I had to do was to use a temporal variable as follow:
Temp_bal =IIf(Fields!b_cy_memo_bal.Value = 0, 1, Fields!b_cy_memo_bal.Value)
And the for the projection
=IIf(Fields!temp_bal.Value = 1, 0,Fields!temp_bal.Value*12/Fields!d_cy_revised_bud.Value)
LVL 13

Expert Comment

ID: 26298558


projection=IIf(C>0 and b>0, (a*12/b)/C, 0)

Open in new window


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question