Adding to an existing incomplete Sequence 2

[pgrice]

I am using CR10 with an Oracle 9i database. Thanks to all the help so far!! It is much appreciated!

I am returning a field that has numbers from 1-15. Sometimes there are a couple of missing numbers. I want to show those numbers, since it shows an blank result for the report reader. How do I do that?

example 1,2,4,5,6,8,9,10,11,13 show up, but I still need to show 3,7,12,14,15. The missing numbers will vary for every report.

 

[Turkbear]

Hi,
How can you show something that is not there?
Why would those numbers not show up..If they are not in the data then why would you want false results?

 

[pgrice]

Hey Turkbear,

It wouldn't be false results for this report. The list of numbers represents box numbers that have addresses associated to each one. If a box is not in our data set, it exists, but has no address associated to it. The users of the reports want to see what box is full(ie has an address associated), as well as, what box is empty(no address associated, yet). It is an issue with the data, but I cannot change that now!

On top of that, the numbers are sequencial, but the first sequence is 1-9, the second and third can be either 1-15 or 1-9 depending, so I can't simply show a list of numbers (a simple count). So I could have configurations/sequences like 1-9, 1-9, 1-15 or 1-9, 1-15, 1-9 or 1-9, 1-15, 1-15, etc...

Does that make sense?

 

[Mike Lewis]

pgrice,

Can you create (or have you already got) a table containing just the box numbers? If so, you could create an outer join from that table to your actual data table. Any box numbers that don't have associated addresses will contain NULL in the address fields. You can tell CR to print the NULLs as empty spaces.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[pgrice]

Mike,

I do have a table with all of the box numbers. I created the left outer join to the address table and that worked! Cheers!

Now I have a problem, in that one of my selection formulas ({ADDRESS.ADDR_TYPE_CDE} = 127}) is now conficting with that join. There are two address type codes (127, 128) for every box number. So I now have two box numbers with 2 different addresses being displayed.

Example
BOX Address
1 123 Main St
1 PO Box 131
2 124 Main St
2 PO Box 151
3 125 Main St
4 126 Main St
4 PO Box 152
5
6 128 Main St
6 PO Box 164

I only need to show the addresses (and box number) when the address type is 127. It should look like this:

BOX Address
1 123 Main St
2 124 Main St
3 125 Main St
4 126 Main St
5
6 128 Main St

Any suggestions on this part?

Thanks,
Geoff

 

[lbass]

Group on {table.box} and then create a formula {@127} for the details section:

if isnull({table.address}) or
{ADDRESS.ADDR_TYPE_CDE} <> 127 then "" else
{table.address}

Then right click on {@127} and insert a maximum. Then suppress the details section.

-LB

 

[pgrice]

lbass,

I tried this and was able to implement everything you mentioned, except for the maximum (wasn't available on right click for me). The report now shows only the addresses that match this {ADDRESS.ADDR_TYPE_CDE} <> 127, which is perfect, but it still shows the {table.box} field value twice if there are two {ADDRESS.DISP_NAME} records. This is the field I want to reduce to one displayed value and no blank lines where the duplicate would be.

Any further suggestions to try?


Thanks,
Geoff

 

[lbass]

It doesn't make sense to me that a maximum would not be available to you, unless the fields in the formula are actually formulas themselves (and not database fields) that use a summary function or the "next" or "previous" functions. If you right click on {@127}, you should see "Insert summary" as one of the options. After clicking on that, you should have a choice of summaries, with "Maximum" being one of the options.

I guess in 10.0 (I have 8.0) the location of "insert summary" might be somewhere else, so you might try highlighting the formula and then exploring dropdown options until you find it...

-LB

 

[pgrice]

I did find it. I didn't search through the sub menus well enough. Now that I tried the whole solution, it works! Now I just need to fit it properly into my template...

Thanks LB.