Display Named Group Values 1

[MsPepperMintUSA]

Hello All:

Any assistance with this matter would be greatly appreciated.

The situation:
I have a group report that group details per department by a specific floor. I have created Named Groups for specific department names to be displayed in a specified order.

The result:
When data is available for the department, the report displays all information by name group correctly.

The problem:
Each name group must displayed on every report, regardless of the named group having any data

The question:
If data is not available in the database for that group on a specific floor, how can I force the named group to display with default value 0 ?

 

[dgillz]

I have a couple of ideas:

1) There are 2 settings, one one the report itself, and one for crystal globally for that client, that convert null values to a default value. Click on File, Report Options, Convert Null FIeld to Default to change this for that particular file only. To globally select this for any report generated at that workstation, click File, Options, Reporting tab, Convert Null field to Default.

2) Create formula fields like if IsNull({fieldname}) then o else ..... Use these formulas in your report.

Let me know if this works, especially option 1. I am still trying to get a handle on when convert null values to default is a solution, and when it isn't. Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine

 

[MsPepperMintUSA]

Crystal Report 7.0 with Sybase 6.0
My apologies for not explaining my situation with greater detail.

I have tried the global default value, but it would cause problems with my other subreports on this report.

I have tried the if IsNull({fieldname}). The problem is not identifiying where to place the default value, but how to force the next "Named Group" Value to appear on the report in the correct order.

For Example:
Let's assume the Named Groups are: EAST, WEST, SOUTH, NORTH
On floor 3, EAST, SOUTH, and NORTH all have values and would be displayed on the report.
What I have is:
EAST 2
SOUTH 3
NORTH 7

What I need is:
EAST 2
WEST 0
SOUTH 3
NORTH 7


TIA

 

[IdoMillet]

You need to use either an Outer Join, a UNION statement, or a subreport.

To help with this choice, we would need a description of your table structure. In other words, what tables and columns are you using?

Cheers,
- Ido ixm7@psu.edu

 

[MsPepperMintUSA]

Thanks

I'll try a full outer join.

 

[MsPepperMintUSA]

The crystal report engine does not interpret a full outer join. Any other suggestions?

To recap:
the situation : have a report with several subreports. Must display all department names for each floor, even though the department may not occupy any space on that floor.

the sample query statement :
select div.dept_id, div.div_id div.name, rm.dept_id, rm.div_id, rm.rm_number,
rm.area from div, rm where rm.div_id = div.div_id and rm.rent = div.rent AND
rm.headcount = div.headcount AND
rm.floor_id = '3' (the data type for floor is char as the floor can be an alpha-numeric string)
The Name Groups in the correct order which reside in crystal report group selection (This value is div.name) :
Dept1, Dept2, Dept3, Dept4, Dept5, other

the detail to be displayed next to the named group :
rm.area (this value is calculated by a subreport based on the space occupied by each div)

solutions tried, but unsuccessful :
1. setting the Default value globally
2. isNull({fieldname})
3. Full join

what crystal report generates for floor 3
Dept 1 500
Dept 3 600
Dept 4 400

what I want generated for floor 3
Dept 1 500
Dept 2 0
Dept 3 600
Dept 4 400
Dept 5 0

what crystal report generates for floor 4
Dept 1 600
Dept 2 600
Dept 4 300

what I want generated for floor 4
Dept 1 600
Dept 2 600
Dept 3 0
Dept 4 300
Dept 5 0

Any additional suggestions would be greatly appreciated.
TIA

 

[IdoMillet]

What do you mean by "The crystal report engine does not interpret a full outer join"?

It may be easier to help if you provide the SQL query you are using.

If you are indeed running into some limitations, you can try to run the report against a VIEW defined with the necessary Outer Join.

Cheers,
- Ido ixm7@psu.edu

 

[MsPepperMintUSA]

I have tried a "left" outer join and a "right" outer join, neither of these give me the desired result. A "full" outer join combines both "right" and "left" and should give all results from both tables regardless of finding a match.


Here is my query statement.
sql statement:

SELECT
rm.rm_id, rm.area, rm.dp_id, rm.dv_id,
div.dv_id, div.name
FROM
div full outer join rm on (div.dv_id = rm.dv_id)
WHERE
rm.area_comn_serv = div.area_comn_serv AND
rm.area_comn_rm = div.area_comn_rm AND
rm.area_comn_ocup = div.area_comn_ocup AND
rm.area_comn_nocup = div.area_comn_nocup AND
rm.area_chargable = div.area_chargable AND
rm.area_comn = div.area_comn AND
rm.cost = div.cost AND
rm.count_em = div.count_em AND
rm.fl_id = '3a'

 

[IdoMillet]

You need a simple left outer join, but in the rest of your WHERE clause, you need to take care of situations where due to no match, the outer record contains all Nulls.

In other words, some of your "WHERE..." conditions should be moved into an "ON..." condition. If you can't or don't want to use Crystal SQL Designer for this, you can achieve the same thing by adding an OR portion allowing for Null value as an alternative to the current WHERE conditions.

Cheers,
- Ido ixm7@psu.edu

 

[MsPepperMintUSA]

Ido,
I have implemented your suggestions and the report generates the appropriate "named groups" ; however, the calculations for the area utilized by each group is no longer correct(due to the seacrh criteria allowing null values).

Should I create another subreport which identifies the "named groups", then use the "named groups" to retrieve the corresponding areas?

If so, then I will need to use a "select case" logic statement that generates the square footage. If the front end were a web page or VB, I would know what to do, but I am not sure how to implement this in crystal report.

Any additional suggestions would greatly appreciated.

csexcel@myexcel.com

 

[IdoMillet]

You probably don't need the subreport approach, though it would work.

If the calculations are thrown off by the null value,
you can easily convert those to zeros using IF IsNull()
logic.

Cheers,
- Ido
ixm7@psu.edu