Patricia,

Yes, this is perfectly do-able.

Does the application already do any automation of Crystal Reports? And, if so, what method does it use for that? Does it use the Report Design Component (RDC)? Or calls to the report API? Or some other method?

It will be easier to help you with this if I know what you already know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

I see Mike has Crystal report experience, I don't know much more than it being a report engine that's often used in MSSQL instead of its own reporting.

And from that perspective I can only weigh in, that if you have the reports as you want them, you can print them using a printer driver that creates a PDF file instead of actually printing and you'd be done with creating PDFs. Unless you also have a need for modifying reports and take them apart, that would be all you need.

There are so many pdf printers, that I even won't recmmend which one to use, with VFP Bullzip PDF has some popularity, as it has a COM interace to make settings, but that's not a USP for using it with Crystal Reports.

Chriss

I see what Mike said in the thread you refeence:

So even back then it was an easy option.

Also the thredad mentions Crstal Reports supported PDF as output format without any third party add on.
So I'd not at all focus on DanNorris2000 mention of two DLLs.

Maybe you already solved this, it would be nice if you tell what solved it for you.

Chriss

Chris, Crystal Reports does indeed support PDFs as an explicit export option. Although it also allows you to print to a PDF driver, it's preferable to export rather than print when automating the process. With printing, the onus in on the user to install and print to the correct driver, which isn't the case with export.

To export to PDF, you first have to set various properties of the ExportOptions object, and then call the Report object's Export method. I haven't yet posted any code for this, because I don't know if Patricia is familiar with the RDC object model, or if they are in fact using the RDC at her client's site.

I see she also wants to split the report into smaller reports based on the grouping. That's also possible, but it needs a still deeper knowledge of the RDC.

Patricia, if you could come back to us to answer these questions, that would be helpful.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Thank you Mike Lewis and Chris Miller for your time. Apologies that notifications for my post did not happen. The VFP application we have was originally implemented in Fox Pro - DOS, then it evolved to vfp5 and finally vfp9. The crystal reports interface evolved as well until our current CR XI version.

I am not very familiar with the RDC paradigm, only ONE of our reports uses it. All the others are fixed reports populated with database data stored in temporary tables. The following 'readme' method of our custom gxreport class explains better how the Crystal Object has been implemented in our application. (It mentions vfp5 and cr8, because the only code changes since then, were to allow the reports to continue working with the latest versions):

We have been asked to look into the possibility to run one CR report that would produce multiple PDF documents separated by their main grouping. E.g. running a report that currently produces a listing with 10 groups, would now produce 10 PDF documents, one for each group.

Any information is greatly appreciated!
Regards,
Patricia Cu
Victoria, BC

• https://files.engineering.com/getfile.aspx?folder=bf0475fa-f43a-4a22-be3e-d

Without reading the documentation. It will be much simpler to run the report you have with just one group of data and therefore run the report 10 times to produce 10 PDFs. mainly because it's still the Windows OS print job queue and I know no report engine that can split it's own single print job into 10 jobs, that's not what a report engine does, that's hat the application printing has to do, split up data for 10 report runs and do them.

Mike knows better about CR capabilites, but the DLLs you mentioned in the old thread are also there to make one print job go to a PDF file. There are limits of what's possible within a report engine, and the job a report is is given by the application starting the print job and directly by Windows. One job, one file. If you thik along the lines that multiple output trays used by one print job demonstrate you could also create multiple files easily by switching something within a report, that's illusionary. The mechanism multiple output trays are usually used is for multiple copies, perhaps also creating batches for book binding, but that's purely printer device logic, not even report definition logic.

So if a report you have prints too many data in one batch, the solution should be to feed it just one group at a time. In the worst case you have to first create a databaase for each report run and feed in just one group of data to print, then empty it and feed in the next group of data. That will need the minimum change in the report about which database and tables it uses for printing, and you have that under control before printing, easily.

Chriss

Thanks Chris, the current implementation leaves the grouping to the Cristal Report File. I.e., the 'criteria' for the grouping is defined in the .RPT file.

Regards,
Patricia Cu
Victoria, BC

No problem, in general then the table with the 10 groups is a head table and all other data has details.

But you can split up data so you only have 1 record in the head table, thus only print one group, the report does not need to change, it will just have one group, and for the next report rn, you only put the next group head record into that table, etc.

In VFP you could use the FOR clause for that, I doubt it works with CR. But you can steer it simply by controlling what data the report sees.

In short: Just because a report has a grouping defined does not mean there have to be more than one group for the report to work.

Chriss

We do not have the 'head' table / 'detail' tables structure for the reports. All the report contents is contained in one final temporary table (which could have been generated after processing multiple previous temporary tables)

