Filtering Detail Band in Reports

[samuelNYC]

Hi,

I'm trying to create a report that has some fields from a parent table in a group band and for each of those records, display some related records from a child table. But I only want CERTAIN records from the child table in the detail (due_dt>{07/01/2010})

I'm on a pre-packaged database built on FoxPro. I'm new to VFP and still learning, but I'm pretty sure all I have access to is the report designer, which is what I'm using.

Anyway, I'm not really sure where to put the expression to filter the detail rows. Specifying this in "Print When" for each field in the detail row gets rid of the fields, but leaves empty rows (even with "skip row if empty" checked). I suspect I should enter this either in the expression field for "target alias" or for "run expression on entry" under edit bands -> detail. But not sure.

Another user's solution was this: "I remembered there is a field for a 'scoping' clause used when there is an intermediary form for selecting a date range or something, and I slipped in the 'AND txncode="810"' and of course [the empty rows] are now gone." (

http://www.tek-tips.com/viewthread.cfm?qid=501168&page=605)

Is this what I should do? Anyway care to explain this just a little for the newbie? I really appreciate any guidance! Thanks,

Sam

 

[Mike Lewis]

Sam,

There are several approaches you could take.

My own preferred method would be to do the filtering outside the report. Basically, you would create a cursor (that is, a temporary table) that contains exactly the data you need for the report, and nothing more. You would just have a single cursor, with data from both your parent and child cursors, and which would reflect your required filter.

You then use that single cursor to drive the report.

The above is a simplified explanation, but it should point you in a possible direction to take.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

Within the VFP Report Form itself you cannot filter the detail rows.

As you have already found out, using the Print When... expression will not give you what you want.

Mike's suggestion above, in one form or another, is what most of use do - Limit the data BEFORE issuing the REPORT FORM command.

Whether it is done by creating a separate Cursor with only the desired records as Mike is suggesting (a GOOD approach) or done by setting limits (FILTER or INDEX) on the 'raw' data table(s) we then send that data to the REPORT FORM.

I'm on a pre-packaged database built on FoxPro.

That's good, but regardless of the database/data tables, how much access do you have to the code in your Application?
Can you access the code so that you can modify it?

Also since it sounds like you might be new to VFP, you might get some benefit from looking at

http://www.garfieldhudson.com/FreeVideos.aspx

Perhaps: Basic Reporting - Parts 1 & 2 and/or Using Related Tables In A Report

Good Luck,
JRB-Bldr

 

[samuelNYC]

Thank Mike and jrbbldr. Conceptually, that seems to make the most sense--prepare or stage the data if you will and feed it into the report.

I'm pretty sure I don't have access to the code. Would there be some type of shell that allows commands? Is this something that would be accessible from the report designer? Either way, I haven't seen anything like that, but maybe I'm not looking in the right place. I know in Access you just click "code" and it takes you to Visual Basic, but I don't see anything like that here (unless I'm missing something).

I'll check out those links, which look useful (Yes, I'm totally new to FoxPro!) and also do some digging in the program's documentation.

Thanks again,

Sam

 

[Olaf Doschke]

As you only can access the report designer (To Mike and jrbbldr: Maybe the foxpro application Samule uses offers usage of report modification via MODIFY REPORT PROTECTED, then you have nothing else but the report desinger).

The print when surely is the way to go, there is no code section in a report you could make use of. Lines should not remain blank, if you set all "Remove line if blank". But this only works, if really all report controls on the line are set that way. If there is something, even just a line control or a label, not set this way, the blank line remains, only really fully grphical blank lines are removed.

I fear there is no more elegant way in your position.

Actually I'd ask the vendor of that software. It's very easy to prefilter data, should be a minor change.

Of course even such changes within a software package can cost the programmer a bit of a headache, if it's done as an individual branch of the whole project, if that's not planned for the main development path, but in general it's a matter of some seconds code change and some minutes compiling.

Bye, Olaf.

 

[samuelNYC]

Thanks, Olaf. I'll give that a shot.

I did finally notice a "code" menu item, which is in the context menu if I view the data environment from a report. But it's grayed out.

I've talked to the vendor a number of times and they're not always that helpful. I might be able to beg them for a report that pre-filters data, but I will have to periodically adjust the criteria of the detail rows and I can't imagine going back to them for every iteration.

Oh well. Thanks again.

Sam

 

[JRB-Bldr]

Let's go back to your original statement...

