Combining formula fields in a crosstab 1

[gav12345]

Hi,

I have a CR10 report accessing data from an Oracle 9i database using a stored procedure.

What I'm trying to do is combine the results from two formula fields into a summarised field of a crosstab. The problem is that, although the values will appear normally if displayed individually, if I try to combine them only one or the other values is displayed.

The formulas look like this:

Formula 1:

if {GET_CONTRIBUTION_PAGE.CM_ITEM_REF} = "P_Code" then
    if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "1.00" then
        formula = " " & chrw(197)
    elseif {GET_CONTRIBUTION_PAGE.CM_VALUE} = "2.00" then
        formula = " " & chrw(198)
    elseif {GET_CONTRIBUTION_PAGE.CM_VALUE} = "3.00" then
        formula = " " & chrw(199)
    end if
end if

Formula 2:

if {GET_CONTRIBUTION_PAGE.CM_ITEM_REF} = "Number" then
    if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "1.00" then
        formula = " " & chrw(194)
    elseif {GET_CONTRIBUTION_PAGE.CM_VALUE} = "2.00" then
        formula = " " & chrw(195)
    elseif {GET_CONTRIBUTION_PAGE.CM_VALUE} = "3.00" then
        formula = " " & chrw(196)
    end if
end if

I've tried calling formula 2 from within formula 1, but only the results of formula 1 are displayed. I've also tried creating a third formula as follows, but this also only displays one of the two values:

Formula 3:

{@formula1} & {@formula2}

I know I could display the two formulas one underneath the other in the report but there are various issues with this, so I really need them to be next to each other within a single cell.

Thanks in advance for any suggestions.

Gavin

 

[synapsevampire]

Try (Crystal Syntax):

whileprintingrecords;
stringvar formula;
if {GET_CONTRIBUTION_PAGE.CM_ITEM_REF} = "P_Code" then
(
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "1.00" then
formula := " " & chrw(197)
else
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "2.00" then
formula := " " & chrw(198)
else
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "3.00" then
formula := " " & chrw(199)
)
else
if {GET_CONTRIBUTION_PAGE.CM_ITEM_REF} = "Number" then
(
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "1.00" then
formula := " " & chrw(194)
else
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "2.00" then
formula := " " & chrw(195)
else
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "3.00" then
formula := " " & chrw(196)
)

I wasn't sure if you expect two characters in some instances, if so, adjust the second 1/2 of the formula to:

...
else
if {GET_CONTRIBUTION_PAGE.CM_ITEM_REF} = "Number" then
(
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "1.00" then
formula := formula & " " & chrw(194)
else
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "2.00" then
formula := formula & " " & chrw(195)
else
if {GET_CONTRIBUTION_PAGE.CM_VALUE} = "3.00" then
formula := formula & " " & chrw(196)
)


-k

 

[gav12345]

Thanks k. - Unfortunately I'm still struggling with that. One or two 'urgent issues' have just come up, I'm hoping to look at this again in a day or two, I'll update then.
Thanks again, Gavin

 

[gav12345]

I've tried everything I can think of, but cannot get the results of the two formulas combined in a single crosstab cell. Its fine if I have a single formula and some text (e.g. '"test" & {@formula1}') but if I try some combination of '{@formula1} & {@formula2}' the crosstab invariably displays one or the other. Seems so simple but I'm at a loss.
I've now just implemented the two formulas as seperate summarised fields in the crosstab (one underneath the other).

Gavin

 

[lbass]

The result of your formula is a text field, so I'm wondering what summary you were trying to use. If you look at the individual formulas, they would be drawn from different records, so if you then combine them into one formula, and use a Maximum, you would only get the results of one of the formulas. I think you could explore this by laying out your combined formula in the detail section, inserting a summary on it, and then noting how this works with your detail and group level data.

I also believe that CR 10 has the option of displaying crosstab summaries horizontally. Look in the customize style tab of the crosstab expert.

-LB

 

[gav12345]

Thanks LB. I've had a good mess around with summaries etc in the report details / groups and unfortunately still can't see a way round the issue. As you said, the summary is Max so only a single value is ever displayed.

CR10 does have the option of displaying crosstabs summaries horizontally but unfortunately only for all summarised fields in the crosstab. - I have 5 summarised fields in total which I'd like to display in the format:
1
2
3
45
- which the Crystal settings don't allow (only either 12345 or
1
2
3
4
5
)
Thanks for your advice in any case, Gavin

 

[lbass]

There is a way of creating a horizontal display in earlier versions that you might adapt for your higher version to apply only to one summary. Please see my suggestion in thread149-980436, which I tested. Please note that in CR 11, you would need to use "Add command" instead of a SQL expressions, since they don't function the same way as in earlier versions. I haven't figured out what your characters translate to in order to know whether this is something you could do in a command, but you might want to explore this if this display is important to you.

-LB

 

[gav12345]

Thanks LB. The characters I'm displaying are actually superscript characters from a font we had to create ourselves (we've had various issues with Crystal and superscripts). I'll try your suggestion. paulmarr's suggestion in the same thread is also probably worth a shot, but seems an awfully messy way of doing things.

 

[lbass]

I wonder if you could put the superscript formula in a separate crosstab which you then overlay on the first crosstab, removing all grid lines and labels.

-LB

 

[gav12345]

Many thanks LB - that's done the job nicely.

Gavin