force report to always show a number of records

[zmlteo]

I'm on Crystal 9 and using a sql database.

I would like the report to always show 15 records regardless of how many the report retrieves. I can limit the records by suppressing using the recordnumber < 15 but I cannot get additional 'records' to show up. I'm using the 'Format with Multiple Column' option in the detail section. I would like the report to show as below if say I have only 5 records in the retrieval:

record1 record2
record3 record4
record5 ----void----
----void---- ----void----
----void---- ----void----
----void---- ----void----
----void---- ----void----
----void----

I'll then need to slid something into the 16th slot which I am not sure if I can do but that will be something I'll worry later.

I hope I've provided sufficent information and I'm grateful for any help.

Thanks

 

[Turkbear]

Hi,
Odd request,since you want something to display when there is nothing to display - Crystal will stop displaying data when the last record that matches the selection criteria is returned, so how can it continue to populate the report?

 

[zmlteo]

It is a rather odd request but I need it as a report which will print registration vouchers & badges. The 16th slot is for the badge.

The report is to be printed on pre-printed paper with the logo in each slot and therefore will need the exact 15 vouchers before the badge can be slide into the 16th slot.

Please, can anyone help?


Thank you.

 

[vidru]

Crystal's not in the business of manufacturing data, so you're best bet is a SQL-based solution using a UNION query to always return a minimum of 15 records.

If you use a Command as the data source, you could do adapt something like this to suit your needs (using the Northwind db as an example):
[tt]
SELECT CustomerID
FROM Customers C
WHERE C.Country = 'usa'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
UNION ALL
SELECT '--void--'
[/tt]

-dave

 

[cjlarue]

you could also create a table with the number of rows you need and then use an outer Join to guarentee the number of rows.

 

[chelseatech]

Or just write a simple formula to print the extra blank lines. A FOR loop using Chr(10) should do that for you.

Don't forget to set the formula field to "Can Grow"

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[lbass]

The following formula will create the blank place holders when the record count is < 15. The following example assumes you are doing this within a group (Ship Via, in this example), and therefore, I created a running total to count records within the group ({#cntwingrp}) to use in the formula:

if count({Orders.Customer ID},{Orders.Ship Via}) < 15 then
(
if remainder(count({Orders.Customer ID},{Orders.Ship Via}),2) = 0 then
(
if {#cntwingrp} = count({Orders.Customer ID},{Orders.Ship Via}) then
totext({Orders.Customer ID}) + replicatestring(chr(13),round(9-count({Orders.Customer ID},{Orders.Ship Via})/2)) else
totext({Orders.Customer ID})
) else
if remainder(count({Orders.Customer ID},{Orders.Ship Via}),2) <> 0 then
(
if {#cntwingrp} = count({Orders.Customer ID},{Orders.Ship Via}) then
totext({Orders.Customer ID}) + replicatestring(chr(13),round(8-count({Orders.Customer ID},{Orders.Ship Via})/2)) else
totext({Orders.Customer ID})
)
) else
if count({Orders.Customer ID},{Orders.Ship Via}) = 15 then
(
if {#cntwingrp} = 14 then
totext({Orders.Customer ID}) + chr(13) else
totext({Orders.Customer ID})
)

You must right click on this formula->format field->common->and check "Can Grow."

To get the badge into the 16th position, place it in a group header section where the right column would be in the detail section. Resize the group header to match the size of the details section, and then format the group header section to "Underlay following sections". Adjust the group header until the badge is in the correct position below the 14th record.

-LB