×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Report Variables not Evaluating
2

Report Variables not Evaluating

Report Variables not Evaluating

(OP)
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?

RE: Report Variables not Evaluating

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

RE: Report Variables not Evaluating

(OP)
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)

RE: Report Variables not Evaluating

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 Smileing

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

RE: Report Variables not Evaluating

Quote:

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

RE: Report Variables not Evaluating

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

RE: Report Variables not Evaluating

(OP)
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.

RE: Report Variables not Evaluating

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:

CODE

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

RE: Report Variables not Evaluating

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

RE: Report Variables not Evaluating

If you use this formula, what do you get

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

Regards

Griff
Keep Smileing

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

RE: Report Variables not Evaluating

Good question, Griff.

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

Chriss

RE: Report Variables not Evaluating

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

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

RE: Report Variables not Evaluating

(OP)
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.

RE: Report Variables not Evaluating

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

RE: Report Variables not Evaluating

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

CODE

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

CODE

(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

RE: Report Variables not Evaluating

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

Regards

Griff
Keep Smileing

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

RE: Report Variables not Evaluating

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 Smileing

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

RE: Report Variables not Evaluating

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

RE: Report Variables not Evaluating

(OP)
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.

RE: Report Variables not Evaluating

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

RE: Report Variables not Evaluating

Hi Carol,

Report Variables are acting on the report level.

You you want the fields to be evaluated you'll have to put the variable expression into the field expression (see attachment)



hth

MarK

RE: Report Variables not Evaluating

Still not sure whether we're getting there, but I'd look into the two report variables you already have, especially their calculation type. If they are set to "Sum", your report detail band (or a group band) will have report fields that use the report variables as their expression to print. Well, then you can add a third report field with inflowreportvariablename-oputflowreportvariablename as its expression and don't need a third report variable at all.

That idea I proposed already, too, but maybe it becomes more apprent after you looked into the details of your two already existing report variables.

Chriss

RE: Report Variables not Evaluating

Hi Carol,

Some years ago I wrote a little demo to show how to use report variables, variable field expressions and the system variable _VFP.SetVar() - it pops up when DBLClicking on the group footer 1 and group footer 2.

https://res.cloudinary.com/engineering-com/raw/upload/v1708940229/tips/planning_aixqj0.7z

If you work a lot with reports, you may want to have a look at "The Visual FoxPro Report Writer: Pushing it to the Limit and Beyond Paperback – January 1, 2002"

hth

MarK

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close