Exporting Crystal Report to Excal

[jschill2628]

I am trying to do two things; I am trying to provide on a Crystal report, my users, with a "final close date", basically the maximum date of the two. The second thing I am trying to do is get duration for how long a “case” has been open.

Formula for “final close date”
totext (maximum([{IRSCLAIM.MISC6_DT},{IRSCLAIM.MISC7_DT}]))

Formula for duration
totext ((maximum([{IRSCLAIM.MISC6_DT},{IRSCLAIM.MISC7_DT}])) - {IRSCLAIM.CREATE_DT})

My problem comes when I try to export the reports (after it’s run) into excel and it fills in the blanks, or nulls with a value of 0.00, for the duration column.

I hope I explained this well enough. I am using Crystal XI. Thanks in advance

 

[SkipVought]

Hi,

You have to provide a date structure to totext...
[tt]
totext (maximum([{IRSCLAIM.MISC6_DT},{IRSCLAIM.MISC7_DT}]),"yyyy/mm/dd")
[/tt]


Skip,

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

 

[jschill2628]

My problem comes when I try to export the reports (after it's run) into excel and it fills in the blanks, or nulls with a value of 0.00, for the duration column.

Formula for duration
totext ((maximum([{IRSCLAIM.MISC6_DT},{IRSCLAIM.MISC7_DT}])) - {IRSCLAIM.CREATE_DT})

I have also tried this formulqa:
If {IRSCLAIM.CREATE_DT} < CDate (2010, 04, 01) then -1 else
{@Final Close Date} - {IRSCLAIM.CREATE_DT}

but I get the following error message "Anumber, currency amount, date, time, or date-time is required here.

 

[jschill2628]

so I have fixed the duration formula except for one thing....
new formula:
totext ((maximum([{IRSCLAIM.MISC6_DT},{IRSCLAIM.MISC7_DT}])) - {IRSCLAIM.CREATE_DT})

...everything looks great in Crystal, but when I export the report into Excel, excel picks up the last calculation and runs with it, until a new duration calculation is formed.

i.e: the current forum is taking 3/30/2010 - 1/1/2010, which equals 88. excel then takes the 88 and puts it in every cell in that column till it reaches another "duration calculation".

 

[SkipVought]

i.e: the current forum is taking 3/30/2010 - 1/1/2010, which equals 88. excel then takes the 88 and puts it in every cell in that column till it reaches another "duration calculation".

Well if I understand your correctly, I would hope so! But on the other hand getting a report in Excel that has some sort of multi-row structure for headings/records, then I pull out my hair and curse the report maker for not sending data in proper table format.

Skip,

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

 

[jschill2628]

hilarious, but I really do have a problem. You are reading my question correctly, but not every single row of information is the same, and I don't want my excel sheet filling in where blanks should be. So to reiterate I have many rows of data, depending on the time frame that my user chooses. For the columns I am concerned with I could get the following information

FINAL CLOSE DATE DURATION
1/3/2010 88
88
88
3/28/2010 7

And on

What I want to get is the following:
FINAL CLOSE DATE DURATION
1/3/2010 88


3/28/2010 7

And on

Again I don’t want excel to fill in the blanks with random numbers. I don’t want anything in the duration column unless the final close date column is filled in. Then and only then do I want it to do a calculation. I’m glad you think this is funny; I am actually trying to figure this out. I hope someone can help me.

 

[lbass]

But you haven't explained WHY there are blanks or nulls. Have you attempted to eliminate them in CR? How?

-LB

 

[Madawc]

Three suggestions.

First, you might get better results if you copied the whole of SkipVought's suggestion, the "yyyy/mm/dd" as well as the use of ToText.

Second, for cases where you get a blank in Crystal, make it an explicit blank, included in the formula field as an alternative to the date. SOmething like

 if ... then (date)
else " "

Third, if both those fail, calculate the date in one formula field and then referenced it from another formula field.

I'm puzzled by the problem. I think it is the export to Excel trying to be helpful, interpret the data, but wrongly in this case.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP