BusinessObject: Query Current and Archive Tables in Single Report

Product: BusinessObject
Module: Crystal Report designer

Consultant, including myself, often design archiving solution to move several years old OLTP data to archiving table, or even to different archiving database.

It becomes a challenge for report designer to pull the data from 2 different tables, or databases. This post will indicate how to design a single rpt report to get the data from 2 tables automatically

1. Create a sub-report to query archive table, which keeps data older than 1 year
2. Create a parameter called "dateQuery"
3. In main report, modify Conditional Suppression formula to hide this year's data if dateQuery is older than 1 year

If DateDiff ("m", {?dateQuery}, CurrentDate ) > 12 Then True

4. 3. In sub-report, modify Conditional Suppression formula to hide old data if dateQuery is this year

If DateDiff ("m", {?dateQuery}, CurrentDate ) <= 12 Then True

This is another solution instead of design another report to query old data. Nonetheless, it is not a bad idea to create separate report to prevent user abusing the report, such as extracting 5 years of data, which severe degrade database, hard disk, and or even congested the network

BusinessObject: Adjust Width of List Box Prompt

Product: BusinessObject
Module: Crystal Report designer

File: C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\Crystal Reports 2011\crystalreportviewers\prompting\css\promptengine2.css

In the prompt menu, all the List Box for filtering have fix width. It is possible to adjust this file to make it wider.

This is a global setting, so you can't control a specific List Box size in specific report. Moreover, if the width is too large (say 1920px), the right side of the screen will not be visible. There could be a "height: 100px" that can be added in, but I never try

.pePromptTextBox

{

    font-size: 8pt;

    background-color: #FFFFFF;

    font-family: Tahoma, verdana;

    width: 300px;

}

BusinessObject: Dynamic Timezone Conversion

Product: BusinessObject
Module: Crystal Report designer

I have see many people posting about timezone conversion related questions, and found many people are proposing a static timezone conversion, e.g. -1 hour for EST and CST.

This solution does not work for people in none USA timezone, for example, server is in USA, but user who view the report is in London, where both countries have different DST

Following solution auto detect server and user timezone, and adjust the time accordingly, instead of using hard coded time different. For illustration purpose, build-in function CurrentDateTime is used, but it can be replaced with column name to convert the time to correct timezone

1. Convert Crystal Report server timezone to user's timezone

ShiftDateTime ( CurrentDateTime, PrintTimeZone, CurrentCEUserTimeZone)

2. Convert UK timezone to Sweden's, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0,BST", "CET,-60,CEST")

3. Convert UTC time to server's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0", PrintTimeZone)

4. Convert UTC time to user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0",  CurrentCEUserTimeZone )

5. Convert server's timezone to UTC time

ShiftDateTime ( CurrentDateTime, PrintTimeZone, "UTC,0")

6. Convert user's timezone to UTC time

ShiftDateTime ( CurrentDateTime, CurrentCEUserTimeZone, "UTC,0")

7. Convert US Eastern timezone to Pacific timezone, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "EST,0,EDT", "PST,60,PDT")

8. Convert UTC time to Eastern timezone, regardless of user and user's timezone

ShiftDateTime ( CurrentDateTime, "UTC,0", "PST,480,PDT")

9. Convert based on timezone defined in user table stored in database

This make use of Oracle functions instead of Crystal Report. So create a SQL Expression in Crystal Report with following value

( Select From_TZ( OrderTimestamp , GlobalDataTimeZone) AT Time Zone TargetDataTimeZone As TzValue From Dual )


10. Same as above, but use client's timezone

( Select From_TZ( OrderTimestamp , GlobalDataTimeZone) AT Time Zone SESSIONTIMEZONE As TzValue From Dual )



Note on Syntax:

ShiftDateTime ( [inputDateTime], [fromTimezone] , [toTimezone] )

where fromTimezone and toTimezone has following syntax

"Standard, Offset, DST, [offset in minute];[DST start date]/[and time],[DST end date]/[and time]"

"" (empty string) will use server's timezone defined in OS

CurrentCEUserTimeZone

Return the timezone (string) of the user viewing the report

PrintTimeZone

Return the timezone (string) of the BusinessObject server

The time zone string must be valid Crystal Report value. I recommend to use CurrentCEUserTimeZone and PrintTimeZone function to see the value on the report while designing the report, and change the OS's timezone to test it

SAP BusinessObject: Hide Group Header on 2nd Page Onward

Product: SAP BusinessObject
Module: Crystal Report designer

It is possible to hide the group header by using condition. Following step will configure the report so that in first page of report, it will display the group header, but on second page onward, it will hide it

