New Owner Brings About A New Name

October 8, 2007 saw Business Objects (Crystal Report's as then owner) being bought out lock stock and barrel by SAP.  Nearly three years later they have decided to bring about a name change in light of their future releases for Crystal Reports and Crystal Reports Server.

As of 7th June 2010 the products shall be known as:

Old Name	New Name
Crystal Reports	SAP Crystal Reports
Crystal Reports for Visual Studio 2010	SAP Crystal Reports for Visual Studio 2010
Crystal Reports for Eclipse	SAP Crystal Reports for Eclipse
Crystal Reports Viewer	SAP Crystal Reports viewer
Crystal Reports Server	SAP Crystal Reports Server
Crystal Reports Visual Advantage	SAP Crystal Reports Dashboard Design package
Crystal Reports Developer Advantage	SAP Crystal Reports runtime server license
Xcelsius Present	SAP Crystal Presentation Design
Xcelsius Engage	SAP Crystal Dashboard Design, personal edition

For more information please check here.

Creating that Spreadsheet Feel

One of the few things that still suprises me to this day, is that in this day and age of scientific advance, free access to limitless information and people and things flying around at radical speeds, that some people abhore change.  If I had a chunk of cash for every time I've worked on putting in a 'new system' that they want it look exactly like the 'old system' then I'd be enjoying my very own Bugatti Veyron (http://en.wikipedia.org/wiki/Bugatti_Veyron) about now.

If you have a static number of columns you can simply lay down lines around the group headings, footers and detail sections.  However, if you are using one of those pesky crosstabs the object lays lines down around the populated data area but what if you want the grid filling the entire page?

Fear not - there is a work around for this as well.  It is achieved by firstly setting your grid size on File/Options to something sensible like 0.1cm and snap to grid.

 

Now create a section at the level where your crosstab will be positioned and draw the grid using the 'Line' objects. In the 'Section Expert' check the underlay next section.

 

Create a second section at the same level as the grid and put a crosstab here and align it to the grid.  With the gridsize being set at the start lining things up should be a doddle.

 

Finally giving you a result similar to below - which you can shape and change with much more flexibility than the built in crosstab options.

Done right you can achieve results like below

You could always export to MS Excel or similar from Crystal Reports - heh, good luck with that!

Examples from Crystal Reports v9.2

Crosstab Running Off The Page

If you've started down the path of using the powerful, yet annoyingly limited power of the crosstab objects and are using subreports then you have no doubt ran head first into data getting orphaned off onto imaginary pages to the right (unless you are returning small amounts of data).

A bit like this:

The easiest way I found to defeat this particular beasty is to get the sql to work out columns and sections that the data belongs to using the ROWCOUNT() OVER function (new in SQL Server 2005). Once you've nailed this you can group by section numbers and voila - no more overrunning.

An example I here you cry!  

Let us imagine a database with sample testing information.  We select one at random, let's call this Dougal.  Dougal has 248 records.

select 
s.original_sample
, s.status
, s.sample_number
from 
sample s
where 
s.project = 'DOUGAL'
order by
s.original_sample
, s.status

This gives us:

Now let us work out the row count for each record.

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
from
sample s
inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
from
sample s1
where s1.project = 'DOUGAL' group by s1.original_sample ) as A on s.original_sample = A.original_sample
where s.project = 'DOUGAL'
order by s.original_sample , s.status

Which now produces :

Now we can work out which column each of the items will go in for each section (not really needed here - but may come in handy elsewhere).

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
, a.myColCount
from
sample s
inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) - 10 *((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as myColCount
from
sample s1
where
s1.project = 'DOUGAL'
group by
s1.original_sample
) as A on s.original_sample = A.original_sample
where
s.project = 'DOUGAL'
order by
s.original_sample
, s.status

Giving:

And finally we now derive the section numbers

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
, a.myColCount
, a.mySecCount
from
sample s inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) - 10 *((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as myColCount
, ((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as mySecCount
from
sample s1
where
s1.project = 'DOUGAL'
group by s1.original_sample ) as A on s.original_sample = A.original_sample
where s.project = 'DOUGAL'
order by s.original_sample , s.status

Giving us:

The rest is plain sailing from here on in.  Open up the Group Expert in the Reports menu and then set up 'SecCount' (or whatever you have called your sections) as a group.

Now add your crosstab to the footer of this group and with a minimal amount of tweaking you should have something like:

A nicely wrapping crosstab set to 10 columns.  Of course changing the figure changes the number of columns displayed, so you have flexibility over font, size and layouts.


Hope this helps someone!

Greetings and salutations

Greetings Weary Traveller,

If you have landed here then you have scoured the lands, traversed treacherous terrain and swam vast oceans in search of how to master the lost art of Crystal Reports.

Unfortunately your journey does not end here, paths still need to be followed. However from here on in I shall accompany you and aid you where I can.

Together we shall conquer idiosyncrasies, bypass impossibilities and generally have a laugh on the way.

I have been using Crystal Reports for over a decade now and have picked up a couple of tricks which I would like to share. I do this not in the search of fame nor fortune, but just to prevent the hairloss, itchyness, sleepness nights, panic attacks or mild niggles that are associated with Crystal Report development.

Watch this space - I shall add as and when I can think of anything interesting to say.

Regards,

Paul Phillips

.Net/Crystal Reports/MIS/BIS Specialist