Crystal XI R2 export to .csv

[former_member1007059]

I have written a Crystal XI R2 SP6 report that looks like this when previewed or printed:

Invoice #       Invoice Amount     Partner 1     Partner 1 %

Invoice #                                     Partner 2     Partner 2 %

Invoice #                                     Partner 3     Partner 3 %

Invoice #                                     Partner 4     Partner 4 %

Invoice #      Invoice Amount      Partner 1     Partner 1 %

Invoice #                                     Partner 2     Partner 2 %

etc, with a maximum of 4 partners per invoice, each on a separate detail line and suppressed if the Partner code is empty using a formula but not actually ticking the Suppress box. The incoming record is the invoice broken into separate G/L accounts, so multiple records per invoice,  and then joined to the customer table, where the partnership information comes from. I reduce this to one record selected per invoice in the same formula with if Invoice = Previous(Invoice).

When I export to a .csv file it looks like this:

Invoice#      Invoice Amount     Partner 1     Partner 1 %     Partner 2     Partner 2 %     Partner 3     Partner 3 %     Partner 4     Partner 4 % (This is all on one line although it may not appear that way here)

How can I have the .csv export look like the printed or previewed report? I do realise that I can export it as an .xls file and then save it as a .csv file using Excel, which works perfectly but is not a long term solution for a daily operation in a business.

Thanks for any help you can offer.

[former_member1007059]

Thanks Jamie,

I appreciate you taking the time and it's obvious you're more experience at SQL coding than I am. With a bit of time in the text books and some google, I've figured out the code. Since I posted this the request has become more complex, to the point where it seems going directly to .csv is not going to be possible. It's hard to believe I can go to a .txt file or an .xls file without trouble but can't get straight to the most used file format for transferring data between systems. So, it seems it's going to have to be to Excel and converted to .csv for now.

[JWiseman]

hi Dick,

you do have one more option. in the xml export format you can use a custom xsl transform and have the output as csv. this may be worth the time spent on creating your transform vs. doing the excel conversion.

here's an example you can look at:

1) go to the File menu  > Export > Manage XML Exporting Formats and press Add

2) press the link that starts with See XML Transform Samples

in that download there is an outlook csv sample that you can have a look at. see the readme.txt to see how the included customer report should be exported.

i would recommend using a good text editor like notepad++ for editing.

here are some more samples as well.

-jamie

[JWiseman]

p.s. i've done some preliminary tests with using xsl transform to csv and so far am able to add different fields to different lines of the export. this seems to be possible as every report object has a specified name when you right click > Format Field > Common tab > Object Name.

forcing fields to a new line in the xsl is done by adding the line

<xsl:text>&#xD;&#xA;</xsl:text>

between your different data sets in the xsl.

-jamie

[JWiseman]

p.p.s. sorry but i noticed that you are currently using xi r2...this is a feature in cr2008 and above.

however, if you want to try it out you can download a copy of cr 2013 as a trial from here.

if you want to attach your report to this discussion (save it as .txt instead of .rpt first) then i can have a look at it to see if the xslt works for the format you want.

apologies,

jamie

[JWiseman]

hi Dick,

not sure if Partner1, Partner2 etc are actually different fields in your db...if they are then consider writing your report off of a Command object using 4 different UNION ALLs.

as an example, in the following i've got 4 different fields that the UNION ALLs will be treating as a single field, Field1. this would be similar to what you would do with the four different partner fields.

when i create a report off of this, i only need the one details line and when i export the data to csv, everything ends up in its original column as the export doesn't move anything around.

i've used a CSTR on the amount field as well so that the following 3 lines will have a NULL for the amount.

-jamie

SELECT
`Orders`.`Order ID`,
CSTR(`Orders`.`Order Amount`) AS Amount,
`Product`.`Product Name` AS Field1
FROM   (`Orders` `Orders` INNER JOIN `Orders Detail` `Orders_Detail` ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`)
INNER JOIN `Product` `Product` ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`

UNION ALL

SELECT
`Orders`.`Order ID`,
'' AS Amount,
`Product`.`Color` AS Field1
FROM   (`Orders` `Orders` INNER JOIN `Orders Detail` `Orders_Detail` ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`)
INNER JOIN `Product` `Product` ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`

UNION ALL

SELECT
`Orders`.`Order ID`,
'' AS Amount,
`Product`.`Size` AS Field1
FROM   (`Orders` `Orders` INNER JOIN `Orders Detail` `Orders_Detail` ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`)
INNER JOIN `Product` `Product` ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`

UNION ALL

SELECT
`Orders`.`Order ID`,
'' AS Amount,
`Product`.`M/F` AS Field1
FROM   (`Orders` `Orders` INNER JOIN `Orders Detail` `Orders_Detail` ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`)
INNER JOIN `Product` `Product` ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`