Menus and Reports can be configured using the Manager Add-On for SAP Business One. Multi-level menus are configurable using a flexible menu setup that provides a navigable way for users to access content within Customer Portal.

Menu management consists of:

  • Configuring Menu Entries and Options. Menu entries define the clickable link that web users see. Each menu entry is tied to a menu option (report, static link, collection, etc.) which must be configured before the menu entry (web link) is accessible.
  • Positioning Menu Entries. Once a menu entry has been configured, it can be moved around so that it is placed where users need to see it.

Once a menu has been configured, advanced settings may be applied:

  • Configuring Golden-Arrow drill-down links. After a grid report has been created and assigned to a menu entry, drill-downs may be needed to view advanced details (for example, order document details, or payment details).
  • Report Views. Report Views allow users to view different configurations for the same report. For example, different charts may be configured based on the same report, or views may include date-specific filters to display historical data in a pivot. Report views can be managed as described in this section.
  • Other Menu Entry Tasks. This section provides guidance on best practice recommendations and advanced tasks, such as deleting reports, managing orphaned reports (reports without a corresponding menu entry), and updating report details for menu entries.

Configuring Menu Entries and Options

Menu entries define the clickable link that web users see. Each menu entry is tied to a menu option (report, static link, collection, etc.) which must be configured before the menu entry (web link) is set up. Menu options (reports, for ex.) may be re-used in multiple menu entries (web links), but generally each menu entry is tied to a single menu option and vice versa.

The walkthrough below shows how to configure menu entries and their corresponding menu options for display on the web.

  1. In SAP Business One, Start the Manager Add-On.

  2. Open zedSuite Manager > Manage Menu to bring up the Menus dialogue.

    zedSuite Manager - Manage Menus entry.
    zedSuite Manager - Manage Menus entry.
  3. Open the Edit Menu Entries screen by pressing the + button (for new menu entries) or using the golden-arrow (for existing menu entries).

    Manage Menu Dialogue: Select the portal (1) and menu (2) from the dropdown. Add a new Category (3) or remove a selected category (4). Move the selected menu entry left (5) or right (6) to nest or un-nest it. Edit Menu Entry details (7). Move the selected menu entry up or down in the menu using the arrows (8) and (9).
    Manage Menu Dialogue: Select the portal (1) and menu (2) from the dropdown. Add a new Category (3) or remove a selected category (4). Move the selected menu entry left (5) or right (6) to nest or un-nest it. Edit Menu Entry details (7). Move the selected menu entry up or down in the menu using the arrows (8) and (9).
  4. Complete the Menu Entry details:

    Web Name (1); Type of menu entry and Link to Type details, if applicable (2); Linked menu option details (3); Menu Icon, if desired (4); Custom CSS Class for alternate icons (5); Optional CSS Class (6); Position of the menu entry relative to the selected entry (7); Active flag (8).
    Web Name (1); Type of menu entry and Link to Type details, if applicable (2); Linked menu option details (3); Menu Icon, if desired (4); Custom CSS Class for alternate icons (5); Optional CSS Class (6); Position of the menu entry relative to the selected entry (7); Active flag (8).
    • Name: The web-friendly name for this menu entry, as it should appear to the end user.

    • Type: Sets the type of this menu entry. Valid options are:

      • Category: Indicates that this menu entry will be used as a header for other menu entry content.

        Does not require a link.

      • Grid: Grids are a report type that display as a list of data.

        Requires a Link to point to the report details for this menu entry. See Creating Menu Options (Reports, Static Links, etc) below.

      • Pivot: Pivots are a report type that display configurable and interactive table options (for example, sales over time analysis).

        Requires a Link to point to the report details for this menu entry. See Creating Menu Options (Reports, Static Links, etc) below.

      • Chart: Charts are a specialized version of Pivots that display results in a graphical format.

        Requires a Link to point to the report details for this menu entry. See Creating Menu Options (Reports, Static Links, etc) below.

      • Static: Static links point to pages within the Portal, or to outside content (such as a marketing webpage, or email login page).

        Requires a Link to point to the Static Link information for this menu entry. See Creating Menu Options (Reports, Static Links, etc) below.

      • Collection: Collections are configured B2B Marketplace product lists that are displayed to customers with a B2B Role. Configuration is required before Collections can be displayed. See B2B Marketplace Administrator’s Guide for a walkthrough of advanced B2B Marketplace Admin options.

        This applies to B2B Marketplace implementations only. Otherwise, do not use this option.

        Requires a Link to point to a specific Collection for this menu entry. See Creating Menu Options (Reports, Static Links, etc) below.

    • Link: This points to the menu option details for this menu entry. This is not applicable to Category menu entries.

      For example, if the Type is set to “Static”, then the Link points to the specific Static Link details; if the Type is “Grid”, then the Link points to the specific query details.

    • Menu Icon: If desired, an icon can be assigned to the menu entry. This loads a font-awesome icon in front of the menu entry.

      Best practice recommendation is to set an icon for all top-level menu entries. Sub-entries should only contain icons on rare instances for entries that are very important or very common. For example: In an admin sub-menu, “Time Sheet” static link might have an icon because everyone uses this daily, whereas remaining admin reports would not have an icon.

      Sample top-level menu entries with leading icons.
      Sample top-level menu entries with leading icons.
    • Opt CSS Class: If desired, a CSS class may be applied to the menu entry, allowing for a font-awesome icon to be displayed prior to the entry. Do not use this option if a Menu Icon has already been applied.

      Note the best practice recommendation for Menu Icons also applies here.

    • Position: This section is mostly informational, although it can be used to make minor position adjustments to the menu entry. Use the main Menu Management screen to move entries.

    • Active: When checked, the menu entry will be available for users who have the necessary roles. When un-checked, the menu will not be displayed for anyone.

Creating Menu Options (Reports, Static Links, etc.)

Menu options define the details of what’s linked on the web. For example, reports, static links, or collections. Menu options are the details displayed by menu entries, and may be configured as part of the menu entry setup. They can also be managed separately using the Manage Reports section of the Manager Add-On in SAP Business One.

