Report writer and cursor 1

[Scott24x7]

Hi All,
I haven't had to use the VFP 9 report writer until now, and the last time I used the report writer was almost 15 years ago with VFP 6 or 7 (don't remember).
I see there is a lot that has changed, and it's gained in power and capability. Unfortunately, my VFP 9 docs are in storage in another country, so I can't have a read of them.
I took on a philosophy from Olaf a few years back when I started working on VFP again (and up until now, we've really only needed to view data in the application, but now we're creating outputs), not to use persistent relationships within VFP, and manage them instead with cursors (limit the data you want to just that in a cursor).
So the problem I have is, since the cursors aren't tables (in the design time sense), how can I create a report based on a cursor data, which is data that is present in a grid on a form. And this will be a "header" record from one grid, but the full detail of the second grid.
Not sure if I'm explaining this clearly enough, I'm very rusty in the report writer.
Also, if there is any report writer overview or docs that are not part of the VFP set that I can review, would appreciate pointing me to those too.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Tore Bleken]

Create a table with the same name as your cursor will have, and delete when you don't need it anymore.

 

[Olaf Doschke]

You can also make it a norm to have your code for report cursor(s) generation end in a suspend within the IDE. Then you have the cursor(s) also for report design 100% as they are when you later run the report.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Thanks Tore and Olaf. So I followed Tore's method for the moment, I am just trying to get the report to show as I need it. So I created the tables as their cursor names in the DBC (EXPENSEGRID and EXPENSEDETAILGRID).
I added them into the Data Environment on the report form, with just one header record in EXPENSEGRID and the details for that record in the EXPENSEDETAILGRID, so essentially it's the parent data in EXPENSE and the child data in EXPENSEDETAIL.

But when I run the report, I only get 1 line in the detail band. I tried adding a relationship between EXPENSEID in both tables (and tried in both directions, and added 1-to-many in both cases), but I never get more than 1 line in the detail band. That band has only fields from EXPENEDETAILGRID in it, so I'm not sure what I'm doing wrong here?
Any suggestions?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

There are two ways to tackle this:

1. SQL query and JOIN the data, you only have one report cursor with N records
2. Wenn you add the two tables a line between them shows, right? Well, right click on it, you'll see it has become a DE object, it has properties, a one-to-many property needs to be .T.
That corresponds to the command SET SKIP TO.

Going back to your first post:

not to use persistent relationships within VFP

I guess that was in the situation with forms DE and grids and things like grid.linkmaster and grid.relationaexpr contradicting relations coming from the DE and then also mixing with SET RELATION and SET SKIP and such, you can shoot yourself.

In that sense, you would use SQL, query a single report cursor and would have no such problems.

As tempting as it is to be able to drag&drop field or whole tables into a form or report band and get label+textbox or other controls, it's not what I use, also not in case of reports.

The DE also has methods aside of the visual environment, so you could use them to write code for all you want. One simple thing is, when you look into the objects representing DBFs, one property Database is hardwired. Seems ok, because a DBF you have is part of that DBC, but you often write with a local development DBC separate from production data and so later the same table will come from another DBC.

VFP doesn't give up on not finding that hardwired path and looks for DBFs along SET PATH, so it's solvable, that later this uses other DBFs, but if you test on dev PC you either rename the development data folder so it's not found and the same redirection happens. I prefer to have better control than with such quirky workarounds.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Hi Olaf,
I like the sound of a single table through a join.
If I have the EXPENSE table, and the EXPENSEDETAIL table, how do I use SELECT [JOIN] to get the data for just he current record pointer on the EXPENSE table? Join in SQL has always eluded me, and VFP help has next to nothing about it.



Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Well, the general base query is

SELECT * FROM EXPENSE ;
   LEFT JOIN EXPENSEDETAIL ON EXPENSEDETAIL.EXPENSEID=EXPENSE.ID

And for one expense:

SELECT * FROM EXPENSE ;
   LEFT JOIN EXPENSEDETAIL ON EXPENSEDETAIL.EXPENSEID=EXPENSE.ID;
   WHERE EXPENSE.ID = X

This corresponds to REPORT FOR clause becoming the WHERE clause of this.

To filter expenses in a form and the report picked expense you would have a cursor of picked expense ID and filter all data with an inner join of that:

SELECT * FROM EXPENSE ;
   LEFT JOIN EXPENSEDETAIL ON EXPENSEDETAIL.EXPENSEID = EXPENSE.ID;
   INNER JOIN PICKEDEXPENSES ON PICKEDEXPENSES.EXPENSEID = EXPENSE.ID

Of course, you can report the expenses of a single employee or whatever other where conditions.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Hi Olaf,
So getting closer, I am missing something stupid here I think.
I created the join and the cursor has everything in it, so that's great.
In the report form then the fields are made to match the cursor (which I just name EXPENSEREPORT). So the fields in the table reference things like EXPENSEREPORT.EXPENSID_A EXPENSEREPORT.EXPENSETITLE EXPENSERPORT.EXPENSEPURPOSE, etc.
But when I try to test the form in the IDE, it doesn't seem to "see" the curosor in the Data Session window, and I can't add that to the Data Environment because it's not part of the DBC... so how do I now "link" the cursor to the report?

