FAQs

How Do I Manage CDD Report File Storage on the 7i Server?

Details: KB ID 6544
CDD Reports run in 7i are created as XML files. One file is created for the report request and one file is created for each page in the report.

Extended Text:
Details: CDD reports in 7i are created as XML files that are stored on the 7i server. The files are placed in a directory designated in the registry on 7i server. Specifically, the registry key that holds the storage location information is:
HKEY_LOCAL_MACHINE\SOFTWARE\BiTech\Report Processing Service\FileStoragePath

The value in this registry key is the directory on the 7i server where the CDD report files are stored. The directory displays a set of folders with names like "{FA616E8A-1A5C-4673-BAE9-39D407F616CB}." These directories correspond directly to the report that is executed in 7i. The URL in the report output window in 7i includes both a Storage and Report tag.

The Report tag is equal to the Folder name where the report files are stored.

For example, for this report output URL:
http:||ifasfarm/ifas7/CDD/ReportFrame.aspx?Report={C097DC7D-AF32-4A06-B293-CF9D9CF24E0A}&Storage=FARM1&Page=1

The 7i server is "ifasfarm" and the report GUID is "{C097DC7D-AF32-4A06-B293-CF9D9CF24E0A}." Given that information, plus the value of FileStoragePath on the 7i server, you can then open the directory that corresponds to this specific report and review the associated data files.  For each report folder, there are two distinct file classes. One class, called "request.xml" consists of the report request that was received by 7i.  This includes information such as the user's BusinessPlus ID, Ledger preference, Connection name, as well as the report specific information such as report ID and selection criteria prompt name and values (if any).  In addition to the request.xml file there are one or more .xml files called "report_n.xml," where the "_n" is the number that corresponds to the page in the CDD report. For example, report99.xml is the data file that corresponds to page 99 of the CDD report. These report page files contain all the data and layout information required for 7i to display a page in a CDD report.

Additional Information: Often times CDD report troubleshooting can be aided by reviewing the folders and files in the FileStoragePath on the 7i server. You can confirm that the directory is being created. You can confirm that the report request is being created and you can confirm the values in the request file. You can also review individual pages for content, including fields that have report or images drills attached. Review other related KB articles for more information about troubleshooting CDD reports, both thick client and 7i server (thin client).

What Registry Settings Are Available for CDD?

Details: KB ID 3472
Where can I get a list of obscure CDD Registry values? (FooterOnEof , DefaultFaceName , DefaultPoint , FaceName , MaxDisplayLen , MaxReports , MaxDesigns , MaxCategories , Optimize , Explain)

Extended Text:
Obscure CDD Registry Values
There are some CDD settings that can be affected by registry entries that don't necessarily appear in the Preferences information.  Remember that it can be very dangerous to make changes to the Windows registry. If you are unsure about making these changes please contact the IS department at your site. The following are all found in the HKCU\Software\BiTech\CDD section of the registry.
Report Execution
Config\FooterOnEof - DWORD Value
Description: Forces Page footer at the end of report. Theoretically this should be the default, but this setting can be used to force that behavior.
values:
0 (default) = Don't force
1 = Force Footer
NOTE: To force it in 7i, the same path applies but use HKEY_LOCAL_MACHINE instead of HKCU

Report Design:
Font\DefaultFaceName - String Value
Description: Changes the default font name to use when creating new CDD reports. Must be a valid windows font name.
Values:
"Times New Roman" (Default)
Font\DefaultPoint - DWORD Value
Description: Change the default font size to use when creating new CDD reports.
Values:
10 (Default)

Macro Editor:
Editor\FaceName - String Value
Description: The font name to be used in the Macro Editor. Must be a valid windows font name.
Values:
"Courier New" (default)
*Note: Font size is controllable in the preferences
Field Size
Settings\MaxDisplayLen - DWORD
Description: Changes the size limit for Report Fields (User Defined-Fields, etc.) that can be set in the property page. Note: this merely controls the validation done by the property page.
Recent Files
RecentFiles\MaxReports - DWORD
Description: Changes the number of items that will be recorded in the "Recent Reports" menu.
Values:
Default value is based on the Windows "Most Recent File" setting. (probably 4)
RecentFiles\MaxDesigns - DWORD
Description: Changes the number of items that will be recorded in the "Recent Designs" menu.
Values:
Default value is based on the Windows "Most Recent File" setting. (probably 4)
RecentFiles\MaxCategories - DWORD
Description: Changes the number of items that will be recorded in the "Recent Categories" menu.
Values:
Default value is based on the Windows "Most Recent File" setting. (probably 4)
Database
Config\Optimize - String Value
Description: Sets the optimizer statement for Informix sessions. Please consult your site's DBA before using these settings.
Values:
"NOT_SET" (Default) = No optimizer value to be used
"HIGH" = Send "set optimization high" to informix
"LOW" = Send "set optimizer low" to informix
Config\Explain - DWORD Value
Description: Turns on tracing for the CDD connection. For Oracle it uses "ALTER SESSION SET SQL_TRACE TRUE" and for Informix it uses "set explain on."  This has no effect on SQL Server. Note: Informix setexplain.out file will probably be build in the \openlink\bin directory on the server.
Values:
0 (Default) = No tracing
1 = Start tracing when report runs

