Menu and Report Management for Vendor 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 InterConnect Manager Add-On.

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

   

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

   

4. Complete the Menu Entry details:

   

   • 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: This applies to B2B Marketplace implementations only. Otherwise, do not use this option. See B2B Marketplace Administrator’s Guide for more information about B2B Marketplace.

       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.

     

   • 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 InterConnect Manager Add-On in SAP Business One.

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

• Go to InterConnect Manager AddOn > 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.

  

• Go to InterConnect Manager AddOn > 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.

  

• Go to InterConnect Manager AddOn > 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).

  

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).

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.

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.

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.

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.

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.

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”). Note that “Vendor” users are based on Business Partners, so they use the BP XPath parameters.

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, InterConnect 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>) (Customer Portal) / dbo.ZEDS_EPSelectAmount(<Card Code>, <Local Currency Field>, <Foreign Currency Field>) (Employee Portal) - this will identify the correct value (local currency or foreign currency) based on the given card code.
     • dbo.ZEDS_CPCurrency(<Card Code>) (Customer Portal) / dbo.ZEDS_EPCurrency(<Card Code>) (Employee Portal) - this will identify which currency ISO code is used by the given card code.
   • HANA:
     • NOTE: Hana functions cannot be installed automatically due to a known SAP Hana issue. As a workaround, these functions can be installed manually within Hana Studio. Refer to Applying Product Licenses - Hana Function Installation
     • "ZEDS_CPSelectAmount" (IN CardCode varchar(50), OUT IsFC tinyint) (Customer Portal) / "ZEDS_EPSelectAmount" (IN CardCode varchar(50), OUT IsFC tinyint) (Employee Portal) - 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)) (Customer Portal) / "ZEDS_EPCurrency" (IN CardCode varchar(15), OUT curCode varchar(3)) (Employee Portal) - 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;

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.

3. Ensure that every Link Column and Display Column pair is unique. That is, if you wish to create two drill-downs, then four fields are needed.

4. Ensure that every Link Column and Display Column is spelled correctly. In Hana, case sensitivity must match both the source query, and the ZEDS_QUERYLINK entry. Misspelled query columns will result in an error when attempting to load the report via the web.

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.

Examples:

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)

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 Vendor Portal website as a user with the Report View Editor role (see Updating Vendor Portal Configuration Settings - Report View 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 Vendor 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 Vendor 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 Vendor Portal Configuration Settings - Report View Settings tab).

1. Login to the Vendor 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.

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.

Vendor Portal Reports

Out-of-the-box reports, their supporting queries, and the user roles that enable these reports are listed below. 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.

B1WebAPI Categories are used to securely host content meant to be accessed via the web. Categories used by Vendor Portal include:

• B1WebAPI - This category contains system queries not meant to be customized.
• B1WebAPI_QueryPortal - This category contains report queries for all portals, including Vendor Portal.
• B1WebAPI_VendorPortal - This category contains Vendor Portal queries.