Differences

This shows you the differences between two versions of the page.

--- app:app_how_to_dash [2019/07/25 17:09]
dtucker [Query Tips and Tricks for APP Dashboards]
+++ app:app_how_to_dash [2019/07/25 22:22] (current)
@@ Line 139: / Line 139: @@
     *  <color #ff7f27>**Select datepart(//week//,getdate())**</color> returns **21.**
     *  <color #ff7f27>**Select datepart(//day//,getdate())**</color> returns **20.**
-  *  Filtering by Date -The SQL <color #ff7f27>**datepart**</color> function can also be used to compare dates in order to automatically select a reporting period. In these examples we will use the SAP® Business One Order Header (ORDR) date and the current date of May 15, 2010.
+  *  Filtering by Date - The SQL <color #ff7f27>**datepart**</color> function can also be used to compare dates in order to automatically select a reporting period. In these examples we will use the SAP® Business One Order Header (ORDR) date and the current date of May 15, 2010.
+    *  Records where the order date is in the current week
+      *  <color #ff7f27>**Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(week,t0.docdate) = datepart(week,getdate())**</color>
+    *  Records where the order date is in the prior week
+      *  <color #ff7f27>**Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(week,t0.docdate) = datepart(week,getdate()) - 1**</color>
+    *  Records where the order date is in the current quarter
+      *  <color #ff7f27>**Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(quarter,t0.docdate) = datepart(quarter,getdate())**</color>
-{{:app/image6.jpeg?10x10}} Records where the order date is in the current week
+==== Numbers ====
-**Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(week,t0.docdate) = datepart(week,getdate())**
+The user interface for SAP® Business One displays decimal numeric values based on the <color #22b14c>**Display**</color> tab setup which can be accessed the SAP menu option for <color #22b14c>**Display**</color> under the **Administration** -**System Initialization** -**General Settings** -**Display** menus.
-{{:app/image6.jpeg?10x10}} Records where the order date is in the prior week
+the **<color #22b14c>Administration</color>** {{:app/image35.jpeg?17x16}} **<color #22b14c>System Initialization</color>** {{:app/image35.jpeg?17x16}} **<color #22b14c>General Settings</color>** menu groups to load the **General Settings** screen.
-**Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(week,t0.docdate) = datepart(week,getdate()) - 1**
-{{:app/image6.jpeg?10x10}} Records where the order date is in the current quarter
-**Select t0.docnum, t0.docdate from ORDR where datepart(year,t0.docdate) = datepart(year,getdate()) AND datepart(quarter,t0.docdate) = datepart(quarter,getdate())**
-==== Numbers ====
-The user interface for SAP® Business One displays decimal numeric values based on the **Display** tab setup which can be accessed the SAP menu option for **Display** under the **Administration** -**System Initialization** -**General Settings** -**Display** menus.
 However, decimal values are stored in the database to six-decimal precision. This means that a decimal value such as US Dollars that is displayed in SAP® Business One as 100.00 will be retrieved and displayed in the APP Charts as 100.000000. The number of decimals can be controlled by the SQL statement in the query. Combinations of **cast** and **round** can be used to arrive at a reasonable display value.
- Display the database value 100.000000 for the column DocTotal with 2 decimals (100.00)
+  * Display the database value 100.000000 for the column DocTotal with 2 decimals (100.00)
+    * <color #ff7f27>**Select cast(t0.DocTotal as decimal(16,2))**</color>
-**Select cast(t0.DocTotal as decimal(16,2))**
+  * Display the database value 55.900000 for the column DocTotal rounded to a whole number (56.000000)
+    * <color #ff7f27>**Select round(t0.DocTotal,0)**</color>
- Display the database value 55.900000 for the column DocTotal rounded to a whole number (56.000000)
+  * Display the database value 55.900000 for the column DocTotal rounded to a whole number (56.000000) and without decimals
+    * <color #ff7f27>**Select cast(round(t0.DocTotal,0) as int)**</color>
-**Select round(t0.DocTotal,0)**
- Display the database value 55.900000 for the column DocTotal rounded to a whole number (56.000000) and without decimals
-**Select cast(round(t0.DocTotal,0) as int)**
+{{:app/image44.jpeg?786}}
 ===== How To Use APP Dashboards =====
 Once an APP Dashboard has been created it is available for viewing via the right-click menu from the SAP® Business One form that it has been linked to.
@@ Line 183: / Line 171: @@
 {{:app/image771.jpeg?236x130}}
-When more than one dashboard is linked to the screen, the titles of those dashboards will display in a **Dashboards** sub-menu off the regular right-click menu below any reports or charts...
+When more than one dashboard is linked to the screen, the titles of those dashboards will display in a <color #22b14c>**Dashboards**</color> sub-menu off the regular right-click menu below any reports or charts...
 {{:app/image772.jpeg?409x148}}
@@ Line 189: / Line 177: @@
 A specific chart from a dashboard can be accessed by double-clicking on the chart window. This opens the standard APP Chart window for additional use.
+{{:app/image44.jpeg?786}}

Wiki

User Tools

Site Tools

Differences

Page Tools