How Do I Enable SQL Tracing in CDD?

Extended Text: KB ID 3615
The following document applies to CDD versions 7.2.3.145 and 7.3.1.231 and later. This feature requires that you not only be able to modify the registry but that you also have access to telnet to the UNIX host in order to retrieve the information. SQL tracing is only recommended as a trouble-shooting mechanism and should not be left on long term due to performance and UNIX file system implications.  Over the years there have been a number of creative methods of enabling tracing for CDD reports. The following is the recommended method of enabling SQL tracing of the Report Query in CDD.

Enabling Tracing:
In the registry locate the following registry keys:
On the 7i Server: HKLM\Software\Bitech\CDD\Config
On the Client PC: HKCU\Software\Bitech\CDD\Config
Add a DWORD named "Explain" and set it to 1. (1 = enabled, 0 = disabled)
Once that registry key is tracing will be enabled the next time you run a report. To disable tracing you will need to change that registry key back to zero (0) and shutdown any active instances of CDD.

Locating the Resulting Trace Information:
ORACLE
If CDD is running against ORACLE essentially this registry entry causes CDD to send a "ALTER SESSION SET SQL_TRACE TRUE" to the database to enable tracing.
Log into the UNIX host and use SQLPlus to connect to the database.
Run "show parameters" and locate the USER_DUMP_DEST parameter.
Exit SQLPlus and change directories to the parameter found in the above step.
There should be a file in that directory that is named ora_<your session id>.trc. You can probably use the time-stamp on the file to determine which one is the trace file from your report.
The .trc files created are cryptic, and require that the tkprof utility be run against them to produce readable output. To do this, change to the USER_DUMP_DEST directory where the .trc files have been created. Find the .trc file and run tkprof: "tkprof <trace file> sys=no explain=/".
You will then be prompted for the output destination and output file name. Typically one would choose the /tmp directory with a filename of one's choosing.
INFORMIX
If CDD is running against INFORMIX essentially this registry setting causes CDD to send a "set explain on" to the database to enable tracing.
Log into the UNIX host and change directories to the OpenLink bin directory. Locate the SQEXPLAIN.OUT file. This file should contain the tracing for your report.
Note: because the INFORMIX method puts all of the trace from any enabled session into the same SQEXPLAIN.OUT method it is advised that the file be copied to another location each time a trace is run. This will prevent the file from growing too large and should simplify the process of determining which trace applies to your session.

How Do I List CDD Reports with The Name of The Report As Well As The Description?

On the 7i web server, add a new DWORD key called "CDDDescStyle" in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BITECH\Report Processing Service Set it to a value of 2 so the report ID displays first, then the report description. Options for this registry key: DESC NAME 1 NAME DESC 2 DESC 3 (Default) NAME 4

How Can I Do Number to English Conversion In CDD?

