Overview
Siebel applications are shipped with standard reports. You create a new report when your requirements are not satisfied by any existing reports and there are significant differences between your desired report and any existing report. To modify these reports or add new reports, you need to use Siebel Tools and Actuate e.Report Designer Professional.
Parameterized reports allow users to pass data into a report executable at runtime and customize the output of the report. The user may narrow down the query, sort specification, or effect grouping based on a field at the report’s execution time. A parameterized report can produce different reports from the same report executable. This topic explains a few complex requirements in reports like,
· Using Dynamic constrained pick lists
· Dynamically changing the columns in the report layout based on parameters
· Handling optional user-inputs.
1.1 Requirement Description:
User needs a hierarchical picklist to display values constrained based on another picklist. For example if the user selects an Account Hierarchy, they will be presented with a list of accounts belonging to that Account Hierarchy from which, they can select multiple accounts.
1.2 Implemented Version: Siebel 7.7.2.1, Actuate 7, SP2
1.3 Solution Description:
· Siebel Configuration
· Actuate e.Report Designer Professional
1.3.1 Siebel Configuration:
1. Create a static pick list for account hierarchy.
| TYPE | LIC |
| ACCNT_TYPE | Parent Account |
| ACCNT_TYPE | Child Account |
(If user selects, Parent Account - Parent Accounts should be displayed and for Child Account - Child Accounts should be displayed. Provided the filter should change dynamically depending upon the account hierarchy.)
| Pick List Property | |
| Name | Account Hierarchy picklist |
| Project | Reports |
| Bounded | True |
| Business Component | PickList Generic |
| Sort Specification | Name |
| Static | True |
| Type Field | Type |
| Type Value | ACCNT_TYPE |
| Long List | False |
2. Create a Dynamic Pick List for getting parent/child account name
| Dynamic Pick List Property | |
| Name | Account Picklist |
| Project | Reports |
| Bounded | True |
| Business Component | Account |
| Sort Specification | Name |
| Static | False |
| Long List | False |
3. Create a parameter BC for parameter applet
| | |
| Name | Account |
| Project | Reports |
| Class | CSSBCVReportParameters |
4. Within the parameter BC, create new fields
| | |
| Field 1 | |
| Name | Account Hierarchy |
| Immediate Post Changes | True |
| Pick List | Account Hierarchy picklist |
| Type | DTYPE_TEXT |
| Pick map Fields | |
| Pick map->Field | Account Hierarchy |
| Pick map->Picklist Field | Value |
| Field 2 | |
| Name | Account |
| Immediate Post Changes | False |
| Pick List | Account Picklist |
| Type | DTYPE_TEXT |
| Pick map Fields | |
| Pick map->Field (1) | Account |
| Pick map->Picklist Field (1) | Name |
| Pick map->constrain(1) | False |
| Pick map->Field (2) | Account Hierarchy |
| Pick map->Picklist Field (2) | Account Type |
| Pick map->constrain(2) | True |
Note: When Immediate Post Changes property is set to TRUE for a BC field, Field data is posted to the server when the focus moves off of the field and then the data is refreshed. This causes an immediate roundtrip to the server and is typically used for constrained drop-down lists and calculated fields. Excessive use affects performance.
5. Copy the Opportunity Parameter Applet by right-clicking it and choosing Copy Record. Change the following properties on the new record.
| Parameter Applet Property | |
| Name | Account Report Parameter Applet |
| Project | Reports |
| Business Component | Account |
| Title | Account Parameter Report |
6. Select Applet > Account Report Parameter Applet > Control and remove the Parameter Label, Report Label, Report Name, SortBy, and SortCriteria controls.
7. Within this applet, create new controls:
| Field | Value |
| Name | Account_Hierarchy |
| Caption | Account Hierarchy |
| Field | Account Hierarchy |
| HTML Type | Field |
| RunTime | True |
To support multi-select option for account (say user need 3 options for account, of which at least one account is mandatory), add three fields for account
| Field | Value |
| Name | Account1 |
| Caption | Account1 |
| Field | Account |
| HTML Type | Field |
| RunTime | True |
| Field | Value |
| Name | Account2 |
| Caption | Account2 |
| Field | Account |
| HTML Type | Field |
| RunTime | True |
| Field | Value |
| Name | Account3 |
| Caption | Account3 |
| Field | Account |
| HTML Type | Field |
| RunTime | True |
8. Select Applet > Account Report Parameter Applet > Applet Web Template. Verify that the Web Template exists in 3 modes; Base, Edit, and Edit Error.
9. Edit the web template layout for the Base mode:
a. Select Base, right-click and choose Edit Web Layout.
b. Right-click anywhere on the template and choose Check Mappings. The Check Mappings window will appear displaying five invalid mappings. Click Yes to remove all invalid controls found.
c. Add the Account Hierarchy, Account1, Account2 and Account3 controls, label and text, to the web template.
10. Right-click the applet and choose Preview to confirm the design of the applet.
11. Save the changes, close the Layout Editor, and repeat the previous step 9 and 10 to the web template layout for the Edit mode.
12. Create a new Report object.
| Report Object | |
| Name | Detail Account Report |
| Project | Reports |
| Business Component | Account |
| Class | CSSActuateReportViewer |
| Access Base DB Name | ACCNT_DET_RPT |
| Report Object | |
| Template Name | ACCNT_DET_RPT |
| Menu Text | Detail Account Report |
13. Within this report, create a new report field:
| Report Fields |
| Parent Account Name |
| Account Name |
Add the required fields, depending upon the requirement.
14. In Siebel Tools, generate the report object library (.ROL) file by selecting Tools > Utilities > Generate Actuate Report from the application-level menu.
15. Add the report to the respective view, and compile the relevant projects.
1.3.2 Actuate e.Report Designer Professional 1. Create a new report object design (ROD).
2. Design the report layout as per the requirement and create the following variable in the top level report object (Super class ssReport).
| Field | Value |
| Name | Account_Hierarchy |
| Type | String |
| Storage | Static (shared by all objects) |
| Visibility | Parameter |
| Field | Value |
| Name | Account1 |
| Type | String |
| Storage | Static (shared by all objects) |
| Visibility | Parameter |
| Field | Value |
| Name | Account2 |
| Type | String |
| Storage | Static (shared by all objects) |
| Visibility | Parameter |
| Field | Value |
| Name | Account3 |
| Type | String |
| Storage | Static (shared by all objects) |
| Visibility | Parameter |
Note: This variable name must have the same name as the name of the applet control object that references the field created to hold the parameter value
4. To filter the records dynamically based upon the user selected account hierarchy, overwrite the Fetch() method of the datastream.
Function Fetch () As AcDataRow Dim theRow As Account_Data_Row
'Filter for Parent Account If Account_Hierarchy = “Parent Account” Then Do Set theRow = Super::Fetch() If theRow Is Nothing Then Set Fetch = Nothing Exit Function End If Loop until (theRow.ss_Parent_Account_Name = Account1 OR theRow.ss_Parent_Account_Name = Account2 OR theRow.ss_Parent_Account_Name = Account3) Set Fetch = theRow End If
'Filter for Child Account If Account_Hierarchy = “Child Account” Then Do Set theRow = Super::Fetch() If theRow Is Nothing Then Set Fetch = Nothing Exit Function End If Loop until (theRow.ssAccount_Name = Account1 OR theRow.ssAccount_Name = Account2 OR theRow.ssAccount_Name = Account3) Set Fetch = theRow End If
End Function
5. Build and Compile the Report.
2. Dynamically changing columns in the report layout
2.1 Requirement Description:
User wants to change the entire column in the report layout dynamically; for Example, the user selects the field name from the parameter applet through multi-select option and those fields need to be printed as columns in the report. Provided the report has a static number of columns.
Scenario: User wants to display the contact details selectively. The report has 5 columns and the user select any 5 fields out of the given options. The field options are Contact Id, First Name, Last Name, Age, Sex, Address, Phone Number, Fax Number, E-mail.
2.2 Implemented Version: Siebel 7.7.2.1, Actuate 7SP2
2.3 Solution Description:
· Siebel Configuration
· Actuate e.Report Designer Professional
2.3.1 Siebel Configuration:
1. Create a static pick list(Field Name Pick List), which holds the field names as per the requirement
| TYPE | LIC |
| FIELD_NAME | Contact Id |
| FIELD_NAME | First Name |
| FIELD_NAME | Last Name |
| FIELD_NAME | Age |
| FIELD_NAME | Sex |
| FIELD_NAME | Address |
| FIELD_NAME | Phone Number |
| FIELD_NAME | Fax Number |
| FIELD_NAME | E- Mail |
2. Map the pick list to the parameter BC filed.
| Field | Value |
| Name | Field Name |
| Pick List | Field Name Pick List |
| Type | DTYPE_TEXT |
| Pick map->Field | Field Name |
| Pick map-> Picklist Field | Value |
3. Create controls in the parameter applet for Column1 to Column5 (5 options).
| Field | Value |
| Name | Column1 |
| Caption | Column1 |
| Field | Field Name |
| HTML Type | Field |
| RunTime | True |
| Field | Value |
| Name | Column2 |
| Caption | Column2 |
| Field | Field Name |
| HTML Type | Field |
| RunTime | True |
| Field | Value |
| Name | Column3 |
| Caption | Column3 |
| Field | Field Name |
| HTML Type | Field |
| RunTime | True |
| Field | Value |
| Name | Column4 |
| Caption | Column4 |
| Field | Field Name |
| HTML Type | Field |
| RunTime | True |
| Field | Value |
| Name | Column5 |
| Caption | Column5 |
| Field | Field Name |
| HTML Type | Field |
| RunTime | True |
2.3.2 Actuate e.Report Designer Professional
1. Create the parameter in the top-level of the report object for Column1 to Column5 as mentioned in the steps of (1.3.2).2. Create 5 global variables g_Column_Value1 to g_Column_Value5.
3. To dynamically change columns in the report layout, declare object variable for each text control as Obj_Column1 to Obj_Column5
4. Overwrite the OnRow and finish method of the Content Frame and add the following code
Sub OnRow( row As AcDataRow )
Super::OnRow( row )
Dim arow As Contact_Data_Row
Set arow = row
'Code for setting Contact detail depending upon the user selection
Select Case Column1
Case " Contact Id "
g_Column_Value1 = arow.ssContact_Id
Case "First Name"
g_Column_Value1 = arow.ssFirst_Name
Case "Last Name"
g_Column_Value1 = arow.ssLast_Name
Case "Age"
g_Column_Value1 = arow.ssAge
Case "Sex"
g_Column_Value1 = arow.ssSex
Case "Address"
g_Column_Value1 = arow.ssAddress
Case "Phone Number"
g_Column_Value1 = arow.ssPhone_Number
Case "Fax Number"
g_Column_Value1 = arow.ssFax_Number
Case "E – Mail"
g_Column_Value1 = arow.ssMail
Case ""
g_Column_Value1 = ""
End Select
Select Case Column2
Case " Contact Id "
g_Column_Value2 = arow.ssContact_Id
Case "First Name"
g_Column_Value2 = arow.ssFirst_Name
Case "Last Name"
g_Column_Value2 = arow.ssLast_Name
Case "Age"
g_Column_Value2 = arow.ssAge
Case "Sex"
g_Column_Value2 = arow.ssSex
Case "Address"
g_Column_Value2 = arow.ssAddress
Case "Phone Number"
g_Column_Value2 = arow.ssPhone_Number
Case "Fax Number"
g_Column_Value2 = arow.ssFax_Number
Case "E – Mail"
g_Column_Value2 = arow.ssMail
Case ""
g_Column_Value2 = ""
End Select
Select Case Column3
Case " Contact Id "
g_Column_Value3 = arow.ssContact_Id
Case "First Name"
g_Column_Value3 = arow.ssFirst_Name
Case "Last Name"
g_Column_Value3 = arow.ssLast_Name
Case "Age"
g_Column_Value3 = arow.ssAge
Case "Sex"
g_Column_Value3 = arow.ssSex
Case "Address"
g_Column_Value3 = arow.ssAddress
Case "Phone Number"
g_Column_Value3 = arow.ssPhone_Number
Case "Fax Number"
g_Column_Value3 = arow.ssFax_Number
Case "E – Mail"
g_Column_Value3 = arow.ssMail
Case ""
g_Column_Value3 = ""
End Select
Select Case Column4
Case " Contact Id "
g_Column_Value4 = arow.ssContact_Id
Case "First Name"
g_Column_Value4 = arow.ssFirst_Name
Case "Last Name"
g_Column_Value4 = arow.ssLast_Name
Case "Age"
g_Column_Value4 = arow.ssAge
Case "Sex"
g_Column_Value4 = arow.ssSex
Case "Address"
g_Column_Value4 = arow.ssAddress
Case "Phone Number"
g_Column_Value4 = arow.ssPhone_Number
Case "Fax Number"
g_Column_Value4 = arow.ssFax_Number
Case "E – Mail"
g_Column_Value4 = arow.ssMail
Case ""
g_Column_Value4 = ""
End Select
Select Case Column5
Case " Contact Id "
g_Column_Value5 = arow.ssContact_Id
Case "First Name"
g_Column_Value5 = arow.ssFirst_Name
Case "Last Name"
g_Column_Value5 = arow.ssLast_Name
Case "Age"
g_Column_Value5 = arow.ssAge
Case "Sex"
g_Column_Value5 = arow.ssSex
Case "Address"
g_Column_Value5 = arow.ssAddress
Case "Phone Number"
g_Column_Value5 = arow.ssPhone_Number
Case "Fax Number"
g_Column_Value5 = arow.ssFax_Number
Case "E – Mail"
g_Column_Value5 = arow.ssMail
Case ""
g_Column_Value5 = ""
End Select
End Sub
Sub Finish( )
Super::Finish( )
Obj_Column1.DataValue = g_Column_Value1
Obj_Column2.DataValue = g_Column_Value2
Obj_Column3.DataValue = g_Column_Value3
Obj_Column4.DataValue = g_Column_Value4
Obj_Column5.DataValue = g_Column_Value5
End Sub
4. To dynamically change the label name, overwrite the Finish() method of the each label control
For Column1 Label
Sub Finish( )
Super::Finish( )
Text = Column1
End Sub
This is repeated for all the column labels 1 to 55. Build and Compile the Report.
3. Handling optional user-inputs
3.1 Requirement Description:
User wants all the inputs in the parameter applet to be optional. When user gives value to a filter it should filter records based upon the selected filter criteria.
Scenario:
There are three filters namely Claim Number, Created Date and Payment Status. If the user optionally enters only one or two filter criteria, only those filter conditions need to be applied.
3.2 Implemented Version: Siebel 7.7.2.1, Actuate 7, SP23.3 Solution Description:
1. Overwrite the Fetch() method of the DataStream as below, where ClaimNumber, CreatedDate and PaymentStatus are parameters
Function Fetch () As AcDataRow
Dim theRow As ssPaymentDataRow
Dim Input_Flag As String,Input_ClaimNumber As String,Input_CreatedDate As String,Input_PaymentStatus As String
Dim Output_Flag As String,Output_ClaimNumber As String,Output_CreatedDate, Output_PaymentStatus As String
'Check for user selected filters
Input_ClaimNumber = IIf(Len(ClaimNumber) <> 0 ,"1","0")
Input_CreatedDate = IIf(Len(CreatedDate) <> 0,"1","0") Input_PaymentStatus = IIf(Len(PaymentStatus) <> 0,"1","0")
'Frame a flag in bit pattern
Input_Flag = Input_ClaimNumber & Input_CreatedDate & Input_PaymentStatus
Do
Set theRow = Super::Fetch()
If theRow Is Nothing Then
Exit Function
Set Fetch = Nothing
End If
'Check for user selected filters with each record
'Checking the Claim Number
If Input_ClaimNumber Eqv "1" Then
Output_ClaimNumber = IIf(theRow.ssCustomer_Claim_Number = ClaimNumber,"1","0")
Else
Output_ClaimNumber = “0”
End If
'Checking the Created Date
If Input_CreatedDate Eqv "1" Then
Output_CreatedDate=IIf((CDate(theRow.ssCreated_Date_Formatted) = CDate(CreatedDate)),"1","0")
Else
Output_CreatedDate = “0”
End If
'Checking the Payment Status
If Input_CreatedDate Eqv "1" Then
Output_PaymentStatus = IIf(theRow.ssPayment_Status = PaymentStatus,"1","0")
Else
Output_PaymentStatus = “0”
End If
'Frame the output flag in a bit pattern
Output_Flag = Output_ClaimNumber & Output_CreatedDate & Output_PaymentStatus
'Allow the records which satisfy the filter criteria
Loop until Input_Flag = Output_Flag
Set Fetch = theRow
End Function
Example:
· If the user gives ClaimNumber = 1000 and Created Date = 01/01/2005 , the report should be generated for records with above claim number and Created Date
· If the user gives PaymentStatus = “Paid” , the report should be generated for records with above payment status only
· If the user doesn’t choose any filter, the report is generated for all the records
2. Build and Compile the Report
