Solved: Template Report [Solved]

bradlegassick · 08-28-2020 12:51 AM

I have a template report which outputs the data to a table. However the cost fields are calculated fields & even if there is a zero value, a 0 is both sent to the Google sheet & to the template. The expression to calculate the cost field is: (ANY(SELECT(BevDB[CostPerUoM],[Ingredient]=[_THISROW].[Ingredient1])))*[Qty1]
I have tried the following IF statement to have the output blank, BUT it still outputs a 0 & not a blank cell. Any help is appreciated. Cheers

SOLVED with this formula thanks to @Steve & @1minManager for helping me out.
ANY(LIST(ANY(SELECT(BevDB[CostPerUoM], ([Ingredient] = [_THISROW].[Ingredient1]))) * [Qty1]) - LIST(0.0))

Steve

Try:

ANY(LIST(ANY(SELECT(BevDB[CostPerUoM], ([Ingredient] = [_THISROW].[Ingredient1]))) * [Qty1]) - LIST(0.0))

Assuming the result is Price or Decimal.

View solution in original post

1minManager

Try this expansion on your equation to make it show nothing in the tempate:

<<IF
ISBLANK((ANY(SELECT(BevDB[CostPerUoM],[Ingredient]=[_THISROW].[Ingredient1])))[Qty1]),
“”,
(ANY(SELECT(BevDB[CostPerUoM],[Ingredient]=[_THISROW].[Ingredient1])))[Qty1]
)>>

Simon@1minManager.com

bradlegassick

Thanks @1minManager, I gave it a try but still have zeros in the sheet & report. Cheers

Steve

Try:

ANY(LIST(ANY(SELECT(BevDB[CostPerUoM], ([Ingredient] = [_THISROW].[Ingredient1]))) * [Qty1]) - LIST(0.0))

Assuming the result is Price or Decimal.

bradlegassick

@Steve thx mate, the result is Price. I’ll give it a go now. Cheers

bradlegassick

Hi @Steve I tried but got this error when saving the expression::
The expression is valid but its result type ‘List’ is not one of the expected types: Price

Steve

Yep. I noticed after your reply I forgot ANY(). I revised the expression above.

bradlegassick

@Steve 100% mate that works! Did you just deduct the value 0.0 from the result? Thx mate appreciate it!

Steve

We calculate the amount using your original expression, put the result into a (one-item) list, then use list subtraction to remove any items in that (one-item) list that have a 0.0 value. If the result was 0.0, it’s removed; if it wasn’t 0.0, it remains.