Report Variables not Evaluating 2

[carolx]

Hello Forum

I have two formulae for report variables in sequence in Report Properties: IIF(flowtypeid = 8,amount,0) and IIF(flowtypeid = 6,amount,0) . The first outputs a non-zero amount, the second outputs 0. The records have non-zero values for flowtypeid = 8 and flowtypeid = 6. Do you know what is happening here?

 

[Chris Miller]

Well, in one record driving the report flowtypeid is either 6 or 8, so in one of the IIFs, of course, you get a 0 value, there's no wy around it.

The IIFs can only both result in a non 0 value, when flowtypiedd would need be 6 and 8 at the same time. That's never the case, of course.

So what do you actually need? If you need non 0 values in both fields you have to have further conditions or other conditions, which can allow that or one of the 0s in the IIFs third positions needs to be another expression. I can't tell without knowing the intention and meaning of ids 6 and 8, what you really need. So one way to get this right surely would be explaining what data you have, its meaning and what you intend to compute or select from it, i.e. what the two report fields mean.

Chriss

 

[carolx]

It is a cash flow program, inflow and outflow records. The Id for the inflow is 8 and for the outflow it is 6. The variable name is flowtypeid. The report variable is used to determine the variance at report end (inflow - outflow)

 

[GriffMG]

Start by prefixing the field names with their alias names - myTable.flowtypeid and mytable.amount
so you can be certain you are getting the right control.

Are you including a field (perhaps just temporarily) in your report so you can see the amount and flowtypeid?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

The report variable is used to determine the variance at report end (inflow - outflow)

which report variable? Are you talking about a third report variable. The two report variable expressions you have posted are only about inflow or outflow, not about the difference. And since each row is either about inflow or outfow, yes, of course the other report variable is 0. Only in a summary, ie. group footer or report footer, you could have an overall meaningful difference.

Meaningful in the sense of interesting. In each single row, where you only have one of inflow or outflow, of course the other value is 0 and the difference of 0 and that inflow or outflow is just that inflow or outflow or the negative value. Unimportant and uninteresting, ususally.

All that said, either you define a third report variable difference and set it to variable1-variabl2 or you set it to ICASE(flowtype=8,amount,flowtype=6,-amount,0). The 0 value now covers cases where the flowtype is neither inflow nor outflow and in case all records you report are either 6 or 8 would never be 0.

Well, and aside from all that, I still don't know what you want to display and why 0 is wrong in your eyes, it actually is correct even with your explanation of the meaning of the data. But you still expect something else.

Chriss

 

[Chris Miller]

One more detail aspect about this in terms of accounting or cashflow is that usually amounts are always positive and assigned to either inflow or outflow, that's just confirming what you said the flowtypeid.

Well, and since an amount is either inflow or ooutflow and never both at the same type, it's written into one of two columns, wich is just a fancy way of avoiding the minus sign and instead put it into one column. I'm trying not to ridicule this, failing a bit, surely, but that's the simple reason you always would expect one of the columns to be 0, nothing wrong about that. As a programmer, especially a database programmer, conditioned to avoid redundancies, you can easily be perplexed why it's not just one column with positive and negative values, but that's what I know is normal in the accounting and cashflow reporting.

So in the end, why do you think a 0 is wrong, or am I just assuming that?

Chriss

 

[carolx]

The variables are ok on the report form. The flowtypeid is evaluating correctly for the inflow but is outputting 0 for the outflow even when I use this formula: IIF(flowtypeid = 8, amount, IIF(flowtypeid = 6, -amount, 0)) for the variance.

 

[Chris Miller]

Aha, now we have a new case and expression.

Well, is your IIF the same as the difference of the tow IIFs? Not directly, and I'd not even look into it, because we have ICASE to cover more than 2 cases, far easier.

As I already wrote, this expression would be either amount or -amount:

ICASE(flowtype=8,amount,flowtype=6,-amount,0)

Which is assuming inflow (flowtypeid=8) should be taken positive and outflow negative.

If you want the difference, you don't need to summarize the expressions, you could simply output reportvariable2-reportvariable1 in a difference report field, or reportvariable1-reportvariable2, depends on which is about inflow and which about outflow.

Well, and one more thought, since the current two report variables are computed already, neither ICASE nor a nested IIF is better optimized as the difference of the two reportvariables. When you start over computing a result looking at the flowtypeid and deciding whether the result should be amount or -amount, you repeat what's already been done twice already, so reportvariable3 defined on the basis of the previous two reportvariables is the best way you can do this without erroring, even if you care about not repeating calculations/computations/evaluations.

