Defining Dashboard navigation

Previous  Next |  Direct link to this topic

Defining what dashboard elements are possible to navigate from and what they navigate to is controlled by the field-names you give your SQL in Dashboards underlying query.

 

This is best demonstrated by an example (Example is in MSSQL but works the same in HANA).

 

Lets say you wish to make the following simple dashboard that show invoices and Top customers among those

 

clip0059

 

The goal here is to have navigation to Invoice, Business Partner and run a Universal Function (Customer invoice details).

 

As mentioned this is all controlled in the SQL Fields... SQL to show data above would look something like this in an SQL Editor

 

clip0060

 

This SQL would result in column names "DocNum", "CardCode", "CardName", "DocDate" and "DocTotal"

 

Looking logically on these field-name Dashboard can actually automatically deduct what most of this would make sense navigate to so most work just by writing the SQL:

 

- If it find a field called "CardCode" it would make sense that the value should navigate to Business Partner

- If it find a field called "CardName" it will assume that given the name is unique it would be able to navigate to a Business Partner by a simple mapping SQL (SELECT CardCode FROM OCRD WHERE CardName = '<the value show>')

 

What it can't know however is that "DocNum" mean "Invoice Number" and that "CardCode" should have an additional special meaning navigating to a Universal Function (SQL Report that show Customer Invoice details).

 

So you need to help it a bit using below navigation keyword table

 

Keyword

Target

Notes

CardCode

Business Partner


CustomerCode

Alias

VendorCode

Alias

LeadCode

Alias

BusinessPartnerCode

Alias

CardName

Lookup via name

CustomerName

Alias, Lookup via name

VendorName

Alias, Lookup via name

LeadName

Alias, Lookup via name

BusinessPartnerName

Alias, Lookup via name

ItemCode

Item Master Data


ItemName

Lookup via name

ProductionOrderNumber

Production Order

Lookup via DocNum to DocEntry

ServiceCallNumber

Service Call

Lookup via DocNum to DocEntry

ItemGroupCode

Item Group

Alias

ItemGroupName

Alias, Lookup via name

WhsCode

Warehouse


WhsName

Lookup via name

ClgCode

Activity


OpprId

Opportunity


OpportunityCode

Alias

OpportunityName

Alias, Lookup via DocNum to DocEntry

ArQuotationNumber

Sales Quoration

Lookup via DocNum to DocEntry

ArOrderNumber

Sales Order

Lookup via DocNum to DocEntry

ArDeliveryNumber

Sales Delivery

Lookup via DocNum to DocEntry

ArReturnRequestNumber

Sales Return Request

Lookup via DocNum to DocEntry

ArReturnNumber

Sales Return

Lookup via DocNum to DocEntry

ArInvoiceNumber

A/R Invoice

Lookup via DocNum to DocEntry

ArCreditMemoNumber

A/R Credit Memo

Lookup via DocNum to DocEntry

ArDownPaymentNumber

A/R Down Payment

Lookup via DocNum to DocEntry

ApQuotationNumber

Purchase Quotation

Lookup via DocNum to DocEntry

ApOrderNumber

Purchase Order

Lookup via DocNum to DocEntry

ApDeliveryNumber

Goods Receipt PO

Lookup via DocNum to DocEntry

ApReturnNumber

Goods Return

Lookup via DocNum to DocEntry

ApReturnRequestNumber

Goods Return Request

Lookup via DocNum to DocEntry

ApInvoiceNumber

A/P Invoice

Lookup via DocNum to DocEntry

ApCreditMemoNumber

A/P Credit Memo

Lookup via DocNum to DocEntry

ApDownPaymentNumber

A/P Down Payment

Lookup via DocNum to DocEntry

empId

Employee Master Data


ResCode

Resource Master Data


VisResCode

Resource Master Data

Lookup via VisResCode to ResCode

ProjectNumber

Project Management

Lookup via DocNum to AbsEntry

InventoryTransferNumber

Inventory Transfer

Lookup via DocNum to DocEntry

InventoryTransferRequestNumber

Inventory Transfer Request

Lookup via DocNum to DocEntry

BeasWorkorder

Beas Workorder

Require Beas add-on to be running and being version 9.2 PL04 or higher

BeasItem

Beas Item Master Data

Require Beas add-on to be running and being version 9.2 PL04 or higher

BeasWorkOrderBom

Beas Work order BOM

Require Beas add-on to be running and being version 9.2 PL04 or higher

BeasWorkOrderPos

Beas Work order position

Require Beas add-on to be running and being version 9.2 PL04 or higher

MULTI_<PrimaryKeyField>_<ObjectTypeField>_[<HumanNameField>]

Multi-target System

Warning: Only supported in grids and is fairly complex to set up, It is highly recommended that you see this video prior to setting this up

 

This allow you to make a single column go to multiple types of documents (Example Invoice and Credit Notes)

 

In order for this to work you will need to give the visual element (Normally the DocNum) the following fieldname

 

MULTI_<PrimaryKeyField>_<ObjectTypeField>_[<HumanNameField>]

 

Where <PrimaryKeyField> is the Primary key (Normally DocEntry), <ObjectTypeField> is the ObjectType (normally ObjType).

 

You can optionally add a 3rd FieldName Reference that give the human name of the Type (Example "Invoice/Credit Note")... If you do not add this extra fieldname the navigation will call the navigation "Multiple Targets"

 

Warning: All mentioned columns need to be on the report or included as a hidden deimension. Else the feature will not appear

Warning: Everything in this special keyword is Case-sensitive

<FieldName>@UF:<UF-Code>

Universal Function

Can be applied to any field as a suffix. So example is field is called "MyField" and UF-Code to call is UF-001 then make the field-name "MyField@UF:UF-001"

 

Then if the Universal Function you can work with the value using keywords:

 

$[B1DASHBOARDVALUE.STRING] or $[B1DASHBOARDVALUE.NUMBER] (if needed to be treated as a number)

 

Based on above we can see that out SQL need to be modified in the following way to work

 

clip0061

 

Resulting in you fields in the dashboard designer look like this

 

clip0064

 

NB: If you already made the Dashboard it would partly break the functionality as the field-names changed (it is normally easy to get back up and running but for this reason we recommend the fieldname change when you initially write the SQL)

 

Finally all we need to do is make the Universal Function

 

clip0062

 

In here you can see that you can use special keyword "$[B1DASHBOARDVALUE.STRING]" (or $[B1DASHBOARDVALUE.NUMBER] if the value from the dashboard is a decimal value)

 

Result of the sample:

clip0063