Extended Text: KB ID 6171
The following macro code and scriptlet work in conjunction to allow a CDD report to display numeric values and English words. Specifically, this scriplet will convert decimal amounts to English word dollar amount. For example: The numeric value 1324.75 will output as One Thousand Three Hundred Twenty Four and Seventy Five/100 Dollars.
The scriplet text at the end of this article can be used to create a scriplet which can be referenced in any report where the functionality is needed.
Using The Scriplet in a CDD Macro:
Dim Numbers2Words
' create script variables
Set Numbers2Words= CreateScriptObject("NumberToWords") ' NumberToWords
is the name of the conversion scriplet
If Amount > 0 Then
CheckWords = Numbers2Words.NumToWords(Amount ) ' If the amount
is not zero perform the conversion into CDD UDF fields
End If
BEGIN SCRIPTLET TEXT:
Option Explicit
'Place Holders for Display
Dim Ones, Tens, Hundreds, Thous, TenT, HundThou, Million
'Amount and Amount Parts - WholeAmt = $ CentAmt = Cents
Dim WholeAmt, CentAmt, Amt
'Length and Positions of String
Dim Length, DollarLen, NumLen, TeenPos, CentPos, DecPos
'Value of each position in string, aAmount being position 1.
Dim aAmount, bAmount, cAmount, dAmount, eAmount, fAmount, gAmount, hAmount,
iAmount
'Values of Position in string for use in macros.
Dim Pos_1, Pos_2, Pos_3, Pos_4, Pos_5, Pos_6, Pos_7, Pos_8, Pos_9
Dim LenNumToWord, Words
Function NumToWords(Amt)
Ones = "" : Tens = "" : Hundreds = "" : Thous = ""
TenT = "" : HundThou = "" : Million = "" : WholeAmt = 0
CentAmt = 0 : Length = 0 : DollarLen = 0
NumLen = 0 : TeenPos = 0 : CentPos = 0 : DecPos = 0
aAmount = 0 : bAmount = 0 : cAmount = 0 : dAmount = 0
eAmount = 0 : fAmount = 0 : gAmount = 0 : hAmount = 0
iAmount = 0
Pos_1 = 0 : Pos_2 = 0 : Pos_3 = 0
Pos_4 = 0 : Pos_5 = 0 : Pos_6 = 0 : Pos_7 = 0
Pos_8 = 0 : Pos_9 = 0 : LenNumToWord = 0 : Words = ""
' "Amt" will Equal Amount of Check being passed to Function
'Positions start at the decimal with position 'a' and work there way to
'the Left. 123,456,789.00
' ihg,fed,cba.xx
'MsgBox Amt
DecPos = InStr(1, Amt, ".")
NumLen = Len(Amt)
'MsgBox NumLen
WholeAmt = Left(Amt, (DecPos - 1))
CentPos = NumLen - DecPos
CentAmt = Right(Amt, CentPos)
DollarLen = Len(WholeAmt)
'Finds And Sets the value of Each position of the String
If DollarLen >= "9" Then
iAmount = Mid(WholeAmt, (DollarLen -8), 1)
End If
If DollarLen >= "8" Then
hAmount = Mid(WholeAmt, (DollarLen -7), 1)
End If
If DollarLen >= "7" Then
gAmount = Mid(WholeAmt, (DollarLen -6), 1)
End If
If DollarLen >= "6" Then
fAmount = Mid(WholeAmt, (DollarLen -5), 1)
End If
If DollarLen >= "5" Then
eAmount = Mid(WholeAmt, (DollarLen -4), 1)
End If
If DollarLen >= "4" Then
dAmount = Mid(WholeAmt, (DollarLen -3), 1)
End If
If DollarLen >= "3" Then
cAmount = Mid(WholeAmt, (DollarLen -2), 1)
End If
If DollarLen >= "2" Then
bAmount = Mid(WholeAmt, (DollarLen - 1), 1)
End If
If DollarLen >= "1" Then
aAmount = Mid(WholeAmt, DollarLen, 1)
End If
'Sets Positon values equal to their position
Pos_9 = iAmount
Pos_8 = hAmount
Pos_7 = gAmount
Pos_6 = fAmount
Pos_5 = eAmount
Pos_4 = dAmount
Pos_3 = cAmount
Pos_2 = bAmount
Pos_1 = aAmount
If Pos_2 <> 1 Then 'Position 1 for Tens
Select Case Pos_1
Case "1" Ones = "One "
Case "2" Ones = "Two "
Case "3" Ones = "Three "
Case "4" Ones = "Four "
Case "5" Ones = "Five "
Case "6" Ones = "Six "
Case "7" Ones = "Seven "
Case "8" Ones = "Eight "
Case "9" Ones = "Nine "
End Select
End If
If Pos_2 = 1 Then 'Position 1 and 2 combined for Teens
TeenPos = Pos_2 & Pos_1
Ones = " "
Select Case TeenPos
Case "10" Tens = "Ten "
Case "11" Tens = "Eleven "
Case "12" Tens = "Twelve "
Case "13" Tens = "Thirteen "
Case "14" Tens = "Fourteen "
Case "15" Tens = "Fifteen "
Case "16" Tens = "Sixteen "
Case "17" Tens = "Seventeen "
Case "18" Tens = "Eighteen "
Case "19" Tens = "Nineteen "
End Select
End If
If Pos_2 > 1 Then 'Position 2 Tens
Select Case Pos_2
Case "2" Tens = "Twenty "
Case "3" Tens = "Thirty "
Case "4" Tens = "Forty "
Case "5" Tens = "Fifty "
Case "6" Tens = "Sixty "
Case "7" Tens = "Seventy "
Case "8" Tens = "Eighty "
Case "9" Tens = "Ninety "
End Select
End If
If Pos_3 >= 1 Then 'Position 3 Hundreds
Select Case Pos_3
Case "1" Hundreds = "One Hundred "
Case "2" Hundreds = "Two Hundred "
Case "3" Hundreds = "Three Hundred "
Case "4" Hundreds = "Four Hundred "
Case "5" Hundreds = "Five Hundred "
Case "6" Hundreds = "Six Hundred "
Case "7" Hundreds = "Seven Hundred "
Case "8" Hundreds = "Eight Hundred "
Case "9" Hundreds = "Nine Hundred "
End Select
End If
If Pos_5 <> 1 Then 'Position 4 Thousands
Select Case Pos_4
' Case "0" Thous = "Thousand "
Case "1" Thous = "One Thousand "
Case "2" Thous = "Two Thousand "
Case "3" Thous = "Three Thousand "
Case "4" Thous = "Four Thousand "
Case "5" Thous = "Five Thousand "
Case "6" Thous = "Six Thousand "
Case "7" Thous = "Seven Thousand "
Case "8" Thous = "Eight Thousand "
Case "9" Thous = "Nine Thousand "
End Select
End If
If Pos_5 = 1 Then 'Position 4 and 5 combined for Thousands Teens
TeenPos = Pos_5 & Pos_4
Thous = " "
Select Case TeenPos
Case "10" Thous = "Ten Thousand "
Case "11" Thous = "Eleven Thousand "
Case "12" Thous = "Twelve Thousand "
Case "13" Thous = "Thirteen Thousand "
Case "14" Thous = "Fourteen Thousand "
Case "15" Thous = "Fifteen Thousand "
Case "16" Thous = "Sixteen Thousand "
Case "17" Thous = "Seventeen Thousand "
Case "18" Thous = "Eighteen Thousand "
Case "19" Thous = "Nineteen Thousand "
End Select
End If
If Pos_5 > 1 Then 'Position 5, Tens of Thousands
Select Case Pos_5
Case "2" TenT = "Twenty "
Case "3" TenT = "Thirty "
Case "4" TenT = "Forty "
Case "5" TenT = "Fifty "
Case "6" TenT = "Sixty "
Case "7" TenT = "Seventy "
Case "8" TenT = "Eighty "
Case "9" TenT = "Ninety "
End Select
End If
If Pos_6 >= 1 Then 'Position 3 Hundreds
Select Case Pos_6
Case "1" HundThou = "One Hundred "
Case "2" HundThou = "Two Hundred "
Case "3" HundThou = "Three Hundred "
Case "4" HundThou = "Four Hundred "
Case "5" HundThou = "Five Hundred "
Case "6" HundThou = "Six Hundred "
Case "7" HundThou = "Seven Hundred "
Case "8" HundThou = "Eight Hundred "
Case "9" HundThou = "Nine Hundred "
End Select
End If
If Pos_7 > 1 Then 'Position 3 Hundreds
Select Case Pos_7
Case "1" Million = "One Million "
Case "2" Million = "Two Million "
Case "3" Million = "Three Million "
Case "4" Million = "Four Million "
Case "5" Million = "Five Million "
Case "6" Million = "Six Million "
Case "7" Million = "Seven Million "
Case "8" Million = "Eight Million "
Case "9" Million = "Nine Million "
End Select
End If
If Amt > 0.99 Then
Words = Million & HundThou & TenT & Thous & Hundreds & Tens & _
Ones & "and " & CentAmt & "/100 Dollars"
Else
Words = "Zero and " & CentAmt & "/100 Dollars"
End If
'
'LenNumToWord = Len(Words)
'MsgBox LenNumToWord
NumToWords = Words
End Function

