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

 

[lbass]

Create a formula like:

if {table.SBU} in ["SBU SYSTEMS BU ACCESS","SBU AMERICAS"] then "SBU SYSTEMS BU ACCESS" else {table.SBU}

Then insert a group on this and make it your outer group.

-LB

 

[ReillyC]

This worked!!!
I created a formula for each SBU. There are 14 all together and I placed the field called site after all 14 SBU formula groups. Perfect.

I have one more question to ask:

1. I have some SBUs that don't have any application info yet. It could in a few months, maybe never. I still need to have the SBU display on the report no matter if there is data or not. Is that possible?

I would like SBU SYSTEMS BU TECH to display even if there is no data yet.

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
SBU SYSTEMS BU TECH
TOTAL 0


Thanks for your help again!
Cheers
R

 

[lbass]

If you have a table that includes all SBUs regardless of whether there is related application info in another table, you could use the "allSBU" table and use a left join from it to the table containing application data. Then you would base your groups on the "allSBU" SBU field.

Otherwise, you can use running totals and text boxes to place the summary information in the report footer. You would choose {table.SBU}, count, evaluate using a formula:

{table.ABU} = "SBU SYSTEMS BU ACCESS" and
{table.state} = "CA"

Reset never.

You would need one running total for each summary.

-LB

 

[ReillyC]

Thanks lbass for your recommendations:

1. I linked the table called SBU and the application data table with a left join. I then updated my formulas with the new SBU field location. I'm still grouping on my SBU and site but I'm not getting any results. What am I doing wrong?


2. My results after using Lbass' second suggestion:
This suggestion worked but I also need to account for the site that falls under each sbu. Can I group on a report footer? By SBU then by site

Actual results:
SBU SYSTEMS BU ACCESS 3


Desired results:
SBU SYSTEMS BU CONNECTIVITY
NY 3
TOTAL 3

SBU SYSTEMS BU BROADCAST
NJ 2
CT 1
TOTAL
SBU SYSTEMS BU TECH 0



Any suggestions
Cheers
R

 

[lbass]

For the left join to work, you can't have selection criteria on the application table.

For the site info in the report footer, you would need to use separate running totals--at the SBU level, the running total evaluation formula would not specify any states, but for the sites, it would.

-LB

 

[ReillyC]

You are correct. I did place a selection criteria on the application but I think I may go with the report footer solution.

I just need one more piece of advice.

In addition to group bu SBU and state I also have to display each quarter for the year.
I believe I will have to create a separate formula for each quarter for each site and SBU grouping.

Is that correct? Or is there a better way to approach this?

Again Many Thanks
R

 

[lbass]

If you want a horizontal display of quarterly data, then you would need separate running totals per quarter. If you don't mind a vertical display, you could insert a group on data, print on change of quarter, and make this your outer most group. Then change your running totals to reset on change of group(date) and move them into the date group footer.

-LB

 

[ReillyC]

Thanks for your advice. I'll be working on the report today. I'll let you know it went.

Cheers
R

 

[ReillyC]

I almost have it.
I decided to use your suggestion of using the table that includes all SBUs regardless of whether there is related application info in another table.

The report is group by my SBU formulas and then by the site field.
Also I created a formula for each quarter:

If month ({table_TABAPP.DATAPP}) = 1 to 3 AND YEAR ({table_TABAPP.DATAPP }) = 2005 then 1 else 0
I placed the formula in the site header.

So I finally got the zeros to display for the SBUs I want to see that have no application data yet. But since removing the date from the selection criteria I now have all our application info for every year they were entered into the database.

So I have the states without SBUs from years less than 2005 at the top of my report. (i.e. PA, RI see below) or I have sites that fall under the SBU but were from a year other than 2005 (i.e. SBU SYSTEMS FL AND MA ONLY)




How do I keep the SBUs I want to display on the report and how do I focus on a time frame.

Q1 Q2 Q3 Q4
PA 0 0 0 0
RI 0 0 0 0
SBU SYSTEMS
NY 4 5 0 6
CT 0 3 3 2
FL 0 0 0 0
MA 0 0 0 0

SBU TECH 0 0 0 0
SBU ACCESS
IL 3 5 8 1

