Archive for April, 2009

Crystal Reports XI and Open Text BI 8.5.1 pros and cons

April 23rd, 2009 2 comments

I’ll revise this post regularly based on my findings and experience.

Items in this list may only show my inability to perform a specific action with one solution, which at least means that there is a usability issue with it 🙂

Crystal Reports XI:

  • + Formula Editor
  • – No permanent data model: a new data model needs to be recreated each time I create a new report
  • – No ability to work on results and combine them together
  • No ability to aggregate on the server: I didn’t find for instance how to create the following statement: With the help of Paul’s comment, I was able to find how this works in Crystal: you need to turn the “Perfrom Grouping on Server”  option for both the report AND the general option. [cc lang=”sql”]select count(my_field) from my_table[/cc]

Open Text BI 8.5.1:

  • – Doesn’t support multiple outer joins with SQL ODBC connection
  • – Can’t report on a table named “users”  with SQL ODBC
  • + Can work with results and create complex “super queries”
Categories: BI thoughts, Tech tips Tags:

Real life experience: displaying DM access control list in Crystal Report

April 17th, 2009 1 comment

A good thing about consulting is that I’m now exposed to real life report requests and different technologies to create them.

This let me compare these technologies in situations outside of the “demo database boundaries”.

Here is an example of useful feature found in Crystal Report XI:

Crystal Report has a powerful formula workshop, which allows the creation of complex fields based on business rules that are not necessarily included in the database.

For instance, our Document Management system stores the access control information about a document in a single numeric field. Creating a report that shows “45”  or “63” as the only information about the access control is quite meaningless for a business user.

The only place I could find some information about the meaning of this numbers was in the DM database documentation:

The ACL is a combination of basic controls which each have a number (1, 2, 4, 8, 16, 32, 64, 128, 256 and 512): substract from the actual ACL number the list value just bellow it until you reach 0 in order to rebuild a list of these basic controls:

for instance, “45” = “32” + “8” + “4” + “1”, so an ACL value of “45” means that the specified user has the combination of these basic controls (1, 4, 8 and 32) on the document.

SQL doesn’t provide the ability to rebuild this kind of logic with its set of functions, and creating a calculated field in the database is not often an option.

Using the Crystal Reports editor, I easily added a formula with a simple VB code (I had no previous experience of VB):

[cc lang=”vb” theme=”default” width=”560″]
‘ Declaring array variables: aMsg for the actual basic controls, and aID for the control numbers.
‘ This is not really necessary, but I thought this would make the code cleaner,
‘ and somehow thought I could automate this loop a bit more 🙂
Dim aMsg(10) As String
aMsg(1) = ” View Profile |”

aMsg(10) = ” View Only Published |”

Dim aID(10) As Number
aID(1) = 1

aID(10) = 512

‘ Declaring the working variables:
‘ – the actual ACL for the document is initialized with the value from the database
‘ – the actual meaning of this number is initialized as a blank message.

Dim aCurrentID As Number
Dim aCurrentMsg As String
aCurrentMsg = “”

‘ In some odd cases, someone specifically “denies access”  to a document, which is a different logic.
‘ As this is quite uncommon in our set up,
‘ and as the logic is not described in the documentation, I avoid checking these values.

if aCurrentID > 1023 Then aCurrentMsg = “More investigation required – Deny applied” : aCurrentID = 0

‘ Start of the loop to rebuild the full access control list: the actual ACL value will be compared to the regular list
‘ and when it’s value is bellow a regular item, we add this regular control to the list, and decrease the ACL value
‘ until we reach 0.
‘ Note: the “:” separates the 2 actions in one line.

While aCurrentID > 0

if aCurrentID < aID(10) Then _

if aCurrentID < aID(1) Then _
aCurrentMsg = aCurrentMsg _
else _
aCurrentMsg = aCurrentMsg & aMsg(1) : aCurrentID = aCurrentID – aID(1) _

else _
aCurrentMsg = aCurrentMsg & aMsg(10) : aCurrentID = aCurrentID – aID(10)


‘ The resulting aCurrentMsg is the combination of all the controls included in the original ACL value
‘ and passed as the result of the formula
formula = aCurrentMsg

Categories: Tech tips Tags: