A good thing about consulting is that I’m now exposed to real life report requests and different technologies to create them.<\/p>\n
This let me compare these technologies in situations outside of the “demo database boundaries”.<\/p>\n
Here is an example of useful feature found in Crystal Report XI:<\/p>\n
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.<\/p>\n
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”\u00a0 or “63” as the only information about the access control is quite meaningless for a business user.<\/p>\n
The only place I could find some information about the meaning of this numbers was in the DM database documentation:<\/p>\n
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:<\/p>\n
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.<\/p>\n
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.<\/p>\n
Using the Crystal Reports editor, I easily added a formula with a simple VB code (I had no previous experience of VB):<\/p>\n
[cc lang=”vb” theme=”default” width=”560″]
\n‘ Declaring array variables: aMsg for the actual basic controls, and aID for the control numbers.
\n‘ This is not really necessary, but I thought this would make the code cleaner,
\n‘ and somehow thought I could automate this loop a bit more \ud83d\ude42
\nDim aMsg(10) As String
\naMsg(1) = ” View Profile |”
\n…
\naMsg(10) = ” View Only Published |”<\/p>\n
Dim aID(10) As Number
\naID(1) = 1
\n…
\naID(10) = 512<\/p>\n
‘ Declaring the working variables:
\n‘ – the actual ACL for the document is initialized with the value from the database
\n‘ – the actual meaning of this number is initialized as a blank message.<\/p>\n
Dim aCurrentID As Number
\naCurrentID = {SECURITY.ACCESSRIGHTS}
\nDim aCurrentMsg As String
\naCurrentMsg = “”<\/p>\n
‘ In some odd cases, someone specifically “denies access”\u00a0 to a document, which is a different logic.
\n‘ As this is quite uncommon in our set up,
\n‘ and as the logic is not described in the documentation, I avoid checking these values.<\/p>\n
if aCurrentID > 1023 Then aCurrentMsg = “More investigation required – Deny applied” : aCurrentID = 0<\/p>\n
‘ Start of the loop to rebuild the full access control list: the actual ACL value will be compared to the regular list
\n‘ and when it’s value is bellow a regular item, we add this regular control to the list, and decrease the ACL value
\n‘ until we reach 0.
\n‘ Note: the “:” separates the 2 actions in one line.<\/p>\n
While aCurrentID > 0<\/p>\n
if aCurrentID < aID(10) Then _
\n…
\nif aCurrentID < aID(1) Then _
\naCurrentMsg = aCurrentMsg _
\nelse _
\naCurrentMsg = aCurrentMsg & aMsg(1) : aCurrentID = aCurrentID – aID(1) _
\n…
\nelse _
\naCurrentMsg = aCurrentMsg & aMsg(10) : aCurrentID = aCurrentID – aID(10)<\/p>\n
wend<\/p>\n
‘ The resulting aCurrentMsg is the combination of all the controls included in the original ACL value
\n‘ and passed as the result of the formula
\nformula = aCurrentMsg
\n[\/cc]<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5],"tags":[],"class_list":["post-48","post","type-post","status-publish","format-standard","hentry","category-tech-tips"],"yoast_head":"\n