What Does The "Multiple Controls with Same ID" Error Message Mean?

Text: KB ID 6202
Multiple controls with the same ID 'TABLE_NAME.COLUMN_NAME' were found.
FindControl requires that controls have unique IDs.
Details:
When creating a web page for 7i prompts, CDD creates control IDs using the TABLE.COLUMN information from the data item being prompted. Thus, When creating multiple drill down prompts for the same data item (to create a range, for example) CDD will create redundant control IDs.
Find the Report
---------------------------------------
The error is presented in 7i, so first use 7i to isolate the Parent Report where the drill down is defined.
Find the Drill Definition:
---------------------------------------
Next, in Stand-Alone CDD, open and run the report identified in the previous step. This will allow you to right click on the data value where the drill down is defined and choose "View Drill Downs." Select the Drill Down in question (the default drill down will be differentiated in a list by the red check mark in the icon) and Click the Edit button.
This will bring up the Drill Down Selection Criteria dialog. Click "Short Format" to display the item names of the selection criteria elements.
Identify the column that is used multiple times. In the information category for the main report, identify the table that is being referenced by Drill Down definition.
Modify The Catalog Table:
---------------------------------------
In Stand-Alone CDD, choose Catalog Manager from the Tools menu.
In the list of tables on the right side (System Catalog Items under Catalog Tables) find the table identified from above. In the list of columns, find the column identified from before and identify the name of the column in the database.
Right click on the CDD table and choose "Add Column." Choose the database column you need to add - be sure to give the new column a unique name.
Update the CDD Category
---------------------------------------
Choose Validate Categories from the Tools menu and find the category in question and Click Validate.
Close CDD.
Reopen CDD.
Run the original report again.
Fix The Drill Definition:
---------------------------------------
Right click on the field where the drill down is defined and select View Drill Downs. Click on the Drill definition as above and click Edit.
Change the duplicate line in the Drill Down Selection Criteria to use the newly created column. Save the Drill Down.
For Drills with more than two duplicated prompts
---------------------------------------
Repeat this entire process as needed for more than two duplicate control IDs.
Additional Info:
For current status review ISC Ticket 333715 and related tickets.

Why Does the CDD Drill Down in a Group Footer Return Wrong Data?

Extended Text: KB ID6209 CDD Drill Downs defined on footer regions return the wrong data.
This is a known bug. CDD will drill down on the wrong record when the drill down is defined in a Group Footer region. Typically, this is not on the first page, but rather on subsequent pages.
Task #295438 should be referenced to determine current status.
There is no specific work around, so a client should be encouraged to rewrite the report so that the drill down occurs from a detail region.

How Do I Display the Report SQL in CDD?