Menu Option details may be accessed in one of the following ways:

  • Go to zedSuite Manager > Manage Menu, then select the application and menu. From here, edit an existing menu option using the golden-arrow drill-down link under the “Item Name” column.

    To edit an existing Menu Option's details, go to Manage Menu (1); Select an application (2), Menu (3) and Menu Option (4), then edit (4) or add (5) Menu Option details.
    To edit an existing Menu Option's details, go to Manage Menu (1); Select an application (2), Menu (3) and Menu Option (4), then edit (4) or add (5) Menu Option details.
  • Go to zedSuite Manager > Manage Menu, then select the application and menu. Add or edit a menu entry, and select the menu entry type and open the menu option details using the golden-arrow drill-down.

    To edit Menu Option details for a new or existing Menu Entry, go to Manage Menu (1); Select an application (2) and Menu (3), then add (4) or edit (5) a Menu Entry; Select Type (6) and edit the menu option (7); Open the Menu Option details (8) or create a new Menu Option (9).
    To edit Menu Option details for a new or existing Menu Entry, go to Manage Menu (1); Select an application (2) and Menu (3), then add (4) or edit (5) a Menu Entry; Select Type (6) and edit the menu option (7); Open the Menu Option details (8) or create a new Menu Option (9).
  • Go to zedSuite Manager > Manage Reports, select an application, then add or edit a menu option. This tool provides the ability to manage menu options (i.e. reports and static links) in the same way as the Manage Menu screen, but does not require a menu entry before the menu option can be configured. Its primary purpose is to provide a way to manage Options (i.e. Reports) that have been orphaned (i.e. they no longer have a menu entry).

    Open Manage Reports (1); Select the application (2); add (3) or edit (4) a Menu Option.
    Open Manage Reports (1); Select the application (2); add (3) or edit (4) a Menu Option.

Once the Menu Option Details screen is open, it may be edited as follows:

  1. Assign a name. This is an admin-friendly name that will not be displayed to a web user.

  2. Select the type.

  3. Assign roles that are permitted to access this report. This will be used to determine whether the related menu entry appears on the web.

  4. Configure option-specific details (see the relevant section below).

Creating Static Menu Options

Static menu options define links, which may be either within the Portal or a page on an external website. They may open in a new tab (ex. for external pages) or within the current window (ex. for sub-pages within the Portal website).

For Static Menu Options (1), assing roles (2) and configure Link Info (3). Set URL (4) and where the link opens (5).
For Static Menu Options (1), assing roles (2) and configure Link Info (3). Set URL (4) and where the link opens (5).

To configure a Static Link menu option, Start by opening the menu option as described above, then complete the following setup:

  1. Set the URL to the desired link. This may be used for internal links (for example, customizations) or external links (for example, corporate resources).

  2. Set the Open Link “target”.
    • Choose _blank to open the link in a new window.
    • Choose _self to open the link normally in the current window.
    • Choose iframe to open the link within an iframe. This can be used in customizations to open certain links within a customized iframe pane.
  3. Press OK to apply changes.

Creating Report Menu Options (grids, pivots, charts)

Report menu options link directly to a configured report (either a Grid, Pivot, or Chart). General report setup is the same for all three types, however Pivot and Chart queries have restrictions on how they are set up in SAP Business One.

All queries must belong to a category starting with B1WebAPI_. For example, B1WebAPI_CP indicates queries that are used by Customer Portal, while B1WebAPI_SU indicates queries that are used by Sales User for Employee Portal.

Grids - Query Setup

Grid Reports offer a straightforward way to display B1 query data on the web. Any query may be displayed as a grid.

In some cases, there may be multiple reports that are similar, but whose only differences are which fields are displayed. In this case, consider using a single report with different Report View Layouts. This will allow web users to see the layout that best suits their needs, and minimizes the number of reports that have to be managed.

In the Query Info section (3), select a query category (4) and query (5). Set the cache duration (6).
In the Query Info section (3), select a query category (4) and query (5). Set the cache duration (6).

Grid Reports can be configured to use any query belonging to a B1WebAPI_ category.

Pivot and Chart Query Setup

The query requirements for Pivots and Charts are the same (referred to below as Pivot reports).

A Pivot report can be used to concisely display values over time (such as sales reports or cost analysis reports). By configuring different report views, the same query can be used to display different views into the same data

For example, a general sales report may be configured with different views for Year-to-Date versus Month-to-Date sales figures. Both views rely on the same basic query, but the data is targeted for a different purpose.

Once a Chart has been fully configured and can be accessed by users, report views for different Chart layouts may be configured. An admin may do this to provide a good starting point for reporting where a good visual reference is needed (see Report Views below).

On the web, a Chart layout may be configured using the Advanced Menu, and by dragging/dropping fields into the Pivot at the bottom of the page. If no pivot is visible, open the Advanced Menu and ensure the option “Show Grid” is enabled.

Chart Options

Advanced chart options can be configured to display different charts with the same query. Options are described below.

Option Description
Chart Type Determines how the data is displayed.
Palette Color scheme for the selected chart.
Chart Width Pixel width of the chart area, including labels.
Chart Height Pixel height of the chart area, including labels.
Show Grid If enabled, shows the Pivot grid which determines the data to display in the Chart.
Show Legend If enabled, shows a legend of different data types. What the legend displays depends on the chart type. For example, Line charts display a legend of lines, while stacked bar charts display a legend of stack segments.

Some chart types can’t show legends for multiple data sets, these are noted below and include: Pie, Doughnut and Funnel charts.
Show Column Totals Not used.
Show Row Totals Not used.
Show Point Labels For certain chart types only, if enabled, labels for data points are displayed on the chart.

