Using Crystal Reports and Xcelsius to deliver updating Dashboards

Posted by jack on May 9, 2010

Business Objects promote the combination of Crystal Reports 2008 and Xcelsius 2008 as a toolset for delivering updating dashboards.
Here is a link to the BO official paper on embedding Xcelsius in Crystal Reports – but be warned it is not that easy to follow.

We have used Xcelsius 2008 with Crystal 2008 and here is some information that might assist.

1. You MUST use the 2008 version of both products (or later). X11 R3 or earlier will not do. If you want to automate production and delivery of the completed reports then you will need to have Crystal Reports Server 2008. You cannot use the Present version of Xcelsius for any data linking- Present is intended for producing visuals for presentations, and does not include the Data Manager component.

2. Start VERY simply…. the process of developement is a chain, and if you make a mistake early one you may not see it till later and then have to re-do all your earlier work. I did a series of test applications starting with a very simple single-sheet Excel table and only moved on when I was happy I understood how it worked. I will put links and details of some of these versions into a later article once I have tidied them up.

3. Follow this basic process:-
3a. Create the basic source data in an Excel spreadsheet. Even if you are using SQL Server or Oracle as the ultimate data source, you must use Excel to start from. If you are using a database as the ultimate data source, then you could just create a Crystal Report that reads and generates the required data table(s) and then export to Excel to form this spreadsheet – if you do this keep the Crystal report as it will save you time later.
3b. Import the Excel sheet into a new Xcelsius project.
3c. Develop your Xcelsius dashboard without amending the structure of the original imported Excel tables. I generally leave the entire source data tabs alone (see past posting), and put all the additions required for the dashboard report onto new worksheets.
3d. Define the data links in Xcelsius menu Data/Connections. You can define these links:-
i) Crystal Reports Data consumer – this is restricted to 1 table per embedded dashboard only, so make sure you design with this limitation in mind. If you really need multiple tables then consider having two embedded Xcelsius objects (if the data is independent – for example staff vacancy rates and order levels) or denormalising the data into long combined rows (if two or more tables are related in the reporting – for example you might want order headers, order detail, salesman and customer all combined).
ii) Flash Variables link – you can have several Flash variables in addition to other data links. These are good for including information like the current date, the selected Crystal Parameters and dynamic report headers.
iii) XML and other data links beyond the scope of this posting.
3e. Export the Xcelsius as a shockwave file. You might have expected Business Objects to enable direct embedding of Xcelsius files in Crystal, but in fact they have used Shockwave as the medium for integrating Xcelsius within Crystal Reports.
3f. Create the Crystal Report file, and make sure it has EXACTLY the same data rows as the source Excel file imported to Xcelsius in step 3b. You will need to be ingenious to deal with the situation where your report would naturally have variable numbers of rows or columns over time – for example you might use a combination of Crystal “Top N” and dummy zero/blank data rows to always have a set number of rows in the data, and then use filtering rules in the Xcelsius coding to ignore unwanted dummy rows.
3g. Embed the Shockwave file in the Crystal Reports header or footer. For some reports using whileprintingrecords formulae to calculate values, this will have to be the footer.
3h. Right-click on the embedded Shockwave file in Crystal Reports and select Flash Data Expert.
3i. Map the table you selected in Xcelsius as “Crystal Reports Data Consumer” in 3di) above in the Xcelsius Data tab to the source data table in the Crystal Report fields.
3j. Map individual fields (eg Crystal Parameter entries) to the appropriate Flash Variables. You may have to use a formula to convert the required entries to strings (eg dates, integers).
3k Suppress display of all the Crystal Report sections apart from the Xcelsius one.
3l. Run the report, and export to PDF to enable use of Excelsius controls in the output file.

So now, what you have is a Crystal Report that loads data to an Xcelsius generated PDF format dashboard presentation instance. You can now schedule this to run automatically in Crystal Reports Server 2008 to post this file to a fixed location for web consumption, or by email to users. so for example it could be published each Monday morning and hold order detail for all new orders received last week, with selectors to display by product and/or customer category (or whatever you decide).