Extended Text: KB ID 6233
There are several ways to identify what SQL statement was used by CDD to create the report. First, run the report to the screen in Stand Alone CDD (this will not work in Insight or 7i). With the report output open, select Report SQL from the View menu. This will show you the main report SQL only (any sub-report SQL or data lookup SQL will not be shown in this screen).
You can turn on File Tracing to get a more complete list of the SQL statements that CDD issues to the database. To turn on File Tracing look here: http://isc1/isc/kb/external/2903.htm
You can also turn on "real-time" tracing when you run CDD. At any time CDD is running, press CTRL+SHIFT+D. This will open an output window called BTTrace that will run behind CDD. A dialog appears informing you that "Debugging is Enabled." From that point on all debugging output from CDD will be output to the BTTrace window. This output will include the SQL statements that CDD uses in the main report as well as sub-reports and data lookups. This is VERY useful. To turn tracing back off, press CTRL+SHIFT+D again. You will confirmed with a dialog that says "Debugging Disabled."
For almost every CDD report these various SQL statements will reference a security view in the form of "BTV_x1_x2." To understand these views (if there are any) look here: http://isc1/isc/kb/external/1736.htm. FYI: If your report SQL does not reference a "BTV_x1_x2" view, then one of two things is true: No security is being enforced (because BusinessPlus says it isn't needed for this user for this table) OR you are running the report in Consolidated Query Mode.

Why Do Dates Now Display As "11/30/1999" Or "1/1/1000" In CDD?

Extended Text: KB ID 6331
DETAILS: As of the release of 7i version 7.6 PowerSchool has switched to the newest release of the Microsoft compiler. This compiler changes the way that date values are created. Previously, a blank or NULL date value was created as a date with all zeros. This date would act and display as a blank value. Now, with the latest compiler version, these dates are assigned a date value of "11/30/1999."
PowerSchool has identified this "default" date value as inadequate to the needs of it's BusinessPlus customers. As such, we have modified our code to use a more innocuous default date value of "01/01/1000" (often displayed as "1/1/100"). This date is considered "safe" because it is obviously outside the date range for BusinessPlus data sets.
This work was done on task 334772 (and related tasks for other specific versions). This will be available in 7.6.2.109 and later.
OTHER DETAILS: For those clients who wish to display a blank on a report where there is a blank or NULL in the database they will have to modify their reports as follows:
Create a user defined field of type character.
Replace the database field on the report with the user defined field.
Put this macro code in the appropriate region of the report macros:
' In this example, the user defined field is called myCharacterString
' This example uses PENAME.CREATE_DT for my blank date test
myCharacterString = PENAME.CREATE_DT
If myCharacterString = "1/1/1000" Then
myCharacterString = ""
End If
SPECIAL NOTE: This KB must be used in cases where you are trying to isolate NULL dates in the database as follows:
MyCharacterString = ""
If Isdbnull("HR_EMPMSTR.ENDDT") Then
MyCharacterString = ""
Else
If HR_EMPMSTR.ENDDT = "1/1/1000" Or HR_EMPMSTR.ENDDT = "01/01/1000" Then
MyCharacterString = ""
Else
MyCharacterString = HR_EMPMSTR.ENDDT
End If
End If
Review KB 7026 for more details on IsDBNull()

How Do I Export to Excel Using a Scriptlet in CDD?

Details: KB ID 6362
This sample report shows a simple Export to Excel example. The report will not run in the 7i environment and should only be used in CDD.

Extended Text:
Report Name: SampleExcelExport (SampleExportToExcel.czf)
Additional Info: The report uses the xlexport scriplet to provide mundane functionality of exporting CDD data to MS Excel. The report consists of a very simplistic PE listing with address info. The user is prompted for export (Y/N).
The report macro shows the way to put data in an Excel data row and add the row to the Excel spreadsheet.

What Settings Are Available for CDD Report File Management Settings on The 7i Server?

Details: KB ID 6363
7i Registry settings that control the lifetime of the CDD report files, as well as the interval between cleanup attempts.

Extended Text:
Registry Key: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\bitech\Report Processing Service

FileStorageCleanup

Name: FileStorageCleanup
Type: DWORD
Value: The value represents the number of minutes between file cleanup cycles.
The clean up cycles remove CDD report files that have "expired".
Default: 720 minutes (12 hours)

ReportExpiration

Name: ReportExpiration
Type: DWORD
Value: The value here represent the number of days that a CDD report file (xml) can be allowed to reside on the server. When a file has resided on the server for more than "x" days, it is considered to be expired and can be removed the next time the cleanup process runs.
Default: 7 days
IMPORTANT: You must restart Data Processing Services for changes in these settings to be recognized by the 7i server.
NOTES: These two values work in conjunction with each other.
FileStorageCleanup should not exceed the expected life of the Data Processing Services. Since some clients choose to restart Data Processing on a daily basis, this interval should not exceed 1440 (24 hours). Once a day should be sufficient, since that equals the granularity of the ReportExpiration interval.
An appropriate interval for FileStorageCleanup is between 8 and 12 hours.
ReportExpiration should be monitored so that the size of the FileStoragePath directory does not become bloated. Also, clients may not wish to leave sensitive data on their server for extended periods, so a shorter period (1 day) may be a reasonable setting.

How Do I Handle Database Schema Changes in CDD Reports?

Details: KB ID 6370
Occasionally database schema changes in BusinessPlus impact CDD reports, categories and/or table definitions. Specifically, database changes that include altering data types in the database are addressed in this article.