This option is useful for Pie, Doughnut and Funnel Charts.
Chart Type Advanced Options enabled - Supported   disabled - Not Supported   - Conditionally Supported
Chart Type Show Grid Show Legend Show Column Totals Show Row Totals Show Point Labels
Bar Charts enabled enabled disabled disabled disabled
Stacked Bar Charts enabled enabled disabled disabled disabled
Full Stacked Bar Charts enabled enabled disabled disabled disabled
Side By Side Stacked Bar Charts enabled enabled disabled disabled disabled
Side By Side Full Stacked Bar Charts enabled enabled disabled disabled disabled
Pie Charts enabled disabled disabled enabled
Doughnut Charts enabled disabled disabled enabled
Funnel Charts enabled disabled disabled enabled
Point Charts enabled enabled disabled disabled disabled
Line Charts enabled enabled disabled disabled disabled
Stacked Line Charts enabled enabled disabled disabled disabled
Full Stacked Line Charts enabled enabled disabled disabled disabled
Step Line Charts enabled enabled disabled disabled disabled
Spline Charts enabled enabled disabled disabled disabled
Scatter Line Charts enabled enabled disabled disabled disabled
Swift Plot Charts enabled enabled disabled disabled disabled
Area Charts enabled enabled disabled disabled disabled
Step Area Charts enabled enabled disabled disabled disabled
Spline Area Charts enabled enabled disabled disabled disabled
Stacked Area Charts enabled enabled disabled disabled disabled
Stacked Spline Area Charts enabled enabled disabled disabled disabled
Full Stacked Area Charts enabled enabled disabled disabled disabled
Full Stacked Spline Area Charts enabled enabled disabled disabled disabled
Range Area Charts enabled enabled disabled disabled disabled
Radar Point Charts enabled enabled disabled disabled disabled
Radar Line Charts enabled enabled disabled disabled disabled
Radar Area Charts enabled enabled disabled disabled disabled
Bar 3D Charts enabled enabled disabled disabled disabled
Stacked Bar 3D Charts enabled enabled disabled disabled disabled
Full Stacked Bar 3D Charts enabled enabled disabled disabled disabled
Manhattan Bar Charts enabled enabled disabled disabled disabled
Side By Side Stacked Bar 3D Charts enabled enabled disabled disabled disabled
Side By Side Full Stacked Bar 3D Charts enabled enabled disabled disabled disabled
Pie 3D Charts enabled disabled disabled enabled
Doughnut 3D Charts enabled disabled disabled enabled
Funnel 3D Charts enabled disabled disabled enabled
Line 3D Charts enabled enabled disabled disabled disabled
Stacked Line 3D Charts enabled enabled disabled disabled disabled
Full Stacked Line 3D Charts enabled enabled disabled disabled disabled
Step Line 3D Charts enabled enabled disabled disabled disabled
Area 3D Charts enabled enabled disabled disabled disabled
Stacked Area 3D Charts enabled enabled disabled disabled disabled
Full Stacked Area 3D Charts enabled enabled disabled disabled disabled
Step Area 3D Charts enabled enabled disabled disabled disabled
Spline 3D Charts enabled enabled disabled disabled disabled
Spline Area 3D Charts enabled enabled disabled disabled disabled
Stacked Spline Area 3D Charts enabled enabled disabled disabled disabled
Full Stacked Spline Area 3D Charts enabled enabled disabled disabled disabled
Range Area 3D Charts enabled enabled disabled disabled enabled

Example: Configuring a Line Chart

To display details in a Line Chart drag and drop the fields as follows:

  • Left-hand column should contain dates. Example 1 shows Year and Month for all time. Example 2 shows Year and Month, with a filter only showing 2015/2016.
  • Upper-left section is for amounts (sales totals, quantities, etc.). Example 1 shows the dollar amount of the sales, while Example 2 shows the quantity of items that were sold.
  • Top row may optionally contain categories for the data, for example Item Groups. Both examples do not use this option.
Line Chart Example 1: Sales trends over time. Left fields: Date_Year / Date_Month; Top field blank; Data field (upper-left): Amount
Line Chart Example 1: Sales trends over time. Left fields: Date_Year / Date_Month; Top field blank; Data field (upper-left): Amount
Line Chart Example 2: Sales quantities for two years. Left fields: Date_Year (filtered on two years) / Date_Month; Top field blank; Data field (upper-left): Quantity
Line Chart Example 2: Sales quantities for two years. Left fields: Date_Year (filtered on two years) / Date_Month; Top field blank; Data field (upper-left): Quantity

Example: Configuring a Pie Chart

To display details in a Pie Chart drag and drop the fields as follows:

  • Left-hand column contains the field used to assess the data, typically date fields. Both examples show years filtered to 2015/2016.
  • Upper-left section is for amounts (sales totals, quantities, etc.). Both examples show sales total amounts.
  • Top row may optionally contain categories for the data, for example Item Groups. This will show a distinct chart for every category of data. Example 1 doesn’t break down the analysis. Example 2 shows data broken down by Item Group.
Pie Chart Example 1: Sales comparison between 2015 and 2016. Left field: Date_Year (filtered on two years); Top field blank; Data field (upper-left): Amount.
Pie Chart Example 1: Sales comparison between 2015 and 2016. Left field: Date_Year (filtered on two years); Top field blank; Data field (upper-left): Amount.
Pie Chart Example 2: Sales comparison for each item group. Left field: Date_Year (filtered on two years); Top field: Item Group; Data field (upper-left): Amount.
Pie Chart Example 2: Sales comparison between for each item group. Left field: Date_Year (filtered on two years); Top field: Item Group; Data field (upper-left): Amount.

Example: Configuring a Stacked Bar Chart

To display details in a Stacked Bar Chart drag and drop the fields as follows:

  • Left-hand column contains the field used to assess the data, typically date fields. The example below shows breakdowns by Year/Month.
  • Upper-left section is for amounts (sales totals, quantities, etc.). This example shows amounts spent by the logged-in customer.
  • Top row contains categories for the data, the “stack” of the stacked bar, for example Item Groups. This example breaks down sales within a time frame by item group.
Stacked Bar Chart Example 1: Customer Purchases by Item Group. Left fields: Date_Year / Date_Month; Top field: Item Group; Data field (upper-left): Line Total
Stacked Bar Chart Example 1: Customer Purchases by Item Group. Left: Date_Year / Date_Month; Right: Item Group; Data (upper-left): Line Total

