Grouping problem?

[f0rg3tfu1]

Hello All,

Crystal 10, SQL Server 2003

This seems like a relatively simple problem.

In this report, I am filtering by 4 building numbers:
{adq_rpt_qa.bldg} in {"n24a", "n24", "u17", "N99"}

The report has to be structured in a way that even if there is no data for a particular building, we want it to show up on the report with no data following it.

Example:

N24A
xxxxxxxxxxxxxxxx

N24

xxxxxxxxxxxxxxxx

U17

N99

It can be a hardcoded header, but Im just having problems getting it to work.

Let me know if you need any more information, I thank you for your help!

 

[BoniR]

Hello there!
I may be able to help but I am not sure what exactly you are asking...
If you are just looking for the groups to display that don't have data...try creating a formula field that will replace one of the fields with a text message...this text can simply be a "." so that it will be hard to see in the report...
You will probably have to go to File/Report Options and check the box next to "Convert Database NULL values to default" and/or "Convert other NULL values to default"
Try this:
If ISNULL({table.field}) then "." else {table.field}
Now the data type you are displaying if the field is null should match the data type of the field or it won't work...
You may also suppress this field by using a conditional suppression and formula through the select expert...
But let me know if you need this and I will try to help...
If this is not what you needed, let me know too and I will try to assist!
Thanks
Boni



Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[f0rg3tfu1]

You were right, that is exactly what I am looking for. I want the group names to show up even if there is no data in the group detail... I tried the formula you gave me but It only shows up the two buildings that HAVE work orders attached to them.

Heres my selection criteria:

{adq_rpt_qa.bldg} in ["N24", "N24A", "U17", "N99"] and
{adq_rpt_qa.wo_type} in ["R", "E", "W", "P", "FSM"] and
not ({adq_rpt_qa.id} in ["Closed"]) and
{adq_rpt_qa.Date_Closed} = {?date_range}

I hope this makes sense... let me know if I need to clarify even furthur. I think we are almost there though... THANK YOU FOR YOUR HELP!!!

 

[BoniR]

hmmm...
let me think about this for a bit...I will ask around the office as well!
Boni


Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[BoniR]

Did I tell you to insert that formula into your details?
If not, I feel like an idiot...
You will have to mess around with the placement of it so that it displays appropriately for your report...
Boni


Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[lbass]

If there is no record corresponding to it, the group will not appear. If you have another table containing building numbers that has a record for each building, you could use a left join from that table to the current table, as long as your filters are on the left most table. Otherwise, depending upon the data you want to display, you might be able to use conditional formulas that are summarized in the report footer, or running totals in the report footer, along with labels. A conditonal formula might look like:

if {table.building} = "N99" then {table.amt}

You could insert a grand total on this and for your example it would display zero, and then you would add a text label next to it.

-LB

 

[f0rg3tfu1]

Is there a formula I could use to insert all of the building names into the selection criteria so they all show up?

Ive been trying to figure out a way to hard code them into the report, but it seems like nothing is working quite right.

Any more ideas?

Once again thank you for all your help!!!

 

[BoniR]

Are you asking for a parameter where the user selects which building names to use for the report prior to report being run?
Boni



Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[BoniR]

If you want it hardcoded...use an asterick (*) in place of the second box in the selection expert...
Boni



Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[f0rg3tfu1]

Hell Boni,

What I meant by Hardcoded is just to use 4 text boxes in the report and show the four different buildings names at all times. The only problem, of course, is if there is data associated with that building then its not going to show up in the right place.

What do you think about a formula that uses the 4 buildings in the Record Selection criteria and somehow forces them to show up in the group?

As always I appreciate everyones time!

 

[BoniR]

Hello there!
Sorry for the delay in reply...
I am really not sure how to go about doing this...I tried a couple of things and it did not work...
I am sorry that I can't help you...
I will keep this problem in mind though and if I happen to come up with anything...I will definately get back to you!
Thanks
Boni



Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269