SQL Report Function

Previous  Next |  Direct link to this topic

Using the SQL Report type, you are able to create your own reports or charts using SQL to replace Reports made using the formatted searches.

image366

The above configuration results in the following report:

Field

Description

Form Title

Here you can define the title of the window showing.

TIP: YOU CAN USE DYNAMIC SYNTAX AND SQL VARIABLES IN THE FORM TITLE TO GENERATE TITLES BASED ON INPUT

Form Width

Where you can define the initial width of the window in pixels

Form Height

Where you can define the initial height of the window in pixels

View type

Where you can select if you want to display the report as a grid or a chart. Read more about using charts here: http://www.boyum-it.com/Link/B1UPCharting/

Form State

Where you can define the initial state of the window (Normal, Minimized, Maximized)

Collapse level

The SQL Report has the option to create collapsible reports. Setting the collapse level is indicated by a number (Set Collapse level to 1 equals that column 1 is collapse-able and so on.)

No Collapse Level =0

image367

Collapse level = 1

image368

Collapse level = 2

image369

Format

When you run the SQL Report, you will get a report with all data shown in plain text, left-justified. Using the Format field, you have the option to make minor changes to the design.

In order to define the Format, press the wizard button

image371

Here you can define formatting of each of the column of the report:

Column

Description

Column #

The column you are defining formatting for

Example in the scenario

image372DocNum is Col 1, CardCode is Col 2, CardName is Col 3 and DocTotal is Col 4

Column title

Here you can define the title of the column

Golden arrow type

Here you define if the column should have a golden arrow and if so what the type of target is for the arrow. You can use any of the predefined or use one of the 3 specialS:

 

LB: Universal Function type to have a Universal function run when you press the golden arrow.

MULTI: That can make a golden arrow that is controlled by object type column and primary key column (see below)

BEAS: These entries will create golden arrows that open windows for the Beas manufacturing add-on. In order for these to work the Beas add-on need to be running and be SAP 9.2 PL04 or higher

NB: Golden arrow Type LB:(Universal function) does not work on B1 Dashboards and will be ignored.

Right justified

Right Justify the data in the column (Example, it makes sense if prices are right-justified)

Sum column

Add a sum row for the column (This can only be done when column data is numeric and collapse level is not used)

Editable

Open the column for editing (Used in combination with the Select-target option where the user should have the option to select a entered value back)

Hide

If you check this, then this column will be hidden. This is beneficial if you need technical values for additional work on the SQL Report (using B1 Validation system) or for the columns of object-type and primary key in use of Multi golden arrow.

Search

If you check this, a search field will be added to the report and you will be able to search in this column of the report (you can mark several columns as searchable)

NB: ONLY WORKS IF YOUR REPORT DOESN'T USE A COLLAPSE LEVEL AND IS SHOWN IN A WINDOW (NOT USE IN SQL REPORT TAB)

NB: IF YOU CHOOSE TO SEARCH ON DATES OR AMOUNTS YOU WILL NEED TO SEARCH USING THE WINDOWS FORMAT.

Column Type

If nothing is defined, then a SQL Report will just show values. You can however override the style of the column to be either Checkbox or Combo box

Checkbox

No formatting of Y/N value

image374

With formatting

image376

Combo box

Turn technical value into meaningful value (NB: Technical value need to be alphanumeric for this to work)

image378

If made editable you can see it is a combo box

image379

Column Width (0=Auto)

By default, a SQL Report will try to best fit the columns based on the values. If you however would like to control the column widths manually you can write the desired width in pixels here

Golden Arrow UF

If you have defined the Golden Arrow type to Universal Function this is the UF run. If type is not LB: UF then leave this fields blank.

Multi Golden Arrow object type column + Multi Golden Arrow primary key column

If Golden Arrow type is Multi then in these columns you the needed data for the error. First it need the object type and second it need the primary key

Example:

We have the following non formatted report

image381

The raw result is this

image383

The goal is to have only 2 columns in final report (Type and DocNum) but because we need golden arrow on DocNum to go to either Order, Delivery or Invoice we take the 2 additional columns (ObjectType and DocEntry)

We then format the report in the following way

image385

-         We mark column 2 and 3 as Hide

-         We mark column 4 as Multi target golden arrow and tell that objectType is in column 2 and primary key is in column 3

The result is the following

image386

Combo box Valid Values SQL