I'm trying to create a report that has some fields from a parent table in a group band and for each of those records, display some related records from a child table. But I only want CERTAIN records from the child table in the detail (due_dt>{07/01/2010})

How were you planning on 'building' your own report and running it?

Does the vendor give you some method or 'hook' for creating your own reports?

If so, then perhaps there would be an opportunity to use that path to get the data pre-processed as needed.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Even if you don't have access to the source code, you could - in theory - write a function that prepares the cursor, then call that function from the very first expression in the title band of the report. The function will create the cursor, and the remaining objects in the report will be able to use it.

I say "in theory" because I suspect it will be difficult to do that without the co-operation of the vendor. At the very least, you will need to know the structures of the underlying tables. Given that the vendor appears to be unhelpful, it might be hard to get that information. But it's worth considering.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[samuelNYC]

Olaf: I tried the "Print When" method again and all the fields disappeared but the blank lines are still there. Everything is set with "Remove line if empty." Puzzling.

JRB: The vendor has included a custom reporting tool that allows me to create reports outside of their canned reports. From what I can tell, it includes many of the same dialog boxes that you'd see in FoxPro (since I've been able to use MS's documentation to learn quite a few things). Here's a screenshot of what that screen looks like.

When you close your report from the designer, you choose the underlying table(s) from a dialog box with a bunch of checkboxes. If you choose multiple tables, you're offered a checkbox that says "Include transactions from selected donors only," which does what it says. I can associate a query with the report, but that only will narrow the parent-level records.

I access the designer from the UI and once a report is saved, it's available from a list with a button to "modify" which takes you back into the designer.

Mike: I'm intrigued by the idea. Do you know of any resources that could give me some basic info on writing a function like that? If it seems doable and I can figure out what I'd need to ask, perhaps I can get some info from the vendor.

Thanks, all, for your input.

Sam

 

[JRB-Bldr]

The screen shot shows me the VFP Report Form Designer, but it does not show where you can assign the data to that Report Form.

A Custom Reporting Tool is supposed to allow you to define the data table/cursor that you want to use and let you 'build' those records any way you want. And, as they have provided, it should also allow you to create your VFP Report Form so that you can display the data as you want.