Regards,
Patricia Cu
Victoria, BC

Even simpler in that case. Just generate te data for one group and then repeat that.

Chriss

Thank Chris, I think it will be easier for me to map your suggestions to our CR implementation with an specific example of our reports. Let me prepare one that I can post to discuss further .

Regards,
Patricia Cu
Victoria, BC

Patricia, thanks for coming back with the information about your set-up. It should be possible to achieve your goal using the RDC, but I note that you do not have very much experience of it which might make it difficult for us to talk you through the steps.

Before going any further, it occurs to me that there are a few third-party utilities available that could solve this problem. Some years ago I used a product called Visual Cut from Millet Software (milletsoftware.com). I don't recall the details, but I do remember using it to split reports according to the grouping; it can also handle the export. It can work through a command-line interface, which should make it possible to automate from within VFP. It's not free, but it could pay for itself in the time it saves you.

I have no recent experience of the product, so can't be sure it will be suitable but it would be worth checking to see if there an evaluation version available.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Okay Patricia,

I look forward to it.

Mainly my idea is, you have data for the fll report (all 10 groups, or lets say X) in one DBF table.
Then I think there will be one field in that DBF that defines the grouping, i.e there is a department field that has the department name, and all records with the same name are one group.

Well, then use the code up to the point you have that DBF file, copy it into allgroups.DBF and ZAP the original DBF to only add back one of all the groups for each report run.
You do that by first determining the groups, iterate over them, add data of one group to the report DBF in each iteration, run the report and be done...

As illustrated here:

Now you ran the RPT X times each with the original.dbf only containing data of one group. No need to split the RPT or do anything to it at all. It's the equivalent of running a FoxPro report FOR groupfield = groups.groupfield. Of course, you could also modify the code that produces the temp report table (original.dbf or whatever it is really called) to produce one DBF per group instead. Whatever suits you best. But with that idea you just split up the data and not the report and are done without changing the report.

There's only one catch I see, a report with groups could have a summary at its end that would now be printed X times and not summarize all groups, as each report run only has data of one group. But then I also don't see how splitting up the RPT could work. In such a case you'd be best off with printing that one report as is to a PDF and look for a tool that can split a PDF into a) all the groups and b) the summary at the end of the report.

I can't think of a more complicated situation that would need much more than that, there could be some records at the start and end you need for all report runs, that's adaptable, the general idea is just prepare the data for one group and run the RPT, repeat until you have all PDFs you need. There's no need to split up the RPT or have any definition or code in it to split it up from inside, you just split up the data outside before running it. But I wait and see your example.

Chriss

Thank you Chris Miller and Mike Lewis for your input. Mike, I will explore your suggestion as well.

Here is one of the reports that would need to be split into separate PDFs based on Group 1. I'm attaching the image files as well b/c the inserts look a bit blurry...

The first page (with colored sections) contains data from the final temporary table, which already processed data from other temporary tables


If user chooses to display it, there can be sub-report sections (based on additional temporary tables) at the end of each 'Responsibility' section


The RPT design is fairly complicated with several groups that may show or not based on user selections


Looking forward to your comments

Thanks,
Patricia Cu

Report Sample File
In case the attachment does not show, here is the link:

https://files.engineering.com/getfile.aspx?folder=...

• https://files.engineering.com/getfile.aspx?folder=419feaa5-5591-4817-b4c7-d

You have nested groups there, I see. That's not normal. And how should that be split up?
I was thinking of the usual case of sequential groups, not nesting.

So where actually do you need to split up the report? It's totally unclear to me how this should be split up. In the end it becomes more probable to methe simplest solution is to split up the result file and not the report.

Chriss

Let's talk about the possibilites in a simpler to understand case of nested groups.

If you have a report about addresses that starts on the level of countries, then regions (like states of counties), then cities, then streets, and then addresses (on the level of house numbers), then the only natural split is at countries.

And then this still is possible without manipulating the report at all, it can stay as is, you just feed data of one country, with all the subgroups belonging to that country.

And then you truncate (delete) all that data and refill it with the data of the next country and print that, and so on.

Chriss

Ok. I had to take a closer look at our Crystal Reports implementation to better understand it myself. I have documented it with screenshots and a bit of code. Please take a look and let me know your comments.

I should've done this sooner instead of making vague questions, apologies.

I appreciate your feedback

Regards,
Patricia Cu

Link: https://files.engineering.com/getfile.aspx?folder=...

• https://files.engineering.com/getfile.aspx?folder=66921fd3-e7b2-45a1-bf67-0