Here you define the SQL that will be converted into valid values if you mark your column as Combo box

image388

This button brings up the CRM for Outlook settings. This is where you define what reporting group this SQL report should belong to.

image389

SQL

Here you write the SQL that creates the data for the report.

TIP: YOU WILL BE ABLE TO USE DYNAMIC VALUES USING THE $[$<ITEM>.<COLUMN>.<TYPE>] FOR DYNAMIC VALUES FROM ITEMS AND $[<TABLE>.<FIELD>.<TYPE>] FOR DATA SOURCES (AS DESCRIBED IN THE VALIDATION CONFIGURATION)

SQL Variables

Here you have the option to define up to 20 SQL Variables ([%0], [%1], [%2], [%3] … [%19]). Compared to Formatted Searches we need to have defined the variables type and description.

In order to define the variable sources, press the Wizard button.

image390

 

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.

Alternative you can define the SQL Variables syntax manually:

This is done by using the following syntax for each of the 20 variables:

%<id>[<type>|<description>];

If you wish to use the variables, then you can format each by changing the syntax.

The following types are possible

        0 (treat as string)

        STRING (as above)

        INTEGER (treat as a whole number)

        NUMBER (treat as a number with decimals)

        DATE (treat as a Date)

        YES_NO (combo box with a Yes and No value (Yes is default))

        NO_YES (combo box with a No and a Yes value (No is default))

        Tableid from one of the following to create Choose from Lists:

o   Business Partner (OCRD), Items (OITM), Warehouse (OWHS), Sales Opportunity (OOPR), GL Account (OACT), Activity (OCLG), Employee (OHEM), Picklist (OPKL), Customer Equipment Card (OINS), ServiceCall (OSCL), Service Contract (OCTR), Solution Knowledge Base (OSLT), Journal Entry (OJDT), Sales/Purchase Documents (OQUT, ORDR, ODLN, ORDN, OINV, ORIN, ODPI, OPOR, OPDL, ORPD, OPCH, ORPC, ODPO), Incoming Payment (ORCT), Outgoing Payments (OVPM), Goods receipt (OIGN), Goods Issue (OIGE), Production Order (OWOR), Project Codes (OPRJ), Campaign (OCPN).

        Tableid from one of the following to create a combo box with their data

o   Item groups (OITB), Sales Employees (OSLP), Shipping types (OSHP), Activitytypes (OCLT), BP Groups (OCRG), BP Groups - Customers (OCRG_C), BP Groups – Suppliers (OCRG_S), BP Priorities (OBPP), Budget distributions methods (OBGD), Countries (OCRY), Currencies (OCRN), Customs groups (OARG), Distribution rules (OOCR), Dunning terms (ODUT), Freight codes (OEXD), Holidays (OHLD), Indicators (OIDC), Inventory Cycles (OCYC), Languages (OLNG), Length and Width UOM (OLGT), Manufactures (OMRC), Payment Methods (OPYM), Payment Methods – In (OPYM_IN), Payment Methods – Out (OPYM_OUT), Payment Terms (OCTG), Pricelists (OPLN), Territories (OTER), Users (OUSR), Weight UOM (OWGT)

        SQL:SELECT <VALUE>,<DESCRIPTION> FROM <TABLE> (treat as combo box with the values from the SQL written).

EXAMPLE 1: IF VARIABLE 0 SHOULD BE OF TYPE DATE AND WITH THE DESCRIPTION "FROM DATE" THE SYNTAX OF %0 SHOULD BE %0[DATE|FROM DATE];

EXAMPLE 2: IF VARIABLE 3 SHOULD BE OF TYPE INTEGER AND WITH THE DESCRIPTION "DOCUMENT NUMBER" THE SYNTAX OF %3 SHOULD BE %3[INTEGER|DOCUMENT NUMBER];

JUST LEAVE THE SYNTAX OF UNUSED VARIABLES SINCE IT ARE IGNORED

Once defined and used in a SQL Report, you will instead of a direct result get a "Define survey variables" window where you enter the prompted data. As shown in this example:

clip0032

As you can see in the above configuration we use [%0] and [%1] to get a from and a to date. Running the SQL Report will before the result give us the following window where we can enter the from and to date defined (%3, %4 and %5 is not shown since they are not used):

image393

Upon pressing the OK button, we get our result as normal based on the entered from and to dates.

image395

Banded row color

