Oracle Datatype of Long and Crystal Formulas

[TheEntertainer]

Whatssup!

We have database on an Oracle 7 platform which has a datatype of Long. We need to show this field based on a parameter supplied by the user, i.e. (Y) but as we can't include it in a formula we surpresses it based on the parameter and then lay a formula over the top which appears when the parameter is N.

This causes a problem because these fields are in details b, along with a second row of financials, the first row of financials is in details a. However when you export to Excel using Crystal 8 although either one or the other field appears it pushes them into separate columns pushing the financial fields out of sync.
i.e.
(In Crystal) Accident Des £ £
details a: Help 100 100
details b Blah, Blah 100 100

(In Excel) Accident Des £ £
Help 100 100
Blah, Blah 100 100

This is because the Blah, Blah field has another field surpressed underneath it in Crystal, which isn't appearing on export but is being allocated a cell (so to speak) in Excel. We are using Crystal 8 (for developing reports although the report runners use 7), Oracle 7.3 database, exporting using the details line to Excel 7 extended and none tabular format.

TheEntertainer
Don't blame it on the sunshine, don't blame it on the moonlight, blame it on the boogie (Name that tune)

 

[glauco]

Hi, If you can't beat them, join them.

How about this idea? Why don't we put another supressed in details a, so this will move a cell in export to ?

Try it, i haven't.

 

[Ngolem]

"We have database on an Oracle 7 platform which has a datatype of Long. We need to show this field based on a parameter supplied by the user, i.e. (Y) but as we can't include it in a formula we surpresses it based on the parameter and then lay a formula over the top which appears when the parameter is N. "

Try explaining this again...I don't quite understand what it is you are doing...What is the problem with Long datatypes??

Excel tries to accomodate all objects in a line...assigning them to separate columns....Why do you need this overlay?

Jim

 

[TheEntertainer]

Thanks for all the responses,

Ngolem

In an ideal world we would have a formula that would be something like this:

If {?Parameter}='Y' then {TABLE.FULL_DIARY}
ELSE {@SHORT_DIARY}

However the field {TABLE.FULL.DIARY} has a type of Long so that when you scroll down the field list you can't see this field (similar to a blob type)and insert it into the formula. So instead on the report we have a formula called {@SHORT_DIARY} which has a conditional surpression on it based on the {?Parameter}='Y' and then place the field {table.full_diary} over the top with a conditional surpression on it based on the {?Parameter}='N'

So at any one report run only one of the fields appears. However when its exported we get the problem detailed in my first entry, that although it surpresses its "shifting" all the subsequent fields a cell over, resulting with them not being in line.

I hope this clarifies things a bit.

I believe I can fly

 

[Ngolem]

yes it does....now I understand. I haven't worked with a Long Data type before (in 4 years of writing reports actually) so I wasn't aware that there was a problem using it in a formula.

So now I understand why you have overlaid the formulas.

As I said before Excel wants to assign a column to every object in a line so that is why you see the stagger.

One approach to fixing that might be to create 2 complete sub-sections in the detail.

One for {TABLE.FULL_DIARY} and one for {@SHORT_DIARY}

The other fields in each line would be duplicated and placed in identical positions horizontaly. The parameter would then be used in the Conditional Suppress in the Section Expert for each subsection...suppressing one while unsuppressing the other depending on the parameter's value.

Another trick that I have used when exporting to Excel is to use suppressed text fields to aid in positioning fields in one section with respect to other fields in other sections.

For example: Let "ST" = suppressed Textbox

a layout might be

{Table.field1} "ST" {@title} "ST" {@Date}

{col1_hdr} {col2_hdr} {col3_hdr} {col4_hdr} {col5_hdr}

{detail1} {detail2} {detail3} {detail4} {detail5}


And finally Crystal seems to get confused when fields are different sizes ... so I try to keep them as close to the same size as possible.

Hope this helps

Jim

 

[TheEntertainer]

Cheers Ngolem

I'll try your suggestions when I return to work next week

 

[Roadkill150]

Hello,
another option to consider is to use the underlay option. Place the one of the fields in a subsection above and underlay it, where it will function the same as your current overlay. The difference being when you export the blank field in excell will be in a higher section, hopefully not affecting your display.
Cheers,
-Bruce Seagate Certified RCAD Specialist.
-Bruce Thuel-Chassaigne
roadkill150@hotmail.com