Exporting Crystal Report to PDF programmatically 2

[Patricia Cu]

thread184-474602

Hello, I am curious to find out whether DanNorris2000 was able to test his suggestion of using the exportmodeller.dll and crtslv.dll files (which must be present on the computer and must be registered) in order to export a crystal report file to pdf programmatically.

Our company currently maintains a VFP9 application and one of our clients wants to be able to split the contents of an existing crystal report (CRXI) into multiple PDF files based on the grouping of the data.

Does anyone know if this is doable and could point me to where I can find out how to implement it?

Thank you for your time,
Patricia Cu
Victoria, BC

 

[Mike Lewis]

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

 

[Chris Miller]

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

 

[Chris Miller]

I see what Mike said in the thread you refeence:

loRep.SelectPrinter("PDF Driver")

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

 

[Mike Lewis]

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

 

[Patricia Cu]

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

 

[Chris Miller]

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

 

[Patricia Cu]

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

 

[Chris Miller]

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

 

[Patricia Cu]

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

 

[Chris Miller]

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

Chriss

 

[Patricia Cu]

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

 

[Mike Lewis]

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

 

[Chris Miller]

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:

* Assume original.dbf is the report data

SET SAFETY OFF
USE original.dbf EXCLUSIVE
COPY TO allgroups.DBF 
ZAP IN original

* adapt "groupfield" here:
SELECT DISTINCT groupfield FROM allgroups.DBF INTO CURSOR groups NOFILTER
SCAN
   INSERT INTO original SELECT * FROM allgroups where groupfield = groups.groupfield
   USE IN original && close it to stop exclusive access
   * command(s) to run the RPT, configured to produce a PDF file
   USE original.dbf EXCLUSIVE && get exclusive access again to be able to ZAP:
   ZAP IN original
ENDSCAN
USE IN original
SET SAFETY ON

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 [tt]FOR groupfield = groups.groupfield[/tt]. 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

 

[Patricia Cu]

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

 

[Patricia Cu]

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

https://files.engineering.com/getfi...817-b4c7-d2041d76b0c6&file=Sample_Report.docx

 

[Chris Miller]

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

 

[Chris Miller]

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

 

[Patricia Cu]

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/getfi...f67-0da72a610957&file=CR_interface_in_GX.docx

 

[Mike Lewis]

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:

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 [tt].Destination.value = '0'[/tt]. 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: [tt].DestinationType[/tt], [tt].FormatType[/tt] and [tt].DiskFileName[/tt]. 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.

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:

SELECT Data
SCAN
  oRpt.RecordSelectionFormula = "{data.Responsibility} = " + ALLTRIM(data.Responsibility)
  oRpt.ExportOptions.DiskFileName = "Management Report " + ALLTRIM(data.Responsibility)
  thisform.ExecuteReport(oRpt)
ENDSCAN

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