Crystal XI - Grouping - Including data not in the group. 1

[ReillyC]

Good Day All
I need some help.
I have a report it's group by SBU then by SITE. IT WORKS GREAT BUT……
Raw data
SBU SITE APP NO
SBU SYSTEMS BU ACCESS CA 1
SBU SYSTEMS BU ACCESS CA 4
SBU SYSTEMS BU ACCESS DE 7


SBU SYSTEMS BU CONNECTIVITY NY 2
SBU SYSTEMS BU CONNECTIVITY NY 5
SBU SYSTEMS BU CONNECTIVITY NY 8


SBU SYSTEMS BU BROADCAST NJ 3
SBU SYSTEMS BU BROADCAST NJ 6
SBU SYSTEMS BU BROADCAST CT 9

Actual Report. - Works fine


SBU SYSTEMS BU ACCESS
CA 2
DE 1
TOTAL 3

SBU SYSTEMS BU CONNECTIVITY
NY 3
TOTAL 3

SBU SYSTEMS BU BROADCAST

NJ 2
CT 1
TOTAL 3



The problem is as usual there are exception to every rule. There are some SBUs that should roll up to other SBUs. For example, SBU AMERICAS now rolls to SBU SYSTEMS BU ACCESS


Raw Data
SBU SITE APP NO
SBU SYSTEMS BU ACCESS CA 1
SBU SYSTEMS BU ACCESS CA 4
SBU SYSTEMS BU ACCESS DE 7
SBU AMERICAS FL 10

Desired Results

SBU SYSTEMS BU ACCESS
CA 2
DE 1
FL 1
TOTAL 4
How do I get the SBU AMERICA under the SBU SYSTEMS BU ACCESS
Grouping?
Help?
Cheers
R

 

[ReillyC]

Thanks for the advice on formula writing.

I'm not sure what am doing wrong now.

The zeros are displayed for the SBUs that don't have data yet but it's not filtering 2005 data. It still displays all data from the database.

When the parameter comes up it displays 2,005 (I guess 2005 was retained from the last time I ran it)instead of 2005. I tried to change the format of the formula in yr but it's not working. Maybe that's why it's not filtering out 2005.

Help again.

 

[lbass]

When the parameter selection box comes up, you have the option of removing old values. Do that and then add the new 2006 value, if that's what you are trying to do.
I'm assuming that you added the formula to the record selection formula area?

-LB

 

[ReillyC]

OMG. ? ? ? It's nearly perfect except that at the very top of the report of have zeroes displaying. I'm not sure why it's there. I can't suppress because if I do all the state information disappears.

I also placed a total on each group by selecting a group footer at random so in addition to the random zeroes I also have a total column with zeroes at the top of the report. Perhaps I'm not approaching the group total properly.


QTR1 QTR2 QTR3 QTR4
0 0 0 0
TOTAL 0 0 0 0

SBU HOME
NJ 3 4 0 7
CT 0 3 4 1
TOTAL 3 7 4 8

SBU ACCESS
NY 2 5 6 7
TOTAL 2 5 6 7

 

[lbass]

If you look to the gray area on the left of the report, what section is the first row of zeros in? Is it in the SBU group or the state group?

Also, is it correct that there are no state rows with all zeros in the named SBU groups?

-LB

 

[ReillyC]

1. The zeros appear in the state group.(VMIS_TABPRO.R_IDESTE}= state.

2.Yes that is correct.

 

[lbass]

What I meant was, is the report accurate when it shows no states with all zeros within an SBU--because we set this up to allow for some with zero information, and now the only place the all-zero groups are showing up is outide of the SBU group.

I'm not sure what to try here, as I can't test the data. It looks like both the state and SBU are null in some cases. Maybe go to the section expert->state group and SBU group->suppress and enter:

isnull({table.state}) and
isnull({table.SBU})

Not really sure.

-LB

 

[ReillyC]

Lbass thanks for your help. I'm just going to ignore the zeroes for now. The data is fine. Many thanks