Parameters for Reports

Any type of query-based report (Grid, Pivot or Chart) may include parameters. Parameters may optionally be applied to a query to be configured within a menu option.

Parameters can be included in a query with the following syntax: [%myParam]

For example, the following query includes parameters for SLPCode, StartDate and EndDate:

SELECT
   Top 100
   O1.DocEntry AS 'ID',
   O1.DocNum as 'RefNum',
   O1.CardCode as 'bp Code',
   O1.CardName as 'bp name',
   O1.DocDate as 'Date',  
   O1.NumAtCard as 'CustRefNum',
   CASE
       WHEN O1.CANCELED = 'Y' THEN 'Cancelled'
       WHEN O1.DocStatus = 'C' THEN 'Closed'
       ELSE 'Open'
   END As Status,
   O1.DocTotal as 'Total $'
FROM ORDR O1

WHERE (O1.SLPCODE = [%SLPCODE])   
   AND (O1.DocDate >= [%StartDate])
   AND (O1.DocDate <= [%EndDate])

ORDER BY O1.DocDate DESC

Parameters may be configured with specific values (for example, SLPCODE may be pulled from the current user’s assigned Sales Person), or they may be configured to allow users to specify their value (for example, with a drop-down or free-text field).

Configuring Parameters

Parameters which are populated automatically can be defined in the Report menu option window on the Parameters tab.

Set the Parameter Type to “Profile (Xpath)” and select the appropriate value to populate that parameter.

For example, if the report is specific to a Sales Person, select “EM: SlpCode” to assign the logged in employee (“EM”) user’s Sales Person Code (“SlpCode”).

Configuring User Specified Parameters

User Supplied Parameters (USPs) allow the web user to define the info used by the report query. USPs can be configured as described below. Select the Parameter Type “User Supplied”, and configure the desired type below.

Supported parameter types that may be configured are:

  • DateTime
  • Decimal
  • Integer
  • List
  • Query
  • Text
DateTime

DateTime parameters allow web users to select a date using a browser-friendly date picker.

A default date may optionally be set.

Decimal, Integer, Text

These parameter types allow web users to enter a value into a field.

A default value may optionally be set.

List

List parameters allow web users to select an option from a drop-down list of pre-supplied values.

For every option that should be displayed to users in the drop-down, press the plus button to add a new list option. Fill in Choice Text (the text shown to the user) and populate Value (the value assigned to the query parameter when the option is chosen by the user). The up/down arrows on the right can be used to sort the list of options.

A default value may optionally be set.

Query

Query parameters allow web users to select an option from a drop-down list, as supplied by a query.

First, create a query that will define the drop-down list of options. The first query column will define what is passed to the report when a user selects the option, while the second query column will define what is shown to users. For example, if we want to provide a list of all sales people, we could define the query:

  SELECT SlpCode, SlpName
  FROM OSLP
  WHERE SlpCode > 0

No default value can be defined for query-based user supplied parameters.

Multi-currency Query Building

Reporting in multi-currency environments requires additional query configuration that’s not required in a single-currency environment. To help simplify the creation of robust multi-currency reports, Vision33 provides pre-configured SQL functions, in addition to queries already configured to use those functions.

These functions automatically return either the local currency or the foreign currency, whichever is appropriate for the given Business Partner.

On SQL, these functions can be accessed within a B1 query, for example, within the query B1WebAPI_CP/OpenOrders.

On HANA, these functions can be accessed from within a stored procedure only, so custom queries that require multi-currency support must be created as stored procedures in HANA Studio. These queries may then be referenced from within SAP Business One, as shown in the HANA example below. An out-of-the-box example of this on Hana can be found in the query B1WebAPI_CP/OpenOrders.

If you have a single-currency query already, you may convert this to a multi-currency query in the following way:

  1. Identify any currency fields in your single-currency query which have a “Foreign Currency” counterpart (for example, DocTotal and DocTotalFC or VatSum and VatSumFC).
  2. Include a column for the currency ISO code within your query. This will clearly identify to the user which currency is being displayed in the query. For example $100 USD versus $100 CAD.
  3. Use the following functions to display multi-currency details:
    • SQL Server:
      • dbo.ZEDS_CPSelectAmount([Card Code], [Local Currency Field], [Foreign Currency Field]) - this will identify the correct value (local currency or foreign currency) based on the given card code.
      • dbo.ZEDS_CPCurrency([Card Code]) - this will identify which currency ISO code is used by the given card code.
    • HANA:
      • "ZEDS_CPSelectAmount" (IN CardCode varchar(50), OUT IsFC tinyint) - this will set the flag IsFC (Is Foreign Currency) to 0 or 1 for the indicated Business Partner (BP). 1 indicates that the BP is using a foreign currency. 0 indicates that the BP is using either the local currency, or is set to use “All currencies”.
      • "ZEDS_CPCurrency" (IN CardCode varchar(15), OUT curCode varchar(3)) - this will set the currency code curCode to the indicated Business Partner’s currency.

Multi-currency Query Building - SQL Example: Display all invoices for the current user

In this scenario, we’re starting with a SQL query which displays all invoices for the specified CardCode, but only in the local currency. This means a customer who performs all transactions in EUR will see USD values (assuming a USD company).

Note that this is specific to SQL. For a Hana version of this example, see the following section.

SELECT
    O1.DocEntry,
    NumAtCard as 'Ref #.',
    CASE DocStatus
        WHEN 'O' THEN 'Open'
        WHEN 'C' THEN 'Closed'
        ELSE 'Not for sale'
    END as 'Status',
    O1.BPLName as 'Branch',
    O1.DocDate as 'Invoice Date',
    O1.DocDueDate as 'Due Date',
    O1.VatSum as '$Tax',
    O1.DocTotal as '$Grand Total'
FROM OINV O1
WHERE O1.CARDCODE = [%cardcode]
    AND ISNULL(O1.U_zed_ShowCP, 'Y') <> 'N'