So…. with Crystal Reports Server 2008 and Xcelsius Engage 2008 it is possible to create dynamic and arresting dashboard presentations that are automatically updated.

Without the (more expensive) Reports Server component, it is still possible to have an updating dashboard presentation, by running the report file manually within a Crystal Reports client. Since the Xcelsius component is only used at design time, the only software licence required to produce updated dashboard instances is Crystal Reports 2008.

Albacore Ltd can save you working all this out by taking your requirements and producing completed dashboards….. or if you get stuck you can contact us for advice.

Comments Off on Using Crystal Reports and Xcelsius to deliver updating Dashboards

Xcelsius Sample Applications

Posted by jack on May 4, 2010

It took a while for the penny to drop, but this morning it suddenly occurred to me to mention the demo Xcelsius applications I am building for the website.

So far there are two – one using a map of Europe to control display of some key statistics , and the other a tabbed report based on demographic data, illustrating use of selectors to control the display of multiple performance indicators.

You will notice that neither is based on entirely fictional scenarios – whilst the stats in the Europe map are a bit shaky (wikipedia 2002 population numbers and land area numbers combined with more recent projections for GDP in 2009), the England demographics dashboard – and apologies to Scots, Ulster & Welsh people for calling the map UK demographics – it is of course English only – is entirely consistent and based on published official statistics from the government UK National Statistics Office .

(To digress – did you notice what the founders of wikipedia did? In English we spell the base work “encyclopaedia” but that was obviously considered too tricky….. or maybe the pre-Internet American language got there first…? As Wilde almost said “Two peoples divided by a not-quite-common language”.)

The demographics came from the Statistics office, and this is a good source of well ordered data. I picked that set of stats more or less at random, and in developing the Xcelsius application I did not have to change the original worksheets at all……. – all I did was add 2 more worksheets and put all my formulae and control stuff in there.

The advantage of this must be fairly obvious – if I have not changed the original sheets, then updating for the next year is a simple copy & paste of the new base sheets over the old and then – provided no-one has changed the format between the two base reports – the whole Xcelsius application is correctly updated for another year.

There are of course some caveats…………for another post.

On the map thing, one annoyance is that Europe keeps changing boundaries. Since Xcelsius 2008 was published Montenegro (have I mentioned yet what a beautiful country Montenegro is, and how you must all go on holiday there…..?) has become independent and Kosovo also recognised as a separate country. We might well have changes nearer home, with the Belgians falling out big-time (I once had a European merger to manage and one task was to communicate with all the workers in their own language. As we only had a small operation in Belgium, and remembering Hercules Poirot (really!) I sent them a French language version – BIG mistake – they were absolutely furious as they were in the other bit of Belgium. Oh, and the other mistake I made was calling someone from the Netherlands “Dutch” – but she took that quite mildly. One sure way to wind up a Scot is of course to refer to him or her as “English” – I know of one Scottish gentlewoman who strongly objected to the Cunard liner being called “Queen Elizabeth 2” on the grounds that the current Queen is of course Elizabeth 1st north of the border – this is particularly piquant as her preceding namesake had the head chopped off her contemporary Queen of Scotland, Mary. You might detect an Englishman with a scottish childhood here. Actually, as I pointed out to her it had nothing to do with how many monarchs of that name bits of the UK had in history, but was the sequel to an earlier Cunard liner also called Queen Elizabeth – now there is a third called Queen Elizabeth 3, which proves my point some decades later).

Sorry, another long digression. the point I want to make is that Business Objects have provided maps for specific divisions of selected geography at a frozen moment in time, but they have not offered an update service – so either you have to make some custom maps or you have to put up with what they supplied – ie no separate Montenegro despite the renowned beauty of both this country and it’s inhabitants.

Another example – Xcelsius 2008 includes a map by County, but most stats are by English Region. There is a way to convert from one to the other, since each country is within a single Region. I did this recently for a customer, and will get around to publishing the approach and some metadata at a later date.

All for now – Jack

Comments Off on Xcelsius Sample Applications
Categories: Xcelsius