But you don't even need a third reportvariable, you can set a repert field to an expression like reportvariable2-reportvariable1, too.

We didn't yet talk about what names your two report variables have. The report might mangle your data in unexpected ways, if you name them as table fields are named, so watch out what you name the reportvariables and whether that collides with data field names, too.

Chriss

 

[Chris Miller]

The other thing you might have wrong is which flowtypeid is what. And since you don't tell which IIF expression goes into what report variable name, I don't have anything to verify this and rely on what you say.

Not saying you definitely have this wrong, but if you have this wrong you'll, of course, struggle to get the right expressions.

Technically, two nested IIFs are possible and until we got ICASE were the only solution to cover three outcomes and not just two, that's right, and nesting them works, there's no technical problem regarding that.

IIF(case1, case1result, IIF(case2, case2result,0)) is the same as
ICASE(case1, case1result, case2, case2result, 0), when case1 and case2 are two logical expressions covering two cases. And ICASE can be used with even more cases, it just needs logical expressions to be checked (the cases) alternating with the result for that case, and finally a result for the "otherwise" case, where non of the previous cases was true. And that can also be applied when you're sure only one of two cases occur to be able to catch an unusual or unallowed case of data.

Chriss

 

[GriffMG]

If you use this formula, what do you get

IIF(flowtypeid = 8, amount, IIF(flowtypeid = 6, 999, 111 ))

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Good question, Griff.

Well, or could amount be 0, too, carolx? That might be a confusing case.
And then, what about .NULL.?

Chriss

 

[carolx]

IIF(flowtypeid = 8, amount, IIF(flowtypeid = 6, 999, 111 ))

flowtypeid = 6 still evaluates to 0. (flowtypeid = 8 is ok)

 

[carolx]

The problem is not crucial. They can get the variance at the end of the report by subtracting the outflow from the inflow or vice versa. I just wanted to include it on the report.

 

[Chris Miller]

The result of that nested IIF can only be 0 in case amount is 0 and flowtypeid is 8, carol.

If you put this IIF somewhere in your report you're not really answering Griffs question. You should open the report table and print the expression result from the command window.

Anyway, something is not working as expected in your report obviously, if you set some report field to that expression and get 0. Your report might look at other data than you think, for example by having a private datasession. Or one of your report variables is name flowtypeid, too, or amount, and it becomes ambiguous what the expression uses. In fact, any variable, also something defined in the code that starts the report.

Chriss

 

[Chris Miller]

Are you perhaps putting the expression you want to print into a wrong property of the report control? It's one of the few simpler explanations remaining.

I mean, it's obvious, you should put it into the Expression of the General tab of a report field properties. One more thing you can easily try is prefix it with = or surround the whole expression with paranthesis:

Either

=IIF(flowtypeid = 8, amount, IIF(flowtypeid = 6, -amount, 0))

or

(IIF(flowtypeid = 8, amount, IIF(flowtypeid = 6, -amount, 0)))

And, if you copied another field from the report and changed the expression, ensure there's nothing in other properties sabotaging the printing, like a print when condition.

Chriss

 

[GriffMG]

With that expression, if the flowtypeid <> 8 you must get either 999 or 111

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[GriffMG]

I think I would use the 'print when' and have two controls, one for typeid=8 and the other for 6
you could make them red and black then as well!!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

So close, I fear since 25 Feb 24 17:07 carolx has given up and may not see any of the further discussion.

Lots of suggestions and little feedback. And the feedback abou your IIF is clearly showing carolx put it somewhere it's not evaluated as is or there's a reason like other data or name collisions, etc.

Okay, then let's put this to rest.

Chriss

 

[carolx]

Sorry I didn't give up. I had to leave. I will look further into the suggestions and if I come up with a solution I will post the results. Thanks very much for the assistance.

 

[Chris Miller]

OK, I welcome that and you.

One more idea I had is, that you misunderstand report variable calculations.

If you want something to be evaluated - as is - per record, you better not at all use a report variable for that but put the expression to print into the expression of a report field.
Report variables are used for one of several calculation types, defaulting to "None".

Report variables are quite complicated, I don't know if you misunderstand what to put into them. If, for example, you want to build a running sum of amount or -amount into a variable, you also have to set the calculation type to Sum. Otherwise your value might just stick to the inital 0 value. That's really important to do and know, if you overlook that, you get nothing.

Chriss