Need help combining formulas

[ginaburg]

I need some help on how to consolidate my formulas. I was thinking I could use CASE but not sure how to put it together.
I'm using Crystal 11.5

Here is an example of two of my formulas. I am then using Print to put them on the report. Everything is working but I have 7 different classes. Then I need to create this same formula for Male/Female and for each Race we have, Black, Hispanic, Asian etc.
Can anyone help me consolidate this formula. If I do each one seperate, I will have about 50 Count and 50 Print formulas.

WhilePrintingRecords;
Numbervar CountApprenticeM;
if ({brptPRWklyCompliance;1.Class} LIKE 'CA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'EA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'FA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'IWA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'KYOA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'LA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'OA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'PDA*' OR
{brptPRWklyCompliance;1.Class} LIKE 'PSA*' AND
{brptPRWklyCompliance;1.Class} <> 'IWAGF') AND
{brptPRWklyCompliance;1.Sex}='M'
then (CountApprenticeM:=CountApprenticeM+1) else 0

WhilePrintingRecords;
Numbervar CountCarpenterM;
if (NOT ({brptPRWklyCompliance;1.Class} LIKE 'C*F*') AND
NOT ({brptPRWklyCompliance;1.Class} LIKE 'CA*') AND
{brptPRWklyCompliance;1.Class} LIKE 'C*') AND
{brptPRWklyCompliance;1.Sex}='M'
then (CountCarpenterM:=CountCarpenterM+1) else 0

WhilePrintingRecords;
Numbervar CountApprenticeM;
CountApprenticeM

WhilePrintingRecords;
Numbervar CountCarpenterM;
CountCarpenterM

 

[hilfy]

You don't necessarily need the print formulas or the variables - if you change the last line of each to be:

then 1 else 0

You can then just insert a summary (Sum) on the report.

The other thing I would do is put the "{brptPRWklyCompliance;1.Sex}='M'" as the first condition of the IF statement. When evaluating Booleans with an "AND", Crystal will stop at the first "false" statement. Since there are only two possible values for Sex, putting that first could speed up your report because this is an "=" comparison, not a "like" comparison.

You could also simplify it by doing this would be something like this:

If {brptPRWklyCompliance;1.Sex}='M' and
 {brptPRWklyCompliance;1.Class} <> 'IWAGF' and
 (Left({brptPRWklyCompliance;1.Class}, 2) in ['CA', 'EA', 'FA', 'LA', 'OA'] OR
  Left({brptPRWklyCompliance;1.Class}, 3) in ['IWA', 'PDA', 'PSA'] OR
  Left({brptPRWklyCompliance;1.Class} = 'KYOA') AND
then 1 else 2

I would then change the other formula to something like this:

if {brptPRWklyCompliance;1.Sex}='M' AND
 (Left({brptPRWklyCompliance;1.Class}, 1) = 'C' and
  Left({brptPRWklyCompliance;1.Class}, 2) <> 'CA' and
  NOT ({brptPRWklyCompliance;1.Class} LIKE 'C*F*')) 
 then 1 else 0

To make creating the formulas easier, once you have one typed and verified, you can then copy it and use it as the starting place for the next one instead of typing all of it from scratch.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ginaburg]

Thanks for the input.

So you don't think there would be any way to combine the formula? I'm still going to have about 50 formulas.

 

[Charliy]

Can you set it up so that you Group by Race? Then you would just need to count Male & Female and reset after each change of Race, plus have a set of Grand Totals counts. You could do it all with 4 Running Totals instead of 50 formulas.

 

[hilfy]

No, I don't think there's going to be any way to combine them and get it to work correctly. And from the looks of it, combining them would probably give you a formula that's too long for the formula editor to handle. You would also still have to have a separate Print formula for each number, so you would still have 51+ formulas.

What type of a data connection are you using? Have you thought about trying to push this calculation down to the database using a stored function?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ginaburg]

I would still need each class. It would look something like this (partial list)

Total Total Minority Hispanic Black Asian
Male Female Male Female Male Female Male Female Male Female
Apprentice 5 2 2 1 1 0 1 0 0 1
Carpenter 8 3 3 0 2 0 1 0 0 0
Foreman
Operator

Each of these would have the columns filled in.

 

[hilfy]

