Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Select and sum specific totals on Cross tab Post Reply Post New Topic
Author Message
Crystal Method
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United Kingdom
Online Status: Offline
Posts: 15
Quote Crystal Method Replybullet Topic: Select and sum specific totals on Cross tab
    Posted: 26 Aug 2009 at 8:19am
Hi,    I have created a cross tab with a maximum of 6 rows = Manufacturing locations (fields named B,F,M,R,S & V) and a maximum of 4 columns = Market Sectors (named 1,2,3,4 for ease).
I need to select 2 totals from the manufacturing location totals (lines(B) and (V)) and show these as a separate summed value.
Is this possible?
I am using Crystal 8.5 in the guise of InforREPORTS.
Seagate Crystal Reports 8.5.0.389 in the guise of INFOR:Reports as part of Infor:COM ERP software.
IP IP Logged
Crystal Method
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United Kingdom
Online Status: Offline
Posts: 15
Quote Crystal Method Replybullet Posted: 27 Aug 2009 at 4:16am
I've decided that the only solution is to re-enter the whole query as a sub report, selecting on 'B' and 'V' only.
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?
Seagate Crystal Reports 8.5.0.389 in the guise of INFOR:Reports as part of Infor:COM ERP software.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Aug 2009 at 7:40am
2 options I can think of, just not sure how they will work in the CT.
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.
If {table.field} in ["b","v"] then {table.valuefield} else 0
IP IP Logged
Crystal Method
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United Kingdom
Online Status: Offline
Posts: 15
Quote Crystal Method Replybullet Posted: 27 Aug 2009 at 8:24am
Thanks DBlank,
 
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. Smile
 
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?
Seagate Crystal Reports 8.5.0.389 in the guise of INFOR:Reports as part of Infor:COM ERP software.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Aug 2009 at 8:36am

For the Zero's,

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.
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Aug 2009 at 8:42am
Here is another link for conditional formating in specific cells based on values...
IP IP Logged
Crystal Method
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United Kingdom
Online Status: Offline
Posts: 15
Quote Crystal Method Replybullet Posted: 27 Aug 2009 at 8:56am
DB, You're good!
 
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 Star 
Seagate Crystal Reports 8.5.0.389 in the guise of INFOR:Reports as part of Infor:COM ERP software.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.