Showing Blank Rows in Crystal Reports

In helping someone on the asp.net forums, I came across an interesting question relating to Crystal Reports. Most reports created print one row for each returned record. There is maybe a header, footer and group by section but in the end there is one row for each returned record. What if you have a need to print a row for the full page, whether or not there are rows for each record? For example; you want this…
CrystalNormal

to become this…..
CrystalwithBlankRows

Believe it or not there are two ways to do this. Both ways have their benefits and drawbacks. I will show you both ways. If there are any other ways please drop me a note and let me know.

First Option:

You can create a stored procedure (if you don’t already use them) which passes in at least one parameter. The idea is simple. Take your initial result and “add to it” blank records that total the number of records for each page. The code below assumes that you have 30 rows to a page. It takes the count of your records and then determines the number of records that will round it to the next whole page. For example if you had 50 records then it needs to generate 10 more.

Ceiling of 50/30 = 1.66666667 or 2 then multiply this times 30 records to a page and subtract that from the 50 records total. This leaves 10 new records to create. Pass this back to Crystal Reports and continue building the report normally. This code allows you to add it to your existing stored procedures with only one for variable. You might even code that value in Crystal Reports to pass it to the stored procedure.

DECLARE @totalPerPage as decimal(8,4)
DECLARE @mtotal as INT
DECLARE @total as INT
DECLARE @intFlag INT = 1
DECLARE @t table (Flag varchar(255))		--Build table matches the query

Set @totalPerPage = 30	--Number of items per page

Set @mtotal = (Select count(*) from users)

--Calculates the number of extra records to round out the number of records to create full pages
Set @total = Cast((Ceiling((@mtotal / @totalPerpage)) * @totalPerPage)- @mtotal as int)

WHILE (@intFlag <=@total) BEGIN
	INSERT @t values (null)				--Add additional Records to Temp table Match the fields in the original query results
	SET @intFlag = @intFlag + 1
END

--Return the Temp Table with the Regular Records

Select firstname from users
Union ALL
SELECT * FROM @t

Second Option:

This method could get really messy but requires no new coding. It doesn’t require any stored procedure or the passing of any variables. This is
set in Crystal Reports. Basically you set up a detail section for each blank occurrence. For example if you have 30 rows then create 30 detailed
sections as below. Then in each detail section you write a formula similar to this.

Option2Image1


If onlastrecord Then
    If COUNT ({Users.firstname}) =10 Then
       False
    Else
      True
else
false

and place it in ‘Detail k’. (Detail a–has your normal row of “real” data.)

……Enough said.

I much prefer the first option because you only have to design one section and you can even allow for the suppressing of all of the blank lines by
filtering firstname is not null in the report. Until I find a different way you are pretty much stuck with these two options.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


8 + 6 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>