Ok, you're going to need to use a cross-tab to get this format, which actually makes this a bit easier.

1. Create a formula that calculates just the class to be used for the row labels on the right. I'll call this {@Class} It would look something like this:

If {brptPRWklyCompliance;1.Class} <> 'IWAGF' and
 (Left({brptPRWklyCompliance;1.Class}, 2) in ['CA', 'EA', 'FA', 'LA', 'OA'] OR
  Left({brptPRWklyCompliance;1.Class}, 3) in ['IWA', 'PDA', 'PSA'] OR
  Left({brptPRWklyCompliance;1.Class} = 'KYOA') then 'Apprentice'
else if {brptPRWklyCompliance;1.Sex}='M' AND
 (Left({brptPRWklyCompliance;1.Class}, 1) = 'C' and
  Left({brptPRWklyCompliance;1.Class}, 2) <> 'CA' and
  NOT ({brptPRWklyCompliance;1.Class} LIKE 'C*F*')) then 'Carpenter'
else ... (one If statement for each class.)

2. Create a formula for Gender ({@Gender}) that looks like this:

if {brptPRWklyCompliance;1.Sex}='M' then 'Male' else 'Female'

3. Create a formula called {@Race} which is similar to those above that will calculate the Race name.

4. Insert a cross-tab into the report in a Report Header or Report Footer section to calculate for the whole report or in a Group Header or Group Footer if you're group the data on some field that's not included in the cross-tab.

5. Right click on the cross-tab and select "Cross Tab Expert". Set the following:

Columns: {@Race} and {@Gender} (in that order - {@Race} should be on top.)
Rows: {@Class}
Summarized Fields: Set this to do a distinct count of some field that is unique to a person, such as an ID number.
​

You can then use other options to format the cross-tab.

The only problem with this method is that it's not going to give you the total male and total female counts. However, you could put them in a second cross-tab which would be configured as above without the {@Race} column.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ginaburg]

I was thinking along the same lines with a cross tab but since I haven't worked with then that much and wasnt sure how to group the formulas, I didn't go that route. thanks for the information. I will give that a try.

 

[ginaburg]

The cross tab works great and it also taught me a little more about cross tabs.

If I could bother you with one more question. For the race formula, I have

select {brptPRWklyCompliance;1.Race}
CASE 'I': 'Amer Indian'
CASE 'A': 'Asian'
CASE 'B': 'Black'
CASE 'H': 'Hispanic'
CASE 'W': 'White'

And this works great but I also need a column that groups all minority so I would need another column where
CASE 'I', 'A', 'B', 'H': 'Minority'

When I add this, it doesn't show as a column. How do I incorporate this into the formula?

Thanks for all of your help.

 

[hilfy]

This is not something you're going to be able to do by just linking tables. However, I know of a way to get this by using a Command (which is just a SQL Select statement). If you'll post the following, I can prototype the query for you:

- Query generated by Crystal (Database menu -> Show SQL Query)
- Any selection criteria in the Select Expert
- The type of database you're connecting to (SQL Server, Oracle, etc.)

You'll want to test my query in a tool outside of Crystal, such as Toad or SQL Server Management Studio, to make sure it's working correctly before you put it in your report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ginaburg]

It's a SQL database but it is written off of a stored procdure.

 

[hilfy]

That's going to be a challenge then. Can you post the code from the stored procedure?

Basically there's no way to do this without using a "Union" query. Going this route would also give you the ability to include the Total Male and Total Female counts in the cross-tab in addition to the total minority counts. My thought is to have the query aggregate the numbers for everything instead of having Crystal do the counts. I've done this type of query a number of times and it's really a more efficient way of generating a report like this because the database will do all of the heavy-lifting.

Basically, it works like this:

Select data with total counts for each class by gender
UNION
Select data with total "minority" counts for each class by gender
UNION
Select data with race counts for each class and gender

Each of the select statements must have the same fields, so for the "race" field in the first Select you would have the word "Total" and for the "race" field for the second select you would have the word "Minority". Then the third select would have the name of the race.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ginaburg]

I ended up doing 3 cross tabs. The original one, one with just Minority and one with male/female.
The problem I had from there was to supress the columns that I didn't want. I went into the group and selected specified order, listed the ones that I wanted to set it to delete all othere.

It worked great.

Thanks for all of your help.