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:

Date functions

March 15th, 2009 3 comments

Almost every time I write a report, I face some date formating and calculation issues.

For once, dates stored in databases usually are in full “date” format, including year, month, day, hour, minutes and seconds, and are associated with a single action or event, such as creation, update, etc…

Then, the reports usually need the information aggregated by month, quarter and/or year, and often with a sliding begin and end date (i.e. count of the number of logged calls per month for the last 6 months).

On to top of this, the dates are not necessarily following a calendar year but often follows a fiscal year instead, and the reporting tools do not feature an easy way to provide “today’s fiscal quarter” for instance.

In order to help with this, I created 3 simple SQL Server user based functions:

The first function returns the fiscal year from any date (including today()):

[cc lang=”tsql” width=”560″]
CREATE FUNCTION FYear (@input_date varchar(255))
DECLARE @CYear int
DECLARE @offset int
SELECT @CYear = Year( @input_date )
IF ( (CAST(YEAR(@input_date) as varchar(2))+ ‘/’ + (CAST((MONTH(@input_date)) as varchar(2)) + ‘/’ + CAST((DAY(@input_date)) as varchar(2)))) > (CAST(YEAR(@input_date) as varchar(2)) + ’06/30/’))
SELECT @offset =1
SELECT @offset = 0
RETURN (@CYear + @offset)

Simply replace ’06/30/’ in the code by the end of your fiscal year, and this function will compare the date with the end of the fiscal year and return the current year or the current year + 1 based on that end.

The second function returns the fiscal quarter of a date:

[cc lang=”tsql” width=”560″]
CREATE FUNCTION FQuarter (@input_date varchar(255))
RETURNS varchar(255) AS
DECLARE @FQuarter varchar (255)
IF (Month(@input_date) < 4)
SELECT @FQuarter =  ‘Q3’
IF (Month(@input_date) < 7)
SELECT @FQuarter =   ‘Q4’
IF (Month(@input_date) < 10)
SELECT @FQuarter =   ‘Q1’
SELECT @FQuarter =  ‘Q2’
RETURN @FQuarter

In this one, simply replace the ‘4’, ‘7’ and ’10’ values by the actual end of your fiscal quarters in order to assign the proper Qx to the result.

The last function returns a formated date as a string with a “YYY/MM/DD” format instead of a date field:

[cc lang=”tsql” width=”560″]
CREATE FUNCTION castyear (@input_date varchar(255))
RETURNS varchar(255) AS
DECLARE @CYear varchar(255)
SELECT @CYear = (CAST(YEAR(@input_date) as varchar(4)) + ‘/’ + CAST(MONTH(@input_date) as varchar(2)) + ‘/’ + CAST(DAY(@input_date) as varchar(2)) )

Adding these function as user defined functions with proper access right to the database users can be useful to further chop the dates and easily extract the month and day of a date in order to create calculated fields and aggregate other facts based on dates.

Categories: Tech tips Tags:

Asking the right questions

February 6th, 2009 No comments
  • english
  • french

Following up on the BI strategy, here is a non exhaustive list of questions to ask before planning and implementing a BI project:

1 – identify the business needs:

  • What is the business of this organization, and what is the purpose of their reporting needs?
  • What kind of reports are needed:
    • Decision support?
    • Performance management?
    • Dashboards and Scorecards?
    • Customer oriented, financial?
    • System analysis?
    • Data Quality?
    • Enterprise BI?
    • Data Warehousing?
    • Master Data Management?
    • Predictive Analysis?
  • Are the needs local to a department or general?
  • Are the needs reproducible or would they be generated on an Ad-Hoc basis?
  • How will reports be used:
    • Embedded and read in another document?
    • Printed and archived for historical purpose?
    • Accessed on line (intranet or extranet)?
    • Accessed on a mobile device?
    • Presented during a projection or in a book?
    • Scheduled on a time or event based frequency?
    • A mix of the above?
  • What will the retention of the report be, should historical reports be kept?
  • Can we define each reports or groups of reports:
    • What is the purpose of the report?
    • What are the metrics of the report?
    • How should the report be sorted, ranked, filtered?
    • In what format should the report be presented: tabular, chart, cross tab, a combination?
    • Are there calculations involved? Exceptions highlighting? What are the business rules for these?
    • What are the selection criteria for this report? Are these criteria pervasive across reports?
  • Who should access the reports, who needs them?

