Sing a Song of Synonym

With the risk of sounding too much like a Wikipedia entry - 'Synonyms' are words who have identical or very similar meanings with different words.  And what does that have to do with a budding Crystal Report writer?  Portability - that's what.  Setting up a synonym in a database is like instead of pointing at a table or a stored procedure you set up a pointer A -> B, (where B is the table/SP) however in another database you can point the same thing elsewhere ie A -> C.  As long as both table/stored procedure returns the required fields everything will be fine.

Supposing you are part of a development environment and have the luxury of multiple state instances (ie Development, Test, UAT, Live etc) in MS SQL 2005 or higher.  You would have therefore have experience of moving reports from one environment to another.

Now, on occasion (and it does happen, believe me) you will need the report to point to different tables/store procedures within the local database depending on the environment.  For example, on the test and development environments you require locally held table data, whereas on the live box you have an independant replicated reporting server and you need to point to that instead.

Here enters the humble synonym.  It creates a very handy abstraction layer.

So in the above example 'syn_DATA' points to [SERVER1].[DEV].[dbo].[DATA] on the dev box, however on the live box [SERVER3].[LIVE].[dbo] it would point to [REPORTS].[LIVE].[dbo].[DATA].  All of this is set at SQL Server level, so is transparent to the reports themselves.

This means, from a Crystal Reports point of view, that all you will need to do, when moving from one environment to another, is just to re-point the server and the job's a good 'n.

Provided that the Synonyms are set up on the database first Crystal Reports will be able to select them from the GUI.

I won't go into setting up a Synonym as there are reams of pages out there already covering that particular topic.

Exporting Crystal Reports into a legible, formatted Excel document

Don't.  Don't get me started.  You may as well ask me to turn lead into gold (which, ironically, I do know how to do - that's easy by comparisson). 

There is no easy way.

What you have is two completely separate and incompatible products whose only common theme is that they both hold data.  It would be like trying to word process in MS Paint (actually a funny story about my 7yr old daughter...another time).

'But there is an export function' I here you wimper.  Press it, see what happens.  At best, it is a rough approximation of the Crystal Report where it has put in a zillion columns and rows to mimic the formatting. (Bless, look it's trying to be helpful)  The problem here is that the primary reason you want it in Excel is to change stuff; add formulas, move stuff around etc. However, with all of this "formatting" it has made a dogs dinner of things and you may as well have typed it all in by hand.  The worst case is that it ramdanglifies the formatting in addition to the negative points above, so it is useless and not pretty.

If you are hell bent on accomplishing anything fancy then I suggest you export the data as csv, import this into MS Excel (or whatever) and use macros to handle the formatting.  (I have had quite good success in the past using this method).  Anything else is just overambitious ignorance and let me spare you the pain.

This is probably of not much use, however I am afraid to say that it is the truth.

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