Grouping with Report based on View

[k1ma2002]

Well, I have been given the wonderful task of creating a simple report generator using Visual FoxPro 7.0. The endusers who will be using this are very unsophisticated so we tried to make everything as easy to use as possible (we created all of the views (remote, based on SQL Server database) for them, did all of the required linking, created test reports for them, created selection criteria generator for them, etc). The problem we are having now is whenever a report has gruping put on it it doesn't work the way I thought grouping was supposed to work. For example
given grouping on id:

id childid
1 1
1 2
2 1
1 3

I would have 3 "groupings" -- 1 for the first 2 ids of 1, 1 for the id of 2, and another group for the last id of 1. I want the grouping to have been only 2 groups -- all of the 1's in one and the 2 in the other

I think this is because of an ordering problem, but I'm not sure how to fix it. I thought of trying to have code in the report (or its dataenvironment) to detect what groupings are on the report and modify the view to order by it but I don't know how to do this either. Any help at all would be greatly appreciated.

Thanks,
Kevin

 

[rgbean]

To get what you "want", just create a temporary index in ID+childID (assuming they are character fields). Note: All views can ONLY have temporary indexes, and they must be recreated after each refresh(). The alternative would be to ORDER the view by these two fields in the first place - as part of the actual view definition.

Rick

 

[k1ma2002]

The index is already in place, sorry I forgot one or two more details. Since all of the views are already made by us in design time, we did all of the linking in the sql statement/remote view itself. The next thing -- ordering in the actual view definition -- isn't something we can anticipate. This is meant to be a report generator. They can take the reports that we gave them and modify them (including changing the grouping) as they see fit. I need to find a way to detect what they changed the grouping to and place an order by clause in the view definition. Of course this is the only way I can think of solving this problem -- there may be better ways out there.

Kevin

 

[rgbean]

Altering Views on the fly is not easy, and perhaps not even a good idea. You may want to consider generating a local DBC temporarily and create a new view for each required situation, or use the temporary index idea.

If the users are smart enough to change the groupings in the report, then just ask them to provide that information in the form that actually fires up their report, and create the temp index on the view using that information.

Rick

 

[k1ma2002]

that could be one alternative, but is there any way to find out what groupings are located in a report?

Kevin

 

[rgbean]

Kevin,
Yes, reports are just tables (.FRX -> .DBF and .FRT -> FPT), you can open them and dig out whatever info you need. The data in them is documented somewhat in the help file, and externally in the files in c:\Program Files\microsoft visual foxpro 7\Tools\Filespec\ (7.0) or C:\Program Files\Microsoft Visual Studio\Vfp98\Filespec\ (6.0).

Rick