Thanks so much
R

 

[lbass]

Can you clarify which SBUs you want to show? For example, you might want to eliminate those with no SBU (from the ALLSBU table. But aren't SBUs with records from previous years possibly those who might have application data in the future? So eliminating SBUs with old application data might also elminate those with possible future data--so what are YOUR criteria? In other words, what do you mean when you say you want to show ALL SBUs whether or not there is application data?

-LB

 

[ReillyC]

I think what I should have asked was:

Is it possible to suppress the states where applications were not filed in 2005? I just need 2005. I want to use this report as a template for 2006, 2007 etc.

But to answer your earlier questions:
1. Yes you are right some SBUs that aren't with records from previous years could possible have some in the future.

2. When I say I want to show ALL SBUs whether or not there is application data, it's because I have a few SBUs that don't have any data yet because they are new but I still have to display the SBU name and a value of zero when I print the report. The zero value would be displayed because we have yet file the application in any state.


I used your advice to create a formula for each SBU group. See below.

if {table.SBU} in ["SBU SYSTEMS BU ACCESS","SBU AMERICAS"] then "SBU SYSTEMS BU ACCESS" else {table.SBU}

Cheers and Thank You
R

 

[lbass]

You might want to create a number parameter for the year {?year}. Then create a formula like this {@inyr}:

if {Application.Date} in date({?year},01,01) to date({?year},12,31) then 1

Then in the section expert->group header (state)->suppress->x+2 and enter:

sum({@inyr},{table.state}) = 0

-LB

 

[ReillyC]

Help I'm lost.
1. I tried this formula in paramter but it's not working.
year({tabapp.dateapp})=?year


2. Where should I place the formula :if {Application.Date} in date({?year},01,01) to date({?year},12,31) then 1.

Should I include this formula in each SBU formula group.
Thanks
R

 

[lbass]

First go to the field explorer->parameter->new and name your parameter "year" and choose "Number" as the datatype. Do not create ar record selection formula using this. Instead, create the formula I mentioned above and place it in the detail section. You can suppress it. Then go to the section expert and follow my earlier directions.

-LB

 

[ReillyC]

Thanks for the instructions.

I placed @inyr in the detail section and suppressed it.

This second part isn't coming together.
Then in the section expert->group header (state)->suppress->x+2 and enter:

sum({@inyr},{table.state}) = 0

Crystal doesn't like @inyr. I get an error message.
"This field name is unknown."


What am I doing wrong again?

 

[lbass]

Did you name the following formula {@inyr}?

if {Application.Date} in date({?year},01,01) to date({?year},12,31) then 1

Also, you need to replace {table.state} with your field for state.

-LB

 

[ReillyC]

Yep I did name the formula {@inyr}.

I also replaced the table.state with the field in our database for state. see below

{VMIS_TABPRO.DATOUV}= application date
if {VMIS_TABPRO.DATOUV} in date({?year},01,01) to date({?year},12,31) then 1


Crystal doesn't like the next part.
(VMIS_TABPRO.R_IDESTE}= state
sum({@inyr},{VMIS_TABPRO.R_IDESTE}) = 0

 

[ReillyC]

I place the @ sign in front of the formula name.

It looked like @inyr but when I looked at the field in detail section it looks like @@inyr. I remove the extra @. It worked but it removed the SBUs that don't have any data yet.


Results
SBU2 4
SBU3 7

Desired
SBU1 0
SBU2 4
SBU3 7

 

[lbass]

Sorry, I think I've lost track of what you're trying to do. If you want to suppress old dates, but keep nulls and new dates, then change {@inyr} to:

if isnull({VMIS_TABPRO.DATOUV}) or
{VMIS_TABPRO.DATOUV} in date({?year},01,01) to date({?year},12,31) then 1

Note that if this gives you the results you want, you could use the following in your record selection formula for a faster report:

isnull({VMIS_TABPRO.DATOUV}) or
{VMIS_TABPRO.DATOUV} in date({?year},01,01) to date({?year},12,31)

Also, note that when creating formulas, add fields and formulas to the formula by double-clicking on them in the field list in the formula editor--you don't need to manually write them into the formula, and this way, they will be accurately rendered in the formula automatically.

-LB