Select in report designer? 4

[Paco75]

Hi,

How do i create a SQL select in report designer? Can we do a SELECT inside a report field or label?

thanks

 

[craigber]

You select data for the entire report. Each report has a private data environment that you can use to do this.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Paco75]

yes i know there is a data environement. But can we perform a select on the tables in the data environment INSIDE the report?

 

[DSummZZZ]

You can do a SQL select in a user defined function in a report field which can return a value to that field.

Beyond that though, you have to have your data sources prepared and available before you run the report.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[craigber]

When you open the properties dialog for a report, one of the tabs is DataEnvironment. You can copy one, or use a custom DE. In that DE you can run a query.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

Paco,

The usual approach is to run the SELECT before you run the report. That way, the results of the SELECT are available to the entire report.

If that's not what you want, perhaps you could explain why you want to run the SELECT from inside the report.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

As the others have said, the usual approach is to run your queries before running the report.

You can certainly attach code to items in a report, including a query, but why? It's much easier to conceptualize and debug that sort of thing by running the reports from pre-prepared summary data that already has all the querying done.

Think of the report like a filter. You pour data in the top, and a formatted page comes out the bottom. The report can do limited calculations and sumarizations but it doesn't do any of the actual querying. You do that before you pour the data in the top.

 

[Paco75]

I got no particular reason to do a select inside the report. I didn't know we can use a select created before launching the report.

So according to what you say i can store a select into a cursor and use it in the report?

 

[Dan Freeman]

So according to what you say i can store a select into a cursor and use it in the report?

Terminology is everything here.

A "stored query" in VFP is quite literally called a Query, and is stored as an object in your DBC. It can be put into a report's DataEnvironment and will be executed automagically when needed.

A View is a similar "thing" inside the database, with the exception that it may (or may not) be able to update the underlying data, and it may (or may not) access non-Foxpro data.

CURSOR is a temporary set of records. (The word itself is actually an IBM acronym for CURrent Set Of Records). Cursors are not stored. The query that creates the cursor is what is stored (see above), and executed to create a cursor as needed.

 

[Paco75]

The system in witch i want to modify the report is a little old and uses only free tables. I added a table for the taxes. each row contains a tax rate and the begin and end period.

The Data Environment of the report already have a table containing the order data. Into that data i need the order date to retrieve the correct tax

So i must create a query with query designer and add it to the report?

 

[Mike Lewis]

No, you don't have to create a query with the query designer (although you can if you want to).

You need to create a cursor. You do that by writing a SELECT. The SELECT must combine your tax data with your order data.

I assume you know how to write the SELECT, but if you don't, then by all means use the query designer. The end product is the same. The designer is intended only to save you writing code by hand.

Either way, you create the cursor before you run the report. You will also need to remove the table from the data environment, as that will confuse the report.

Having created the cursor, make sure it is in the selected work area, then run the report as usual. The fields in the report must be based on those in the cursor.

In summary:

SELECT <some fields > FROM Orders ;
  JOIN TaxData ON .... WHERE ... ;
  INTO CURSOR MyCursor

SELECT MyCursor
REPORT FORM MyReport ....

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

You hadn't mentioned an existing system before.

OK, a query must be stored in a DBC but it can query data from free tables, no problem there. You can easily develop your query that joins the two tables outside of the report and make sure it's getting the data right before going ahead.

In THEORY, you should be able to delete the table from the DE of the report and drop in the view.

Here's where I'm betting that will break down: I'll bet every report control (field) refers to the original alias (i.e. alias.fieldname) and changing the alias will break everything. The likelihood of this being a problem increases proportionately with the number of report elements and the complexity of the grouping and summary bands. Murphy will make sure.

It's completely unnecessary to use an alias in a report but it was the default for many years so a lot of people did it.

There are various fixes ranging from hacking the FRX to smoke-and-mirrors with aliases. How much adventure are you up for? <g>

 

[Paco75]

OK thanks everyone i will go trough the select into cursor solution i think it will be the easiest way to do it.

 

[Dan Freeman]

Well, don't leave us hanging. Let us know how it goes!

 

[Paco75]

It works #1

 

[Olaf Doschke]

Now I'm a bit curious how you did it.

As you didn't started from scratch (or did you toss away the old report?) it's not that straight forward to switch from a report dataenvironment to a precreated report cursor. At least in general.

Since you mentioned it, I could better understand, why you had the idea to add to it via some SQL inside the report at all.

It surely is better to not add such complicated patchwork to reports, but redo them with the right strategy.

As a general recommendation I would pooint out report listeners, which are helper classes for a report and really just add a code section to report events, the legacy frx report doesn't have at all.

A very good and useful usage of these reportlisteners is done as report sculptor:

http://www.report-sculptor.com/

There are more third party tools like XFRX, FRX2Any and List&Label is an alternative Report Engine popular at least here in germany.

Bye, Olaf.

 

[Paco75]

I did all the selects necessary to the report and stored the result into a .dbf file on local drive. I put that table in DE of the report so i got only minor changes to do on existing report.

 

[Olaf Doschke]

OK, thanks for reporting back, Paco.

An advantage of a DBF file is, you can repeat the last report, eg after a crash, misprint, paper jam, etc., from that perspective it's a good idea.

Still I like my reports without doing any data access themselves. That surely is founded on the habit of having a data access layer in a framework. Data access shouldn't be done anywhere else inside gui or report files, but be centralizedin those data access classes.

You could also query into a cursor and remove the dbf from the report DE. Nothing needs to change, if the cursor will have the same name as the DBF.

In the end it's up to you, you can of course also have dbf as the endpoint of the data access layer prerun and as the starting point of the report.

Bye, Olaf.