Has Value () Workaround: 3
Key Figures Calculation (Running Totals): 4
Object Formatting using HTML Tags: 5
Underline/Highlighting a Cell for each change of Hierarchy Level 6
Hierarchy Node Expansion with Bex query as Source: 8
Retaining Hierarchy indentation when exporting the report to Excel 10
Column value overlap when viewing a report with hierarchy indentations via Launchpad 11
Creating Combination chart for comparative analysis (Actual Vs Target) 12
Dynamic Sorting Order. 13

Brief Overview

This document provides some of the reporting functionality which are needed for most of the reporting clients but do not have a direct solution to achieve the same.

This document provides a detailed step by step process of implementing each workaround based on the reporting requirement.

Workarounds

1. Has Value () Workaround:

Requirement: Display the prompt values selected by a user to run the report in report footer. For optional parameters “All” should be displayed if no value for that particular parameter is selected.

Issue: In general we use hasvalue() function to check weather a parameter has a value or not. But, report crashes when the hasvalue () function is directly used in a formula due to product instability.

Work around: Create a separate formula to check prompt value as “hasvalue(<parameter_name>)” and refer this formula in other formulas where it’s required.

In the below example formula variable f_check_value = hasvalue(<Param_Name>).

f_check_value is then used in a logical check formula to display the output as needed.

Step1: Create a variable with the hasvalue function as needed

Step2: Use the above created variable with other available functions

2. Key Figures Calculation (Running Totals):

Requirement: The key figure values should be aggregated up to group level dimensions.

In the below scenario, Amount value should be aggregated to “Market” level on which we have grouped

Issue: If it’s plain data, we can simply insert a summary on “Amount” to required level. But, when we get hierarchical data into crystal, Crystal is treating parent and child records as normal records. When we insert summary on those key figures, the values are getting summed multiple times.

Here, we need to sum only values which are 1st level hierarchy of GL account. If we directly apply a summary on “Amount field”, it sums all values here and gives a wrong value as shown in picture. So, we found a way to sum on only 1st level values as marked below.

Work around:

Step 1

Insert a group on “GL Account” (The dimension placed in detail section). This grouping helps in finding hierarchy level of “GL Account”.

Step 2

Create a running total field to aggregate the key figures. Set the Evaluate condition as “Formula” and use the below formula. So, that this field will consider the “Amount” values only when hierarchy level of “GL Account” is 1.

hierarchylevel(groupinglevel({ZALC_GL_DEMO.Alcon GL Account\GL Hierarchy})) = 1

Step 3

Place these running totals in group footers, so that we can get the total summary of key figure for that particular group level.

This way, the report displays the correct aggregation for each “Market” value.

3. Object Formatting using HTML Tags:

Requirement: User wants to see different text formatting to be applied based on the parameter selections.

For example, If the user selects default value for a particular parameter it should be displayed in Blue and italic, else it should be shown in a different format.

Issue: Multiple values with different conditional formatting cannot be inserted into a text box and even if multiple text boxes are used adjacent to each other the conditional formatting affecting the textbox alignment.

Work around:

To keep multiple formatting features like Font Size, Bold and Italic for default values, HTML tags should be used and Text Interpretation should be set as “HTML text”.
Create formula fields as below to display user selected values.

Sample HTML Code

If the user selects a parameter section for PRODUCT_CD as “PRODUCT1” (default selection) then the value is displayed in Italic and in a single row with the following logic:

if {?PRODUCT_CD}="PRODUCT1" then

"Product Hierarchy: <style=font-size:8pt "+ {?PRODUCT_CD}

else

"Product Hierarchy: <style=font-size:8pt "+ {?PRODUCT_CD}

4. Underline/Highlighting a Cell for each change of Hierarchy Level

Requirement: The key figure/measure values should be underlined when the “hierarchy level” ends. as shown below

Issue: In general, we can compare a record with its previous/next records by using previous/next functions. But, these functions will not work when we want to compare records at Group level. We need to display data at group level to get Hierarchical view.

Work around: We can achieve this through a sub report as mentioned below

Step 1

Take the copy of the same report and create few formula fields as below and place them in report and save that report as “Child Report”.

Filed Name	Formula	Placed at	Use
f_count_declare	*shared* *numbervar* rec1; rec1:=0;	Report Header	It initiates a variable to store record number.
f_count_calculate	*shared* *numbervar* rec1; whileprintingrecords; rec1:=rec1+1;	Group Header along with result objects	It assigns record number into given variable.
f_var_declare	*shared* *numbervar* *array* BU; *ReDim* BU[1000000]; BU[1]:=1;	Report Header	It initiates an array variable to store Hierarchy level of GL Account for each record.
f_var_calculate	evaluateafter({@f_count_calculate}); *shared* *numbervar* *array* BU; *shared* *numbervar* rec1; BU[rec1]:= Hierarchylevel(Groupinglevel({ZALC_GL_DEMO.Alcon GL Account\GL Hierarchy}));	Group Header along with result objects	It assigns Hierarchy level of GL Account for each record to the array BU based on record number.

Step 2

Insert this report as a sub report into main report in report header and create link between the common prompts (If any). Suppress this section from being displayed in main report.

