A Simple Example
-
Accessing Data from a
Database
In the example at right,
four simple SQL Scripts have been linked together to
provide the Pocket Conn user with a rich
understanding of the Discounts offered by Sales Directors to their Customers.
Keep in mind this is only an example, based on Microsoft's
Northwind sample database. Pocket Conn can return any data from your database and
can link any number of scripts.
Sales Discounts - The
first script returns a list of all of the Sales Directors
and the Average
Discount % they have offered their customers. A KPI (Key Performance
Indicator) has been defined to highlight an Discount % Average that
is greater than or equal to 6.5%. This sort of report gives a
very clear overview of the situation.
Total Sales for
Director
- Tapping on a Sales
Director's row brings you to a summary of the Sales Director's
total sales. Robert King has a high Average Discount % and you want to
understand this impact on your business.
Customer Sales for
Director
- Tapping on any row of Robert King's
summary brings you to a listing of all of
his Customers, and the
Average Discount % for each. This report has the same KPI
defined as the Top level, but here it applies to each Customer. This
again fleshes out Robert King's story.
Customer Sales
- Tapping on a Customer's row brings a list of
Robert King's Orders for that Customer. |
|
A simple Example
Login è Select Sales Discounts Report è
Drill into Robert King è Drill into his
Customers è Drill into Eastern
Connection's Orders è
Tap Home |
|
This same Drill-down capability
works for Windows Command Line commands as well. You could
just as easily, for example, return a list of all of the Windows
Services installed on a Computer and Tap a row to Start one.
The Directives (those lines below that start with '--]' are very
similar for the Operating System Connector.
Learn more...
Sample Scripts Sales Discounts Script:
--------------- Pocket Conn Script
---------------
--]Name=Sales
Discounts
--]Description=Sales Discounts by Sales
Director
--]Publish=Yes
--]Style=6:3:3
--]Grid=Yes
--]Rotate=No
--]KPIColumn=3
--]KPIRedLimit=6.5
--]KPIGreenLimit=5
--]KPISet=Circle
--]DrillService=Total
Sales for
Director
--]DrillParam=EmployeeID
--]DrillValue=2
--------------------------------------------------
SELECT
Emp.[FirstName] + ' ' + Emp.[LastName] as 'Sales
Director',
Emp.[EmployeeID] as 'ID',
cast ((avg
(Det.[Discount]) * 100) as Decimal(10,2)) as 'Avg Disc
%'
FROM [Orders] Ord,
[Order Details] Det,
[Employees]
Emp
WHERE Ord.[RequiredDate] >= '1/1/2011'
AND
Ord.[OrderID] = Det.[OrderID] AND
Ord.[EmployeeID] =
Emp.[EmployeeID]
GROUP BY Emp.[FirstName] + ' ' +
Emp.[LastName], Emp.[EmployeeID]
Total Sales for Director Script:
--------------- Pocket Conn Script
---------------
--]Name=Total Sales for
Director
--]Description=Total Sales for specified
Director
--]Publish=No
--]Style=6:3:3
--]Grid=Yes
--]Rotate=Yes
--]Param=EmployeeID
--]ParamValue='1'
--]DrillService=Customer
Sales for
Director
--]DrillParam=EmployeeID
--]DrillValue=2
--]DrillSubtitleColumn=1
--------------------------------------------------
SELECT
Emp.[FirstName] + ' ' + Emp.[LastName] as 'Sales
Director',
Emp.[EmployeeID] as 'ID',
cast
(sum(Det.[UnitPrice] * Det.[Quantity]) as Decimal (15,2)) as
'Gross Sales',
cast (sum(Det.[Discount] * Det.[UnitPrice] *
Det.[Quantity]) as Decimal (15,2)) as 'Discounts',
cast
(sum((Det.[UnitPrice] * Det.[Quantity]) -
(Det.[Discount] *
Det.[UnitPrice] * Det.[Quantity])) as Decimal (15,2)) as
'Net Sales'
FROM [Orders] Ord,
[Order Details] Det,
[Employees] Emp
WHERE Ord.[RequiredDate] >= '1/1/2011'
AND
Ord.[OrderID] = Det.[OrderID] AND
Ord.[EmployeeID] =
Emp.[EmployeeID] AND
Emp.[EmployeeID] = '@1'
GROUP BY Emp.[FirstName] + ' ' + Emp.[LastName],
Emp.[EmployeeID]
Customer Sales for Director
Script
--------------- Pocket Conn Script
---------------
--]Name=Customer Sales for
Director
--]Description=Sales by Customer for specified
Director
--]Publish=No
--]Style=6:3:3
--]Grid=Yes
--]Rotate=No
--]KPIColumn=2
--]KPIRedLimit=6.5
--]KPIGreenLimit=5
--]KPISet=Thumb
--]Param=EmployeeID
--]ParamValue='1'
--]DrillService=Customer
Sales
--]DrillParam=CompanyName
--]DrillValue=1
--]DrillSubtitleColumn=1
--------------------------------------------------
SELECT
Cust.[CompanyName] as 'Customer',
cast ((avg (Det.[Discount])
* 100) as Decimal(10,2)) as 'Avg Disc %',
cast
(sum(Det.[UnitPrice] * Det.[Quantity]) as Decimal (15,2)) as
'Gross Sales'
FROM [Orders] Ord,
[Order Details] Det,
[Employees] Emp,
[Customers] Cust
WHERE
Ord.[RequiredDate] >= '1/1/2011' AND
Ord.[OrderID] =
Det.[OrderID] AND
Ord.[EmployeeID] = Emp.[EmployeeID]
and
Emp.[EmployeeID] = '@1'
and
Cust.[CustomerID] = Ord.[CustomerID]
GROUP BY
Cust.[CustomerID], Cust.[CompanyName]
Customer Sales Script
--------------- Pocket Conn Script ---------------
--]Name=Customer Sales
--]Description=Customer Sales by Order
--]Publish=No
--]Style=6:3:3
--]Grid=Yes
--]Rotate=No
--]KPIColumn=2
--]KPIRedLimit=6.5
--]KPIGreenLimit=5
--]KPISet=Check
--]Param=CompanyName
--]ParamValue='Island Trading'
--------------------------------------------------
SELECT Ord.[OrderID],
cast ((avg (Det.[Discount]) * 100) as Decimal(10,2)) as 'Avg Disc %',
cast (sum(Det.[UnitPrice] * Det.[Quantity]) as Decimal (15,2)) as 'Gross Sales'
FROM [Orders] Ord,
[Order Details] Det,
[Customers] Cust
WHERE Ord.[RequiredDate] >= '1/1/2011' AND
Ord.[OrderID] = Det.[OrderID] AND
Cust.[CustomerID] = Ord.[CustomerID] and
Cust.[CompanyName] = '@1'
GROUP BY Ord.[OrderID]
For more information, please
contact us at
support@prlnorthamerica.com
|
|
Pocket Conn |
|
For
End-Users
|
Pocket Conn | |
For IT
Professionals
|
|
Pocket Conn | |
Want more details? Contact info@prlnorthamerica.com
and we'll send you the Pocket Conn Help file. See how
easy it is to setup and deploy
|
|
