Exporting Crystal Report to PDF programmatically
Exporting Crystal Report to PDF programmatically
(OP)
thread184-474602: export code for Crystal
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
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
CODE -->
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
Regards,
Patricia Cu
Victoria, BC
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
Regards,
Patricia Cu
Victoria, BC
RE: Exporting Crystal Report to PDF programmatically
Chriss
RE: Exporting Crystal Report to PDF programmatically
Regards,
Patricia Cu
Victoria, BC
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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:
CODE
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
In case the attachment does not show, here is the link:
https://files.engineering.com/getfile.aspx?folder=...
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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=...
RE: Exporting Crystal Report to PDF programmatically
I'll try to answer the two questions at the foot of the document:
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.
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:
CODE -->
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
RE: Exporting Crystal Report to PDF programmatically
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:
CODE -->
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
RE: Exporting Crystal Report to PDF programmatically
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:
CODE -->
Chriss
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
Chriss
RE: Exporting Crystal Report to PDF programmatically
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:
CODE -->
oRpt.RecordSelectionFormula = "{gx21d.sortfield1} = " + ALLTRIM(gx21d.sortfield1)
do this:
CODE -->
oRpt.RecordSelectionFormula = "{@section1} = " + ALLTRIM(gx21d.sortfield1)
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
RE: Exporting Crystal Report to PDF programmatically
So overall:
CODE
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: Exporting Crystal Report to PDF programmatically
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?
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
&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
RE: Exporting Crystal Report to PDF programmatically
Mike, thank you for your suggestion. Should've included the code snippet as well. Will do in the future.
Regards,
Patricia Cu
RE: Exporting Crystal Report to PDF programmatically
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:
CODE --> VFP9
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:
CODE -->
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
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
RE: Exporting Crystal Report to PDF programmatically
Link is:
https://files.engineering.com/getfile.aspx?folder=...
Regards,
Patricia Cu