2 – What are the technical constraints?

  • What is the budget for the BI project?
  • What is the time line for the BI project?
  • What kind of security applies to the content of the reports or system and how is this security handled and applied: at the system, the database or the report level?
  • Where is the data stored?
    • Is is easily available?
    • Is there a test environment?
    • Is it a multi-sources report?
    • Are some of the content not available in databases?
    • Are some of the content stored in proprietary systems (such as ERP, etc…)?
  • How clean is the available date, would there be any data cleansing required?
  • Is the data “report ready”, or are transformation or transfer required and possible?
  • Is the operational system accessible, or should the data be duplicated? How often, how “fresh” should the data be? Are there any concerns in accessing the operational database?
  • Metadata: are they defined, needed, accessible?
  • Are there non structured information involved?
  • Is there a corporate graphic chart to follow (for portrait, landscape, web reports)?
  • Are there some technical constraints: platform, OS (client and server), web browser, Database, network, security, login, hardware (RAM, CPU, etc..)?
  • What languages should the reporting project be available in?
  • What training is planned or scheduled?
  • Are there other existing similar projects we could rely on or improve?


Categories: BI thoughts Tags:

Building a top down BI Strategy

January 21st, 2009 No comments
  • english
  • french

Planning, implementing and maintaining a BI project is not an easy task.
Ever since companies started 20 years ago with “Decision Support Systems” and “Data Warehouses” projects, one simple advice has always been the most important of all: use the 80-20 rule for your project time:
80% of the project’s time should be spent understanding the end users needs to defining the project, and the remaining 20% should be used to implement the project.
Most failures in BI projects are due to a nice (and costly) tool that doesn’t quite answer the end users needs and eventually adds more to their workload than helping them making good decisions, and eventually is not used.

And by needs, I mean real needs, not just what they want:

If you ask someone what car they want, chances are that they will describe you their dream car, a car that they will probably never be able to afford! But when you ask them what they really need, the dream car disappears to be replaced by one that is just pragmatic, with 4 wheels, and engine and seats for everyone!

The same applies to BI projects while defining it. Some user would dream about a system that would magically take decisions for them, automatically gathering heterogeneous information from everywhere.
But their real needs might not be so fancy, and some indicators and an easy access to the database information, with the insurance of the quality of that information is often all they need.
This is what a good BI project should provide.

Categories: BI thoughts Tags:

A little bit about Pierrekiroule Consulting…

January 15th, 2009 1 comment
  • english
  • french

Bilingual (French / English), I have spent more than 10 years working in Europe and North America with Business Intelligence (BI) and Enterprise Content Management software editors.

My main approach to solving problems is pragmatic: get to know the end users, their real issues and what they need prior to implement the right solution and the right tool for them.

As I do not represent a vendor, I can find the right solution for the customer.

My top 3 work values are:

  • Creativity
  • Services to help solving problems
  • Interpersonal relations

My top 3 soft skills are:

  • Designing
  • Improving
  • Presenting


Categories: Uncategorized Tags:


January 5th, 2009 No comments
  • english
  • french

Welcome to Pierrekiroule Consulting!

I share here with you my experience and findings about Business Intelligence (BI), Document Management, or other thoughts, findings and experiences.

To contact me, please send me an email at: consultant (at) pierrekiroule (dot) com

You can check my Linkedin resume and connect to me here (link opens a new window).

Categories: Uncategorized Tags: