Cross tab excel export 1

[northw]

Hi All,

I have cross tab report, which is exporting perfectly to excel and excel data only, the only issues I have are, some of the number fields are converted to string some of them are exporting as number, we have to select them and convert in order to do any calculation in the excel, how can this be changed?
and one other issue is that the row headers appear at the bottom of the group which is confusing, how can i change it to appear on the first line of the group? Please suggest.

Thanks in advance!

 

[SkipVought]

hi,

Examples please. What is it in CR? How has it changed in Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MCuthill]

northw,

I have had the same issue with numbers converting to a string. In my experience, this has happened when the "number" is part of the Row Headers (the summary fields have stayed numeric). I have not found a way to correct the groups appearing at bottom -- that seems to simply be the way Crystal Exports. In some cases, I have reconfigured the crosstab to a manner where "all" levels of the row are concatenated to one string. It doesn't look as "clean" (as all Row Titles are on each row of the cross tab), but it does help in identifying each row at a glance.

My apologies that I lack a solution, happens to be you asked about something I actually had to deal with last month (I have a number that "has to" appear in the row headings, and I can't sum it once exported).

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[northw]

Thanks for the reply Skip!

1. It is a database field in number format, I just removed the "." and zeros and parantheses for negetive value.
2. In the report I have used Purchases class in the rows and there can be multiple purchases, so it shows purchases and all the related details, so when I export it to excel, I get purchases label in the bottom row of the group (all purchases.)

Thanks!

 

[northw]

Thanks for the reply Mike!

Could you please tell me the work aroud you configured for the grouping issue?

Thanks!

 

[SkipVought]

secondly, I'd have drawn and quartered, anyone who would export reports to Excel for post-processing. What a sentence to pass on the poor soul who must endure such abuse, and that might even be self-abuse!

Please consider exporting a proper TABLE, from which Excel can be properly employed to do all sorts of wonderous and amazing analysis and reporting, as it is designed to do.

Furthermore, export your data as a .csv for a controlled IMPORT into Excel, rather than OPENING the file in Excel in an UNCONTROLLED fashion, whereby, Excel can and will CHANGE certain data by making built-in assunptions that are designed to "help" the unwary spreadsheet user.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MCuthill]

Skip: That is what I have done -- the reports all export as Crosstabs that are auto-formatted by various formula in Excel (mostly VLOOKUPs). Not the most fun for initial setup, but thankfully not something that has to be done "manually" each report run, just a "copy-paste". Far from ideal, but sometimes Management needs things presented in a fashion Crystal cannot handle, or there are "off system" adjustments that need to be made in the final presentation (I work in the Financial sector).

NorthW: I'll do the best I can to explain/help, could be a little tricky within the textual limitations here, but will give it a go. As I (for whatever reason) am stuggling to envision your exact scenario, I'll make it generic and hopefully that is enough to transpose to your scenario.

Lets say you have 10 purchases (1 through 10), across 4 purchase classes (A through D) - both are Row Headings. Column Headings are moot, and should be able to be left as is.

Right now, you likely get results that look like:
[tt]A
...1
...2
...3
B
...4
...5
C
...6
...7
...8
D
...9
...10[/tt]
Where your "primary" row heading is "Purchase Class" and "Transaction Number" is the secondary heading.

If you were to create a concatenated string of all Row Headings, you could then use this Formula as the only Row Heading formula. This will remove the "sub headings", but will replicate some data on you (and perhaps where Skips comment comes into play - it becomes an export table of sorts).

In the example above, your headings would become:
[tt]A1
A2
A3
B4
B5
C6
C7
C8
D9
D10[/tt]
This may not work for your specific scenario (please provide some sample data if you are needing more than above, or if I have completely misunderstood the structure), but is more or less what I have had to do for a couple reports.

Hope this helps!

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[PeterDimitrov]

If there is no explicit requirement to use crystal it might be much easier to process your data using Ad-hoc query. The first 5 min. of this video show the same report created with Crystal cross-tab and Ad-hoc query:

http://www.r-tag.com/Pages/Preview_Demo.aspx

The export from Ad-hoc query does not require post processing, allows multiple layouts and in general is faster. The tool used in this video is available for free from this link:

http://www.r-tag.com/Pages/FreeCrystalReportsViewer.aspx

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[northw]

Thanks Mike for a detailed explanation, I will pitch this suggestion, but I am sure the users will not like it.

Thanks PeterDimitrov, will give it a try.

 

[SkipVought]

@MCuthill
"auto-formatted by various formula in Excel (mostly VLOOKUPs)."

Just a technicality. FORMATS refer most often to some sort of DISPLAY that takes some value and performs a DISPLAY TRANSFORM, like for a BOLD or Date or Currency or some Conditional Format for shading, for instance.

"but thankfully not something that has to be done 'manually' each report run, just a 'copy-paste'."

You might consider using IMPORT, rather than "copy-paste" in order to make the "'off system' adjustments" as seemless as possible. I typically run a scheduled Business Objects export (a .csv file) that I simply suck into my workbook and perform the workbook update routines on a button click.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MCuthill]

Hey Skip!

In reply to the first comment -- most of those format changes happen as well, but also "presentation groupings" (the raw extract can't easily be changed to have the exact setup I need to present in). A Crosstab dump just doesn't cut it for most end users. haha.

On the second one, sadly this organization does not have CR Enterprise, and therefore I can't schedule anything. *sigh* (last gig I had server power and enterprise at my fingertips). Alot of the files are setup to just be "update links" that pull automatically, but the "worst case" ones require a copy-paste as not all the users are savvy with the level of interfile linking I sometimes like to have going on and it is just simpler to provide a file with the data included (if they are looking to audit the details). Other cases, the files are submitted to regulatory agencies, which would not have network access.

I have managed to more than double the number of reports generated each month since starting the position, but have kept the time required to generate approximately the same as when I started (not bad, given I lack Enterprise haha). I am always looking for more efficiencies and much appreciate the suggestions! Sadly, sometimes the environment is simply to restrictive to allow all the efficiencies I would like to implement.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."