How to retain Conditional Colorformat in excel export?

building reports crystal reports
1

Hi,

How to retain Conditional Colorformat (in crosstab) in excel export?

In my crosstab report i have applied the conditional color formatting to summary value using ‘CurrentFieldValue’ function like below.

If current fieldvalue =0 then cryellow else crnocolor

But, when i 'm trying to export this in to excel -dataonly not able to get applied colors for summary field.

I’m not sure color updates will be reatined after export into excel in crosstab reports.

Please let me know if there is any trick for this.

Thanks for your help!

meher (BOB member since 2008-09-01)

2

If you export the data only to Excel thats what you get… :stuck_out_tongue:

You will have to export the report to Excel, in totality, or use Live Office to get what you require…

Mak 1 :uk: (BOB member since 2005-01-06)

3

I’m not sure what you are saying…

meher (BOB member since 2008-09-01)

4

In Crystal can you not export the whole report structure to Excel, or can you only export the data itself?

Mak 1 :uk: (BOB member since 2005-01-06)

5

I don’t think so… bcoz this is an exceptional case.

we can export any report including color formatting into excel -data only if you apply normal color format to the report but this is a crosstab with conditional color format.

meher (BOB member since 2008-09-01)

6

So, I’m guessing its supposed to be similar in apperance, in most instances.

I think the problem is probably just with a cross tab because of the way Crystal calculates its report layout coordinates…

I don’t think you could find a way around this…

Mak 1 :uk: (BOB member since 2005-01-06)

7

Ok, I think there sould be a solution for this.

Let’s wait some one can put the solution .

meher (BOB member since 2008-09-01)

8

Hi

This is an urgent request,

Can anybody please suggest me on this?

Thanks!

meher (BOB member since 2008-09-01)

9

Hellow Meher,

Unfortunately, exporting conditional formatting in cross-tabs into Excel - Data Only is not supported. However, I might have a couple alternatives that might work for you.

  1. Use a spreadsheet as a Template - create a new Excel file with two worksheets. One should have everything formatted the way you want using Excel’s conditional formatting. The other should hold the raw data as is comes from Crystal. Use formulas in the formatted sheet to point to the raw data. As long as the columns and rows don’t change too much, you have a solution.

  2. Set zeros as something else - Since your conditional formatting is for a value of 0, you have other options. In the formatting of the field, you can customize the Number format. The checkbox for suppressing zeros is also NOT supported in your export. However, you CAN use the Show Zero Values As dropdown. You can use the default dash in the list or type in your own value like ***.

I hope this helps.

JBray :us: (BOB member since 2008-07-15)

10

Hi Irkuka,

Thanks for your help!

I didn’t understand the first option like creating formulas to point from one worksheet to another.

Users want to see only conditional color format if value of the field equal to zero instead of keeping something else as zeros.

But, anyway i have exporteport that report to “Excel(97-2003)” with color updates as they requested.

But i have one issue with this,

  1. Is there any way to restrict repeated row lables for every page in crosstab?

once again thanks for your help!

meher (BOB member since 2008-09-01)

11

You should be able to change the setting in the Cross-Tab Expert. Under the Customize Style tab is a checkbox for Repeat Row Labels. Try turning it off.

Also, I have attached an example of option 1. You would export your report and then copy-paste it into the Raw Data tab.
Excel Template w Conditional Coloring Example.xls (15.0 KB)

JBray :us: (BOB member since 2008-07-15)

12

I didn’t select that option (repeat row labels) but it still showing the same.

meher (BOB member since 2008-09-01)

13

Iruka,

I think that option is for to restrict the row lables or to display horizontally, but i want to restrict the row labels vertically like don’t want repeated labels for page2 - page3—etc.

meher (BOB member since 2008-09-01)

14

Ah, you are referring to the fun stuff when export to Excel. That is an automatic feature based on the paper size. I am not sure how you can turn that off, but you can change the paper size through File > Page Setup. Depending on security and what version of Crystal you are using, you may be able to set a custom page size that is larger than your cross-tab.

JBray :us: (BOB member since 2008-07-15)