The report writer feels like a different world to VFP... I'm feeling very amature at the moment, I just remember nothing about it.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Or to put another way, how do I get the report form to recognize that the CURSOR is the source of the data?

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

The report simply works on the active workarea, that's also true when you use the report DE, the DE has a property "InitialSelectedAlias" which determines the active workarea and that determines the report driving cursor.

That also means using expressions in report controls like table.field makes that tablöe the report driving cursor automatically. It's always the active workarea. So when you have selected a workarea differing from the one in report control expressions, well, you get the same data from the expressions as that table isn't scanned and the record pointer doesn't skip in there, just because you specify it.

There also is no report property for the report driving cursor, this is simply always just the current workarea and the way a DE can change that is with its InitialSelectedAlias.

Which also raises a problem in conjunction with running a report from a method of a grid control, when you want the driving alias to be something else than the grid.recordsource, as the grid has the "misbehavior" (by design) to activate its recordsource. Just an aside info.

You can also deduct that because of the FOR clause of the REPORT FORM command. This is a FOR clause applied to the active workarea filtering records which are printed. If you go for SQL you won't need this anymore as you filter your data by querying into the cursor you want to print all records of.

If you only put field names in the report control expressions you can also generate similar structured cursors or table or views to drive the report and as I already told to have code composing the cursor you need before running the report is what will drive it. Ypi could do all this in the report DE OpenTables method to have this encapsulated within the report, but doing it separate from the report and let the report run without a private own datasession you have the advantage of using the same pre-report data fetching to drive several similar reports, to use differing pre-report code to feed the same report with different strategies of getting that data also from differing sources, and last not least, as already said you can also run this pre-report code ending in SUSPEND before REPORT FORM and then instead do MODIFY REPORT and have that data available for testing the report in the designer with the preview.

Yes, you don't have that cursor in the DE, you can drag&drop fields into the report, but you don't do that. If you still want to do that you could also use COPY TO and save the report cursor into a DBF, put that in the DE temporarily for report design and later remove it again if you insist on that.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Hi Olaf,
Thanks for the run down, but I'm clearly still missing something.
You mention "The report simply works on the active workarea, that's also true when you use the report DE, the DE has a property "InitialSelectedAlias" which determines the active workarea and that determines the report driving cursor."

So I created the cursor EXPENSEREPORT with the code you provided earlier:

SELECT * FROM EXPENSE ;
   LEFT JOIN EXPENSEDETAIL ON EXPENSEDETAIL.EXPENSEID=EXPENSE.EXPENSEID;
   WHERE EXPENSE.EXPENSEID = 38 INTO CURSOR EXPENSEREPORT

I have the Data Session window open, and that cursor selected in the window.
I open the Report form in the IDE.
I right click on the form and open the Data Environment.
I see the "InitiallySeletedAlias" property, but I'm unable to change it.
I try to run the report by right clicking on "Preview Report".
The report immediately asks me for a table, with the open files dialog. Selecting the cursor isn't an option, though it is the "Active Work Area", and all I can do is either pick a table from a folder, or cancel. It isn't respecting the active work area as the data source. I've read through your notes above 3 times, and I just seem to be missing something (frustrating).

I tried it by launching it in the application as well as you mention, from a click event on a print button, where I perform the same select in code, and then issue:
REPORT FORM EXPENSEREPORT PREVIEW
and again, it doesn't recognize the cursor, which is the active work area as a source for the report. I must be missing something obvious but it's just not clear to me.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Then your report has private datasession, it shouldn't have one.

In report properties, look into the Data Environment tab. Is the checkbox "Report uses a private datasession" checked? Well, then uncheck that. It's not checked by default. But you might have been recommended earlier to make use of the DE, but this is not what we want to do anymore with the concept of reports driven by the caller or a pre-report routine. Reports having a private datasession obvicously are good to run separately and side effect free but to control data before REPORT FORM you will want the rpoert run in that same and not a private data session.

Also, I just mentioned InitaklSelctedAlias to illustrate what you would use, not what you should use. It does nothing you couldn't dp with SELECT alias. It's simply the crrently selected active workarea. Nothing special. This property of DE does nothing more, but it only allows you to select from the objects in the DE.

You don't need to do anything like that, as the SQL query doesn't only create the cursor EXPENSEREPORT, it also makes it the active workarea.

If you ask because you expect everything in the datasession to appear in the dataenvironment - no, that's not happening. What tables you pick and put together in the dataenvornemnt are not immediately opened, they will be opened at runtime, all you do is visually specify some USE commands.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Olaf,
Man, shoot me in the head! That was it. I felt like I must be missing something obvious somewhere, and I had looked in the Data Environment for something along those lines, but didn't find anything. I have some vague memory of "private data sessions" from years back, but it had long since faded. I set that off, and it worked like a charm against the cursor alone, which was JUST what I was hoping for. I had "kludged" it just to get it working by creating cursor result structure into a DBF, then I would call the select statement, open the table exclusively, zap it, and then copy each record in one-by-one using SCATTER/GATHER MEMVAR MEMO, and then letting it run against that, but I knew it would not be multi-user friendly in the long term. This was GREAT, and what I was expecting. Feeling so stupid for this one now, thanks heaps, and lesson learned.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."