1. Split the group header
2. On 1st group header, specify condition InRepeatedGroupHeader
3. On 2nd group header, specify condition NOT( InRepeatedGroupHeader )

End of post

Crystal Report: Default input directory

Product: BusinessObject XI
AKA: Crystal Report
Module: Central Management Server (CMS)

It is often to see following file name in CMS web UI about the Crystal Report files. If you wonder where is the exact file location, copy out all the rpt, or change its location. This post will show you the detail

For example, rpt report "Interactive Parameters" stores in location "frs://Input/a_178/003/..." So where is so call Input directory? It is a parameter name in CMS which is configurable to any directory.

Default Directory: C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\FileStore\Input\

Login to CMS web portal with a username who has privilege to view the server configuration (if just need to view, and not to change).

Change the drop down list box on top to "Servers" to display server's configuration

On left panel, click on Service Categories - Core Services, and look for server name containing name ".InputFileRepository." The column "Kind" will show "File Repository Server." You will see 2 entries, one for "Input" and one for "Output"

 Double click on entry with ".InputFileRepository" to view its parameters

Placeholder %DefaultInputFRSDir% (I called it parameter) is the location of frs://Input, while %DefaultOutputFRSDir% is frs://Output.

You can create additional place holder and used in report export purpose, so that end users does not need to know the exact location. This simplify them if there is a storage upgrade, or directory re-org. It will be invisible to them during such migration/upgrade/re-org

During report scheduling, following default output destination is referring to parameter %DefaultOutputFRSDir% configured above. Change it if you need to

End users can specify their own custom output directory as needed. Following screenshot, I am reusing existing parameter Output, but adding 2 sub-directory called HR, and 2012-02. This is another simpler approach to organized the files without adding parameter (place holder)

Solving Missing Value in Crystal Report Parameter Screen

Product: BusinessObject XI
Problem description:
In parameter screen, any dynamic parameter which contains value longer than 5,000 rows will only show the first 5,000

Root cause:
BusinessObject default to retrieve first 5,000 records for the dynamic parameter. This is to eliminate major impact to database server. This configuration is a global value that user configurable in Windows Registry

Solution:
Launch Registry Editor (regedit.exe), and navigate to following
HKEY_LOCAL_MACHINE - SOFTWARE - Business Objects - Suite 12.0 - Crystal Reports - DatabaseOptions - LOV

Change following 2 keys to max number of records that applicable. I don't recommend to put in more than 10,000 as it is not practical to let user select 10,000 values in parameter screen

MaxRecordsToRead - Default is 5,000
MaxRowsetRecords - Default is 5,000

If the program still running, terminate and launch again (such as SAP BO Windows service). The program only read the registry once when it starts up, but not every time you refresh the report. This applies to both designer and web interface. Less experience people will tell you to reboot the PC, BOE server, web server, any every piece of software. Don't do that if you are wise

Crystal Report: Reasons for "Encapsulation Page Error"

Product: BusinessObject Crystal Report

Following are possible cause for "Encapsulating Page Failed"

1. If uses sub-report, ensure sub-report's database authentication is correct
2. If rpt file is from other database environment, or not from original designer, open the rpt with Crystal Report designer, then execute it to identify any database related error. Possible errors
2.1. View does not exists
2.2. Column does not exists
2.3. Database function does not exists
2.4. No privilege to access table or view which get false alarm that view or table does not exists
2.5. Database account locked, or password expired
2.6. Other database related error
3. If using NTFS, ensure BusinessObject installation directory has proper privilege. Re-install the product if can't identify the exact NTFS privilege
4. Bug in Crystal Report server. Install the latest Service Pack to the server, not to Crystal Report designer

Please use following PayPal donate if my post helped

Solving Missing Value in Crystal Report Parameter Screen

Product: BusinessObject XI
Problem description:
In parameter screen, any dynamic parameter which contains value longer than 5,000 rows will only show the first 5,000

Root cause:
BusinessObject default to retrieve first 5,000 records for the dynamic parameter. This is to eliminate major impact to database server. This configuration is a global value that user configurable in Windows Registry

Solution:
Launch Registry Editor (regedit.exe), and navigate to following
HKEY_LOCAL_MACHINE - SOFTWARE - Business Objects - Suite 12.0 - Crystal Reports - DatabaseOptions - LOV

Change following 2 keys to max number of records that applicable. I don't recommend to put in more than 10,000 as it is not practical to let user select 10,000 values in parameter screen

MaxRecordsToRead - Default is 5,000
MaxRowsetRecords - Default is 5,000

If using Crystal Report CMC, then bounce Crystal Report server to activate these configuration

BusinessObject: Sync up Data Foundation with Database

