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

 

[Patricia Cu]

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:

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

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

 

[Chris Miller]

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:

[highlight #FCE94F]SELECT DISTINCT sortfield1 FROM gx21d into Curosr [highlight #EF2929]Group1Values[/highlight] nofilter

* Set properties of DestinationType/FomatType once (they don't change)
oRpt.ExportOptions.DestinationType = "file" && to be determined by documentation, could be something like "file" or a number from an enumeration that corresponds to "file"
oRpt.ExportOptions.FormatType = "PDF" && to be determined by documentation, could be something like "PDF" or a number from an enumeration that corresponds to "PDF"
[/highlight]
SELECT [highlight #EF2929]Group1Values[/highlight]
SCAN
  oRpt.RecordSelectionFormula = "{gx21d.sortfield1} = " + ALLTRIM([highlight #EF2929]Group1Values[/highlight].sortfield1)
  oRpt.ExportOptions.DiskFileName = "Management Report " + ALLTRIM([highlight #EF2929]Group1Values[/highlight].sortfield1)
    thisform.ExecuteReport(oRpt)
ENDSCAN

[highlight #FCE94F][/highlight]

Chriss

 

[Patricia Cu]

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

 

[Chris Miller]

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

Chriss

 

[Mike Lewis]

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:

oRpt.RecordSelectionFormula = "[highlight #FCE94F]{gx21d.sortfield1}[/highlight] = " + ALLTRIM(gx21d.sortfield1)

do this:

oRpt.RecordSelectionFormula = "[highlight #FCE94F]{@section1}[/highlight] = " + 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

 

[Chris Miller]

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:

SELECT DISTINCT sortfield1 FROM gx21d into Curosr Group1Values nofilter && you might need to query this from Oracle instead of doing a native VFP query
* you might also get these groups from the code preparing the Oracle gx21d table. And I might still have a wrong idea about what is where...

* Set properties of DestinationType/FomatType once (they don't change)
oRpt.ExportOptions.DestinationType = "file" && to be determined by documentation, could be something like "file" or a number from an enumeration that corresponds to "file"
oRpt.ExportOptions.FormatType = "PDF" && to be determined by documentation, could be something like "PDF" or a number from an enumeration that corresponds to "PDF"

SELECT Group1Values
SCAN
  oRpt.RecordSelectionFormula = "{@section1} = " + ALLTRIM(Group1Values.sortfield1) && taking in Mike's correction
  oRpt.ExportOptions.DiskFileName = "Management Report " + ALLTRIM(Group1Values.sortfield1)
    thisform.ExecuteReport(oRpt)
ENDSCAN

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

 

[Patricia Cu]

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

 

[Mike Lewis]

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Patricia Cu]

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?

 

[Mike Lewis]

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

 

[Chris Miller]

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

 

[Patricia Cu]

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

 

[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:

   Case .Destination.value = '2' &&To File
      if !empty(nvl(this.grp1valtable,'')) && Not empty means user wants to split report. Array has already been populated.
*TODO: User interface to define directory to export to. Currently saving to same directory where the application is located
         oRpt.ExportOptions.DestinationType = 1 &&DiskFile
         oRpt.ExportOptions.FormatType = 31 &&PDF
         oRpt.ExportOptions.PDFExportAllPages = .t. &&Export all pages
         for lnindex = 1 to alen(this.grp1valarray, 1) && Array contains distinct values for the primary group on the report
            reportformula = "{@section1} = '" + alltrim(this.grp1valarray(lnindex, 1)) + "'"
            reportname = "Management Report " + alltrim(this.grp1valarray(lnindex, 1)) + ".PDF"
            [highlight #D3D7CF]oRpt.RecordSelectionFormula = reportformula[/highlight]
            [highlight #D3D7CF]oRpt.ExportOptions.DiskFileName = reportname[/highlight]
            oRpt.Export(.f.) && Export w/o prompting the user
         endfor
      else
         oRpt.Export(.t.)
      endif

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

[highlight #D3D7CF]reportname variable = "Management Report 600304.PDF"[/highlight] and
[highlight #D3D7CF]reportformula variable = "{@section1} = '600304'"[/highlight]

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:

   ...
   thisform.createTables()   
   ...
   oRpt = .crystalcontrol.OpenReport(.RepFileName.Value)
   ...
   for i=1 to oRpt.Database.Tables.Count
      oRpt.Database.Tables(i).SetLogOnInfo(ogx.odbm.DataSourceName,ogx.odbm.DataSourceName,alltrim(ogx.odbm.userID),ogx.odbm.password)
   endfor
   ...
   executeReport(oRpt)
   ...

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

 

[Chris Miller]

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

 

[Patricia Cu]

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

 

[Patricia Cu]

Here it is:

Link is:

https://files.engineering.com/getfi...e_GX_Report_with_Implementation_to_Split.docx

Regards,
Patricia Cu