Reporting Outside of SimCorp Dimension

Crystal Reports has been part of the SimCorp Dimension platform for a great many years and is used by many for essential reporting. For some Crystal Reports developers, developing reports for SimCorp Dimension can be a little frustrating. Say I wanted to design a simple report in Crystal Reports and add the following SQL in a Crystal Reports Command instead of adding the individual tables to the report.

SELECT * FROM SECURITIES WHERE ISIN = 'DK0060495240'

Saving this report and trying to add the rpt file in SimCorp Dimension using the Report Files window will however produce the following error message.

Report verify failed: Unknown table : .COMMAND

Crystal Reports is so well integrated into SimCorp Dimension that the same report (rpt file) can be used for many different purposes by altering the search conditions and parameters in the Report Properties Settings, without changing the underlying report file. The Standard Reports that accompany SimCorp Dimension are a good example of generic reports that can be adapted by any client without using the Crystal Reports designer for any of the derivations. This of course requires a great deal of knowledge about the data dictionary and which tables and views are used within the report, in order to offer this level of integration and also the reason why the use of Commands do not work with SimCorp Dimension. 

Another issue when developing Crystal Reports is that tables and views have to be known to SimCorp Dimension before they can be reported on. Say I have an Oracle table with confirmed trades and that I have written a report that matches confirmed prices with trades in SimCorp Dimension. Adding the necessary Dimension tables and the external table with confirmed trades, is not a problem in Crystal Reports. The report works perfectly well from within the Crystal Reports designer, but as soon as I try to add this report, SimCorp Dimension will tell me that the external table is unknown.

It turns out that writing your own SQL and using external tables have the same solution and it is called the Report External Tables window in SimCorp Dimension. The scenario given in this post uses three different types of tables, a SimCorp Dimension Extract Table which I will call U_TRADES, TRANSMAIN in SCDAT and finally an external table which I will call X_TRADES in a schema outside of Dimension (that is, the schema has not been set up in the Database User Schemas Administration window).

Extract table U_TRADES

Column	Description
TRANSIK	TRANSIK extracted from TRANSMAIN
TRANSEX	TRANSEX extracted from TRANSMAIN

For this scenario, the above extract will have identified the trades we are interested in and saved these in the extract table U_TRADES.

External table X_TRADES

Column	Description
X_TRANSEX	Confirmed transaction number
X_TRAPRICE	Confirmed price

What I would like to achieve with my new report is to list all confirmed prices that deviate by 10% in an Excel spreadsheet using Crystal Reports. Adding TRANSMAIN and the extract table U_TRADES to my new report is straight forward, linking the tables on TRANSIK in both tables. Saving this report and adding the rpt file in SimCorp Dimension will work without further configuration. But we want to add an external table, X_TRADES, to our report and compare the confirmed price to the traded price.

Assuming you have added a new table called X_TRADES to a schema in Oracle, this table will now need to grant access to the user that will execute the report from within SimCorp Dimension. This user will always be the Oracle user SCDAT.

GRANT SELECT ON external.X_TRADES TO SCDAT WITH GRANT OPTION;

All I have to do now is add X_TRADES to the window Report External Tables in SimCorp Dimension which has two columns, Schema name and Table/View name. Assuming the schema is called external, this is entered in the Schema name column and X_TRADES in the Table/View name column and then simply save. 

To list the trades of interest, I have added the following code to Supress the Details section in Crystal Reports Section Expert.

If {TRANSMAIN.TRAPRICE} = 0 Or {X_TRADES.X_TRAPRICE} = 0 Then
   False
Else
(
   NumberVar PCT_Deviation := ({X_TRADES.X_TRAPRICE}-{TRANSMAIN.TRAPRICE})/{TRANSMAIN.TRAPRICE}*100;
   If Abs(PCT_Deviation) >= 10 Then
      False
   Else
      True
)

False meaning “don’t suppress” and True meaning “suppress” the Detail section, only showing the lines where the deviation is 10% or more or the prices are zero.

The above should now be ready to add and save as a Report File in SimCorp Dimension without any issues. While adding the Report Properties, change the Report Output to File and add a Reference File. Change the Output format to Excel. Both the Data Extraction and Crystal Reports creating the Excel Spreadsheet can now be added to a Batch Job Group and scheduled to run at the required time. 

If you wish to write your own SQL and use this in SimCorp Dimension as if you had written the SQL in a Crystal Reports Command, simply add an Oracle view with your SQL and then add the view to the Report External Tables window. The view will also require granting select to Oracle user SCDAT in the same manner that privileges were granted above to select from external table X_TRADES.