Crystal Report

Previous  Next |  Direct link to this topic

With the Crystal Report Function type you are able to launch Crystal Reports which are an external Reporting program from Business Objects that are owned by SAP (For more information on crystal reports see http://www.businessobjects.com ).

image418

Design the report:

In order to design reports to use with the functionality, you will need to have the Business Objects Crystal Reports designer (Not included with B1 Usability Package) where you design the report you which to launch

image420

NB: BOYUM IT DOES NOT HANDLE SUPPORT ON CREATING THE CRYSTAL REPORT LAYOUT FILES.

Database setup:

Depending on the crystal report that you have created, you might have set the report to connect to the SAP Database. If that is the case, it is needed that you provide information about the database login. These information is entered in the B1 Usability Package Main configuration by navigating to Administration > Add-ons > B1 Usability Package > B1 Usability Configuration (Under the options tab). If you do not provide this information you will experience that each time you run the report it will ask for database login information.

Using the functionality:

When your report is ready you will need to set the configuration to launch it. To do so, go to Administration > Add-ons > B1 Usability Package > Module Configurations > Universal Function > Universal Function Configuration. switch to add mode and choose the type Crystal Report type.

image418

Data

Description

Crystal Report File

Here you choose your crystal report layout you wish to launch. You can type in the path to the file or press the "…" button to choose the file. Once you choose the file it will be scanned for parameters and shown below.

NB: PLEASE BE AWARE THAT IF MULTIPLE USERS NEED TO RUN THE REPORT, THE PATH TO THE FILE SHOULD BE A SHARED PATH (EXAMPLE: \\SERVER\REPORTS\REPORT1.RPT)

Update parameters

If you have already chosen a Crystal Report file, but after selection, you update the layout to include more parameters, you can click this button to update the list of parameters below.

TIP: IF YOU ALREADY ENTERED VALUES FOR THE PARAMETERS AND PRESS UPDATE THE SYSTEM WILL TRY TO PRESERVE EXISTING VALUE AS LONG AS THE PARAMETER NAME IS THE SAME.

Parameters

In Crystal Reports, you have the option pass along parameters to the report (example: a specific customer code or a specific document number).

Column

Description

Parameter

Here the names of the parameters in the report is automatically drawn and shown

Value

Here you enter the value that you wish to send along to the report. You have several options on how to pass along parameters

-         No value

o   If you leave the value empty, the report at launch ask you for a value before showing the report

-         Fixed value

o   You can enter any fixed value (123, abc and so on all depending on your report parameters value)

-         Null value

o   Enter the special keyword $[NULL] to pass along a NULL value

-         Dynamic Syntax Value

o   Use the common dynamic syntax ($[$<item>.<col>.<type>] or $[<table>.<field>.<type>]) to pass along data from the active window in SAP Business One. For more information on the dynamic syntax, see the dynamic syntax guide

§ Sample: $[$8.0.NUMBER] to pass the document number on a Sales Order.

-         DocKey@ keyword

o   This special keyword defined by SBO8.8 will give you the DocEntry-field of all sales and purchase documents

-         ObjectId@ keyword

o   This special keyword defined by SBO8.8 you the object-type of the document type

§ 23 = A/R Quotation

§ 17 = A/R Order

§ 15 = A/R Delivery Note

§ 16 = A/R Return

§ 203 = A/R Down Payment

§ 13 = A/R Invoice

§ 14 = A/R Credit Note

§ 22 = A/P Order

§ 20 = A/P Goods Receipt PO

§ 21 = A/P Goods Return

§ 204 = A/P Down Payment

§ 18 = A/P Invoice

§ 19 = A/P Credit Note

§ 33 = Activity

§ 24 = Incoming Payment

§ 46 = Outgoing Payment

§ 97 = Sales Opportunity

§ [Other can be found in the “SDK Help Center” under BoLinkedObject Enum]

-         SQL value

o   Use the SQL:<your SQL> syntax to execute your SQL where the result of the SQL will be passed along as the value. In <your SQL> you can use the dynamic syntax

§ Sample: SQL:SELECT DocEntry FROM OINV WHERE DocNum = $[$8.0.0]

-         Variables

o   Use one of the 20 input variables(([%0], [%1], [%2], [%3] … [%19]))

-         Range

o   Use this if you have a Crystal Parameter that require a range value (from/to (both inclusive)). In order to specify such a value you can write Range:<from>|<to> or RangeSql:<sql>

Sample1: Range:1|10 will give all values from 1 to 10

Sample2: Range:[%0]|[%1] will give all values between values entered in the variables.

Sample3: RangeSql:SELECT 1,5 will give all values from first SQL column values (1) to second SQL Column value (5)

-         Multi

o   Use this if you have a Crystal Parameter that require multiple values. In order to specify such a value you can write Multi:<value1>|<value2>|...|<valueN> or MultiSql:<sql>

Sample1: Multi:1|3|5 will give values 1, 3 and 5

Sample2: Multi:[%0]|[%1]|[%2] will give the 3 values selected in the 3 variables.

Sample3: MultiSql:SELECT TOP 10 DocEntry FROM OINV ORDER BY DocEntry will give values 1 to 10 from the 10 records the SQL produce (from the first column)

Variables

Here you have the option to define up to 20 Variables ([%0], [%1], [%2], [%3] … [%19]) there can be used as parameters in the Crystal Reports.

image422

Column

Description

Variable

The variable you are defining the source for

Source

The Source. It can a custom value or a list of common values like Business partners etc.

If can find the list of value you wish you also have the option to define a SQL sentence that should retrieve the values as a combo box.

Caption

The caption of the variable (The text shown when prompting the user for the value)

Source SQL

If you in the Source select SQL as source, you define the SQL here (else it is not used). The SQL sentence should be a SELECT statement with two selected columns. first value will be seen as the value of the selection and second will be seen as the description.

SELECT <VALUE>,<DESCRIPTION> FROM <TABLE>

Sample: SELECT CardCode, CardName FROM OCRD

would result in a list of all Business Partners. CardCode will be the value for the customer select and CardName will be the description that help them choose the value.

Title

The title of the window shown when prompting the user for the values

NB: THE DEFAULT TITLE IS ‘DEFINE SURVEY VARIABLES’ IF NO TITLE IS GIVEN.

Source

Here you can define what data source the crystal report is using when launching. A Crystal Report as default have a data source attached to it within the report-files, and choosing the option "As in report" will let the report know that it should use the source as it was designed. In scenarios where you use the report in multiple database or if it is not designed on location you can choose the "Set this database as Data source for report". Doing this will cause B1UP to on the fly tell the report that it should the currently logged in database instead of the on embedded into the report. If you have problem with “Set this database as Data source for report” you can try the “This database Compatible Connection”. This will force the Crystal Report to use predefined value that should work on every system.

Customer connection: If you select 'Custom connection' as the connection type you can specify an alternative connection method using the '- Define new - ' option. This allows you to connect to a different database system.

 

Connection

Press this button in order to set the DB Information connection

image423

NB: This is shared across all configuration so you only need to set it once.

Action: Show Report

If you check this option, you will launch and show the report in the B1UP: Crystal Report viewer. image425

From here you have the option to print and export the report to other formats

Viewer

Here you decide if the Crystal viewer should be external (Window that can be moved outside SAP) or internal (SAP window showing the Crystal)

NB: In SAP Browser Access this selection is ignored and are always served as PDF

Action: Save Report

You also have the option to directly save the report. For that you need to provide a filename and a file type.

TIP: YOU HAVE THE OPTION TO MAKE THE FILENAME UNIQUE BY USING THE FOLLOWING KEYWORDS IN THE FILENAME:

-         $[DATE] FOR CURRENT DATE

-         $[TIME] FOR CURRENT TIME (HH:MM)

-         $[LONGTIME] FOR EXTENDED CURRENT TIME (HH:MM:SS)

-         THE NORMAL $[$ITEM.COLUMN.TYPE] DYNAMIC SYNTAX

 

NB: In SAP Browser Access this selection is ignored and are always served as PDF

File Type

What type the file is

NB: In SAP Browser Access this selection is ignored and are always served as PDF

Open

If the file saved should be opened after generation.

Action: Print Report

Finally, you have the option to print a report directly. For that you will need to provide the printer name. You can either enter it or choose it by pressing the "…" and chose from the list

image427

You also have the option to choose the number of copies.

TIP: NUMBER OF COPIES CAN USE THE DYNAMIC SYNTAX / SQL: SYNTAX

NB: In SAP Browser Access this selection is ignored and are always served as PDF

Action: Block Event

The block event is a special action that you can do after you have launched a report. It lets you block the action that SAP was normally about to do when your launched your report. An example where this is handy is if you replace the normal print functionality of SAP Business One (You want to press the print toolbar button and launch your report but do not want the print from SAP as well (you want to block it))

NB: SAP Browser Access does not technically support this feature

Note on Recommenced Crystal Report Data sources:

Data source on MSSQL

Data source on SAP HANA

We recommend using OLE DB (ADO) data source with provider Microsoft OLE DB Provider for SQL Server

image429

We recommend using ODBC (RDO) data source with following HANA connection string:

DRIVER={B1CRHPROXY};DATABASE=<SOME_DATABASE>;PWD=<SOME_PASSWORD>;SERVERNODE=<SOME_IP_ADDRESS>

 

image431