Re: Report (template error) and EXPRESSION ASSISTA...

Efren · 03-20-2020 05:51 PM

Hi Guys !
I have a problem with a report that generates PDF at night, it is a summary of the business activity.

In general, the report reads the [CORREO] accounts of each of the stores from a table, and for each store [User] generates a summary.

after doing many tests, I came to this conclusion …

FORMULA 1 (PHOTO 1)
SUM (SELECT (MTTO [Cost Parts], AND ([MONTH] = MONTH (TODAY ()), [User] = [_ thisrow]. [BRANCH], [YEAR] = YEAR (TODAY ()))))
indeed in the table [MTTO] there is no “SUCURSAL” field, but in the virtual field the “EXPRESSION ASSISTANT” works fine and gives me the correct values …
however, testing it in “TEMPLATE” (PHOTO 3) of the report gives the following error!

Expression 'SUM (SELECT (MTTO [Cost Parts], AND ([MONTH] = MONTH (TODAY ()), [User] = [_ thisrow]. [BRANCH], [YEAR] = YEAR (TODAY ())))) ‘is invalid due to: Error in expression’ [_THISUSER]. [SUCURSAL] ‘: Unable to find column’ SUCURSAL '.

FORMULA 2 (PHOTO 2)
SUM (SELECT (MTTO [Cost Parts], AND ([MONTH] = MONTH (TODAY ()), [User] = [_ thisrow]. [BRANCH], [YEAR] = YEAR (TODAY ()))))
In “EXPRESSION ASSISTANT” it does not work, it results in ZERO in everything.
putting it in the “TEMPLATE” (PHOTO 4) of the report also makes the calculations wrong, ZERO

I AM CONFUSED AND TIRED, PLEASE A LITTLE HELP …
HOW IT SHOULD BE THE CORRECT FORMULA THAT FUNCTIONS IN BOTH PARTS !!

Steve

This:

_ thisrow

should be this:

_thisrow

Efren

Thx @Steve for your quick attention …

but it is well written [_THISROW]! what else could it be?

Steve

I’m confused. Are you translating your expressions? If so, please don’t.

You originally posted this expression:

There is clearly a space between _ and thisrow.

You then said:

Which is fine, because the expression you posted didn’t reference a column named SUCURSAL.

Then you said:

In which, again, there’s space between _ and thisrow.

The expression also doesn’t match the one in photo 3. Did you translate it?

Interesting also that the error references _THISUSER (not _THISROW), which doesn’t occur in the expression at all. I can’t explain that.

The spell-checking marks in photo 3 obscure the expression. Please turn spell-checking off for screenshots.

The most recent screenshot you provided references a column named _thisrow]. [SUCURSAL. I suspect the space between [_thisrow]. and [SUCURSAL] might be confusing things. Try [_thisrow].[SUCURSAL] instead.

I would also advise removing spaces between function names and the open parenthesis that follows. For instance, I recommend SUM( rather than SUM (.

All-in-all, I would reformat your expression from this:

SUM (SELECT (MTTO [Cost Parts], AND ([MONTH] = MONTH (TODAY ()), [User] = [_ thisrow]. [BRANCH], [YEAR] = YEAR (TODAY ()))))

to this:

SUM(SELECT(MTTO[Cost Parts], AND([MONTH] = MONTH(TODAY()), [User] = [_thisrow].[BRANCH], [YEAR] = YEAR(TODAY()))))

or, if you prefer more spacing:

SUM( SELECT( MTTO[Cost Parts], AND( [MONTH] = MONTH( TODAY() ), [User] = [_thisrow].[BRANCH], [YEAR] = YEAR( TODAY() ))))

Efren

Thx @Steve, i 'll put more attention to the spaces … and test again, thx

Report (template error) and EXPRESSION ASSISTANT error