Nesting an IF STATEMENT in Crystal Reports 2

[PeterConnick]

Nesting an IF STATEMENT in Crystal Reports

Example

Category = (1,2,3,4)

I need to group these groups by

1
2
123
1234

How would a write an if statement to handle this

If Category = 1 then

“1”

else if Category = 2 then

“2”

else if Category = 1 or Category = 2 or Category = 3 then

“123”

else if Category = 1 or Category = 2 or Category = 3 or Category = 4 then

“1234”

else

“Not a valid Group”

The problem that I am running into is that category 1 never ends up in the 3rd or 4th condition.

 

[bdreed35]

This is confusing.
Do you want to group each record into multiple categories?
If a record has Catgory = "1", what are you expecting to be the outcome?
Please post some expected outcome so that you can paint a clearer picture as to what you want.

~Brian

 

[PeterConnick]

Sorry about the confusion.

The user has asked for a report to be group by season code.

Spring = 1
Summer = 2
Spring and Summer = 1, 2
Fall = 3
Winter = 4
Fall and Winter = 3, 4
Total Seasons = 1,2,3,4

I want to get the sales group by the categories listed above.

Total Sales in Spring = 100
Total Sales in Summer = 200
Total Sales in Fall = 300
Total Sales in Winter = 400

I should have the follow total for the groups list above

1 100
2 200
3 300
4 400
(1,2) 300
(3,4) 700
(1,2,3,4) 1000



If season code = 1 then

“1”

else if season code = 2 then

“2”

else if season code = 3 then

“3”

else if season code = 4 then

“4”


else if season code = 1 or season code = 2 then

“12”

else if season code = 3 or season code = 4 then

“34”


else if season code = 1 or season code = 2 or season code = 3 or season code = 4 then

“1234”

else

“Not a valid Group”

This is what I end up with

1 100
2 200
3 300
4 400
(1,2) 0
(3,4) 0
(1,2,3,4) 0


I hope this is clearer

 

[synapsevampire]

I think that you misunderstand how databases work, and how Crystal presents data.

A grouping on 1 will ONLY show those with 1 in it. Later stating that group = 1 or 2 or 3, will have already grouped any 1's into the 1 group, data is not replicated over your conditions.

You might create a formula such as:

If {table.field} in [1,2,3,4] then
"1234"
else
If {table.field} in [5,6,7,8] then
"5678"
else
"Other"

Now use this as the main group, and within this group create a group for the field.

Try posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[synapsevampire]

Oh, I see, you posted while I was posting so I didn't see your second clarification.

Since you want the combination of Spring and Summer before the individual Winter and Fall, you'll need to use Running Totals or formulas (as discussed in my previous post, that isn't how grouping or databases work).

Insert->Field Object->right click Running Totals and select the field that you want to sum

In the Evaluate Use a Formula enter the criteria for each total, such as:

{table.season} = 1

for the combination totals, use:

{table.season} in [1,2]

Continue through all of the sums needed, and rather than trying to explain the means for accomplishing the desired output in the future, I'd suggest posting requirements, example data and the output required until you're more comfortable with data and reporting.

-k

 

[PeterConnick]

Crystal Reports 10
Oracle Database 9i

This is my real question as I am new to Crystal. I normally do this type of thing in the command window by using the UNION SQL command, but I ran out of room in the command window.

Thanks for the advise above.

Is it possible to do this in crystal?

Dim a as string
Dim b as string
Dim c as string
Dim d as string
Dim e as string
Dim f as string
Dim g as string

if season code = 1 or season code = 2 or season code = 3 or season code = 4 then

g = “1234”
if season code = 1 or season code = 2 then
c =“12”
If season code = 1 then
A = “1”
else if seson code = 2 then
b = “2”
end if
else if season code = 3 or season code = 4 then
f = “34”
If season code = 3 then
D = “3”
else if seson code = 4 then
e =“4”
end if

else

“Not a valid Group”

end if

 

[synapsevampire]

That isn't a SQL language I'm aware of, and the whole statement would never go beyond:

If season code = 1 or season code = 2 or season code = 3 or season code = 4 then
g = “1234”

Each row would be part of G, and no other group would be created.

As my previous post references, this is a hierarchial grouping, please reread it and code accordingly, or just create the Running Totals and be done with it.

-k

 

[PeterConnick]

Here is an example in SQL (Really the code is over 3000 lines)
It will not all fit into the command window

Select item, sales, 'SPRING' as "SORT"
from sales_table
where season_code in (1)
union all
Select item, sales, 'SUMMER' as "SORT"
from sales_table
where season_code in (2)
union all
Select item, sales, 'FALL' as "SORT"
from sales_table
where season_code in (3)
union all
Select item, sales, 'WINTER' as "SORT"
from sales_table
where season_code in (4)
union all
Select item, sales, 'SPRING & SUMMER' as "SORT"
from sales_table
where season_code in (1,2)
union all
Select item, sales, 'FALL & WINTER' as "SORT"
from sales_table
where season_code in (3,4)
union all
Select item, sales, 'TOTAL' as "SORT"
from sales_table
where season_code in (1,2,3,4)

I want to group by sort as I have 6 other groups in this report. This needs to go in the third group.

The IF statement I provide is more like VB code.

I tried doing your suggestion of

If {table.field} in [1,2,3,4] then
"1234"
else
If {table.field} in [5,6,7,8] then
"5678"
else
"Other"

How do I get group 1 in this report

If {table.field} in [1,2,3,4] then
"1234"
else
If {table.field} in [5,6,7,8] then
"5678"
else
"Other"
If {table.field} in [1] then
"1"
Else
“OTHER”

Does not work.

 

[synapsevampire]

What the Union is doing is creating multiple rows for the same types.

You can use a UNION in Crystal reports, although I can't imagine why you would need 3000 lines, what you've supplied makes sense by itself, just use that as the Add Command and group by the SORT.

Crystal doesn't fabricate data.

Though you might use subreports to do the same thing, that seems inefficient.

With the grouping I suggested, you might have 2 major groups, derived from a formula (Insert->Field Object->right click Formula) consisting of:

if season_code in [1,2]
then
1
else
if season_code in [3,4]
then
2

Select Insert->Group and point to this formula you created.

Now insert another group which is based on the database field season_code

Now you can create summaries at the season_code level, which in the first main group will get 1, 2, and then have a summary at the formula you created group level, which will produce the totals for 1 and 2.

This will also show the 3,4 and 3 and 4 group summaries.

Suppress all group headers and show the summaries in the group footers.

-k

 

[PeterConnick]

That makes senses. I will give it a try.

Thanks again

 

[lbass]

Another approach would be to set this up as a manual crosstab with columns for spring, summer, spring/summer, etc. You would create formulas like the following for the details section:

//{@Spring}:
if {season.code} = 1 then {table.sales}

//{@Summer}:
if {season.code} = 2 then {table.sales}

//{@Spring/Summer}:
if {season.code} in [1,2] then {table.sales}

...etc. For the total, you would just place {table.sales} on the report canvas. You would then right click on each formula or field and insert a summary (sum) and then suppress the details section.

You would then have a display like:

Spring Summer Spring/Summer Fall Winter Fall/Winter Total

100 200 300 300 400 700 1000

-LB

 

[synapsevampire]

I agree, LB, which is what I'd originally suggested, only using Running Totals (same thing basically).

-k