ORDER BY O1.DocEntry DESC

To make this compatible with mutli-currency environments, we need to update the query as follows:

Identify any currency fields that have a “Foreign Currency” counterpart.

This query has VatSum and DocTotal fields that have Foreign Currency alternate values (VatSumFC and DocTotalFC respectively), as highlighted below.

SELECT
    O1.DocEntry,
    NumAtCard as 'Ref #.',
    CASE DocStatus
        WHEN 'O' THEN 'Open'
        WHEN 'C' THEN 'Closed'
        ELSE 'Not for sale'
    END as 'Status',
    O1.BPLName as 'Branch',
    O1.DocDate as 'Invoice Date',
    O1.DocDueDate as 'Due Date',
    O1.VatSum as '$Tax', -- LOCAL Tax amount
    O1.DocTotal as '$Grand Total' -- LOCAL Doc Total amount
FROM OINV O1
WHERE O1.CARDCODE = [%cardcode]
    AND ISNULL(O1.U_zed_ShowCP, 'Y') <> 'N'
ORDER BY O1.DocEntry DESC

Add a column for the currency

Now that we’ve identified the fields with foreign alternatives, we need to add a field to identify which currency is being displayed, as highlighted below:

SELECT
    O1.DocEntry,
    NumAtCard as 'Ref #.',
    CASE DocStatus
        WHEN 'O' THEN 'Open'
        WHEN 'C' THEN 'Closed'
        ELSE 'Not for sale'
    END as 'Status',
    O1.BPLName as 'Branch',
    O1.DocDate as 'Invoice Date',
    O1.DocDueDate as 'Due Date',
    O1.VatSum as '$Tax',
    O1.DocTotal as '$Grand Total'
    , dbo.ZEDS_CPCurrency(O1.CardCode) as 'Currency' -- NEW Currency ISO Code field
FROM OINV O1
WHERE O1.CARDCODE = [%cardcode]
    AND ISNULL(O1.U_zed_ShowCP, 'Y') <> 'N'
ORDER BY O1.DocEntry DESC

Use the FC functions

Now we need to update the query so it pulls the correct currency values to match the Business Partner’s currency with the ZEDS_CPSelectAmount function, as shown below:

SELECT
    O1.DocEntry,
    NumAtCard as 'Ref #.',
    CASE DocStatus
        WHEN 'O' THEN 'Open'
        WHEN 'C' THEN 'Closed'
        ELSE 'Not for sale'
    END as 'Status',
    O1.BPLName as 'Branch',
    O1.DocDate as 'Invoice Date',
    O1.DocDueDate as 'Due Date',
    dbo.ZEDS_CPSelectAmount(O1.CardCode,O1.VatSum, O1.VatSumFC) as '$Tax', -- NEW Dynamically selected Tax amount (Local or Foreign)
    dbo.ZEDS_CPSelectAmount(O1.CardCode, O1.DocTotal, O1.DocTotalFC) as '$Grand Total' -- NEW Dynamically selected Doc Total amount (Local or Foreign)
    , dbo.ZEDS_CPCurrency(O1.CardCode) as 'Currency'
FROM OINV O1
WHERE O1.CARDCODE = [%cardcode]
    AND ISNULL(O1.U_zed_ShowCP, 'Y') <> 'N'
ORDER BY O1.DocEntry DESC

The end result is a query that displays the current user’s currency details (whether foreign or local):

SELECT
    O1.DocEntry,
    NumAtCard as 'Ref #.',
    CASE DocStatus
        WHEN 'O' THEN 'Open'
        WHEN 'C' THEN 'Closed'
        ELSE 'Not for sale'
    END as 'Status',
    O1.BPLName as 'Branch',
    O1.DocDate as 'Invoice Date',
    O1.DocDueDate as 'Due Date',
    dbo.ZEDS_CPSelectAmount(O1.CardCode,O1.VatSum, O1.VatSumFC) as '$Tax',
    dbo.ZEDS_CPSelectAmount(O1.CardCode, O1.DocTotal, O1.DocTotalFC) as '$Grand Total'
    , dbo.ZEDS_CPCurrency(O1.CardCode) as 'Currency'
FROM OINV O1
WHERE O1.CARDCODE = [%cardcode]
    AND ISNULL(O1.U_zed_ShowCP, 'Y') <> 'N'
ORDER BY O1.DocEntry DESC

Multi-currency Query Building - Hana Example: Display all invoices for the current user

In this scenario, we’re starting with a Hana query which displays all invoices for the specified CardCode, but only in the local currency. This means a customer who performs all transactions in EUR will see USD values (assuming a USD company).

Note that this is specific to Hana. For a SQL version of this example, see the previous section.

Unlike SQL functions, which may be executed from within B1 queries directly, Hana functions which contain parameters must be executed from Stored Procedures. As such, this walkthrough provides the stored procedure with query logic, and the B1 call to that stored procedure.

Originally, our Hana query is a B1 query:

SELECT O1."DocEntry", O1."DocNum", O1."DocDate" AS "Date", O1."NumAtCard" AS "Your Ref #",
    CASE
        WHEN O1."CANCELED" = 'Y' THEN 'Cancelled'
        WHEN O1."DocStatus" = 'C' THEN 'Closed'
        ELSE 'Open'
    END AS "Status",
    "DocTotal" AS "Total $"
FROM OINV O1
WHERE O1."CardCode" = [%CardCode]
  AND IFNULL(O1."U_zed_ShowCP", 'Y') <> 'N'
ORDER BY O1."DocEntry" DESC;

Starting with our B1 query, we must convert this standard Hana query into a stored procedure, taking care to convert the parameters from B1 syntax into Hana.

Once this is done, we’ll then replace our query in B1 with a call to the new stored procedure. This sets us up to be able to use the necessary parameters within the new stored procedure.

New Hana Stored Procedure (which contains the query logic):