Extended Text:
There are several places in CDD where you may need to update the table/column definitions after a database schema change in BusinessPlus. This is most often true after and HFS or other software upgrade is applied. This KB deals specifically with database tables that have had one more column data types modified in the upgrade. Always refer to release notes for the version in question when deciding if you need to follow the procedures outlined below.
Added/Dropped Columns: If a column is added or removed from a table during an HFS or other upgrade you must use the Catalog Manager to update the affected Catalog Tables. Specifically, chose Tools->Catalog Manager. This will open the Catalog Manager dialog. In the Catalog Tables pane, find the affected table and right click. Choose Validate to bring up the Table Validation dialog. This will allow you to add or remove columns as needed. Table Validation will ONLY allow you to add or remove columns listed in the dialog box. This applies to concatenated columns that are broken out into individual columns during an HFS or other update.

Altered Data Types:
When the data type of a column in the database is changed Table Validation will not resolve the issue. Instead, you must re-import the table definition from the database. It is recommended that you rename the existing table so that you will not lose the current definition in case of problems. To rename an existing table, launch Catalog Manager. In the Catalog Tables pane, find the affected table and right click. Choose Proprieties and then select the Details tab. Modify the Table Name value to a new name and click OK. Next, find the database table in the pane on the right. Click to select the table then click the Import button. If your original Catalog Table name was not the same as the default name used by the Import function, modify the new table name to be the same name used for the original table.

Additional Notes:
In general, it is recommended that you use the default name given during the Import process for all or the CDD Catalog Tables. This eliminates the number of redundant table definitions, as well as simplifying both the Catalog update process and the Report design process. If you do use unique names for the imported tables, you should only do so when you have specific needs, such as adding derived columns such as substring, etc.

Special Note:
In some rare cases the Table Validation/Category Validation combination does not properly update the Information Category. Often these categories are usable for CDD and Insight, but not for 7i reports. In this case you will receive an error in 7i indicating that "The server encountered an error attempting to open the report design." When you receive this error you must manually reproduce the category from scratch. NOTE: Opening and saving the existing category does not resolve this issue - you must reproduce the category.
You can verify this condition by Tracing the report using registry tracing. The Trace output will indicate that there was an "Error un-serializing report from database."

Why Do I Get The "Object Reference Not Set to An Instance Of An Object" Message?

Details: KB ID 6386
When the user attempts to run a CDD report from a dashboard favorite they receive the following error: OBJECT REFERENCE NOT SET TO
AN INSTANCE OF AN OBJECT

Extended Text:
Possible Cause: One possible cause of this problem is that a user may not have the correct functional security required to run CDD reports.
Specifically, the user may not have the ability to "Run Report with No Criteria." You can confirm this by reviewing the user's functional security setup in System administrator.
The user must at least have "Run Report with No Criteria" set to Read and Execute. When changing user security roles you must always choose "Reset Security." Furthermore, you may need to flush the 7i cache here:
http:/<server>/ifas7/admin/config/admin.asp -> Cache Management -> Update Server.

Keywords: dashboard favorites object reference not set to instance of object CDD report reports 7i security error

Why Does the CDD/7i Drill Down Not Drill on Double Click?

Details: KB ID 6415
Non-default drill downs in CDD/7i do not automatically execute when the drill field is double-clicked. Instead you must select the Drill name from the pop-up list.
If you wish to use the double-click option in 7i, you must set at least one drill definition as "default."
To set one of the Drill Definitions as "default," follow these steps:
1. In standard CDD, open and run the report in questions.
2. Right click on the drill field and choose View Drill Downs.
3. Select the appropriate Drill Definition from the and put a check in the Set As Default check box.
4. Click Cancel (The changes are automatically saved when you click the check box).
The drill you selected in step 3 will now execute immediately when the drill field is double clicked in 7i. NOTE: You may need to shut down and restart your 7i session (and all open Internet Explorer windows) for the change to be recognized in 7i. Additionally, some clients may need to flush the 7i caches.

Why Does My CDD Report Not Run?

Details: KB ID 6428
CDD report design will not run. After prompts, if any, the cursor returns immediately and the report does not run.

Extended Text:
Possible Causes: Check to confirm that there is at least one item from the main Information Category on the report design. If the report design does not use any of the fields from the main Information Category the main Report SQL will be empty and the report will not run.
Sample Trace Output:
10:46:26 1 Start Debugger
10:46:28 2 CReportDoc::Initialize
10:46:28 3 ReportDoc::Initialize Handle Prompts
10:46:28 4 ReportDoc::Initialize Building Query List
10:46:28 5 ReportDoc::Initialize Done Building Query List
10:46:28 6 ReportDoc::Initialize Done Handling Prompts
10:46:28 7 CReportDoc::Initialize Setting Report Printer
10:46:28 8 CDD Prompts - if any
10:46:30 9 Calling BuildSQL.
10:46:30 10 Failed to BuildSQL.
10:46:30 11 RunReport caught a CUserException!
10:46:30 12 CBTActiveMacroFunctions::OnFinalRelease()
10:46:30 13 CBTActiveMacroFunctions::~CBTActiveMacroFunctions()
Solution: If you find that there are not any fields from the main Information Category in your report design, you have 2 options:
You can place at least one item from the main Information Category onto the report design.
You can edit the report properties. Click on the "Drill" tab and select at least one field to be retained for drill down.
This issue is addressed in ISC ticket number 349617.