Product: BusinessObject
Sub-product: Business View Manager

I alter table and view in database level. Use following procedure to sync up in Business View Manager

1. Open "Data Foundation" which contains the table or view
2. From pull down menu Tools - Verify Database
3. It will download column definition from database, and automatically add or remove columns

BusinessObject: Login to CMC with custom port

Product: BusinessObject

Normally when you login to Central Management Console, no port number is specified, but just the hostname, or IP address. This will default to port 6400 (protocol TCP for firewall purpose)

However, it is possible to specify hostname and port number as shown below. In other word, it is possible to install multiple instance of CMC server, which points to different repository database, or single database with multiple database schema/username (simplified database management) for non-PROD or research purpose.

In project development, keeping the cost down by hiring less DBA, VMware admin, Windows admin, UNIX admin is one of the main saving in human resource budgeting.

Although virtual server (VMware, Oracle VM) often be used for multiple non-PROD environment, they are indirectly require additional maintenance. For example, 6 environment of DEV1, DEV2, QA1, QA2, UAT1, UAT2, there will be 6 Unix to patch, 6 times Unix user management, 6 databases, 6 Crystal Reports, etc.

So if it is not necessary, keep it simple, especially for databases where there are a lot of developers, and users has no clue about its design, maintenance, recovery, tuning, patching, known bugs, etc

BusinessObject Crystal Report Firewall Configuration

Product: BusinessObject

Open following ports in firewall in order for Crystal Report Designer, Business View Manager, or any thick client to connects to Central Management Console

If using web interface, then only need port 80 of the web server

1. Protocol TCP port 6400

Note: Needs to go to web portal of Central Management Console, and configure fix port
1. Select "Servers" from drop down list box on upper left hand corner
2. Click "Servers List" on left panel, look for "Kind" name "Central Management Server"
3. Double click on it, and it will open Properties window
4. Scroll down to section "Common Settings"
5. Disable "Auto Assign" checkbox
6. Fill in "Request Port" as 6400
7. Click "Save and Close" button

There are other ports if you are using following
Web Application Server port, protocol TCP port 8080. Recommend to set to port 80 due to common corporate security rule
Server Intelligence Agent (SIA) port, protocol TCP port 6410


Note: Any port above is default value. It is configurable to other port

For load balancing, provide virtual hostname to Crystal Report designers, Business View Manager users, as well as Tomcat web server to connect to CMC. In firewall, open the port base on virtual IP, and physical IP.

Although open access to physical IP is optional, it is critical for technical support team to identify the problem, and shorten the time-to-resolution by 99%

Please use following PayPal donate if my post helped

Genesys with BusinessObject (Crystal Report) Business View Filter

Product #1: Genesys CCA

Product #2: SAP BusinessObject

When using Crystal Report (BusinessObject designer) to design report for Genesys CCA (DataMart), Business View Manager is a common software that will be used for advance customization.

This screen shows how to enable filtering in Business Element called "O_Queue_No_Agg be"

My advice is not to create filter in Data Foundation layer, but in Business Element layer. Keep all the filter and parameter definition in this layer, which will be inherited by Business View, List of Values, and Prompt Groups

Please note that by default, filter's right is "Not Applied." Designer needs to modify the right to apply to Everyone, or fine grain user/group control.

There is no way to use Business View Manager nor Crystal Reports designer to see the generated SQL, nor value with the filter applied. The trick I used is to open Business Element, and choose "Right Test View" from Tools pull down menu. It will display Crystal syntax for it.

In my example, I only want to show virtual queue that has naming convention VQ in front of the queue name.

For anyone who is using my design, please create index on O_Queue_No_Agg table on column Object_Name for performance tuning.

Crystal Report - Retrieve report name from database

Product: Crystal Report XI

BusinessObject stores its table as binary data type. However, they provided a web interface called Query Builder which can used to query the API-base virtual table

URL: http://crystalreport_cmc_web_server/AdminTools

Login as the same ID as Central Management Console

To display Crystal Report schedule job going to be executed, enter following query into the text box


SELECT SI_NAME, SI_NEXTRUNTIME, SI_SCHEDULEINFO.SI_STARTTIME, SI_PROCESSINFO.SI_RECORD_FORMULA
FROM CI_INFOOBJECTS WHERE SI_RECURRING=1

Most of the forums only show programming API to access property of above virtual table. In my example, I am showing that even using SQL, it is possible to extract the next level object. The limitation is still joining this virtual table where API will be more convenient

Use following query to map file names stored in frs://Input to report name. Please be aware of any duplicate name


SELECT SI_ID, SI_CUID, SI_KIND, SI_NAME, SI_FILES FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_NAME like '%Agent Performance - Daily Common%'