Step 3

Create the following variables in main report.

Filed Name

Formula

Placed at

Use

f_count_declare

shared numbervar rec2;

rec2:=0;

Report Header

It initiates a variable to store record count.

f_count_calculate

shared numbervar rec2;

whileprintingrecords;

rec2:=rec2+1;

Group Header along with result objects

It assigns record number into given variable.

Step 4

The above variables give the same kind of record number as like sub report.

Step 5

Select key figure (on which we want to apply underline) à Right click à Format field à Appearance à Copy the below formula for “Bottom”.

evaluateafter({@f_count_calculate});

shared numbervar array BU;

shared numbervar rec2;

If BU[rec2] <> BU[rec2+1] then crsingleline

5. Hierarchy Node Expansion with Bex query as Source:

Requirement: User wants to view data for a hierarchy field up to a selected level. I.e. if the user selects Product hierarchy level as 2 then report has to display data up to two product child levels.

Issue: It is not possible to bring the hierarchy level number of a dimension from a BEx query dynamically to a Crystal Report.

Work around:

Step 1

Get the hierarchical data for all levels into the Crystal Report.

Step 2

Create a hierarchy level capture formula to capture the hierarchy level of the required dimension as below. For this first we insert group on Hierarchy field.

Hierarchylevel(Gropinglevel(<Filed Name>))

Step 3

Then create a crystal report level Parameter and use it to dynamically hide/show to hierarchy group’s header/footer up to required levels as per the user inputs.

6. Retaining Hierarchy indentation when exporting the report to Excel

Requirement: User wants to view hierarchical data in an indented format even when the report is exported to an Excel sheet.

Issue: It is not possible to retain the hierarchy indentation while exporting the report to an excel sheet.

Work around:

Instead of using the “X” co-ordinate conditional formatting (the traditional method to show indentation in crystal report), use the below formula to left pad the column with spaces. This will help retaining the indentation even when exported to Excel.

Step 1

Create a formula field as below and place that in report instead the original filed.

7. Column value overlap when viewing a report with hierarchy indentations via Launchpad

Requirement: User wants to view hierarchical data in indented format via Launchpad.

Issue: When applying indentation logic in the report, it displays fine when viewed through Crystal designer. However, the column values overlap when viewed through BI Launchpad.

Work around:

Step 1

Copy the CrystalReports.properties file from the location "<Business Objects Install

Directory>\Tomcat6\webapps\BOE\WEB-INF\config\default" to the location

"<Business Objects Install Directory>\Tomcat6\webapps\BOE\WEB-INF\config\custom".

Step 2

Open the file CrystalReports.properties from the custom directory into notepad.

Step 3

Set the parameter crystal_enable_text_clipping=true.

Step 4

Save the changes made in the file.

Step 5

Restart the Tomcat service in CMC.

Step 6

Try to view the crystal reports in CMC/BI Launch Pad. The column width for the fields should be displayed as expected.

8. Creating Combination chart for comparative analysis (Actual Vs Target)

Requirement: User wants to have a “Combination chart” which includes BAR and LINE to have Comparative analysis.

Issue: Crystal Report does not have a direct “Combination chart”. It has either BAR or LINE chart only.

Work around:

Step 1

Insert a normal “BAR” chart and provide the required data as below.

Step 2

When we view the chart preview it displays two bars (series) for each salesman. I.e. one BAR for Actuals, one for Targets.

Step 3

To change second BAR as LINE, click on that bar, right click and select “Format Series”.

Step 4

Change the series as “Line” instead of “Riser”.

Step 5

The chart will look like a “Combination Chart” for comparative analysis.

9. Dynamic Sorting Order

Requirement: User wants to have a “Dynamic Sorting” based on his selection. i.e., based on user selected field, the report has to be sorted.

Issue: Crystal Report does not have a direct dynamic sorting option

Work around:

Step 1

Create a customized “Parameter” which lets the user to select the required field on which he wants to apply sorting.

Step 2

Create a formula field using the below formula which outputs the corresponding field based on user selection.

If

{?Sorting Field} = "Emoplyee ID" then Cstr({Sheet1_.Employee ID})

else If

{?Sorting Field} = "Emoplyee Name" then {Sheet1_.Emp Name}

else If

{?Sorting Field} = "Salary" then Cstr({Sheet1_.Salary})

else

Cstr({Sheet1_.HireDate})

Make sure the above formula field must have all result fields of same data type. If not, convert them by using Cstr as shown above.

Step 3

Apply the sorting on the above formula field.

Step 3

The report sorts the data based on user selection input

Whitepaper on Crystal Reports for Enterprise 4.0 Workarounds

Table of Contents

Brief Overview

1. Has Value () Workaround:

2. Key Figures Calculation (Running Totals):

3. Object Formatting using HTML Tags:

4. Underline/Highlighting a Cell for each change of Hierarchy Level

5. Hierarchy Node Expansion with Bex query as Source:

6. Retaining Hierarchy indentation when exporting the report to Excel

7. Column value overlap when viewing a report with hierarchy indentations via Launchpad

8. Creating Combination chart for comparative analysis (Actual Vs Target)

9. Dynamic Sorting Order

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z