Showing posts with label crystalreports. Show all posts

Monday, February 14, 2022

Crystal Reports: How to suppress the display of a field / column with a Boolean condition

A. Use Crystal Report Suppress

1. Open the rpt template in SAP Crystal Reports, right click on the field you want to suppress, and select Format Field.

2. Select the Common tab in the Format Editor window, check the Suppress, and click on the corresponding X-2 button.

3. In the popped up window, enter a boolean value to specify when to suppress the field. A true value will suppress the field display and a false value will allow it to display.

4. Click the Save and close to save.

B. Use Crystal Report Formula

1. Click the View menu and select Field Explorer.

2. Right-click the Formula Fields and choose New, enter a formula name, and click OK.

3. Write your condition for displaying the field values.

4. Click the Save and close to save.

Tuesday, October 20, 2020

Crystal Reports: ORA-12170: TNS:Connect timeout occurred [Database Vendor Code: 12170]

In the SAP Crystal Reports editor, you open the Database Expert and trying to view/edit the command, after you have entered your database login credentials you received the following error.

This can be caused by several issues. The following are some of the ways to fix it.

1. ping the database machine to make sure you can access that machine.

2. Check if the database host of your Oracle Instant Client is using the correct IP address. If you are accessing the database on a remote machine, localhost or 127.0.0.1 will not work.

a. Open your Control Panel.

b. Open the Administrative Tools window.

c. Right-click on the ODBC Data Sources and open it.

d. Select the System DSN tab to look for the Driver used by your data source.

e. Select the Drivers tab and locate your driver.

f. Open a file explorer, go to Program Files (x86)\Oracle Instant Client\<your driver>.

g. Open the tnsnames.ora file in a text editor, change the HOST = <the correct database IP address>

3. Make sure the firewall is not blocking the connection.

-----------------------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Friday, June 19, 2020

Crystal Reports: java.sql.SQLException: ORA-00904: "table_name"."column_name": invalid identifier

This error can be very misleading. It may have nothing to do with the database table not having the column.

I had a Crystal Report query as below, and it kept throwing such error util I modified the query.

WITH preResult1 AS (
select
max(age) as age,
personDeptId,
personId,
personName
from person, department
where personDeptId = deptId
group by personDeptId, personId, personName
),
preResult2 as (
select
age,
personDeptId,
personId,
personName,
ROW_NUMBER() OVER (PARTITION BY personDeptId ORDER BY age desc) as age_rank
from preResult1
)
SELECT
personName,
age,
numberOfTitles,
awardsReceived
FROM preResult2
INNER JOIN activity on preResult2.personId = activity.personId
WHERE age_rank <= 5

After I modified the query as shown below, the error was gone.

WITH preResult1 AS (
select
max(age) as age,
personDeptId,
personId,
personName,
numberOfTitles,
awardsReceived
from person, department, activity
where personDeptId = deptId and person.personId = activity.personId
group by personDeptId, personId, personName, numberOfTitles, awardsReceived
),
preResult2 as (
select
age,
personDeptId,
personId,
personName,
numberOfTitles,
awardsReceived,
ROW_NUMBER() OVER (PARTITION BY personDeptId ORDER BY age desc) as age_rank
from preResult1
)
SELECT
personName,
age,
numberOfTitles,
awardsReceived,
FROM preResult2
WHERE age_rank <= 5

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

Monday, November 12, 2018

Crystal Reports: Retrieve a license key from a local machine

1. Open the SAP Crystal Reports console.

2. Click at the Help on the top menu and select License Manager

3. Write down the first part of the Existing key codes that is displayed somewhere you can reference later.

4. Click at the Window icon at the bottom-left corner of your computer monitor, type regedit, and open the regedit.

5. In the Registry Editor window, select the HKY_LOCAL_MACHINE in the left panel.

6. Type Ctrl+F to open the Find window. In the Find what slot, type the part of key code that you have saved in step 3. Click the Find Next button. The search may take some time to accomplish.

7. After the search finishes, read the complete license key from the right panel of the Registry Editor.

-----------------------------------------------------------------------------------------------------------------
Watch the blessing and loving online channel: SupremeMasterTV live

Friday, July 21, 2017

Crystal Reports: How to wrap column text?

Open the report in the designer.

1. Right click on the field and select Format Field.
2. In the popped up Format Editor window, select the Common tab. Then, check the Can Grow check box and enter the maximum number of lines allowed.

---------------------------------------------------------------------------------------------------------------

Wednesday, October 5, 2016

crystal reports group sum and total sum

It is very often that a report displays data grouped by some category, displays a group summary at the end of each group and a total summary at the end of the report.

Following is about how to create the formula fields to accumulate the counts and display them.

If it is a simple account such as the sum or count of a single field, you can insert a summary by

1. Click the Insert on the top menu and select Summary.
2. In the Insert Summary window, choose the field you would like to use, the type of calculation you would like to perform on this field, and the location you want this summary to be on your report.
3. Click OK to close the window.
4. Find the summary on your report, right click on it and select Format Field.
5. Click each tab on the popup Format Editor window to specify the appearance of it.

However, if you want to create a complicated counter or summary that involve more than one field or complex logic, you may following the guide below to create it.

1. Open the Formula Workshop. From top menu, click Report, then select Formula Workshop
2. Create a reset formula, which will set the count to zero when a new group starts to display. Right click on Formula Fields and select new, enter the formula name, and click OK. Enter the following into the lower-right pane.

Shared Numbervar recordCount;
recordCount := 0;

For example you want to count on the records which have been created or updated, you can set it like this.

  Shared Numbervar countInserted;
Shared Numbervar countUpdated;
countInserted:=0;
countUpdated :=0;

Place the formula in group header.

3. Create the increment formula.

In general, it is like this.

Shared Numbervar recordCount;
recordCount := recordCount + 1;

As in the above example, it will look like this.

  Shared Numbervar countInserted;
  Shared Numbervar countUpdated;

if(not isModified({<createDate>}, {<modifyDate>})) then
countInserted:=countInserted+1;
else
  countUpdated:=countUpdated+1;

countInserted;
countUpdated;

Add the formula to the detail section.

4. Create the display formula(s).

In general

Shared Numbervar recordCount;
Shared Numbervar totalRecordCount;
totalRecordCount := totalRecordCount + recordCount;
recordCount;

As in the example, you need to created two display formulas, one for the newly created records and one for the updated records.

Add the formula(s) to the group footer.

5. Create the display total formula(s).

In general

Shared Numbervar totalRecordCount;
  totalRecordCount;

As in the example, create two display total formulas, one for the created and one for the updated records.

Add the formula(s) to the report footer.

Reference:

1. How to count number of grouped rows in the Crystal Report

---------------------------------------------------------------------------------------------------