CREATE PROCEDURE "ZEDS_CustomQuery" (CardCode nvarchar(20) )
AS
BEGIN
SELECT O1."DocEntry", O1."DocNum", O1."DocDate" AS "Date", O1."NumAtCard" AS "Your Ref #",
    CASE
        WHEN O1."CANCELED" = 'Y' THEN 'Cancelled'
        WHEN O1."DocStatus" = 'C' THEN 'Closed'
        ELSE 'Open'
    END AS "Status",
    "DocTotal" AS "Total $"
FROM OINV O1
WHERE O1."CardCode" = :CardCode -- CONVERT the B1 parameter into a Hana parameter
  AND IFNULL(O1."U_zed_ShowCP", 'Y') <> 'N'
ORDER BY O1."DocEntry" DESC;
END;

Updated B1 Query that calls the stored procedure:

CALL "ZEDS_CustomQuery"([%CardCode] )

Now that the query has been converted into a Hana Stored Procedure, we can proceed the same as in SQL.

To make this compatible with mutli-currency environments, we need to update the query as follows:

Identify any currency fields that have a “Foreign Currency” counterpart.

This query has a single currency field: DocTotal which has a Foreign Currency alternate value DocTotalFC, as highlighted below.

CREATE PROCEDURE "ZEDS_CustomQuery" (CardCode nvarchar(20) )
AS
BEGIN
SELECT O1."DocEntry", O1."DocNum", O1."DocDate" AS "Date", O1."NumAtCard" AS "Your Ref #",
    CASE
        WHEN O1."CANCELED" = 'Y' THEN 'Cancelled'
        WHEN O1."DocStatus" = 'C' THEN 'Closed'
        ELSE 'Open'
    END AS "Status",
    "DocTotal" AS "Total $" -- LOCAL Doc Total amount
FROM OINV O1
WHERE O1."CardCode" = :CardCode
  AND IFNULL(O1."U_zed_ShowCP", 'Y') <> 'N'
ORDER BY O1."DocEntry" DESC;
END;

Add a column for the currency

Now that we’ve identified the fields with foreign alternatives, we need to add a field to identify which currency is being displayed, as highlighted below:

CREATE PROCEDURE "ZEDS_CustomQuery" (CardCode nvarchar(20) )
AS
currStr varchar(3); -- NEW Parameter to capture the Currency Code string
BEGIN
CALL "ZEDS_CPCurrency" (:CardCode, currStr);
SELECT O1."DocEntry", O1."DocNum", O1."DocDate" AS "Date", O1."NumAtCard" AS "Your Ref #",
    CASE
        WHEN O1."CANCELED" = 'Y' THEN 'Cancelled'
        WHEN O1."DocStatus" = 'C' THEN 'Closed'
        ELSE 'Open'
    END AS "Status",
    "DocTotal" AS "Total $"
    , :currStr AS "Currency" -- NEW Currency ISO Code field, the value will be populated later...
FROM OINV O1
WHERE O1."CardCode" = :CardCode
  AND IFNULL(O1."U_zed_ShowCP", 'Y') <> 'N'
ORDER BY O1."DocEntry" DESC;
END;

Use the FC functions

Now we need to update the query so it uses the “isLC” flag to identify the correct currency value to match the Business Partner’s currency, as shown below:

CREATE PROCEDURE "ZEDS_CustomQuery" (CardCode nvarchar(20) )
AS
isLC tinyint := 1; -- NEW Parameter to capture whether this is a Local Currency or not
currStr varchar(3);
BEGIN
CALL "ZEDS_CPSelectAmount" (:CardCode, isLC);
CALL "ZEDS_CPCurrency" (:CardCode, currStr);
SELECT O1."DocEntry", O1."DocNum", O1."DocDate" AS "Date", O1."NumAtCard" AS "Your Ref #",
    CASE
        WHEN O1."CANCELED" = 'Y' THEN 'Cancelled'
        WHEN O1."DocStatus" = 'C' THEN 'Closed'
        ELSE 'Open'
    END AS "Status",
    CASE :isLC -- NEW Case statement to determine which value to use
        WHEN 0 THEN "DocTotal"
        ELSE "DocTotalFC"
    END AS "Total $",
    :currStr AS "Currency"
FROM OINV O1
WHERE O1."CardCode" = :CardCode
  AND IFNULL(O1."U_zed_ShowCP", 'Y') <> 'N'
ORDER BY O1."DocEntry" DESC;
END;

The end result is a stored procedure that displays the current user’s currency details (whether foreign or local):

CREATE PROCEDURE "ZEDS_CustomQuery" (CardCode nvarchar(20) )
AS
isLC tinyint := 1;
currStr varchar(3);
BEGIN
CALL "ZEDS_CPSelectAmount" (:CardCode, isLC);
CALL "ZEDS_CPCurrency" (:CardCode, currStr);
SELECT O1."DocEntry", O1."DocNum", O1."DocDate" AS "Date", O1."NumAtCard" AS "Your Ref #",
    CASE
        WHEN O1."CANCELED" = 'Y' THEN 'Cancelled'
        WHEN O1."DocStatus" = 'C' THEN 'Closed'
        ELSE 'Open'
    END AS "Status",
    CASE :isLC
        WHEN 0 THEN "DocTotal"
        ELSE "DocTotalFC"
    END AS "Total $",
    :currStr AS "Currency"
FROM OINV O1
WHERE O1."CardCode" = :CardCode
  AND IFNULL(O1."U_zed_ShowCP", 'Y') <> 'N'
ORDER BY O1."DocEntry" DESC;
END;

Creating Collection Menu Options

B2B Marketplace Collections may be added to a menu once they have been configured. See B2B Marketplace Administration Guide: Configuring Collections

Positioning the Menu Entry

Active menu items are displayed on the portal in the order they are displayed on the manage menu screen.

To move the position of a menu item, select the menu item and click the up arrow to move the menu item one level up or click the down arrow to move the menu item one level down.

Creating a Sub-Menu Entry

If an existing top-level menu entry needs to be moved so it becomes a sub-menu, select the top-level menu entry. Move it up or down so it is immediately below the desired parent menu entry. Note that if that top-level menu entry already has child entries, “immediately below” will be below those child entries.

