Is this possible?

I am using Crystal 8.5 in the guise of InforREPORTS.

This seems to me to be a long-winded method of getting to an apparently simple task.  Does anyone have a simpler solution.  Here's the orginal sql on which the xtab is based.

 

SELECT
    RELFB."STATUS1", RELFB."STATUS2",
    RELEA."SATZART", RELEA."FIRMENNR", RELEA."ARTIKELNR", RELEA."BEZEICHNUNG", RELEA."EDATUM", RELEA."EJAHR", RELEA."EMONAT", RELEA."ETAG", RELEA."ANR", RELEA."MENGE", RELEA."ME", RELEA."UMSATZ", RELEA."AGKNZ1", RELEA."STATUS2",
    RELFIRMA."KTXT", RELFIRMA."FGKNZ_1"
FROM
    "INFOR"."RELFB" RELFB,
    "INFOR"."RELEA" RELEA,
    "INFOR"."RELADRESSE" RELADRESSE,
    "INFOR"."RELFIRMA" RELFIRMA
WHERE
    RELFB."RNR" = RELEA."RNR" AND
    RELFB."ADRNR2" = RELADRESSE."ADRESSENR" AND
    RELADRESSE."FIRMANR" = RELFIRMA."FIRMANR" AND
    RELEA."SATZART" = 'ABA' 

    RELFB."STATUS1" = 'L'
ORDER BY
     RELEA."STATUS2" ASC

 

The new criteria would select "STATUS2" as one of either 'B' or 'V', and create a grand total.  Is there a solution without using the sub-report option?

1. You can use a Running Totals to get the value. Conditionally SUM the values using a formula...{table.field} in ["b","v"] ... for record inclusion.

2. Create a formula field that will allow you to get the sum and use the field n your CT.

 

I created a formula called @forBVSum

If {RELFB.STATUS2} in ["B","V"] then {RELEA.UMSATZ} else 0

 

I added this to the Summarized Fields section, and the correct value is given where the the Total column and Total row intersect. 

 

Unfortunately, there are subtotals for each column entry where the row=B or row=V ( and 0s where rows are not 'B','V').  Is there anyway of suppressing the subtotals and zeros?

right click on any field in the cell and select Fromat Field.

Click on the Number Tab

Click on Customize button

check the Suppress if Zero as True (upper left corner)

Save close all windows.

This must be doen seperately for totals.

 

For the subtotals here is from Help files

If you have more than two groups in your Cross-Tab you can suppress the subtotal and label for one of them.

1. Right-click the blank top-left area of the Cross-Tab and select Cross-Tab Expert from the shortcut menu.

   The Cross-Tab Expert appears.

2. Click the Customize Style tab.
3. Click the field whose subtotal you want to suppress.

   The Suppress Subtotal and the Suppress Label check boxes become active.

4. In the Group Options area, select the Suppress Subtotal check box.
5. Click the Suppress Label check box to suppress the label associated with subtotal.
6. Click OK.

http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6798 - http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6798

 

I suppressed zeros as you said.

 

My CR 8.5 doesn't have Cross-tab expert so...

I right clicked each subtotal I didn't want.  Tab 'Common'.  Checked the Suppress box.

The total I did want at the intersection, I only want for company 'L' so I suppressed using the formula:

not ({?Company} = 'L')

Just need to add a conditionally suppressed label to the new subtotal and my work here is done