Report not showing selected records 2

[SitesMasstec]

Hello colleagues!

I am trying to display in a report only records which have in the PSTAT field the content "PENDENTE". This field lenght is set to 10 characters.

The variable QualStatus is defined during run time, and the user typed "Pendente" to be stored in it.

The code is:

SELECT * FROM PLANPROD WHERE ALLTRIM(UPPER(PSTAT))=ALLTRIM(UPPER(QualStatus)) INTO CURSOR curPedProd

SELECT curPedProd

REPORT FORM RELACAOPEDPROD TO PRINTER PROMPT PREVIEW

But the report shows all records (not only the records which has "PENDENTE" in the PSTAT field!

What am I missing?

Thank you.

 

[Hebert89]

tuff code

 

[Tore Bleken]

Are you absolutely sure that the cursor is the data source for the report? If so, try to add Nofilter to the select statement.

 

[ggreen61]

Try for your select statement:

SELECT * FROM PLANPROD WHERE ALLTRIM(UPPER(PSTAT))=="PENDENTE" INTO CURSOR curPedProd

 

[Joe Crescenzi]

A few things come to mind.

I'm not sure why you would get ALL the records, but when you compare 2 strings in VFP, especially something that has been trimmed, the default behavior is to only compare up to the length of the string being compared, so you will get a lot more matches.

For example, if:

X = "HELLO"
Y = "HELLO WORLD"
Z = ""

You will get interesting results:

Y = X returns TRUE, because it only compares the first 5 letters of X.
X = Y returns FALSE, because it looks at all the letters in Y.
X = Z returns TRUE, because it doesn't compare ANY part of Z.
Z = X returns FALSE, because it tries to see if all the letters are in Z (the empty string).

However, if you use == it tells the system to check for an EXACT match (the same as setting SET EXACT ON).

The point here is that if you use ALLTRIM() on both side, it **** TYPO FIXED *** will NOT be an exact match, especially if both sides have different trimmed values.

Another option is to do the reverse, instead of ALLTRIM(), you could consider PADR(QualStatus,10), and it will look for the whole thing.

 

[Chriss Miller]

I bet it's the difference between a report that uses a private datasession vs one which doesn't.

If you want to prepare the data to print before you do the REPORT FORM call, like you do, your report should not use a private datasession, as a report with a private datasession will only use the data from its own (therfore called private) datasession and not what data you prepare in workareas, cursors, whatever - before your report call.

If the report has the table to print in its dataenvironment, it prints the full table. It doesn't matter whether you run a query that only fetches the data you want to print or not, it doesn't matter at all what you do datawise before the report run at all in whatever is the current datasession, the report get its data from its own new private datasession, it opens the tables you put into the report data environment and if you instead want control from outside, then keep the report at the default regarding its data environment and let it NOT use a private datasession, don't put anything in it's data environment and prepare the report data right before running the report, as you do.

 

[Premal Vala]

Hello colleagues!

I am trying to display in a report only records which have in the PSTAT field the content "PENDENTE". This field lenght is set to 10 characters.

The variable QualStatus is defined during run time, and the user typed "Pendente" to be stored in it.

The code is:

SELECT * FROM PLANPROD WHERE ALLTRIM(UPPER(PSTAT))=ALLTRIM(UPPER(QualStatus)) INTO CURSOR curPedProd

SELECT curPedProd

REPORT FORM RELACAOPEDPROD TO PRINTER PROMPT PREVIEW

But the report shows all records (not only the records which has "PENDENTE" in the PSTAT field!

What am I missing?

Thank you.

Why don't you use the simpler option by using FOR clause with REPORT command?

so, instead of using select query, simply use the table and run report command with for clause as below:

SELECT PLANPROD

REPORT FORM RELACAOPEDPROD FOR ALLTRIM(UPPER(PSTAT))==ALLTRIM(UPPER(QualStatus)) TO PRINTER PROMPT PREVIEW

 

[SitesMasstec]

The use of '==' presented by Greg worked fine. Now the report works as intended!

Also, the lesson presented by Joe is very didactic. I will print it and let it on my desk to refer to it when using comparisions between data.

Tore said:

Are you absolutely sure that the cursor is the data source for the report?

Well, the command SELECT curPedProd before REPORT FORM... is not for the purpose of using the Cursor curPedProd for that report?

SELECT curPedProd
REPORT FORM RELACAOPEDPROD TO PRINTER PROMPT PREVIEW

Chriss: I used to have (some programs I developed have yet) a temporary table in the Data Enviroment to print a report. But as you my colleagues have advised me long before, I have been using cursors instead, whenever is possible.
Also, I have never defined 'private datasession' in any program. I understood, I must not use it for cursors and temporary tables. Correct?

 

[Joe Crescenzi]

Also, the lesson presented by Joe is very didactic. I will print it and let it on my desk to refer to it when using comparisions between data.

Another interesting thing is that this odd set of rules is not just for fields and variables, so unless you have exact on or use ==, the order of your comparisons will have a direct impact on the result, so any shorter strings on the right side will only compare that number of characters on the left.

Examples:

"HELLO WORLD" = "HELLO" returns TRUE, because it only compares the first 5 letters on the right side.
"HELLO" = "HELLO WORLD" returns FALSE, because it looks at all the letters in "HELLO WORLD".
"HELLO" = "" returns TRUE, because it doesn't compare ANYTHING.
"" = "HELLO" returns FALSE, because the word is NOT in the empty string.

So, a query with MyField = "B" will return every record where MyField starts with B, but where MyField == "B" will only return records that are exactly B.

As I said, an alternative to == is to NOT trim your values, but PAD them to the full length of the field.

 

[Chriss Miller]

I assume your like means your report now works, after you unchecked "Report uses a private datasession".

You still don't get what private datasession means, it seeems to me.

While private datasessions are very nice for forms, as you (or your users) not rarely run several forms in prallel, which then all have their own private datasession not influencing each other apart from still sharing the underlying data stored in the DBFs, but they all have their own workareas, their own record pointer and current record, for example, even using the same DBF, so you can also do things like running the same form multiple times, each positioning on another ID or record number and displaying that.

But reports? The same advantage applies, if you (or your users) would run several reports in parallel, they wouldn't influence and perhaps interfere in a bad way with each other. But what is typical for reports? You run one, you preview and print it, then you may run another, maybe the same report, but filtered differently, maybe a completely different report. How many times in your life did you have the need to run multiple reports in parallel? I don't mean batch print jobs, they're typicallly done in one report run, I mean two different reports. Do you?

If you answer no, like I'd do, you know the advantage of private datasessions for forms becomes a disadvantage for reports, in the normal case. Private datasessions indeed have the same advantage of fully encapsulating what the report prints within the report, within its FRX/FRT file pair and just DO REPORT FORM will do the report, not depending on any preparation, as all is inside it. But to use that advantage code like you do for preparation would need to be within the report. It's possible, but you can't easily use it, as reports still are not classes and objects as forms are. You can for example use parameterized views in the report data environment that filter the data as necessary, but you don't have the WITH parameter clause as you have with DO FORM, you can only define view parameter variables that are seen by the report by being scoped public or private, you can't pass something into a report with a WITH parameters clause. That's the major difference. It's not unusual to use predefined variables visible in a larger scope than just locally. You're in the same situation with forms when you would want to use something as a parameter in a form load event or a form data environment that's not having access to the forms init parameters, you can always see variables scoped private or public.

But generally the easiest way to use a report is with a default datasession, the easiest way to use a form is with a private datasession. And in general, for both forms and reports, the inverse can also be useful, it always depends on the context.

I write this because I have the impression you learn private datasesion is good so apply it everywhere. As you see the opposite can be true as is in use cases of forms vs reports. It's logical you come here, if you have a problem, not if you would have made the right choice. But this is not the first time it turns out the private vs default nature of a datasession is all that needs to change for something to work vs not to work. It seems you're not getting anything you're teached, that's what's so disappointing.

 

[SitesMasstec]

Wow, another great lesson from Chriss!

Yes, Chris, all my reports are unchecked for 'Report uses a private data session'. In one computer I can only run one report (or other program function) at a time. The application doesn't allow to run the same program at the same time in the same computer.

Of course I run a report, and after it finished, I may run it again to filter some data, etc.

I will make a test, using two computers running the same report (in a network server), to feel its behavior with private data session on and off.

Thank you!

 

[GTGeek88]

Mentioned, perhaps indirectly . . . do the fields in the report have an alias in front of them for their data source? They shouldn't, generally.

Also mentioned is how the comparison works. An alternative is this:

FLen = len(<TableName.FieldName>)

for upper(<TableName.FieldName>) = upper(padr(alltrim(<VariableName>), FLen))

 

[Chriss Miller]

I will make a test, using two computers running the same report (in a network server), to feel its behavior with private data session on and off.

You don't need to test this. Any preparation of data can only be seen by a report that does NOT have a private datasession. So if that's already the case but the reort still does not give you what you expect, then the thing to do is select the correct workarea before REPORT FORM. You don't test with private datasession on/off, you already know what will and what won't work.