How Do I Create CDD Selection Criteria Against Datetime (Timestamp)?

Details: KB ID 6471
Writing selection criteria against a datetime (timestamp) results in the error: Non-numeric character in datetime or interval. (-1262)

Extended Text:
In INFORMIX you cannot properly create selection criteria against a datetime (timestamp) data type. Putting in a valid date, such as "12/31/2005" will result in an INFORMIX error: Non-numeric character in datetime or interval. (-1262)

Resolution:
You must create a new column in your database table that is based on the datetime field that you are attempting to select against. Instructions for adding this new field are included in the attached document.

Why Are CDD Reports Not Displaying Translated Descriptions?

Details: KB ID 6523
CDD Report fields that are set up to translate to the descriptions from the GLUPGN screens do not display the translation. For example, if the description of the GLOBJ.PART02 field in the catalog manager is set up to display the medium description from the GLUPGN screen (\godm), it instead displays the field name (PART02).

Resolution:
This issue is usually a result of NUUPUS security setup for the user that does not specify a default Ledger on the MISC tab. Users should be set up with default ledgers (both GL and JL) in order for CDD to properly translate the descriptions for key and object parts, etc.

Why Am I Getting the Error In 7i Running CDD Reports: A Semi Colon?

Details: KB ID 6529
CDD reports with runtime criteria can occasionally cause this error. This error is directly related to the ampersand character (&) being used in the name of the runtime selection criteria.

Resolution:
Rename the runtime selection criteria associated with the report design so that the ampersand character is removed. Ideally the only "special" character that will ever be used in the CDD report name or description is the underscore character (_).

How Can I Use CDD to Do a Document Attachment Lookup for Documents?

Details: KB ID 6562
The CDD report, AttachDetails_002, can be used to review attachment data. The report uses several tables mentioned in KB 6279.
To search by attachment value, use the runtime criteria named "AttachValue" when running the report. You will be prompted for a value (the criteria use the "like" operator so you can enter partial values with wild-cards). The report will then search for any documents where at least one of the attachment values matches the prompt value.
To search by Attachment ID, use the runtime criteria name "AttachID" when running the report. You will be prompted for an Attachment ID and the report will list all documents that are attached to that ID.

Why Don't CDD Drill Downs Using Sysperiod Work The Same In 7i?

Symptoms: KB ID 6572
When you define a drill down in CDD using the system field SYSPERIOD as a part of the drill selection criteria the report may not work properly in 7i. Symptoms can range from the drill down report not returning any data to the drill down report returning incorrect results.

Details:
The CDD System Field SYSPERIOD is a numeric field. In CDD this field is handled differently and can be used as a selection criteria to select data from the database regardless of whether the data item is a character or a numeric. In 7i, the value of the field is held in an XML storage which first converts the item into a specific numeric value. This numeric value includes a decimal point and trailing zeros (4 becomes 4.00).
This means that the value cannot be used to select against a character field. You can verify this by looking at the request.xml file on the 7i sever for the drill down report in question and verifying that the value does include a decimal place and trailing zeros.

Resolution:
You can change your drill down selection criteria to use the CDD System Field SYSPERIODCHAR. This field value is correctly stored in 7i XML storage and can be used to retrieve the correct values. If you are experiencing a CDD report that is not drilling correctly in 7i, and SYSPERIOD is used in the selection criteria, try changing it to use SYSPERIODCHAR to determine if the report starts working correctly. Be sure to log completely out of 7i before testing the new drill down definition. You can confirm that you are using the new drill down definition by looking at the request.xml on the 7i server and verifying that the value does NOT include a decimal place and trailing zeros.
NOTE: This issue is addressed in ticket 299894 (7.6.3.138).

How Do I Export to Xlsgen Using a Scriptlet in CDD?

Details: KB ID 6582
This sample report shows a simple Export to XLSGEN example. The report is designed to be run on the 7i server and will not perform properly in "thick" CDD.

Extended Text:

Report Name: Sample_XLSGEN_Export (SampleExportToXLSGEN.czf)

Additional Info: The report uses the xlsgenexport scriplet to provide mundane functionality of exporting CDD data to XLSGEN. The report consists of a very simplistic PE listing with address info. The user is prompted for export (Y/N).
The report macro shows the way to put data in an XLSGEN data row and add the row to the Excel spreadsheet. Note: The resulting Excel file is attached to the CDD/7i report output window. For details please refer to CDD/7i documentation.
For additional details regarding XLSGEN object model and usage please see attached XLSGEN documentation.
Download Note: The attached help file has been modified to allow downloading through a firewall. After you download the help file you must rename it and include the .chm extension (rename xlsgen_help to be xlsgen_help.chm).

Why Am I Getting the Error "Mask Does Not Exist In Next Question”?

