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

Developer bias

Posted by jack on May 3, 2010

Bank Holiday Monday…….. and it’s raining. A good time to try to get that cancellations report completed……..

I am using Crystal Reports from SQL Server, and I have 2 tables – one holds orders, and the other order transactions. The orders are a combination of history (independently loaded and verified) and future orders, calculated on the basis of order transactions.

The transactions are of 3 types – N for new, M for Modified and C for Cancelled, and are imported from a 3rd party supplier by an overnight automated process that is better than 99% accurate most days (the import uses a vertical pipe separated test file) but on a handful of days each year it all goes tits-up and the entire load is lost.

For 2 years all the business wanted was a forward projection of order levels, and for this the current arrangement was fine; now they want to understand two new things:-
1. Cancellation Rates
2. Individual customer behaviours.

To be clear, there are no separate order headers and order lines – there are orders, and the transactions used to create, modify or cancel those orders. I can cross-reference orders to transactions using the order number. The Orders table does not include cancelled orders at all.

MY FIRST ATTEMPT was based on purely the transactions – I reasoned that since the transactions have proved a reliable indication for future order levels, it ought to be OK to use just the Transactions table to derive both order numbers and cancellation levels.

Uh-uh!! The business manager (of course) compared this new report to his existing order history numbers report and there were discrepancies……”there seems to be something wrong with this report as it is not giving the same numbers…..”

One of my weaknesses is a desire to explain. Realising my users are non-technical does not deter me – it just makes the explanation much longer, so that first they and then (rather later) even I find myself losing the will to live. It really is much simpler just to say “I’m terribly sorry, I will take this away and fix it”.

So I thought about how to fix this. I am more a SQL person than a Crystal one (though I have used both for years) so generally I prefer to create views in the database rather than have complicated structures in the report – I find in most cases I can avoid the need for sub-reports and lots of whileprintingrecords formulae.

To digress for a minute there is this to be said for using views:-
1. A single view can be used in many reports
2. It keeps the processing on the SQL server box (though since I am using Crystal Reports Server for this client and it sits on the SQL Server box this is perhaps academic)
3. Views can be built on views, allowing complexity to be acheived in stages, whereas using Crystal from the base tables can make the completed report hard to understand.
4. If later you realise a logical error occurred in building the view, you need only correct the view, whereas cloning a report with faulty logic to produce multiple variants will require each individual report to be corrected for the one fault.

But back to my report. I am now thinking “OK, so I need the Orders in the Orders table (to get the existing order numbers correct) and the Cancellations from the Transactions table (to get the cancellations in the report)” So I tried to mash up a view that made a Union of views of the two base Tables.

But this did not work. for reasons that are too tedious to explain….. I switched to doing something else, as in my experience the worst thing to do when bogged down in a complex problem is to bash away at it.

I was in Waitrose this afternoon, shopping for dinner and listening via my phone to the Goldberg variations (Bach is soo very conducive to thought. I have the Angela Hewitt recording, warmly recommended) when it suddenly struck me …. base the new report on the existing orders report and use sub-reports for the cancellations!

Now we are coming finally to the point of this posting – I have grown so used to following my preference for making views at the database end that the only time now I use sub-reports is when creating dashboards, where in effect the top-level report is a canvas and parameter input mechanism, and all the reporting is in small sub-reports. In all other situations I have a bias against the use of sub-reports. Normally this is not an issue and indeed makes the reports I write more efficient than using sub-reports, but on this occasion it is the right thing to do.

Next time I will talk about some data warehousing issues I am grappling with, or maybe integrating SalesForce data.

Jack

Comments Off on Developer bias
Categories: Crystal Reports