Patricia, the document you uploaded is much more helpful. I now understand better what you are trying to achieve, and more importantly what you already know.

I'll try to answer the two questions at the foot of the document:

Quote:

How to trigger the Export to PDF option w/o displaying the interface

The interface is being displayed in your ExecuteReport method in the branch of the DO CASE for .Destination.value = '0'. To refrain from displaying the report, you need to bypass that bit of code. It's not completely clear to me how you do that, but presumably you need to set that value somewhere to '2' rather than '0'.

To export the report to a PDF, you will need to set certain properties of the report's ExportOptions object, specifically: .DestinationType, .FormatType and .DiskFileName. You are presumably already doing that somewhere, but it is not clear to me where you are doing it and what values you are using.

Quote:

How to split the report contents based on group 1

To do this, you need to export the report multiple times - in a loop - once for each of the values of the Responsibility field. In each case, you set the record selection criterion to one of those values.

To demonstrate, I will assume you have a table called Data that contains a field called Responsibility:


This will export a series of reports, each with the filename "Management Report" followed by the Responsibility to which it applies.

Please note that the above is NOT meant to be working code. I can't see the rest of your code, so I have now way of testing it or of knowing how well it will work. I hope it will give you a general idea, but you will need to understand your existing code to get it to work.

I hope this all makes some sense. Come back if anything needs clarifying.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Hi Mike, thank you very much for your feedback.

There is an option to select Destination 'File' in our vfp CR interface, which currently can only be selected manually by the user. When 'File' is selected and user clicks the 'Print' button, the following line of code happens:

oRpt.Export(.t.)

this line displays the native CR export window (with whatever default values CR sets) where the user selects the Format and Destination, and clicks OK. The current implementation is not setting the properties you mention (.DestinationType, .FormatType, .DiskFileName) but I will investigate what values are available for them and set them programmatically to test.

Your demonstration on how to split the report looks promising. The temporary data table (called gx21d) has a sortfield1 field that is used as criteria for the .rpt Group #1. There is already a formula in the .rpt with the value of the gx21d.sortfield1 field, called @section1 (used as criteria for the Group #1). If I understand correctly your code sample:


If the criteria for the Group#1 in the .rpt file uses formula @section1, would I need to use it in the code above or it doesn't matter?
I will also do some tests with this idea. Thanks!

Regards,
Patricia Cu
Victoria BC

Does that mean you have a gx21d.dbf table? And the group#1 field is gx21d.sortfield1? Well, that could be applied to my code, too.
But I understand you'd rather use the CR feature "RecordSelectionFormula" to extract the data of one specific group.

I would suggest one more change, because you only want to run the report for distinct values of sortfield1, so you'd first do:



Chriss

Hi Chris, thank you for your feedback.

The temporary database tables that are being generated for a Crystal Report in our application, are done in an Oracle database. The report is connecting directly to the database, and loading the table that was created prior to calling thisform.ExecuteReport(oRpt) method.

Your code sample makes sense... I wonder what would be generated if I did not selected the distinct sortfield1 cursor. Maybe I will test both cases.

Thanks!

Regards,
Patricia Cu
Victoria BC

Well, my guess is you repeatedly create the same reports, as many times as a value of sortfield1 occurs.

Chriss

Patricia,

Given that the grouping criterion is @section1, then that is what you should use in the record selection value. In other words, instead of this:


do this:


Chris's approach is quite different from mine, although equally valid. He is suggesting that you do the record selection at the SQL level, whereas I am doing it within the report. My method would probably require fewer changes to the existing code. On the other hand, keep in mind that my solution might need some tweaking because I am not familiar with the rest of the code.


EDIT. Supplementary information: In case it isn't obvious, the curly brackets in the above code are used to delimit a database field (which is always in the format table.field). The @-sign indicates the name of a formula (which is a bit like a function in VFP).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

I'd say with my wayy of finding the groups outside of the report run it would still not be completely correct, as you don't loop gx21d outside of the report, but only the DISTINCT groups. Otherwise you get a lot of repetitive reports as you iterate each group multiple times.

So overall:

You have things here, that are setting report properrties, but you still do this from outside before the report is executed. Since my solution determines the distinct groups into another cursor "GroupValues", the right side values must be taken from that. I think Mike still makes an error on this side, since I think gx21d has all data with repetetive group values, and you don't iterate that from the VFP report conrolling loop, the report does iterate that data inside, but group by group. So all in all my straight forward approach to split data outside instead of splitting up the report into multiple reports still holds true, it's just that Mike knew you can let a Crystal report do what VFP does witha FOR clause by setting the report object RecordSelectionFormula formula.

Chriss

