Defining Dashboard navigation

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

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

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

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	Item Master Data	Lookup via name
ProductionOrderNumber	Production Order	Lookup via DocNum to DocEntry
ServiceCallNumber	Service Call	Lookup via DocNum to DocEntry
ItemGroupCode	Item Group	Alias
ItemGroupName	Item Group	Alias, Lookup via name
WhsCode	Warehouse
WhsName	Warehouse	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
ApRequestNumber	A/P Request	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
PmxContainer	PMX WMS Container Management	Require PMX WMS 2020.09 or higher to work
PmxPickListProposal	PMX WMS Picklist Proposal	Require PMX WMS 2020.09 or higher to work
PmxPickList	PMX WMS Picklist	Require PMX WMS 2020.09 or higher to work
PmxOSE	PMX WMS Organizational Structure	Require PMX WMS 2020.09 or higher to work
PmxMoveOrder	PMX WMS Move Order	Require PMX WMS 2020.09 or higher to work
PmxInventoryLockingInAdvance	PMX WMS Inventory Locking in Advance	Require PMX WMS 2020.09 or higher to work
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) [See a video of this feature]

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

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)

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)