Thursday, 18 April 2013

Roll Up Roll Up ... Why Crystal Reports for Enterprise Loves the Universe

With previous versions of the Crystal Reports integration with the Business Object universe your universe query was converted into an SQL command, a purely SQL object. This approach was an arranged marriage, required to enable the houses of Business Objects and Crystal to join together and rule business intelligence as one.

Over time and under the tutelage of supreme ruler SAP, the two technologies have developed a special kind of love which has been rewarded with shiny new editions conceived and built to work together.

With the SAP BusinessObjects BI4 Information Design Tool (easy for you to say…), universes now sit firmly in the current century with multi-source, modular components and modern user interface. Crystal Reports for Enterprise also appears with the BI4 release. It has very similar functionality to Crystal Reports 2011 in most areas, but delivered within a more modern user interface, designed to be more intuitive.

A definite functional difference with CR4E vs CR2011 comes when you query a universe and the way Crystal rolls up the data.

Where Crystal traditionally returns every row of data, one of the clever things that Web Intelligence has always done is roll up / collapse the data to a short list of distinct entries. So for example, in the xtreme database (the Crystal sample db) orders table there are 2192 transactions/rows, each with one of six couriers in the ‘ship via’ column. Querying this column in Crystal 2011 either directly against the database or via a universe will return 2192 rows in the report. Querying it with CR4E via a BI4 universe will return six rows. CR4E now effectively applies a group by clause to the display data based on the universe dimension objects displayed in the report, just like Web Intelligence always has. It’s what Business Objects calls semantically dynamic.

Whilst report designers experienced with SQL may want the flexibility of controlling roll up themselves via Crystal grouping (which still exists in CR4E), the CR2011 behaviour of repeating distinct values in this way can be confusing for non-technical users. If someone selects ‘Courier’ they would naturally expect to see a list of all couriers; seeing them repeated over multiple rows is confusing.

There are also many cosmetic differences targeting CR4E at less technical users, for example:

  • Crystal beginners used to get confused between 'suppress' and 'hide' - what was 'suppress' is now 'hide'; what was 'hide' is now 'show on drill down only'.  (Sounds confusing but makes sense when you use the tool).
  • Sections also confuse beginners, 'details' is now called 'body'.
  • In CR2011, sums are inserted by default in the group footer, whilst the group name is in the group header, making the two hard to correlate.  With CR4E you are promoted with a plain English 'sum above or below data?' option.
And finally, a health warning:  Crystal Reports for Enterprise (CR4E) only supports the new .unx flavour of universe, so don't get caught out!

Written by:  Angus Menter, Business Intelligence Project Manager, DSCallards.
For more information, visit

No comments:

Post a Comment