Press the “move right” button (->) to make the selected menu entry a sub-menu entry of the one above it. First level sub-menu entries are identified by a “- -“ before the menu item name.

Note that every additional level adds another “- -“ to the beginning of the menu entry name.

Making Sub-Menu Entries into Top-Level Entries

Similar to Creating sub-menu entries above, if a sub-menu entry needs to be made back into a top-level menu entry, press the “move left” button (<-) until it’s at the correct level.

Golden Arrow drill-downs

Golden Arrow drill-downs can be used to link reports to their details (for example, an order summary report can be linked to the order details for each DocEntry, or a Customer Summary report can be linked to customer details for each customer).

Golden Arrow drill-downs can be configured for query reports as described below. This configuration requires manual edits to the ZEDS_QUERYLINK user defined window.

  1. In SAP Business One, open the user defined window ZEDS_QUERYLINK (Portal Query Doc Link). To do this, go to Tools > User Defined Windows > ZEDS_QUERYLINK.

  2. Create a new row for every report link that requires golden arrow drill-down:

    Golden Arrow setup - ZEDS_QUERYLINK fields

    Field Definition
    Code This must be unique, such as CUSTOM001.
    Name Use something unique to describe the drill-down to an admin, for ex. Sales link BP.
    Linked Query Select the query. If this is a custom query, it will most likely be a number, which can be determined by looking at the ZEDS_QUERYMASTER UDT (user defined table) in B1.
    Link Column The column with link data such as BP Card Code or Document Entry number.
    Display Column The column with the data that will be displayed, such as the BP Name.
    URL Template A URL with {0} to denote where the Link Column data will be loaded. See below for examples.
    Grid Column Title The label for the column on the website.
    URL Open Method If empty, this will open normally. It may be optionally set to _blank to open in a new window.

Template Reference

Below is a list of common link templates and their expected link data for each application. Some links are only available when certain roles or licenses are assigned to the user accessing the link.

Description URL Template Link Column Description Application
Activity Details ~/SalesUser/Activities/view/{0} Version 92.7 and higher only.

ClgCode of the Activity.
Employee Portal
Business Partner Details ~/SalesUser/#{0}/details Business Partner CardCode Employee Portal
Credit Memo Document Details ~/docviewers/CreditMemoDetails.aspx?b1datauri=/object/display/oCreditNotes/{0}.aspx DocEntry for the Credit Memo document Customer Portal
Delivery Document Details ~/docviewers/DeliveryDetails.aspx?b1datauri=/object/display/oDeliveryNotes/{0}.aspx DocEntry for the Delivery document Customer Portal
Employee Portal
Edit Order ~/order/edit?type=o&docEntry={0} DocEntry for the Order document Customer Portal
Employee Portal
Edit Quote ~/order/edit?type=q&docEntry={0} DocEntry for the Quote document Customer Portal
Employee Portal
Invoice Document Details ~/docviewers/InvoiceDetails.aspx?b1datauri=/object/display/oInvoices/{0}.aspx DocEntry for the Invoice document Customer Portal
Employee Portal
Invoice Document Details - Print Layout ~/docviewers/InvoiceDetailsPrint.aspx?b1datauri=/object/display/oInvoices/{0}.aspx DocEntry for the Invoice document Customer Portal
Order Document Details ~/docviewers/salesorderdetails.aspx?b1datauri=/object/display/oOrders/{0}.aspx DocEntry for the Order document Customer Portal
Employee Portal
Vendor Portal
Order Document Details - Print Layout ~/docviewers/SalesOrderDetailsPrint.aspx?b1datauri=/object/display/oOrders/{0}.aspx DocEntry for the Order document Customer Portal
Payment Document Details ~/docviewers/PaymentDetails.aspx?b1datauri=/object/display/oIncomingPayments/{0}.aspx DocEntry for the Payment document Customer Portal
Purchase Invoice Document Details ~/docviewers/salesorderdetails.aspx?b1datauri=/object/display/oPurchaseInvoices/{0}.aspx DocEntry for the Purchase Invoice document Vendor Portal
Purchase Order Document Details ~/docviewers/salesorderdetails.aspx?b1datauri=/object/display/oPurchaseOrders/{0}.aspx DocEntry for the Purchase Order document Vendor Portal
Quote Document Details ~/docviewers/QuoteDetails.aspx?b1datauri=/object/display/oQuotations/{0}.aspx DocEntry for the Quote document Customer Portal
Quote Document Details ~/docviewers/SalesQuoteDetails.aspx?b1datauri=/object/display/oQuotations/{0}.aspx DocEntry for the Quote document Employee Portal
Quote Document Details - Print Layout ~/docviewers/QuoteDetailsPrint.aspx?b1datauri=/object/display/oQuotations/{0}.aspx DocEntry for the Quote document Customer Portal

Examples:

Sales Order document details link for Customer Portal (1); Sales Order document details link for Employee Portal (2).
Sales Order document details link for Customer Portal (1); Sales Order document details link for Employee Portal (2).

Business Partner data golden arrow drill-down example

To configure a query for opening BP details as a drill-down, ensure that the chosen query has both the BP Card Code and BP Name as fields in the query. Then, configure the following details:

BP data Golden Arrow example (Employee Portal)

Field Value
Code (some unique code) BPEX01
Name (some unique name) BP Example 1
Linked Query (select the query)
Link Column CardCode (assuming the query returns the column CardCode)
Display Column Name (assuming the query returns the column Name)
URL Template For Employee Portal, use the URL Template: ~/salesuser/businesspartner/viewlist.aspx?objectcode={0}&amp;CACHEOUT=true
Grid Column Title Business Partner
URL Open Method (leave blank)

Report Views

Report Views allow for different views of the same report, allowing custom views of specific columns and filters.

For example, one view might filter based on a specific date range, while another view might group together data based on customer name. Examples of Charts that use Report Views are outlined above.

Default Layouts

A default layout for a chart, grid or pivot report will load automatically when a user visits that report. A default layout allows users to revert to a known layout, which can be controlled by the admin.