Hello Chris and Mike,
Just got back from vacation. Merry Xmas and Happy New Year to both.

Prior to leaving, I did manage to run a quick test to silently export our 21D crystal report to a PDF format and save it to a local directory, with success!
I still have not tested splitting the report, but I will as soon as I tackle other things that I got assigned. Will keep you posted and once again, thank you for your help, it is greatly appreciated!

Best regards,
Patricia Cu

Thanks for the update, Patricia. Good to hear that you are making progress.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Hello again!
While implementing my test to split a crystal report, I am getting the following error, and I don't know why. Would anybody have an idea of why this is not allowed?

Patricia,

Please copy and paste the actual coode into a forum post. I don't know about anyone else, but I find it very hard to read the code in your screen shot.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

You can't use a property in macrosubstitution.

&this.property doess not work, but you don't need macro substitution here, because the property IS the array, isn't it? It's not containing the name of an array.
You could also create that array without using dimension and COPY TO at all, but right after the execsql created T_distinct you can do SELECT * FROM T_distinct INTO array ... to create the array.

And if you only need part of the fields, please in the first place just select those fields you need in the SQL select query you send over with the execsql method. You're wasting network usage by reading in data you then throw away anyway.

Chriss

Chris, thank you for your feedback. Will try your suggestion.
Mike, thank you for your suggestion. Should've included the code snippet as well. Will do in the future.

Regards,
Patricia Cu

Hello again.
I have made some progress regarding my test to split an existing Crystal Report file into multiple PDF files.

As I had previously stated, our application runs the Crystal Reports in several steps:

1) User opens the VFP form for a specific report (to select filters and then click the 'Report' button).

2) The 'Report' button will process the filters, build the SQL for the temporary DB tables, instantiate the General Report Screen (which is the interface where the user prints the report after adding a title, subtitle, and output option). Here in the 'Report' button logic, I added a step where if the user wants to split this report, I add an extra SQL for a temporary DB table that will contain the distinct values of the main table group1.

3) On the General Report Screen, the user will click on the 'Print' button, which will in effect create the temporary tables in the DB, instantiate the CrystalRuntime.Application and configure it prior to sending to selected output

4) At the time of creating the tables, I added a step where I identify that this report will be split, retrieve the distinct group values from the extra temporary table from step 2, and dump those values into a new property array.

5) In the executeReport() method, I loop through the property array values and configure additional CrystalRuntime.Application properties:


The good news is that for my test report, where there are 14 distinct group1 values, I manage to generate 14 PDF files where the file name clearly displays each of the array values.

The bad news is that only the PDF file for the first value on the array has actual content. The remaining 13 PDF files are empty and only display the report headers/titles.

I have verified that while processing the array values, both the reportname and the reportformula variables (seen in the code snippet above) contain the expected values each time, i.e., for index = 14 (last value in the array) the

reportname variable = "Management Report 600304.PDF" and
reportformula variable = "{@section1} = '600304'"

so I am not sure why only the first value in the array is the one that generates a PDF with actual content, but the rest don't.

Prior to getting to the executereport() method described above, the following has been done:


oRpt is not reset until after executeReport is done, so its DB connection and the DB tables should be available the whole time the splitting based on the new array is happening..

Any ideas/suggestions are welcome!

Regards,
Patricia Cu

With what you describe it seems setting reportformula variable {@section1} is not enough to let the report get the data for a section. It will require to know the report in more detail to see how to make it report the section of data it should.

As your first report is just its section I assume it's possible you could just change the nesting level and run the table creation and report for the 14 sections instead of iterating the 14 sections within one report run, even though the aspect of getting 14 PDF files already works.

It was my initial idea anyway, to actually NOT split the report but just feed it with data about one group repeatedly.

The change of {@section1} obvioulsy has an effect, you don't repeatedly print the first section only, but the other 13 sections come out empty. What's in the temp tables? Are you putting in the data of a single section only? If so then you mixed up my and Mikes ideas and ended up getting this.

Sorry, but from what you show I don't get the overview I need. Especially the last code section you post tells me that there is NO iteration of sections between report initialization and printing it. All you do between OpenReport and executeReport is setting the logon info, which I think is just a technical necessety for Crystol to get access to the data. I would assume that you would need to do several executeReports, one per section. But Mike is more versed with how to handle Crystal reports.

Chriss

Hi Chris,

I am a visual person and it is very hard to include images within a post here. I will expand on the initial word document I attached a while back, to include the implementation to split the report. I will include screenshots to make what I am trying to do easier to understand.

Regards,
Patricia Cu

Here it is:

Link is:
https://files.engineering.com/getfile.aspx?folder=...

Regards,
Patricia Cu