New user of Reports Studio, having made the jump from Query Studio...
I have two Data Items in my report, FORECAST_CASH_IN and CASH_IN.
The value of CASH_IN can sometimes be zero.
I then have a Layout Calculation, where I want to display one figure as a percentage of the other.
abs(([Query1].[FORECAST_CASH_IN]-[Query1].[Cash In])/[Query1].[FORECAST_CASH_IN]) (cell is defined as percentage data type).
Works fine, apart from when the denominator (Cash IN) is a zero. That is obviously an invalid calc so it outputs '/0' in the report. Not meaningful for the users.
I tried to use an IF statement:
if ([Query1].[Cash In] = 0) THEN 'n/a'
ELSE
(abs(([Query1].[FORECAST_CASH_IN]-[Query1].[Cash In])/[Query1].[FORECAST_CASH_IN]))
...but I get RSV=VAL-0002 Invalid Expression, with a CRX-API-0003 data type error.
I've tried various different things with the syntax/brackets and whatever I do I can't use this IF approach.
I am stuck with the report stating '/0' where the calculation involves a Cash In value of zero – its fine, it gives the users what they need, but I wish I could put some other meaningful value in there!
Just Produce the formula without any If statement. Then change the data format from the options. Within there is an option "Divide by zero Characters" from here you can change what it displays for that data item when the result is division by zero.
Hope that helps
Yes, data format is the way to go.
For future reference, the reason your if statement expression was invalid is because both possible outcomes must be the same data type. In your expression, the "if" result was a character string and your "then" result was a number. Same concept with a case statement...all "when" clauses must evaluate to the same data type.
Thanks for the response, yes I thought it was maybe a data type issue...can't confirm at the moment this solution works as busy with other work but will have a go as soon as I can. Many thanks!
Great, that works, thanks. Didn't know that option even existed!
Thanks Holsy for the Solution :)