The Default view can be refreshed by selecting the view from the view drop-down or by re-loading the query using the left-hand menu.

If changes are made to a layout, an asterisk (*) will be displayed next to the name in the saved layouts dropdown to indicate that there are unsaved changes.

A default layout can be deleted by clicking “Delete” from the saved layout dropdown.

Setting default layouts for all users (admin users only)

  • Login to the Customer Portal website as a user with the Report View Editor role (see Updating Customer Portal Configuration Settings - General Settings tab for more information).

  • View a report and make changes that should be visible each time the report is loaded by any user.
  • Click on the gear icon to display Advanced Options.
  • Click the Save Layout As button.
  • Click the button “Set Default for all users”. If a name is not entered for the layout, it will be named “Default (G)”.

Steps to update a layout

  • Login to the Customer Portal website normally.

  • View a report and make changes that should be visible each time the report is loaded.
  • Click on the Advanced Options icon.
  • Press the Save Layout button. This will overwrite the previous saved view with the current view.
  • Any changes will be saved into a table within SAP Business One. These changes will now display automatically every time the report is accessed.
  • When the layout has been saved, a new entry will be available in the Saved Layouts dropdown.

If another user loads a global default and makes changes, they must choose to save those changes under a different name. There is no Save As button when a non-super user is viewing a global default layout.

Named Layouts

With the Portals, customized chart, grid or pivot report layouts can be created and saved for later re-use. These custom report views can be loaded from the Named Layouts drop-down of the report’s page.

Once changes have been applied to a layout, an asterisk (*) will be displayed next to the name in the saved layouts dropdown to indicate there are unsaved changes. Once the changes have been saved, this asterisk will disappear.

Note that using the Save Layout button to save changes will overwrite the existing version of that report view. To create a new view, use the Save Layout As… button and give the report view a different name. Global named report layouts cannot be edited by non-admin users, however they can be viewed by all users. Personal named report layouts can be edited by a user, however they can only be viewed by the user who created it.

To delete a named layout, use the delete link next to the layout. Note that only personal report views can be deleted by non-admin users.

Saving a Named Layout

To save a Named Layout for personal use:

  1. Login to the Customer Portal website normally.

  2. Open a report and make the desired changes to the filtering, column hiding, column widths, etc.
  3. Press the Advanced Options icon.
  4. Press the Save Layout As… button to save a new report view.
  5. Enter a name for the new layout and press OK.
  6. Once the named layout has been saved to a user’s account, it will be available to that user only.

Saving a Named Layout for All Users (admin users only)

To save a Named Layout for global use, the user saving the layout must be assigned the Report View Editor role (see Updating Customer Portal Configuration Settings - General Settings tab).

  1. Login to the Customer Portal website normally.

  2. Open a report and make the desired changes to the filtering, column hiding, column widths, etc.
  3. Press the Advanced Options icon.
  4. Press the Save Layout As… button to save a new report view.
  5. Enter a name for the new layout, place a checkmark in the Available to All Users check-box, and press OK.
  6. Once the named layout has been saved, it will appear in the Named Layout drop-down for all users.

Other Menu Entry Management Tasks

Deleting a Menu Entry

Delete a menu entry by selecting the entry in the left-most column and using the delete button “-“ marked in the screenshot below.

Select a menu entry, then delete it using the - button (3).
Select a menu entry, then delete it using the - button (3).

Best practices for menu Entry setup

Don’t bury menus too deeply. Menus may be set up in as many levels as desired, however if items are buried too many levels down, it can be hard for users to find what they’re looking for. Aim for 2-4 menu levels for moderately sized navigation.

Keep icons tidy. Icons are available and should be applied at the top-level menu items. They help with navigating a multi-level menu by providing an at-a-glance context for where a user is within the site. Sub-menu options typically don’t need icons of their own, as they have enough context based on their parent menu’s icons. In short: when it comes to icons, less is more.

Don’t over-crowd menus. Similar to icons, if there are too many items in a menu, it will be hard for users to find what they’re looking for. Keep the number of items around half-dozen or less, or consider splitting a menu into sub-menus if it grows to more than a dozen items.

Customer Portal Reports

Out-of-the-box reports for Customer Portal are listed below, along with their supporting queries and the user roles that enable these reports. These reports may be modified within a system, so the details below are for initial installation, and may be configured differently within a B1 company to meet business needs.

Super is a special Customer Portal role that permits access to all menu entries and reports. Otherwise, the specific user roles below must be assigned to a user before they can view the menu entry or report.

Report B1 Query Description and Associated User Roles
Billing > Credit Memos B1WebAPI_CP / OpenCreditMemos Lists credit memos applied to the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires the role Billing
Orders > Your Orders B1WebAPI_CP / OpenOrders Lists orders placed by the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires the role Order
Orders > Your Quotes B1WebAPI_CP / YourQuotes Lists quotes placed by the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires the role Order
Reports > All Deliveries B1WebAPI_CP / DASH_FULL_DELIVERY Lists all deliveries shipped to the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires either of the roles Billing or Order
Reports > All Invoices B1WebAPI_CP / InvoiceReport Lists all invoices assigned to the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires either of the roles Billing or Order
Reports > All Orders B1WebAPI_CP / DASH_FULL_ORDERS Lists all orders placed by the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires either of the roles Billing or Order
Reports > All Payments B1WebAPI_CP / DASH_FULL_PAYMENTS Lists all payments applied to the currently logged in Customer’s Business Partner. Document details may be viewed by drilling down into the document.

Requires either of the roles Billing or Order
Service Calls > Closed Service Calls B1WebAPI_CP / ClosedServiceCalls Lists all service calls assigned to the currently logged in Customer’s Business Partner, including open and cancelled. Service Call details may be viewed by drilling down into the document.

Requires the role Service CP
Service Calls > Open Service Calls CP B1WebAPI_CP / OpenServiceCalls Lists active service calls assigned to the currently logged in Customer’s Business Partner. Service Call details may be viewed by drilling down into the document.

Requires the role Service CP