With this option you can define if every second row should have color

Without banded rowsimage396

With banded rows

image397

 

 

NB: DUE TO TECHNICAL LIMITATIONS BANDED ROW COLORS CAN’T BE USED IN COMBINATION WITH COLLAPSE LEVEL (WILL NOT COLOR SUB-LEVELS) AND COLUMN SORT (WILL NOT RE-BAND THE COLORS)

Show line numbers

With this option you can define if there should be shown a line number on each row

Without line numbers clip0035

With line numbers

clip0034

 

 

NB: DUE TO TECHNICAL LIMITATIONS SHOW LINE NUMBERS  CAN’T BE USED IN COMBINATION WITH COLLAPSE LEVEL, (WILL only number the rows there can collapse/expand)

Fixed col. count

This option allows you to have x of the first columns fixed so you can see them even if you do horizontal scroll

image398

 

Refresh Option

Here you have the option to add a refresh-button to the Report. If you choose this option you can press the refresh-button on the report to re-run your SQL.

Auto Refresh

With this option you have the ability to make the SQL you show auto refresh it data in a regular interval. This is handy if you have a display that "statically" show a report and need to update automatically.

image399

Select Data Target

This gives you the option to turn your SQL report into an advanced selection-window and send data back to the window that opened it. If your target is a matrix and you select multiple lines, they will all be send to the matrix.

The values sent back are based on the order of the columns. If you as example have an item code in column 1 and a target of $[$38.1.0] the item code will be transferred to the item column

It is also possible to select multiple columns by specifying multiple targets separated by a “;”. If you as an example have a SQL result with an item code in column 1 and a price in column 2 you could specify the following, Select-target: $[$38.1.0];$[$38.14.CURRENCY]. This will result in the item code going to column 1 (item code) and the price going to column 14 (unit price).

If you have a column that you do not wish to transfer, then simply add a “;” In the above example if you had an Item name between the item code and the price you Select-target should be $[$38.1.0];;$[$38.14.CURRENCY]

image400

NB: Please note that the Select Back system have the following restrictions:

SELECTING MULTIPLE BACK IS ONLY SUPPORTED ON SALES AND PURCHASE DOCUMENTS + SAP Generated UDO's!

SELECTING MULTIPLE BACK TO BOM-ITEMS IS NOT SUPPORTED!

SELECTING ANY OTHER SINGLE DATA BACK CAN ONLY TARGET HEADER LEVEL FIELDS (aka Select Back to Grid like structures on other Add-ons or B1UP is not supported - This include example no support for selecting back to an UDT Handler.)

NB: PLEASE NOTE THAT THE FIRST VALUE YOU SELECT BACK TO A MATRIX NEEDS TO BE THE PRIMARY FIELD OF THAT MATRIX THAT FORCE A NEW LINE. EXAMPLE: IF YOU WISH TO SELECTED BACK TO SALES ORDER LINES THE FIRST THING YOU NEED TO SELECT BACK NEED TO BE THE ITEM CODE (THAT WHEN SELECTED BACK WILL GENERATE A NEW LINE FOR NEXT ROW). IF IT IS NOT THAT YOU WILL GET A BAD VALUE ERROR SETTING THE VALUES.

As you can see the normal Close-button is replaced by a select and a cancel button that allow you to select data.

First column is selection checkbox

Default select back system is by row-selection but in some cases people do not find this intuitive, want to have a default selection or want to select based on the value of value in an editable column. For that reason, you can turn the option to make the first column of the SQL be a Y/N value displayed as a selection checkbox.

image401

image402

NB: IF YOU USE THIS FEATURE, IT WILL IGNORE ANY SETTINGS IN COLLAPSE LEVEL SINCE SAP SDK CAN’T SHOW CHECKBOXES IN A COLLAPSED STATE.

Form Type Suffix

This field can be used to ensure unique form types for each SQL Report. This allow you to add unique function buttons and other settings per report (or a group of reports if you give them same suffix). It is recommended to always have a suffix but for legacy reasons you can leave it blank

 

NB: Suffix will suggest the same as your code but due to people that wish to group reports and technical reason the prefix can max be 6 chars so if you write a code that is over 6 chars it will not reflect on the suffix

 

NB: If you need to add additional authorizations to an SQL was you can only have a 1 char suffix (else the total form type goes beyond 20 chars. The limit for the actual report is 4 chars)