Extended Text: KB ID 6690
Error when opening CDD Reports Folders: "Mask does not exist in next question master. (TDPA)"
This error indicates that the mask in the parenthesis is not found as a valid mask in the BusinessPlus subsystem. The steps listed below walk through the process of removing a mask from NUUPJB. The actual error may vary depending on the Mask listed in the parenthesis. Also, the error may occur multiple times, with different masks, if the client has multiple invalid masks in NUUPJB.
1. Log into BusinessPlus
2. Go to the "Next Question Prompting" screen by entering the mask NUUPJB and hit enter.
3. Once you are in the "Next Question Prompting" screen hit the F7 key to go into "Browse Mode," your cursor should now be at the bottom of the screen at the "Mask" prompt.
4. Hit the "Tab" key twice to go to the "Question" prompt, type TD01 and hit enter.
5. Hit the "Tab" key once to get the cursor in the "MENU?" prompt, type a Y and hit enter.
6. You should now be in the "Menu" screen and there will be a list of the masks that you want to erase. If you erase the masks in this screen and then save the changes by hitting the "Enter" key they will no longer show up in Insight.

How Do I Add Substring Columns in The CDD Catalog Manager?

Details: KB ID 6740
Using the CDD Catalog Manager it is possible to add a substring column to a CDD table. The attached Word document includes screen shots and step by step instructions on this process. Below is the syntax required for each of the DBMS engine currently supported by CDD.

Extended Text:
DBMS Syntax

INFORMIX:
column_name[start,end]
Example: column: pe_name[2,5]
value: Administrative Builders
result: dmin

SQL Server:
column_name[start,length]
Example: column: pe_name[2,5]
value: Administrative Builders
result: dmini

ORACLE:
column_name[start,length]
Example: column: pe_name[2,5]
value: Administrative Builders
result: dmini

Why Are Decimal Values Truncated in Xlsgen Export?

Extended Text: KB ID 7025
Why are decimal values being truncated when I export using XLSGEN in a CDD macro?

Answer:
Your report might be using the "Number" method to export the data value. Change to use the "Float" method to force XLSGEN to respect the decimal places.

CDD Report File Management Settings on The 7i Server

Details: KB ID 6363
7i Registry settings that control the lifetime of the CDD report files, as well as the interval between cleanup attempts.

Registry Key: HKEY_LOCAL_MACHINE\SOFTWARE\bitech\Report Processing Service
Name: FileStorageCleanup
Type: DWORD
Value: The value represents the number of minutes between file cleanup cycles.
The clean up cycles remove CDD report files that have "expired".
Default: 720 minutes (12 hours)
Name: ReportExpiration
Type: DWORD
Value: The value here represent the number of days that a CDD report file (xml) can be allowed to reside on the server. When a file has resided on the server for more than "x" days, it is considered to be expired and can be removed the next time the cleanup process runs.
Default: 7 days
IMPORTANT: You must restart Data Processing Services for changes in these settings to be recognized by the 7i server.
NOTES: These two values work in conjunction with each other.
FileStorageCleanup should not exceed the expected life of the Data Processing Services. Since some clients choose to restart Data Processing on a daily basis, this interval should not exceed 1440 (24 hours). Once a day should be sufficient, since that equals the granularity of the ReportExpiration interval.
An appropriate interval for FileStorageCleanup is between 8 and 12 hours.
ReportExpiration should be monitored so that the size of the FileStoragePath directory does not become bloated. Also, clients may not wish to leave sensitive data on their server for extended periods, so a shorter period (1 day) may be a reasonable setting.

Review CDD Pages in Memory

Extended Text: KB ID 6912
Pages in Memory is an option that allows you to tune CDD performance. It allows you to specify the number of pages to be held in memory once a report is run. The default value for Pages in Memory is 5. The minimum is 1 and the maximum is 100.
On computers with limited RAM (Random Access Memory), Pages in Memory can be set to a smaller value. On computers with expanded RAM, or in situations where you want to be able to browse back and forth through a report that is many pages long, Pages in Memory can be set to a higher value. However, even on the most advanced computer there is a limit to the number of report pages that can be stored. That is why there is a maximum value for Pages in Memory. One thing to keep in mind is that the amount of memory used per report page will vary depending on the complexity of the report: the more items on the report, the more memory it will need to store the report page.  One "side-effect" of having a limited number of Pages in Memory occurs when you try to print a report after you have already paged forward past the number of Pages in Memory set in the Report Preferences. For example, if your Pages in Memory is set to 30, CDD can only keep 30 report pages active at any given moment. This means that when you scroll to page 31, CDD must drop the first page. At this point, if you tried to print the report, you would only be able to print from page 2 forward. The only way print to whole report is to run the report again and print it before scrolling past the Pages in Memory limit. Since the pages that you have not yet displayed are not in memory yet, Pages in Memory only affects "previous" pages.
Often times user may want to review the report output before sending the output to the printer. That can only be done by running the report a second time and sending it to the printer without scrolling forward past the Pages in Memory setting. However, it is worth noting that there is no maximum number of pages for a CDD report, and there is no maximum number of pages that CDD can send to the printer.

Review Pages in Memory

1. In the CDD menu, click Edit, then select Preferences.

2. Select the Report tab. Pages in Memory indicates the number of pages. The default is 5 and the maximum is 100.

3. Enter the desired number of pages, then click Apply to save your change.