Other useful queries

Get Full Client Reports 

SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( 'webi' , 'CrystalReport') 

Get All reports 

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report' And SI_INSTANCE=0 

Get All Webi reports 

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Webi' And SI_INSTANCE=0 

Get All universes 

Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND ='Universe' 

Get All Users 

SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER' 

get all personal categories 

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='PersonalCategory'

Get all groups 

Select * from CI_SYSTEMOBJECTS Where SI_KIND='UserGroup' 

get all folders 

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder' 

get all categories 

select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='Category' 

Tag: reverse lookup CMS report name, BO AdminTool tips

Crystal Report - Delete unused dynamic parameter group

Product: Crystal Report 2008 - XI

Whenever dynamic parameter is used with new parameter defined, Crystal Report automatically creates following
1. Data Foundation - Visible in Business View Manager
2. Business Element - Visible in Business View Manager
3. Business View - Visible in Business View Manager
4. List of Values - Visible in Crystal Report
5. Prompt Group - Visible in Crystal Report

Note: "Visible" above means without using "Repository Explorer," above objects are usable from File - Open (Business View Manager), or parameter selection (Crystal Report)

Therefore, after report design finalize, a lot of unused objects will left behind. This post shows the tool and step used to remove any object above, but I use prompt group as an example

For this example, I use new dynamic view for table called Budget with parameter filter on department. Following are automatically created (this is the 2nd report using Budget table, so it automatically added "_1" to all the name)
1. Data Connection - Budget DC_1
2. Data Foundation - Budget DF_1
3. Business Element - Budget BE_1
4. Business View - Budget BV_1
5. List of Values - Budget > Department_1
6. Prompt Group - Budget > Department_1 - Prompt Group_1

As you can see above, if I have daily, weekly, monthly, quarterly, and yearly tables, the naming convention above will be very confusing to be reuse.

Following these steps to delete unwanted (or rename) them:

1. In Crystal Report designer, open the prompt group to find out the List-of-Value (LOV) name

Launch Business View Manager, and login to Crystal Report server as Administrator (any user who has privilege)
Open Repository Explorer from pull down menu View - Repository Explorer
Repository Explorer will show up on right panel, unless you have moved it to somewhere else
Inside Repository Explorer, open the folder where above objects store. In my case, I use folder name "Custom Daily"
Select prompt name "Budget Prompt Prompt Group 2"
On toolbar, click on X to delete
Close any open window which accessing the object. Business View Manager will show an error if the object is currently open. Inform everyone to close

Although Crystal Report (designer interface) can see Repository Explorer, it can't delete these objects .

Automated creation of these object has automated naming convention which is confusing and not easily reusable for other report design.

Therefore, for cleaner and proper report design, it is best to delete unused objects, as well as rename them.

This procedure can be used to rename the objects as well by right click on them, and click Rename

If the prompt has been remove and created again, this will be the prompt group name. You can see it is very messy

Budget > Department_1 - Department_1 - Prompt Group_1

Moreover, this name needs to update in the select formula which is affecting the readability, especially during troubleshooting

Please leave me a positive comment if this post help

Please use following PayPal donate if my post helped

Syncing thousands of files across multiple destination in UNIX

Product: UNIX, Linux

UNIX file system, regardless of which type, most of them do not have self defrag feature, or it is an extra license plus configuration (re-format, re-mount) to use it. This post indicated a free and relatively quick way to defrag, and improve the file system respond time

Some applications that created 100,000 of files size 4 GB, and needs to move out to its dedicated mount point.  "ls" command alone takes 3 min to return, and tar-untar takes 45 min to complete.

The challenge is how to complete the migration within 30 min of downtime.

Assumption, there are files that are old.  Time stamp can be use to indicate whether time is changed

Solution

1. Copy current files to new destination: (cd /dir2; tar cvf - /dir1 | tar xvf -)
2. Find out files changes in last half day, if supported, or 1 day and save it. This will reduce time require to copy new files /tmp/newfile.txt: find /dir1 -mtime -0.5 (only in Linux)
3. There are 2 approaches to sync up new files now
4. Option 1: use "for F in $(cat /tmp/newfile.txt); do G=$(basename $F); if [ ! -f /dir2/$G ]; then cp $F /dir2; fi; done
5. Option 2: Use rsync and supply /tmp/newfile.txt to sync between /dir1 and /dir2

This approach can optimize file system which gives a slow respond time when executing "ls" command. It is near impossible, or very time consuming to identify it is a fragmented inode (technical word). It could applies to database, CRM, Genesys log, Crystal Reports output directory, any html location, web servers with many scripts or logs, etc