One 3rd party Custom Report Writer is Crystal Reports
(see tutorial video list at:

http://www.google.com/search?q=crys...roup&ct=title&resnum=1&sqi=2&ved=0CCgQqwQwAA)

You might look at that for your answers.

And I'd recommend contacting the vendor and tell them that their "custom reporting tool" is missing a BIG piece of the puzzle. It needs to also allow you to tell it what data tables to use and how they are to be used - such as allowing you to run your own query on the data table(s) and submit the results to your new Custom Report Form.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Sam,

Do you know of any resources that could give me some basic info on writing a function like that?

Basically, you need to be able to write a SQL SELECT statement. Do you know how to do that?

If so, you write a SELECT that extracts all the fields required for the report into a single result set (that is, cursor). The fields need to be obtained from the underlying tables, and the field names, within the result set, must match the names that the report expects.

To determine the structures of the underlying tables, you could open them in Visual FoxPro (if you own a copy of it), and look at the structures in the table designer.

If you don't know anything about SQL or SELECT, then I'm afraid it's going to be very difficult to explain what you need to know in a few forum posts like this. That's no reflection on yourself, of course. But your best bet might be to find someone who can sit down with you and talk you through it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Sam,

JRB-Bldr mentioned the possibility of using Crystal Reports as an alternative to your vendor's reporting tool.

I'm all in favour of Crystal Reports, and I am inclined to endorse this suggestion.

But you must be keep in mind that it is a big product, and not one that you can learn in a hurry. More importantly, you will still need to understand the structures of the underlying tables, which comes back to the amount of help you can expect from your vendors.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[samuelNYC]

Hello,

JRB: There's a way to define the tables for the form builder, but you're right that it seems to seriously limit my options--I can say which tables, but I can't DO anything with them. See the attached image for the dialog boxes. I've used Chrystal Reports before (years ago) for a similar situation with a different database. I'll revisit--thanks for the suggestion.

Also: I do plan to have a conversation with the vendor about this stuff. Maybe they could make some improvements in the next build.

Mike, thanks also for your suggestions. And no, I've never tried to learn SQL or any database language and maybe now's not the time to jump in. I just took a look at W3schools, though, and writing a select statement looks simple enough. Something like:

SELECT tbl1.field1, tbl2.field1, etc.
FROM tbl1
LEFT JOIN tbl2 // since I want tbl1 record to show up no matter what
ON tbl1.field=tbl2.field //where the fields are the keys

I can fill in the table/field names, etc. Presumably there would need to be a WHERE statement in there somewhere. I'm sure I would hit a wall, though, with the Cursor business. I see some basic instructions here:

http://msdn.microsoft.com/en-us/library/aa977464(v=vs.71).aspx

Just for kicks, let's say I COULD muddle my way through the SQL, would these statements just be inserted into the "on entry expression" field for the topmost band?

Thanks again.

Sam

 

[JRB-Bldr]

I'd think that the SQL Query statement required to do what you want would be relatively simple.

I would hit a wall, though, with the Cursor business.
Don't worry too much about the Cursor business. A Cursor is merely a memory resident data 'table' instead of one or more files in a directory. In VFP you can generally use them just like a data table.

While your VFP Help file has a better description of how to use the syntax (type: HELP select - SQL) or Google for VFP SELECT SQL to find other references, you might do something like:

* --- VFP Code ---
SELECT tbl1.field1,;
    tbl2.field1,;
    <and so on>;
  FROM tbl1, tbl2;
  WHERE tbl2.field = tbl1.field;
  AND <any other limiting criteria>;
  ORDER BY <any sequence order>;
  INTO CURSOR Results READWRITE

* --- Now Send Results To VFP Report Form For Printing ---
SELECT Results
REPORT FORM MyReport NOCONSOLE TO PRINTER
USE

"would these statements just be inserted into the "on entry expression" field for the topmost band?"
Not any SQL Query statements.

However, just for "FUN!", you could try something.
You could TRY to put a SET FILTER TO <whatever limiting criteria you want> in the Page Header On Entry Expression and in the Page Footer On Exit Expression put a SET FILTER TO. Then try to run the report to see what happens.

Personally I don't have a lot of confidence in this working - especially since I have never done it like this, but it is worth a try.

Good Luck,
JRB-Bldr

 

[samuelNYC]

Okay, I'll give the Set Filter To idea a shot. But it looks like I'm running out of real options.

On reading Mike's function idea again, I understand it a little better (I think). The select statement is what I would do if I had access to the code from the report (which I don't). I think Mike's suggesting I write a user-defined function that does that and then call it from the expression field in the form-builder (filling in whatever parameters there, I guess).

I love the idea, assuming I understand it, but knowing how to declare the function, etc. is a few steps out of reach for now. For example, even if I wrote the function, I'm not sure how I'd get it to a place where it could be called, especially without access to any kind of command prompt available. My framework for functions is completely based on php and javascript, which isn't much help here.

Anyway, thanks again. I'm guessing there's just no way of doing this, but I appreciate all the feedback nonetheless.

Sam

 

[Dan Freeman]

It sounds like you're limited to capabilities exposed by the application you're working with.

To take full advantage of almost every suggestion in this thread you'd really need to be running VFP itself. You could (probably) use the same report (and clearly the same data), but would have full flexibility.

On the other side of that coin, you'd fully have the ability to destroy that application. <s>

 

[Olaf Doschke]

I still wonder why blank lines remain. Could there be some very narro "invisible" controls? You'd see those, if CTRL+A, perhaps.

Also the design band has about double line height, how about reducing that height?

Bye, Olaf.

 

[TamarGranor]

First, I gather that the app you're using is Donor Perfect fundraising software?

I'm wondering whether the problem you're having with Remove Line If Blank is that you have some other things running through the line, such as lines or boxes?

Tamar

 

[Olaf Doschke]

Yes, Tamar.

I also said already "If there is something, even just a line control or a label, not set this way, the blank line remains, only really fully grphical blank lines are removed."

From the screenshot in Sam's attachment it doesn't look so, but the dropdown mmenu is in the way to see all of the detail band.

Bye, Olaf.

 

[TamarGranor]

I'm so glad you made me look at the figure again, Olaf. Here's what I think is happening. The line that contains the fields is getting removed, BUT the blank line below it in the detail band, of course, isn't. And that's what's giving the extra white space.

If that's it, Sam, your options are to tighten up the detail band to eliminate that white space (which you probably don't want to do), or to put a field with something invisible in that line, set so that it's blank when you need it to be. Maybe an IIF() where it puts some unprintable character when you do have data and the empty string when you don't.

Tamar