sharperlight 2.9 training manual

Training Manual

Sharperlight 2.9

www.sharperlight.com

[email protected]

mailto:[email protected]

Disclaimer: The information in this document remains the current view of phiLight Software International PtyLtd and is subject to change without notice. This position is due to changing market conditions and shouldnot be interpreted as a commitment to the correct operation of any technology or product containedherein.

This document is intended as information only and phiLight Software International Pty Ltd makes nowarranties, express or implied as to the information in this document.

Sharperlight 2.9 Training Manual

Copyright 2010-2014 phiLight Software International Pty Ltd

Printed: May 2014

Document Version: 1.2.4

Published by phiLight Software International Pty Ltd

All other copyrights and trademarks are the property of their respective owners

All rights reserved.

The copyright of this document and the computer software described herein and provided herewith are theproperty of phiLight Software International Pty Ltd. No part of this publication or the computer softwaremay be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any human orcomputer language, in any form or by any means or otherwise used without the express written permissionof phiLight Software International Pty Ltd.

phiLight Software International Pty Ltd15 Ohio PlaceMarangarooWA 6065AUSTRALIA

3Contents

Copyright 2010-2014 phiLight Software International Pty Ltd Simplified Intelligence

Table of Contents

Part I Introduction 7

................................................................................................................................... 91 Overview

................................................................................................................................... 112 Terminology

................................................................................................................................... 123 Audience

Part II Setup 14

................................................................................................................................... 141 Datamodel Installer

................................................................................................................................... 152 Client Setup

................................................................................................................................... 163 Service

................................................................................................................................... 174 Login

Part III Query 20

................................................................................................................................... 211 Query Interface

......................................................................................................................................................... 22Filter

......................................................................................................................................................... 27Selection

......................................................................................................................................................... 28Output

................................................................................................................................... 312 Data Types

................................................................................................................................... 323 Mode

................................................................................................................................... 334 Product

................................................................................................................................... 335 Superfield

................................................................................................................................... 346 Table

................................................................................................................................... 357 Favourites

................................................................................................................................... 358 Preview

................................................................................................................................... 379 Save and Import

......................................................................................................................................................... 37Publisher

......................................................................................................................................................... 38Clipboard

Part IV Drilldown 41

................................................................................................................................... 411 Output Fields

................................................................................................................................... 422 Group by Column

................................................................................................................................... 433 Save

Part V Excel Add-in 46

................................................................................................................................... 461 Concepts

......................................................................................................................................................... 46What is an Add-in?

......................................................................................................................................................... 47Formulas

......................................................................................................................................................... 49Recalculation

................................................................................................................................... 512 Query Manager

Sharperlight 2.9 Training Manual4


................................................................................................................................... 523 Query Builder

......................................................................................................................................................... 52Filter Attributes

......................................................................................................................................................... 55Output Attributes

......................................................................................................................................................... 57Output Filters

......................................................................................................................................................... 58Filter Names

......................................................................................................................................................... 59Output Expressions

......................................................................................................................................................... 61Sub Query

......................................................................................................................................................... 63Drill Through

................................................................................................................................... 654 Formulas

......................................................................................................................................................... 65Value Formula

......................................................................................................................................................... 66Table Formula

................................................................................................................................... 675 Drilldown

................................................................................................................................... 686 Stacking Tables

................................................................................................................................... 697 Detach Formulas

................................................................................................................................... 708 Table Totals

Part VI Explorer 72

................................................................................................................................... 721 Design

................................................................................................................................... 732 Filter

................................................................................................................................... 733 Settings

................................................................................................................................... 744 Save

Part VII Publisher 77

................................................................................................................................... 781 General

................................................................................................................................... 792 Security

................................................................................................................................... 803 Report

................................................................................................................................... 834 Table

................................................................................................................................... 845 Chart

................................................................................................................................... 856 Pivot

................................................................................................................................... 857 Feed

................................................................................................................................... 868 Excel

................................................................................................................................... 889 Caching

................................................................................................................................... 8810 Links

Part VIII Web Channel 91

................................................................................................................................... 921 Table

................................................................................................................................... 932 Chart

................................................................................................................................... 953 Report

................................................................................................................................... 954 Pivot

................................................................................................................................... 965 Feed

................................................................................................................................... 976 Excel

Part IX Dashboard 99

5Contents


................................................................................................................................... 1001 Properties

Index 102

Part

I

7Introduction

Simplified IntelligenceCopyright 2010-2014 phiLight Software International Pty Ltd

1 Introduction

Operational systems hold a wealth of meaningful information but it can be challenging for theaverage user to easily access timely information. Sharperlight is an agile technology that can berapidly implemented to address information bottlenecks and release the hidden value in theseoperational systems.

Sharperlight is a reporting and integration framework that overcomes many of the cumbersomeaspects of traditional Business Intelligence (BI) technologies. It is focused at all levels of theorganisation and enables rapid report output with a consistent user interface and it takes the guesswork out of querying complex operational data stores.

Empowering the end-user is central to the Sharperlight value proposition; it provides simple tools sousers can manage their own reporting needs in a self-service fashion. The technology in Sharperlightis a fundamental shift in how organisations manage their own business intelligence. Departmentsand cost centres can now get the data they need, when they need it and in a format that can beeasily distributed without reliance on reporting specialists and IT departments.

Query Access

The Sharperlight Query Engine can securely deliver content over the web, as Reports, Charts, PivotTables, Dashboards and Mashups. Dynamic Drilldown and configurable Drill Through is standardfunctionality.

Sharperlight has a native Microsoft Excel® add-in that provides information in an environment thatusers are comfortable with and it gives them the freedom to produce refreshable cell-basedformulas, tables and charts with a few keystrokes. Excel content can also be written back to thedatabase, subject to the rules enforced in the Datamodel and user rights.

Microsoft SQL Server Reporting Services® (SSRS) and Microsoft SQL Server Integration Services®(SSIS) can consume query data sets through the Sharperlight .NET Data Provider.

Drilldown Options

Sharperlight uses a dynamic metadata layer to access and query complex operational systems. Thisabstraction layer allows seamless Drilldown, so the end-user can easily delve into the informationand reveal further analysis.

Drilldown is not constrained by rigid decision trees, any field can be drilled into and if necessary, thevery scope of the query can be changed on the fly. Complementing the concept of Drilldown,Sharperlight has Drill Through templates where analysis layouts can be constructed and then sharedwith the wider user audience.

Web Enablement

The Sharperlight Web Channel gives web browser access to content without the need to installdesktop applications. The Web Channel uses a secure web service that can communicate over HTTPor TCP within a local network, wide-area network and even through the internet.

The Web Channel exposes published queries that can be re-filtered and refreshed on-line. Thiscontent also supports drill through, data pivoting and export. The browser experience can be furtherextended to include remote access to the Query Builder, Publisher and Explorer by the installationof the Sharperlight browser client.

Bi-directional

Sharperlight provides the infrastructure to easily define Writeback logic, validation and processing

8 Sharperlight 2.9 Training Manual


rules. These Writebacks are available as web data entry forms in the Web Channel and from withinMicrosoft Excel®, the Sharperlight Excel Add-in can seamlessly bulk load worksheet data into adestination operational system.

Writebacks are secured and designed to conform to the guidelines of the underlying applicationsystem. Simple generic writebacks can update and insert into a single database table, SQL scriptingcan extend this logic to update multiple tables at the same time and code libraries can be developedthat specifically address interfacing, automation and communication constraints.

Dynamic Metadata

The secret to getting accurate and timely information lies in the concise definition of the data, thishappens in the Sharperlight Datamodel. Through this metadata layer an application database can betransformed into a meaningful abstraction layer that end-users can use and interact with.

The Datamodel layer can dynamically and intelligently adjust to application databases and it caninherit security conditions, business rules, hierarchies, virtual tables and calculated fields.

In simple terms, the complexity of the application database is hidden from the user whilst stillproviding the power and flexibility of rapid query access. The user does not need development skillsor need to rely on consultants to develop their own reports.

9Introduction


1.1 Overview

At the core of the Sharperlight technology is a dynamic abstraction layer called a Datamodel. TheDatamodel encapsulates and centralizes the business intelligence of an application database, thisallows end-users to easily create and manage their own reports and analysis content.

Datamodels are accessible by end-users through a range of presentation layers. In the SolutionArchitecture diagram below, the client components that are delivered with Sharperlight include aMicrosoft Excel® Add-in, a Query Builder, Explorer for drilling into query sets and Publisher forpushing queries out to a web browser. These presentation layers communicate with theSharperlight Gateway and this gives them access to the Sharperlight Query Engine and theDatamodels that it is hosting.

Solution Architecture

Query Builder

The Query Builder is the common user interface for constructing and modifying queries. The samequery dialog can be run from the desktop, from within Microsoft Excel®, in Publisher and even in theMicrosoft Reporting Services Development Studio. The Query Builder separates filters, selectionfields and output fields into three drag and drop panes. The constructed query can be saved to anexternal file format for re-use in another presentation layer.



Explorer

Explorer is the common user interface used for drilling down and slicing data sets. From thedesktop, Explorer can be use as a reporting and analysis toolset in its own right, from withinMicrosoft Excel® the Sharperlight formulas allow Drilldown into Explorer and Microsoft SQL ServerReporting Services reports can use action points to automate an ad-hoc Drilldown into Explorer.

Publisher

Publisher is the management interface for cataloguing, laying out and web rendering queries. Thesequeries are made available in a web browser through the Sharperlight web service.

Excel Add-in

The close integration between Sharperlight and Microsoft Excel® means that end-users canempower worksheets with intelligent formulas that are easily created, filtered and refreshed. Thisautomation is managed and controlled by the dedicated Sharperlight Excel Add-in.

Service

The Sharperlight Service is the communication layer for exposing Datamodels to remote users and italso hosts its own website for the remote access of published queries. This Service can be passwordprotected and it supports several methods of user authentication.

Web Channel

Publisher and the Service work together to deliver published queries to the Sharperlight WebChannel. These published queries are viewed in a web browser and they can be interacted withusing drill through, pivoting and file export. The browser experience can be further extended toinclude Query Builder, Publisher and Explorer by the installation of the Sharperlight browser client.

Solutions

Solutions fall into two categories, there are generic solutions like Writeback in Excel and then thereare custom solutions that extend a Datamodel with forms, automation and settings. A Writebacksolution uses control logic in the Datamodel to control record validation and destination whilst acustom solution could be any manner of .NET code hosted within a Datamodel.

Data Providers

The Datamodel technology and the Sharperlight Query Engine can be exposed to externalapplications and Business Intelligence technologies by using the Sharperlight .NET Data Provider.This means the same query template generated in the Query Builder can also be used intechnologies like SQL Server Reporting Services® and SQL Server Integration Services®.

11Introduction


Studio

The Sharperlight Studio is the development environment for crafting Datamodels. The Studio useswizard driven development to rapidly prototype a Datamodel and its depth of functionality allows aDatamodel to be incrementally improved with greater scope and application awareness.

1.2 Terminology

Datamodel

Most application databases consist of tables, views and field relationships. The SharperlightDatamodel takes these building blocks and exposes them in a structure and layout that can be moreeasily navigated and consumed by end-users.

The control logic in a Datamodel can adapt to the configuration of the underlying applicationdatabase, so the user experience is closely aligned to labelling, logic and fields available in thesource system. For example, if the source system can re-label the transaction date fields to reflectthe installation and client terminology, then the control logic in a Datamodel can read in thesecustomizations and display them in the Sharperlight field selection list.

The Sharperlight Query Engine interprets the structure of a Datamodel and automates the controllogic in the Datamodel. Working together the Query Engine and the Datamodel can handle complex requirements ranging from record level security, to supporting multiple database platforms,complex runtime calculations and version control logic (this allows backward compatibility of legacyapplication databases).

Writeback

One of the key ways Sharperlight differentiates itself from other ad-hoc reporting technologies is itsability to Writeback structured data from within Microsoft Excel® to an application database, API orweb service. Writebacks are formulated within a Datamodel and it is the decision of the Datamodelauthor how the Writeback will work and how it is controlled. Sharperlight has a generic Writebackform that simplifies the selection of feeder cell ranges and it allows Writeback templates to besaved and reused.

Dashboards and Mash-ups

Dashboards are a high level view of summary values, charts, dials, gauges and trend indicators thatare designed to focus the attention of the user audience and assist in the decision making process.Usually a dashboard is laid out to fill a single screen and lends itself to quick glances and usersreacting to emerging trends. Though often delivered in a web browser, dashboards are not exclusiveto the web and Microsoft Excel® is often used for simple desktop dashboards.

Mash-ups take the dashboard concept and extend it to include diverse content from multipleoperational systems and allow content from external websites.



Drilldown and Drill Through

Some software vendors use Drilldown and Drill Through interchangeably to describe the action ofinterrogating a high level value to display a more detailed perspective of the same information.With Sharperlight the terms have quite different meanings because Drilldown initiates an ad-hocdrill whilst Drill Through is the selection of a pre-configured drill template.

1.3 Audience

This manual covers the core concepts and features in Sharperlight. No prior knowledge ofSharperlight technologies is required, however it would be advantageous to had some knowledge ofreporting concepts.

The screen images and practical exercises in this manual, may be different to the Datamodels thatthe user audience plans to work with. Even with these differences the concepts and functionalitydocumented in this manual are applicable to all Sharperlight users.

It is recommended that users should be familiar with Microsoft Excel if they want to use the ExcelAdd-in for Sharperlight.

At the end of some topics there are training exercises for users to work through and build theirproduct awareness. For some Datamodels there are supplemental training guides that extend thesetraining exercises and provide detailed instructions specific to the focus Datamodel audience.

Part

II



2 Setup

To work through all the exercises in this manual, the user will need an installation of Sharperlight. Ifit is installed, it will have its own program folder with applications like Client Setup, Query, Explorer,Publisher and Studio.

If Sharperlight is installed on a computer that already has Microsoft Excel or SQL Server ReportingServices, it will configure these applications to work with Sharperlight.

2.1 Datamodel Installer

Datamodels are the central interface layer than Sharperlight uses to interact with operationalsystems. On a clean installation the only Datamodel available will be the System Datamodel,additional Datamodels need to be added through the Datamodel Installer or created from scratchusing the Studio.

Datamodel Installer

The Datamodel Installer is the management dialog for adding, deleting and updating Datamodels. Innormal circumstances, any change to the available Datamodels will force a recompile of all theDatamodels together. However, sometimes the source files can become locked by the operatingsystem and the Force Recompile cannot initiate a complete recompile of all Datamodels.

15Setup


2.2 Client Setup

The Client Setup dialog manages the Services for Datamodels and the connection details for Localand Remote Connections.

Client Setup

Local Connections

To access Datamodels on the local computer, the Local Connections allow the user to setconnection details and access settings. Depending on the topology of a customers Sharperlightinstallation, there may be no Local Connections enabled and all the Datamodels are made availablethrough Remote Connections. Alternatively, there can be a mix of Local and Remote Connectionswhich share the same or different Datamodels.

Remote Connections

Remote Connections are used when remote servers are delivering the Datamodels through theSharperlight Service. The use of a dedicated application server to expose Datamodels reduces theprocessing load on the client computer and minimises the network traffic between client andserver.

Training Exercise

Enable and disable the Local Connections by clicking on the Local Connections node and right handclicking to enable and disable. With the Local Connections enabled select the System Datamodelnode and review the Attributes and Version details in right hand side product details pane. TheVersion Stamp of a Datamodel can be very important when planning upgrades and attempting toresolve functional issues.

Navigate to the Application node and click on the About item. This will display a separate Aboutdialog with Sharperlight product details, including the Version and the internal Build Version.



2.3 Service

Multiple Datamodels can be hosted on a single centralized application server. The Sharperlightpresentation layers then access the Datamodels on this server through a Datamodel Service. Theconfiguration of this Datamodel Service is maintained through Client Setup and the illustrationbelow shows what the Service Details might look like.

Service Details

The Service Details pane in Client Setup allows full control on the description, security,communication and email settings. The Service Details are explained in detail below.

Item Description

Attributes

Description This is the description for the Datamodel service whenclients connect to this instance and it becomes the defaulttitle when accessing the List page in the Web Channel.

Password This password will secure all communication betweenSharperlight clients and this Datamodel Service. If apassword is set all clients will need this password in theirconnection settings.

Connection Details

Protocol Will accept either HTTPS, HTTP or TCP. This determines thecommunication protocol used between the client interfaceand the Datamodel Service.

Port The accessible port for connecting to the Datamodel Service.

Timeout The timeout delay in seconds for sending and receiving datafrom the Datamodel Service. 0 reverts to an internal default.

Web Channel

Port The accessible port for connecting to the Web Channel forPublished Queries and Web Data Entry forms.

17Setup


Item Description

Use Online Libraries The Service uses several readily available open sourcelibraries for rendering web content, these can be hostedlocally or downloaded from the internet.

Authentication Sharperlight supports several common authenticationmodes. Logins created in Site Setup are accessed usingBasic. NTLM and Windows authentication will validateagainst the domain and network login.

Services

Application Service URL Virtual directory shared under Internet Information Services(IIS) that gives clients access to the Sharperlight browserclient installation files. The Web Channel will still workwithout this but Query Builder, Explorer and Publisher arenot accessible in the browser without these installation files.

External Base Address URL The resolved name or IP address for the server.

The Service can either be manually started using the Service shortcut in the Sharperlight programfolder or it can be configured as a Windows Service. The automation of the Service can be changedusing the Service Dialog and changing the Startup Mode. If the Service is not running, then remoteSharperlight clients cannot connect to the Datamodels on the local computer.

Service Dialog

2.4 Login

To access any of the Sharperlight presentation layers, the user must have a valid login andpassword. Sharperlight has a security structure of user logins, user rights and roles. These settingscan be assigned to a native Sharperlight user login or assigned to a network login. The keyadvantage of using a network login, is that authenticated users can log directly into any of thepresentation layers without the need to enter any username or password.

The illustration below shows the default login dialog for a native Sharperlight user. This dialog will



be displayed if the network login of the current user is not listed in Site Setup or if the SharperlightDatamodel Service is configured for Basic authentication.

Login Dialog

Part

III



3 Query

The Query Builder is the common user interface that is shared across the Sharperlight presentationlayers. Irrespective of whether a query is crafted in Microsoft Excel®, in Publisher or using theSharperlight web client, it is the same Query Builder that users interact with. This consistent lookand feel for crafting queries is extremely important for an end-user audience because it acceleratesthe learning curve and simplifies the transition to other interfaces.

Query Builder

The Table lookup defines the focus for the query. Though described as a Table lookup, the availablequery objects could be anything from database tables, views, stored procedures, functions, tovirtual tables or even in-memory data sets or web services. For the purposes of this manual, theterm Table and query object are interchangeable.

21Query


3.1 Query Interface

The query interface is divided into three distinct sections called the Filter, Selection and Outputpane. The Selection list is only populated with fields and folders after the Table lookup has been setwith a query object.

Field items in the Selection pane can be double clicked and they will be added to the Output pane.Each pane supports drag and drop, so Selection fields can be dragged into the Output and Filterpane. Alternatively, the Filter items below the Table lookup can be dragged into the Output pane.

Query Interface

The Query Mode, Product and Table lookups have special significance and are explained in theirown sections in this manual. Some Datamodels also have a special over-riding lookup called aSuperfield which can interact directly with the Datamodel control logic and adjust its behaviour onthe fly. A Superfield can have any description, for example Company, Database or Entity but italways falls between the Product and the Table lookup. Superfields are explained in more detail inthere own section in this manual.



3.1.1 Filter

The filter pane controls the type of query generated, the source of the query and what query objectwill be queried. Additional filters after the Table lookup, refine the query scope to only return therecords that are relevant.

To rationalize and optimize end-user querying, some of the query objects in the Table lookup havemandatory filters. These filters automatically appear after that Table is selected and they have aslightly different darker row colour to the other filter items.

Query Filter Pane

The Query Mode, Product and Table filters are discussed further under their own sections in thismanual.

Note The far right Details column for the Mode and Product filters display the current userlogin and current Datamodel version.

23Query


Filter Attributes

The Filters that restrict a query are the rows displayed below the Table filter. If the user doubleclicks on the Filter description of any of these rows, it will display the Filter Attributes dialog.

General

The General tab shares the same settings between all record filters and the Table filter. The Generalsettings affect the operation of the overall query.

Item Description

Top Options

All Rows This is the default, all rows are returned.

Top (n) Rows A specified number of rows can be set and the SQL querystatement will constructed to only return this number ofrows.

Top Percentage Similar to the Top (n) Rows but it returns a percentage of theunderlying query set.

Other

Distinct Values This option adds a DISTINCT statement to the generated SQLquery statement and this in turn means the result set willonly return unique values without duplicates.

Suppress repeating values up tocolumn

This option doesn't change the underlying query it justsuppresses duplicate values being display row after row for aspecific number of columns across. Only affects PublishedQueries in the Web Channel.

Attributes

The Attributes tab has all the main filter options that are used on a regular basis. The most commonfilter operators are Between, In, Like and a special command '<all>' which has the effect ofsuppressing the filter in the query result set.

Item Description

Operators

Equal to Sets the from filter to '=' and the to filter is an input value.

Between The from filter and to filter become a filter range inrestricting the query result set.

Greater than Sets the from filter to '>' and the to filter is an input value.

Greater or equal to Sets the from filter to '>=' and the to filter is an input value.

Less than Sets the from filter to '<' and the to filter is an input value.

Less or equal to Sets the from filter to '<=' and the to filter is an input value.

In the list of values Sets the from filter to '<in>' and the to filter can be a commadelimited list with ranges separated by '..' two dots. Forexample, 2050..2150,3050.

Like match using wildcards Sets the from filter to '<like>' and the to filter can be any



Item Description

valid SQL wildcard character. For example, '%' for any value,'_' underscore for any single value in a set position.

Is Null Sets the from filter to '<null>'. Is used to return only valueswhich are nulls in the database, as opposed to emptystrings.

Not equal to Sets the from filter to '!=' and the to filter is an input value.

Not greater than Sets the from filter to '!>' and the to filter is an input value.

Not greater than or equal to Sets the from filter to '!>=' and the to filter is an input value.

Not less than Sets the from filter to '!<' and the to filter is an input value.

Not less or equal to Sets the from filter to '!<=' and the to filter is an input value.

Not in the list of values Sets the from filter to '!<in> and the to filter can be a commadelimited list with ranges separated by '..' two dots.

Not like match using wildcards Sets the from filter to '!<like>' and the to filter can be anyvalid SQL wildcard character.

Not Null Sets the from filter to '!<null>'. This clause will specificallyexclude null records from the query results.

Other

Prompt Only Filter These are filters that are going to be referenced in otherfilter rows. Setting a filter to prompt only means the filterwill be suppressed in the generated query statement.

Name Naming a filter means the from and to input value can bereference in sub queries, in filtered columns and incalculations.

Description Allows the user to change the filter description to make itmore informative.

When fields are added from the Selection list into the Filterpane they retain there full path name. For example, if theSelection list displays a join to the CA \ Chart of Accountsand the Account Name from this join is added to the Filterpane then its default description will be \CA\Account Name.

Behaviour This is a dropdown list with the items Default, Single ValuesOnly and Hide Filter. Single Values Only will make a filteronly available for single filter items and not ranges orcombinations. Hide Filter only has affect in the Web Channelwhere the filter is hidden in browser filter bar.

Functions

Functions are only available on string and date files.

Item Description

String Options

Segment By entering a start position and a length the filter string fieldwill be evaluated on a segment of the actual value.

25Query


Item Description

Date Options

Year Evaluates a date to the year, eg 2009

Year / Quarter Evaluates a date to the year and calendar quarter number,eg 2009/3

Year / Month Evaluates a date to the year and a two digit calendar month,eg 2009/07

Quarter Evaluates a date to the calendar quarter, eg 3

Month Evaluates a date to the month of the year.

Week ( from Jan 1st) Evaluates a date to the number of weeks since 1st January ofthe current year.

Week (from first Sunday) Evaluates a date to the number of weeks since the firstSunday of the current year.

Week (from first Monday) Evaluates a date to the number of weeks since the firstMonday of the current year.

Weekday Evaluates a date to its day number in a week assumingSundays is the first day of the new week.

Day of Month Evaluates a date to its day number in a month.

Day of Year Evaluates a date to its day number in the year

Date and Time Evaluates a date with a time component, eg 17/07/200910:40:08 AM.

Date Part

Time Part

Hour Part

Lookup Dialogs

For each row in the Selection pane they will have an associated lookup dialog or wizard tographically display the available filter items. The Mode, Product and Table lookup dialogs will bediscuss under their own sections but the remaining lookup dialogs can be separated into Standard,Date, Period and Hierarchical Lookup Dialogs. The relevant lookup dialog can be displayed fromwithin the Query Builder by clicking on the row button situated between the Description and Valuescolumns.

The Standard Lookup Dialog is the most common lookup for most Datamodels. It will display one ormore columns of information with either one or two value selection boxes on the right hand side.The user double clicks on a row and the left most column value is copied to the From Value anddouble clicking again on another value will copy it to the To Value (if it is available).

The Refresh button on the Standard Lookup Dialog will re-query the underlying data source anddisplay the latest available list of values. The Clear button will wipe entries from the From and ToValue entry boxes.

One of the features of the Standard Lookup Dialog is the way multiple non-contiguous values canbe selected using either the CTRL and/or SHIFT keys. The selection of multiple values will convertthe filter to an '<in>' statement.



Standard Lookup Dialog

The Date Lookup Dialog is designed specifically for handling dates. It has a date spinner, it supportsoffsets to the selected date and it has numerous options for calculating the start or end of week,month, quarter or year.

Date Lookup Dialog

The Period Lookup Dialog is normally only relevant for accounting and ERP systems that have aprocessing period concept where yyyymm or yyyyppp has special significance. The Period wizardsupports both two and three digit periods; it has offsets and options for calculating the start andend of a quarter or year.

27Query


Period Lookup Dialog

The Hierarchical Lookup Dialog is a special wizard for visualizing and allow end-users to single andmulti-select hierarchical filter sets.

Hierarchical Lookup Dialog

Training Exercise

Use the Query Builder and set the Mode to Detail Report. Add some fields from the Selection listinto the Outputs pane and add some fields to the filter pane if there is no mandatory filtering. Usethe Lookup Dialogs in the filter pane, to change filters and then use the Preview to see how thequery set changes with different filters combinations.

3.1.2 Selection

When a query object is selected in the Table lookup, the Selection list will be populated with fieldsand relationships that exist in the underlying Datamodel. The core elements in the Selection list arefields, folders and joins. The joins differ slightly from the folders in that they are prefixed with theunique join alias, for example CA \ Chart of Accounts would be a join to the Chart of Accounts table



which has a table alias of CA.

The Selection list can be very large and deep with joins within joins, down many levels. To simplifythe search for fields the Find button (binoculars) will display a Find text box for searching theavailable field items. The Find will return all fields that have the combination of letters entered,often the same field will be returned multiple times because it is reference multiple times in theDatamodel. As a general rule, always select the field that is highest in the selection tree. This shouldcreate the simplest join and place less load on the database engine.

Query Selection Pane

Right hand clicking on a field in the Selection list will display a number of options like Output, OutputAll and Information. The Information dialog can display the unique code paths of the field and table;plus it will display the extended descriptions of both if they have been added to the Datamodel.

3.1.3 Output

The Output pane displays which fields are going to be returned in the query result set. Fields can beadded to the Output pane from the Selection list individually by drag and drop, or by double clickingon them or by using the Output option in the Selection list right hand click menu. This same menu inthe Selection list has an Output All option to add all fields in the Selection list into the Output Pane.

29Query


Query Output Pane

Output Attributes

Double clicking on a row in the Outputs pane or clicking on the Attributes lookup button will displaythe Output Attributes dialog. Some Attributes are set with user intervention whilst the Query Modewill intelligently select Aggregation types when the modes Aggregation and Summary Report areused.

General

Item Description

Total Row

Auto The total row is displayed in the Preview pane, on PublishedQuery Tables and when a Table Formula is created in Excel.Auto uses the settings in the Datamodel and has someadditional logic for handling Expressions.

Sum Sums the row values.

Average Averages the row values.

Count Counts the row values.

Other

Name This is the unique alias name that will be referenced in



Item Description

Expression calculations and can be used in Sub Queryfiltering.

Customise Caption The field caption defaults to its Datamodel description andmay include a unique path to the field. This caption can bechanged to whatever is more relevant or site specific.

Hidden Some output fields are necessary for calculations and sortingbut they are not relevant in the output data set; hiddenenables these fields to removed from the output display.

Allow Null Values The default handling of NULL values is to return blanks fortext fields, 0 for numbers and 1900-01-01 for dates.Enabling this option will return NULL values as <Null>.

Sorting

When fields are added to the Output pane, the order top to bottom becomes the default sort orderof the fields when queried. To over-ride this behaviour, the Sorting tab in the Output Attributesdialog can be used to set the ascending and descending order of output fields. Unsorted fields aredisplayed in the left hand pane, these fields can be added to the Sorted right hand pane. Theselection of ascending and descending sort order is completed by selecting a Sorted field andclicking the relevant button on the far right hand side.

Output Attributes - Sorting

Formatting

The Formatting tab only has relevance when the query is published in the Web Channel. Theformatting does not affect the Preview tab in Query Builder nor does it change how values arereturned in Microsoft Excel®.

31Query


Item Description

Customise Numbers

Currency Prefix Select the preferred currency prefix from the drop down list.

Decimal Places Type in the number of decimal places or use the increase ordecrease controls.

Decimal Separator Enter in the separator or select from the drop down list.

Thousand Separator Enter in the separator or select from the drop down list.

Suffix Enter in the suffix or select from the drop down list.

Show negative values inbrackets

Show negative values in red

Show zero values

Customise Dates

Format Select from the drop down list or enter a custom format.

Training Exercise

Use the Query Builder and set the Mode to Detail Report. Select a Table and use the Output Alloption available in the Selection pane. Remove some of the fields using the right hand click menu inthe Outputs pane, using the Delete option.

3.2 Data Types

All the output fields in the Selection list have a data type indicator that provides a quick visualmeans of differentiating data types. This becomes important when filtering and outputting fieldsbecause the data type will determine the functions that can be used to manipulate a field. Forexample, an alphanumeric field will allow a substring to be filtered or outputted but it cannot besummed.

Query Data Types

Data types are defined in the underlying datamodel layer and sometimes multi-use fields will beforced into a generic data type like alphanumeric. With the use of an Output Expression, multi-usefields can be validated and then converted into a more meaningful data type.



Item Description

Data Type

a Alphanumeric A general purpose field that is treated like normal text eventhough it might combine numbers, dates and comments.

c Currency A numeric field that has decimal places, the precision of thedecimal places is controlled in the datamodel.

d Date A date field that does not return any time part. In the Filterpane the Date Lookup dialog will only allow filtering on thedate part.

dt Datetime A date field that will also return the time part.

id Identity Many databases support a unique record counter thatuniquely identifies each row in a table. This identity fieldsignifies that the field is an identity column in the databasebut there can be situations where this field is not unique.

p Period This field is a six or seven digit integer which equates to areporting or accounting period in the syntax YYYYPP orYYYYPPP. It will display the Period Lookup dialog in the Filterpane.

tf True or False Usually a boolean field that contains 0 or 1 to differentiateTrue or False. The query engine uses a CASE STATEMENT toresolve and return the terms True or False rather than thevalue.

# Number A numeric or integer field that may or may not have decimalplaces.

3.3 Mode

The Mode filter in the Query Builder defines the structure of the generated query. Each mode altersthe basic premise for the query and will return slightly different information. Before constructing aquery make sure the correct Mode has been selected in the Filter pane, it will avoid confusion whenthe result set is different from what was expected.

Query Mode Dialog

In most cases, the Mode can be changed on an existing query and all the user need do is update theAggregations for the number columns.

33Query


Item Description

Query Mode

Aggregations Returns a single aggregation. For example, the outputcolumn is summed, or counted, or average, etc

Summary Report Returns grouped elements where some columns areaggregated whilst others are grouped on there values.

Detailed Report Returns a transactional row by row list of all the recordsavailable under the current filter criteria.

References Returns a single row of referential data.

Lookups Returns field lookup data related to the filter.

3.4 Product

The selection of a Datamodel for querying is referred to as selecting a Product in the Query Builder.The available Datamodels can be displayed by opening the Products dialog. Depending on theconfiguration of the Client Setup, there may be Local and Remote Datamodels. Selecting a Product(Datamodel) will then mean that all subsequent queries constructed against that Product willreference back to its connection details and query service. If a connection is no longer valid or is re-mapped to another connection alias; then the queries will switch allegiances to the next availableconnection, starting with the Local Connections and then trying the Remote Connections.

Products Dialog

3.5 Superfield

A Superfield is a special filter that can control the operation of a Datamodel and adjust itsbehaviour on the fly. The use of a Superfield is at the discretion of the Datamodel author but once itis defined, it will be displayed between the Product and Table filters in the Query Builder. A simpleexample of a Superfield could be the database, where an application has multiple databases tostore company or organisation specific information. In this case, the selection of the Superfield willdecide which database is queried from. Complex business logic can be tied to a Superfield so whenit changes, additional security can be enforced and/or fields and tables might be disabled from viewin the Selection list.



Superfield Dialog

3.6 Table

The Table tree is a visual structure of query objects grouped into logic folders. The selection of aTable will create the focus for the query design because the Table represents all the available fieldsand related information that has been defined in the Datamodel. Often the Table dialog will have afolder structure that mimics or is closely aligned to the underlying application and its menu. Forexample, accounting systems usually have modules that cover the General Ledger, Debtors,Creditors and Sales; these may become top level folders in the Table tree.

The structure of the Table tree is controlled by the Datamodel author, however end-users cansuppress the display of folders and tables using the Restrict Access to Tables option in Site Setup.

35Query


Table Dialog

3.7 Favourites

Favourites is special folder available in the Table Tree and the Selection List that can be used togroup together frequently used fields and tables.To add a Favourite, right hand click on the treestructure and select Add to Favourites from the right hand click menu. To remove a favourite, selectthe item to be removed from Favourites and use the right hand click menu and select Remove fromFavourites.

Favourites

3.8 Preview

The Preview tab in Query Builder gives end-user, immediate feed-back on the result set they areextracting and assists them in visualizing how they may want to change their query. The Previewlimits the number of rows returned to 20,000, this can be changed but this updated Row Limit will



only exist while the Query Builder is open.

Query Builder - Preview

On the top right hand side of the Preview tab can be seen a row count of records returned and thetime it took took to query and return the result set.

Item Description

Save

Query as File Saves the query template (layout and structure) to a file thatcan then reused in other Sharperlight client applications.

Data as CSV File Saves the result set in the Preview pane to a CSV file.

Data as HTML File Saves the result set in the Preview pane to a HTML file.

Information

Query Information Basic query information with the execution time and time toreturn the data.

Detailed Query Information Detailed query information with the resolved generate SQLstatement and the unresolved SQL statement with controltags.

Other

Refresh Re-run the query and refresh the result set rows in thePreview pane.

Print Prints the result set to the selected printer, the user isprompted for a report Title.

Training Exercise

37Query


From a Summary Report, Preview the record set and then do a Drilldown - Summary. Select someoutput fields that will give a good spread to the row being drilled into. Try to include a date field, aperiod and a status code or category code.

3.9 Save and Import

One of the reasons why Sharperlight content can be rapidly deployed, is that it shares a commonquery language where queries can be saved to file and then re-used in the other presentation layers.So a query prototyped in the Query Builder, can be saved and imported into the Sharperlight ExcelAdd-in or into Publisher.

Save Query

Training Exercise

Save the current query structure to a file and it will be reused in subsequent training exercises.

3.9.1 Publisher

The Query Builder can transfer queries into Publisher using the Publish item in the Filter menu.Published queries can also be imported into the Query Builder using the Import Query menu item.

When publishing a query from Query Builder into Publisher, the Publish menu item will load Publisherin the foreground. To transfer the query, select the New button in Publisher and the Publish Querydialog will be displayed with the query imported and it will auto assign a code and title. Select OKand the Published Query will be saved and added to the query list.



Publish Query

3.9.2 Clipboard

The Filter menu Copy to Clipboard item takes the loaded query and copies its query structure intothe standard Windows Clipboard. With the query in Clipboard it can be imported into another QueryBuilder using the Import Query From Clipboard menu item. Alternatively, the query could be pastedinto an email client and sent to work colleagues or support.

39Query


Clipboard Paste

Part

IV

41Drilldown


4 Drilldown

Drilldown is an important concept and ability of Sharperlight where a result set can be drilled into,to visualize additional details and trends. When using Drilldown it assumes the focus of the drill willbe around the numeric fields in the query set. Even if a text field is selected and drilled into, thedefault output fields will be the numeric fields in the result set.

In Sharperlight terminology Drilldown is an ad-hoc experience where the user can easily selectoutput fields on the fly. Drill Through is a related technology supported in Sharperlight and it isdiscussed further in the Query Builder section in the Excel Add-in topic.

Drilldown

4.1 Output Fields

After selecting Drilldown the user is presented with the Output Fields dialog. The available fields andfolders will be the same Selection list that would appear for the source table. Some of the fields inthe list may be greyed out. This happens when fields are already filtered to a single value and thus,outputting these fields will not add any new field combinations.

The user selects which fields they want to include in the output and these additional fields willbecome analysis dimensions for the selected value. Or in other words, the value column will besliced by these additional elements.



Drilldown Output Fields Dialog

4.2 Group by Column

After the Output Fields are selected and accepted, the Drilldown interface will be displayed. This isthe called the Explorer window and it, like the Query Builder, are common user interfaces that areused throughout the Sharperlight solution. Explorer can be used as another presentation layer forSharperlight because it has its own filtering, layout control and the Explorer reports can be savedinto reusable report packs. The advanced features of Explorer are explained in more detail, in theExplorer section of this manual.

From a simple Drilldown, Explorer will display sequential tabs for each Drilldown. So a Drilldownfrom the Preview pane in Query Builder will load Drilldown 1 in Explorer and from within Explorerthe end-user could then Drilldown again and this would generate Drilldown 2 and so on.

One of the methods of visualizing the data set is to group the output fields using the Group byColumn function. To group the fields and generate sub-totals, select a column label and drag it ontothe Group by Column bar just above the column headings. This type of grouping supports multiplelevels and to remove a group, just select it and drag it back onto the output rows.

43Drilldown


Group by Column

Training Exercise

Group the record set into sub-totalled categories with the Group by Column function. Create thegrouping and then remove the grouping, by dragging the group back onto the row set.

4.3 Save

As users work with Explorer they may reach a point in their analysis where they would like to reusethe existing layout or return to the same query set at some later date. Explorer has a save optionwhere the current query criteria and tab layout can be stored in a report pack file. At a later datethe user can then open this Explorer Report Pack and refresh the enclosed queries, with querystructures and layout maintained. To access the Save menu item, right hand mouse click on theresult rows and select Save - Report Pack from the sub-menu.



Save Drilldown

Training Exercise

Using the Drilldown from the Group by Column exercise, save the layout into a Report Pack.

Part

V



5 Excel Add-in

The Sharperlight Excel Add-in gives users easy access to the Query Builder and Explorer from withinMicrosoft Excel®. The Sharperlight queries are anchored in the worksheets, using formulas and theconstruction of these formulas is wizard driven through the Query Builder interface. These formulasfully support cell referencing and automatic recalculation, so the result set will refresh as filters arechanged or when recalculation is forced. The formulas are also Drilldown points where the resultset can be drilled into, using Explorer.

Some Datamodels also allow Writeback from Excel into the underlying application database. TheseWritebacks are controlled from within the Datamodel and the author of the Datamodel, decideshow the Writeback will interface with the underlying production system. Security can also beapplied to Writebacks so only allocated users have access to Writebacks.

Sharperlight Excel Add-in

If Microsoft Excel® 2003, 2007, 2010 or 2013 are installed on a client computer and thenSharperlight is installed, the Excel Add-in will be automatically configured to load into Excel. If theSharperlight Excel Add-in doesn't load automatically it can be manually added to Excel using theAdd-Ins menu in Excel.

5.1 Concepts

The Sharperlight Excel Add-in gives end-users seamless integration with Microsoft Excel®. Itachieves this by adding a Sharperlight menu to Excel, where the Query Builder can be used to createintelligent formulas that refresh directly from a Datamodel and its underlying application database.These formulas have all the normal behaviours of Excel formulas, so they can be copied, moved,referenced to cells and name ranges, and they are recalculated just like any other formula.

5.1.1 What is an Add-in?

An Excel Add-in is a file that loads into Microsoft Excel® and makes additional functionalityavailable to the user. The Sharperlight Add-in is an XLL file which contains compiled code that addsa menu to the Excel main menu and the right mouse click sheet menu. From this menu end-userscan create Sharperlight formulas using Query Builder, Drilldown on them and manage all the queriesin a workbook using a Query Manager.

47Excel Add-in


Sharperlight Right Mouse Click Sheet Menu

5.1.2 Formulas

Sharperlight uses intelligent formulas to anchor the extraction of data into Microsoft Excel®. Thetwo main query formulas are called the Table Formula and a Value Formula. These have their ownmenu items and they generate different styles of output into Excel.

The Value Formula outputs a single value into a cell which could be a reference value , a summarybalance or even a count of events. The Table Formula is used for query sets which could containmultiple columns and rows; on refresh (recalculation) the Table Formula can resize to includeadditional columns and rows.

Item Description

Formula

=mdTable(queryname,table, args)

Table Formula returns a table data set that may spanmultiple rows and columns, subject to the arguments thatfilter the query. Normally queries have a name thatreferences a query template that is stored elsewhere in theworkbook but some simple queries may be populateddirectly in the formula.

The table data set is generated into a Microsoft Excel® tableobject.

=mdValue(queryname,args)

Value Formula returns a single value into a worksheet cellsubject to arguments that filter the query. Similar to above,most formulas have a named query but simple queries maybe contained entirely in the formula.

Cell Referencing and Name Ranges

One of the key reasons why people are drawn to spreadsheets for calculations and analysis; is thatthe formulas can be referenced to other spreadsheet cells and the entire worksheet can be drivenbased on a few key cell references. Sharperlight takes full advantage of the cell referencing



concepts in Microsoft Excel®. The Query Builder supports both absolute and relative formulareferences, and it supports named ranges. The resulting Sharperlight formula will include the cellreferences and name ranges as arguments to the formula.

Query Builder - Name Range and Cell Referencing

Automatic Lookups

The relationship between a Sharperlight formula and its cell referenced filters, has an importantbenefit to users who want to change the cell referenced values. Rather than relying on a goodmemory or returning to the Query Builder to view valid filter combinations; all the user need do isdouble click on the referenced cell and the relevant lookup dialog will be displayed automatically.These Automatic Lookups use the native dependency between formulas and cell references withSharperlight leveraging this relationship to open the appropriate lookup dialog.

49Excel Add-in


5.1.3 Recalculation

Within Microsoft Excel® there is a complex calculation hierarchy of dependent cells which triggerthe calculation and recalculation logic in a workbook. Because Sharperlight uses formulas in Excel itinherits this calculation logic and in normal circumstances, Value and Table Formulas will recalculatewhenever a dependent cell is updated. For especially large and complex query sets, it may be wiseto set a workbook to manual recalculate and then the end-users has full control on when they wantto initiate a recalculation.



Formula Dependency

In the illustration above it displays the formula dependency between a Table Formula and itsfiltering. The colour coding and associated referencing is standard Excel functionality by selectingthe formula and hitting F2. If any of the dependent filter cells are change they will force arecalculating of the related Table Formula.

If a Value or Table Formula doesn't have any external cell referencing then it will not berecalculated unless a full recalculation is forced. To overcome this situation, the Sharperlight ExcelAdd-in includes a Volatile Formula that can be wrapped around self contained Value or TableFormulas. This formula will force the query to refreshed every time a worksheet recalculation istriggered.

Volatile Formula

51Excel Add-in


5.2 Query Manager

Except for very simple formulas, most Sharperlight formulas have a unique name that references aquery template stored elsewhere in the workbook. The key advantage of this arrangement is thatthe same query template can be reused multiple times in the same workbook. The result data setmight change because the cell references, that drive the filtering, might have different criteriahowever the layout of filters and outputs could be the same. So a workbook might containhundreds or thousands of formulas but they all reference the same query templates. This is a verypowerful extension of the query concept and the Query Manager is used to visualize and overseethese stored query templates.

Query Manager

It is often better to open the Query Manager, create a query template and then construct the querystructure. In this way the query will start out with a meaningful name and it will be easier to referback to it at a later date. Queries that are directly created using the Value or Table Formula menuitems, are given a generic names based on the source table.

From within the Query Manager, query templates can be created, edited and deleted in the viewpane. Query templates in the view pane also have a right hand mouse click menu that allows thetemplate to be copied or renamed. From the same menu it gives an option to create a Value orTable Formula in the current cell selected in the worksheet.

Item Description

Button

In the input box next to the New button enter a query nameand then select New, the Query Builder will be loaded withthe entered query name in the Query Builder title bar. Onselecting Finish in the Query Builder, the preferred queryname will become its name in the Query Manager pane.

If the New button is selected without an entered queryname it will default to QueryName1 and increment the lastdigit to keep it unique in the workbook.

The Edit button will allow query names to be changed. Thisshould only be attempted on queries that are not referencedin existing workbook formulas.



Item Description

The Delete button removes the query name from the currentworkbook.

The Refresh button reloads the query pane, sometimesnecessary when numerous queries have been copied andthen renamed.

Training Exercise

Open the Query Manager, input a new query name and select New. When the Query Builder dialogis open, import the query template saved in the exercise that related to the Save section. If thequery has filters, reference them to underlying worksheet by entering valid filter values in worksheetcells and then reference the filters by selecting the from and to filter items, and double clicking onthe relevant cell in the worksheet.

5.3 Query Builder

Within Microsoft Excel® the Query Builder can be accessed from the Query Manager and throughthe Value Formula and Table Formula menu items. The query templates that are aliased in theQuery Manager and in Sharperlight formulas are stored in a hidden sheet in the Excel workbook. Sothe workbook is completely self contained and can be distributed to other users if they want to usethe same queries and reports.

The topics below build on the concepts explained in the Filter, Selection and Output sectionsmentioned above.

5.3.1 Filter Attributes

The Query Builder in Microsoft Excel® is exactly the same interface shared throughout theSharperlight solution. So the Filter, Selection and Output concepts explained under the query sectionin this manual are all still the same. The key difference with Excel is that the filtering can bereferenced to the underlying workbook and the output results are extracted into the Excelworksheets.

In the Query section of this manual it explained the Filter Attributes, this section will focus on theGeneral Filter Attributes.

Top

When a user double clicks on filter, table or Superfield row in the Filter pane it will display the FilterAttributes dialog. The General tab in the Attributes dialog has some global settings that are commonto all filters. The Top Options are particularly important because they can easily reduce the numberof rows returned from the underlying application database without rigidly controlling the otherfilters. Top is useful for quickly reviewing the information in a table that might have thousands ormillions of records. When used with Sorting the Top option can easily craft top x reports withoutthe overhead of extracting all rows and then removing the irrelevant records. Sorting supportsascending and descending sorts, so descending could be used for the bottom x reports whilstascending could be used for top x.

53Excel Add-in


Filter Attributes - General

Suppress Zero Rows

Enabling zero row suppress will remove all rows that have zero in their numeric and currency outputcolumns. This is a global setting that affects all column values. To only affect specific columns it isbetter to use a filter condition that excludes rows with zero column value or an Output Expressionsthat conditionally hide rows with zero values.

Distinct

Sometimes the user needs to query all records that are unique without duplicates. A SummaryReport can do this because it will generate a group by clause to return unique records, however ona large table it may be very slow. The Distinct option takes a query and adds the DISTINCT SQLcommand to the generated SQL Select Statement; this is often faster than relying on a SummaryReport.

String Options

The String Options are only available on character or text fields. This option is accessed on theFunctions tab and it allows the end-user to filter on a substring or segment of the stored field value.For example, a code might starts with a grouping alpha and then has two digit team identifier and isthen followed by a 3 digit item number. With the String Option these three distinct segments can beseparated and filtered independently.



Filter Attributes - String Options

Training Exercise

Create a Top 10 Table Formula in Excel. Reference the filters to the underlying worksheet and useSorting to order the aggregated value field.

55Excel Add-in


5.3.2 Output Attributes

In the Query section of this manual it explained the Output Attributes for Formatting, Sorting andGeneral output settings. This section will focus on the Output Attribute Functions.

Output Attributes - Functions

Number Options

It is often better to groom numbers before they are returned in Excel, rather than trying to wrap anExcel formula around a Sharperlight formula. For the simple things like returning the absolute valueor reversing the sign or scaling the value; the Number Options can do these without relying onexternal formulas.

Item Description

Number Options

Absolute Returns the absolute value of a number, so it returns everyresult as a positive value.

Reverse Sign Reverses the returned value, so positives become negativesand negatives become positive. Often used with doubleentry accounting systems where the revenue accounts arenegative values but the required output is a positive.

Scale Adjusts the returned value to output just 000's or 00's. Canbe used with any scaling factor.

Aggregations



Datamodel developers have the option to set the default output aggregation mode of a numericfield. In most cases the developer would leave them set to Auto which will mean numeric fields aresummed when outputted.

Item Description

Aggregations

Sum Returns the sum of all values.

Count Returns the number of items in a group.

Average Returns the average of values in the query range.

Minimum Returns the lowest value in the query range.

Maximum Returns the highest value in the query range.

Distinct Sum Returns the sum of unique values.

Distinct Count Returns the count of unique values.

Distinct Average Returns the average of unique values.

Date Options

The Date Options are useful when a query set needs to grouped or sorted on a date or time part,and not the full date.

Item Description

Date Options

Year Returns the year part of a date in a four digit number.

Year / Quarter Returns the year and quarter number in the format YYYY/Q.The quarter is calculated on a calendar year.

Year / Month Returns the year and month number in the format YYYY/MM. The month is calculated on a calendar year and it padsthe month to two digits with a leading zero for single digitmonths.

Quarter Returns the calendar year quarter number for a date.

Month Returns the calendar year month number for a date.

Week ( from Jan 1st) Returns the calendar year week number for a date, assumingthe week calculation starts on the 1st of January.

Week (from first Sunday) Returns the calendar year week number for a date, assumingthe week calculation starts on the first Sunday in a year.

Week (from first Monday) Returns the calendar year week number for a date, assumingthe week calculation starts on the first Monday in a year.

Weekday Returns a number indicating which day of the week a datefalls into. Sunday is the first day of the week for thiscalculation.

Day of Month

Day of Year

Date and Time

57Excel Add-in


Item Description

Date Part

Time Part

Hour Part

5.3.3 Output Filters

The concept of an Output Filter is to restrict an output field without constraining the overall query.To apply a filter to an output, the filter must have been added to the Filter pane and the filter itemneeds to be named. Looking at the image below, the Attributes tab in the Filter Attributes dialogcontains the name of the filter. Once a filter has been named it will be displayed in the filter detailscolumn prefixed with an @ at sign.

Filter Attributes - Filter Name

With the named filter defined, output the field that will be selectively filtered based on the selectedvalues in the Filter pane. Then open the Output Attributes on that field and the filter namereferenced in the Filter pane will know be available in the Output Filters selection list. Select thefilter name relevant to the output field and from now on that field will be restricted to its filtereditems.

There is no limit to the number of output fields that can be constrained by Output Filters thougheach filtered column must have its own filtered value and name in the Filter Pane.



Output Attributes - Output Filters

Dynamic Output Filters are similar to the Output Filters explained above but don't consolidate to asingle column, they will generate a separate output column for each filter value combination.

Training Exercise

Using the Top 10 Table Formula created in the previous section, change the aggregation on thevalue field to average and re-extract. Create a new Table Formula, name one of the filters and thenuse this named filter in a dynamic column.

5.3.4 Filter Names

Filters have a default description that is defined in the Datamodel and this description can bechanged in the Filter pane for each query. Filters can also be named and this is a different conceptto changing the description; with named filters the filter from and to values can be cascaded intoother filters. A named filter becomes a variable that can be reused and they are identifiable with aprefix of @ for filter names and % for output names.

59Excel Add-in


Filter Names

Often several filters will be calculated based on the same filter value, rather that using multiplereferences it is easier to reference the filter value once and then name it for the other filters to use.The naming convention for filters assumes that the name refers to the from value box in a filter butthe to value box can be referenced by suffixing the filter name with a 2. For example, a filter namedAnalysisCode has a from and to value; the reference for the from value would be {@AnalysisCode}and the reference for the to value would be {@AnalysisCode2}.

5.3.5 Output Expressions

Sometimes a query will have most of the required fields for a report but some of the fields need tobe concatenated together or some mathematical calculation needs to be completed before theyare fit for purpose. In Microsoft Excel® this could be done after the data set has been extracted intothe worksheet but it may leave redundant columns and the query is no longer self contained andportable. To overcome this issue, Output Expressions can be created in the Output pane and thesehave there own calculation engine that supports a wide range of events, functions and objects.

Query Builder - Output Expression

To add an Expression, select any output row and use the right hand mouse click menu and select theAdd Expression item. This will open the Output Attributes dialog but it will have another tab for theExpression. Each Expression has a Unique Code, Data Type and Expression Formula.



Output Attributes - Expression

Usually an Expression will attempt an evaluation or calculation on the other output fields. In theObjects and Functions pane, the current row and previous row field names can be viewed anddouble clicking them will transfer their reference into the Expression Formula. Make sure that theData Type reflects the context of the expression because the calculation engine will generate errorsin the output if the Data Type is inconsistent.

Item Description

Functions

Conditional Conditional functions like IIF() and Switch().

Math Mathematical functions like convert text to number Val(),round to decimal places Round(), minimum Min() ormaximum Max() of two input values.

Strings String manipulation like upper case Upper(), lower caseLower(), trim a field Trim(), left pad a field with charactersPadLeft() and formatting a number Format().

Dates Date manipulation and evaluation functions like datedifference in days DateDiff(), get current date and timeNow(), add to date DateAdd() and is the input a valid dateIsDate().

Data Data evaluation functions like is null IsNull(), is a booleanIsBoolean() and is a valid accounting period IsPeriod().

Other

Language Translation Uses the Google language translation services on theinternet to translation an output fields from one language to

61Excel Add-in


Item Description

another LanguageTrans("en","es","FieldText").

Formatting Used to display colour icons in cells based on a weighting orconditional row and column formatting.

HTML Use the Google chart or map service or the SharperlightREST Service to include sub-reports and sub-elements into atable for the Web Channel.

Email Send an email message or send an a report to specifiedemail recipients.

Training Exercise

Create a Table Formula, select some text fields and a numeric fields. Add an Expression thatconcatenates two fields together and add another field that multiplies a numeric field by a specificvalue.

5.3.6 Sub Query

A Sub Query is a child query that can extract data independently from the parent query and its resultset can then form part of the parent query. The major advantage of using sub queries, is that theyare not restricted to the same query table, or filters and they don't even have to use the sameDatamodel as the parent query. The Query Builder can use a Sub Query as a filter but more often itis used in the output pane.

Often sub queries are aligned to the parent query by using some of the same filters or output rows.To link the child query to the parent query, first name all the Filter and Output Attributes. Filters withnames are displayed with a @ prefix in the Filter Details and they have a % prefix in Outputs pane.Then use the Add Sub Query menu item in the Outputs pane to display the Query Builder for the SubQuery. Select the relevant Table and set the filters in the Sub Query to use the filter names from theparent query. These filter names are listed and selectable by clicking on the down arrow icon on theright of the from and to filter boxes.



Query Builder - Sub Query

63Excel Add-in


5.3.7 Drill Through

A Drill Through has some similarities to an Output Sub Query because they both create a child querytemplate and they can use named filters from the parent query. However, the key differencebetween them is that the Drill Through query is displayed in a separate window. In Microsoft Excel®this window will be Explorer whilst in the Web Channel the Drill Through will be displayed in its ownbrowser window. Drill Through templates are created and maintained in the Filter pane of the Query Builder.

To create a Drill Through, name all the filters and the output fields similar to what needs to be donefor a Sub Query. Then use the right hand mouse click menu in the Filter pane to select the DrillThrough Menu.

Query Builder - Drill Through

With the Drill Through dialog open, use the New button to create a new query template. Give it aCode and a Menu Title, then use Edit Query to construct the query layout for the Drill Through.

Item Description

Details

Code The code defaults to D1 to Dn where n is an incrementingnumber. It can be overwritten with any alphanumeric



Item Description

combination the user prefers.

Type

Menu Title This title is displayed on the selection menu.

Options

Report Title

URL Parameters

Drill Through templates are displayed on right hand mouse click menu of Sharperlight formulas inMicrosoft Excel® and in web content published in the Web Channel.

65Excel Add-in


5.4 Formulas

Microsoft Excel® workbooks can contain numerous sheets and each sheet can contain hundredsand thousands of cells. So a workbook could contain millions of values, formulas and dependentcells. Since most workbooks are generally poorly documented, the size and complexity of aworkbook can severely limit other people from using them. Using Sharperlight in Excel has the samepotential dangers, so when designing spreadsheets it is recommend that they adhere to thefollowing simple design guidelines.

1. Layout formula blocks to the right of the key filters, so the filters are clearly visible andidentifiable.

2. Try to re-use the same query templates in Excel and where possible reference Sharperlightformulas to the underlying worksheet for filter values.

3. Static filtering that might be rarely changes or is specific to the rows or columns should belocated on the right hand side or bottom of the report layout.

4. Avoid hiding columns and rows because they are easy to overlook and are often accidentallydeleted during revisions.

5. Global filters that are shared throughout a workbook, like current date or company code, shouldbe located on a default worksheet. These filters should then be defined as name ranges and therange is then used instead of cell referencing in the Query Builder.

6. Avoid wrapping Excel formulas around Sharperlight formulas, this will stop the Query Builderfrom recognizing the formula and loading it.

Microsoft Excel® - Sharperlight Design Guidelines

5.4.1 Value Formula

Value Formulas give the spreadsheet designer complete control on layout and the formatting of areport. However, they will not natively expand and retract with the filtered range. So the ease oflayout and formatting may be at the loss of easy maintenance going forward.



When evaluating whether to use a Value Formula instead of a Table Formula, consider the followinggood points about a Value Formula.

a. Self contained formula in a single cell.

b. Great control on cell formatting and layout.

c. Integrates into existing layouts.

d. Easy to combine data from multiple sources.

e. Ideal for static summaries.

The cell references in a Value Formula can be edited and updated using the standard Excel formulabar. Cell references can be change from absolute to relative and visa versa, this only affects theedited cell and will not update the seed query that is maintained in Query Manager.

5.4.2 Table Formula

Since the release of Microsoft Excel® 2007 there has been a logical object called a Table. Thisobject has a default name and its rows and columns can automatically grow and shrink to reflectthe data. Tables have an optional summary row, shown at the bottom of the table, that can showstatistics like Sum, Average and Count. Sharperlight uses the Table object as a container for thequery data that comes from a Table Formula.

Table Formulas use an anchor formula in the worksheet to reference the related Excel Table, thequery template name and the filter cell references. This formula can be moved anywhere on theworksheet and it will continue to function provide the Excel Table name and query template exist.

Table Formula Menu

The formatting of a Table Formula data set in Excel is purely controlled by the formatting options inExcel. The formatting controls in the Query Builder only affect web content published to the WebChannel.

Copying Table Formulas

Copying Tabling Formulas can introduce some unusual behaviours if the user doesn't recognise whatthe formula is actually doing. Table Formulas reference the name of query being extracted, then thetable name that is being populated and then cell references. If a Table Formula is copied it will havethe same query name and it will refresh the same table name. Table Formulas can be copied butremember to change the table name to something unique in the workbook, when the formularefreshes it will create the named table if it doesn't exist.

67Excel Add-in


Excel Formulas in Tables

Excel Tables can use normal Excel formulas on the rows and the table referencing is named to thecolumn or column total. Table Formulas create tables that still adhere to this concept andcalculation columns can be inserted at will. The double click Sharperlight menu on tables has a Noneitem, if a manually created column is set to None, then the Table Formula will not attempt torefresh the column and it will effectively ignore it.

5.5 Drilldown

The Sharperlight formulas anchored in Microsoft Excel® are also action points for Drilldown. TheDrilldown menu can be displayed in two ways. With Table Formulas the resulting table will display aTable Formula menu if any of the rows are double clicked. The Drilldown Summary and Detailedoptions are available from this Table Formula menu.

Table Formula Menu - Drilldown

The Sharperlight Add-in menu and the equivalent right hand mouse click menu also have a Drilldownitem. To action a Drilldown from these menus, selected a Value or Table Formula and then selectDrilldown from the menu, it will display a small selection dialog for the user to decide on a Summaryor Detailed Drilldown.

Sharperlight Add-in Menu - Drilldown

Training Exercise



Using one of the previous Table Formula exercise, Drilldown on a row and on the formula.

5.6 Stacking Tables

Table Formulas use the native Table object in Microsoft Excel®. One of the big advantages of thisdata container is that it automatically grows and shrinks to correspond with the rows extracted. Iftwo or more tables are stacked above each other, provided there is an empty row to delineate thetables, they should adjust in size and maintain there relative position. This works relatively well inMicrosoft Excel® 2007 and it is even more consistent in Microsoft Excel® 2010 and 2013.

Stacking Tables

When planning to Stack Tables it usually better to construct a common query template that is thenreused for each table block. In this situation, each block will have a different filter range, the sameoutput columns and the Description for first output columns is updated to describe the query block.

69Excel Add-in


Stacking Query

The Table Formulas will flow relative to each other but simple sub-totals in the worksheet will notand they will be overwritten. To overcome this limitation, create a Table in Excel without a headeror footer row, and position it below or between the existing Table Formulas. Maintain at least oneempty row to delineate the tables. This manually created Table will have the same behaviour as aTable Formula and it can reference the other tables for sub-totals and grand totals.

Stacking Total

5.7 Detach Formulas

If a Workbook containing Value or Table Formulas is given to someone who doesn't have theSharperlight Add-in, they can see the formulas and the last calculated result but they can't refreshedthem. If they attempt to edit the formula it will return #NAME? indicating that the formula is not avalid formula name. To avoid any confusion in the distribution of Sharperlight workbooks to a non-Sharperlight audience, it is a good idea to detach the Sharperlight formulas using the DetachFormulas option on the ribbon. This utility will strip out all mdTable() and mdValue() formulaswithout affecting normal Excel formulas.

Detach Formulas

With the Table Formulas the Sharperlight content menu on the table, has a Detach Table Formula



option. This provides an easy means of removing the anchor formula and turning the table intostatic data. The Detach Table Formula will delete the mdTable() formula but it will not force onelast table refresh which is what would happen if the Table Formula was just deleted manually.

5.8 Table Totals

Sharperlight Table Formulas create and refresh Excel Tables. When the Table is first generated theTotal Row will be set with aggregation functions based on the query template. The aggregationfunctions on the Total Row can then be changed using normal Excel functions or set to None.

Table Total Functions

Excel Tables do not allow sub-totalling and even though sub-totals can be calculated in the QueryBuilder, using a Sort order and a Group, these sub-totals will not be outputted into the Excel Table.There are several strategies to overcome this limitation in Excel. For example,

a. Create two tables side by side with one summarised at the preferred level of sub-totalling.

b. Insert a Pivot Table into the worksheet using the Table data for the data source and create theappropriate summary levels.

c. Convert the Table into a normal Excel range and this will allow the traditional Excel Subtotalsto be generated. Unfortunately, the Table Formula needs to be detached from the tablebefore it can be converted into a range.

Part

VI



6 Explorer

The Sharperlight Explorer is a standalone reporting and analysis tool that can be opened from thedesktop or automated from the Preview pane in Query Builder, from a Table Formula and ValueFormula in Microsoft Excel, or in a web browser using the Sharperlight browser client. Explorer is itsapplication title and it is used by the other presentation layers for Drilldown and Drill Through.

6.1 Design

Query templates are the building blocks for Explorer reports.When Explorer is opened by aDrilldown or Drill Through, the client application generates a query template and then passes it toExplorer to extract and load. If Explorer is run from the Sharperlight program menu or from thedesktop, it will prompt the user to Open a report pack or to create a New query template using theQuery Builder.

Explorer - Open or New

Report Pack

Explorer can have multiple query templates open at the same time and if they are all saved togetherthey become a Report Pack. These packs also save the tab layout and formatting of the individualquery templates.

Training Exercise

Open Explorer from the Sharperlight program menu and use New to create a simple SummaryReport in the Query Builder. After extracting the query, Drilldown on a row to see how new querytabs are stack alongside the original query template.

73Explorer


6.2 Filter

The Filters that restrict a query set can be modified using the Filter dialog in Explorer. To display thiswindow, right hand mouse click on the output grid and select the Filter menu item.

Explorer - Filter Dialog

6.3 Settings

The Settings for Explorer control the titles and the layout of the query tabs. To display the Settingsdialog, right hand mouse click on the output pane and select the Settings menu item.



Explorer - Settings

Item Description

Tabs

Title This is the name on the tab for the current query template.

Position The tabs for query templates are by default displayed acrossthe top of the Explorer interface but they can be aligned onthe left, right or bottom.

Appearance

View

Theme Colour scheme

Show Grouping Shows or hides the Group by Column header.

Show Column Filtering Shows or hides the pick list for filtering column items.

Alternating Rows

Auto Column Resize

Title

Label This title appears at the top or bottom of the output pane. Itsupports dynamic references that cannot be displayed onthe tab title.

Position Top or bottom, centre, left or right.

Reference Dynamic references that provide more context at extractiontime. These references include user details, date and time,and filter values.

6.4 Save

The analysis in Explorer can be saved to several different file formats. To re-extract the same querytemplates and layout at a later date, use the Save to Report Pack or the Create Desktop Shortcut.The Save to a Report Pack will create a single file containing all the query tabs currently open whilstthe Create Desktop Shortcut does the same thing, it also creates a desktop shortcut referring backto saved report pack file.

75Explorer


Explorer - Save

Training Exercise

Using the query template constructed in the Explorer - Design training exercise, save it as a ReportPack.

Part

VII

77Publisher


7 Publisher

The Sharperlight Service delivers remote access to Datamodels and it has a REST Service that canrender Query Builder queries in a browser. This web delivery supports filtering and the end-users canchange the filter values and refresh the output. Multiple output layouts are available for the samequery, so the same data set can be rendered as a table, chart, pivot table and an RSS feed.

All the published queries are accessible through a unique URL address but there is a main list pagethat displays all the available published content and this is referred to as the Web Channel. Topublish a query, the end-user opens Publisher, creates a New item and then creates a query usingQuery Builder or imports an existing query template. They then configure how they want it to lookin the Web Channel.

Publisher

From the main query list in Publisher there is a right click mouse menu that contains usefulmaintenance functions. From this menu queries can be easily cloned and they can be saved to andloaded from a file.

Publisher - Right Mouse Click Menu



7.1 General

The General tab contains the main descriptive details for a published query, like titles, contacts andsimple versioning. To add the query select the Edit Query button and the Query Builder will bedisplayed for query construction.

Publisher - General

To create a published query it must have a Code and a defined query, everything else is optional.

Item Description

Details

Code The query code is auto generated on all new publishedqueries. The code can be change to any combination ofalpha-numeric characters.

Group The group can be any label and it is particularly useful in theWeb Channel where it can be used to filter the displayedpublished queries.

Title This is the default title for the browser tab and outputcontent.

Report Title If it isn't blank this title will be displayed on the outputcontent rather than the default title. Dynamic references canbe added to this title.

Description The description is displayed below the output content title.Dynamic references can be added to the description.

Contact

Author

79Publisher


Item Description

Email

Link

Version

Major/Minor/Revision

History

Training Exercise

Create a published query and give it a title, a brief description and use Edit Query to construct thequery template. Alternatively, import the query template saved to file during the Query Savesection. Save this published query and view it in the Web Channel.

Note To view a published query in the Web Channel the Sharperlight Service needs torunning.

7.2 Security

This Security tab restricts and places control over the published queries and who can see them andaccess them. This is report level security whilst data level security is inherited from the underlyingDatamodel.

Publisher - Security

.



Item Description

Security

Restrict editing to this UserAccount or an Administrator

Show in Public Service Listing Some published queries are used as sub-reports or asseparate frames in a dashboard, so they don't need to bedisplayed in the WebChannel.

User Authentication If a published query is enabled for the Public Service Listingand User Authentication is disable, everyone can see and runthe query.

Restrict access to these UserGroups

Only show in Public Listingwhen User Account is memberof

Access Code

Seed Keyword

Access Code Hash

Other

Expires On

History

Training Exercise

Use the security setting for "Show in Public Service Listing", disable this option on the query thatwas created in the previous exercise and refresh the Web Channel list page to see the impact.

7.3 Report

The Report tab controls the layout of filters and the default output type. It also contains layoutcontrols for constructing dashboards that can bring together other published queries into a singleunified webpage.

81Publisher


Publisher - Report

Item Description

Report

Default Type The default output type which is hyperlinked to the querycode in the Web Channel. The types include table, chart,pivot, rss and table basic.

Prompts

Enable If this is disabled the query filters are not displayed asprompts, the default values in the query template are thenused.

Show Title

Background Colour

Stack Filters The default layout for filters has them positioned on thesame row and thus, they will line wrap if they don't all fit onbrowser page. Enabling this Stack Filters will stack individualfilter items per row.

Options

Show Advanced Menu If the Sharperlight Service is fully configured then end-userscan open the Query Builder, Explorer and Publisher in thebrowser. This option will display these advanced menu itemsif enabled.

Create Split Reports based onFilter



Training Exercise

Using the published query created in the previous exercise, disable the title, stack the filters andchange the background color.

83Publisher


7.4 Table

The Table tab controls the rendering of the table output. It covers the layout of the table but it doesnot control the formatting of query fields, this is handled in the Query Builder (see Output Attributes- Formatting).

Publisher - Table

Item Description

General

Show Prompts Displays the filters on the published query.

Show Title Displays the title defined in the General tab.

Show Description Displays the description defined in the General tab.

Background Colour

Data

Limit report row count to Row cut off to restrict the number of rows displayed in thetable.

Page Size

Page refresh every Frequency in seconds where the table will be automaticallyrefreshed.

Table Header Row

Show Header Row

Background Colour

Table Rows

Alternating Rows



Item Description

Background Colour

Total Row

Show Total Row

Total Caption

Background Colour

7.5 Chart

Sharperlight includes both a HTML5 and a Flash charting engine. The broadest range of charts areHTML5 and the Flash charts are listed at the bottom of the Chart Type list.

Publisher - Chart

Item Description

General

Chart Type The types of charts include bar, line, pie, stacked, motion,timeline, area and treemap.

Data

Limit data row count to Row cut off to restrict the number of rows displayed in thechart.

Page refresh every Frequency in seconds where the chart will be automaticallyrefreshed.

85Publisher


Item Description

Legend

Show Display the chart legend.

Position Position of the chart legend.

7.6 Pivot

Pivot tables have always been popular because they allow an information consumer to easilyvisualise a diverse data set by dragging and dropping row and column dimensions, to slice a measurevalue. Sharperlight has its own Pivot browser, so published queries can be pivoted within a webbrowser.

The Pivot tab allows for a default layout of slicer, row, column and measures. This layout can bechange in the browser and populated to re-generate the pivot table.

Publisher - Pivot

7.7 Feed

The Feed service in Publishers uses the industry standard RSS (Really Simple Syndication) format topush information out to RSS clients. These web feeds are useful for event notifications and tohighlight exceptions.

There are numerous RSS Readers available on the market, most modern web browsers and email



clients support feed services whilst Windows Vista and Windows 7 have a desktop gadget fordisplaying Feed Headlines.

When creating a feed, the focus is on the key information with a Title and Content details. Touniquely identify each feed message, the published query will need a Unique ID and it is useful tohave an Updated Date Time.

Publisher - Feed

The Publisher Feed extends beyond just delivering RSS, it also outputs Atom and OData feeds.

7.8 Excel

Excel worksheet publishing is an alternative to publishing queries where an entire Excel workbook orspecified worksheets can be published to the Web Channel. Excel publish uses Microsoft Excel inthe background to refresh Sharperlight formulas and it then saves the nominated content intoHTML, PDF or XPS. Filters can be added to the published query and these can be associated to theSharperlight formulas in the Excel workbook.

To associated filters in the Web Channel with the cell filter referencing in the Excel workbook,create a simple published query with filters that reflect the filter referencing in the workbook. Namethe filters in published query and set the filter referencing in the workbook to use name rangeswhich have the same names as the published query. The filter values in the Web Channel will becopied to the same name references in the workbook and it will then be recalculated.

87Publisher


Publisher - Excel

To optimize the layout of the display format, it is suggested that workbook filters are located on aseparate worksheet and the output sheets are aligned to the top left of the worksheet.

Excel Workbook



7.9 Caching

Caching retains an aged copy of the last query data set that was extracted. The next time the queryis run the cache age is evaluated against the settings for days, hours, minutes and seconds. If thecache is older that these settings then the cache is freshened with a new query data set. Cachingcan use the Windows Task Scheduler to automate the regular refreshing of the cache.

Publisher - Caching

7.10 Links

Each output format on a published query has its own unique URL address. The key advantage ofthese addresses is that each can be hyperlinked into external documents and the content can beused in other websites by referencing an iFrame to the unique URL address. For example, a tablecould be included on a company's intranet website whilst still retaining the filtering and without anyloss of security.

89Publisher


Publisher - Links

Training Exercise

Copy one of the URL addresses from the Links tab and paste it into a web browser address bar tosee if it opens the Web Channel content. If the connection fails, the reference to localhost mightneed to be updated with the remote server name and access port, eg http://sharperserver:8080/mdService1Rest/

Part

VIII

91Web Channel


8 Web Channel

The Sharperlight Web Channel is the common term for the web service that delivers publishedqueries. This terminology is used because the list page can be branded with a custom title.

The list page could contain numerous published queries and so to simplify the navigation process,the Group filter can be used to only display Group codes that start with the entered letters.

Web Channel List

The main list page of the Web Channel details the Code, Title, Author and Group of each publishedquery. Icons are used to quickly visualise which rendering formats have been enabled for each queryand the same icons will hyperlink to the specified format. The Code is also a hyperlink to the defaultlayout, hence it is blue and underlined.

Item Description

Icons

Report Displays the configured report layout.

Table Displays the table.

SilverLight Displays the table rows in a SilverLight grid.

Chart Displays the selected default chart type.

Pivot Displays the pivot browser.

Excel CSV Opens a File Download dialog for saving the table contentinto a CSV file format.

Basic HTML Displays a simplified table without prompts or othercomplex features.

RSS Opens the RSS Feed in the default RSS Feed Reader.



The authentication settings for the Service will determine whether a User Login is required to accessa published query.

8.1 Table

Tables are similar to the Query Builder Preview pane, with row headers, rows and totals. As can beseen in the illustration below, the filters are retained in the browser and the lookups for each filtercan be viewed by selecting the button with two dots. The Submit button will refresh the query.

If a row is selected in a table there is a right hand mouse menu available. Depending on theconfiguration of the published query it may display items for the Query Builder, Explorer andPublisher; it may also display a Chart, Pivot, and Save As.

Table

Output Expressions have a special signification in the Web Channel because the expressions for URLaddresses, HTML content and Icons can further customize the look and feel of table.

93Web Channel


Table - Output Expression

8.2 Chart

The charts are rendered in a flash format, so the Adobe Flash® Player will need to be installed in thebrowser to view the charts. They will normally auto size to the browser pane and they supporthovering information when the cursor is positioned over the series data.

Using the dashboard layout control on the Report tab in Publisher, charts and tables can be stackedtogether to present a unified view of information.



Bar Chart

Charts and tables have a refresh page setting in Publisher. For real-time data sources, setting therefresh rate can be a useful visualisation tool because the rendered chart will periodically updatewith the latest information.

Pie Chart

95Web Channel


8.3 Report

A report can combine Tables, Charts and Pivot content in the same web page. The look and feel canalso be heavily customize through the use of design layouts, custom HTML and custom styles.

Report Layout

8.4 Pivot

The Pivot browser is populated based on the default slicer, row, column and measure locationsdefined in the Pivot tab. Once extracted into the browser the end-user can move query fieldsaround in the configuration pane. After modifying the layout, use the Populate button to reload thePivot table.

The configuration pane can be hidden by selecting the document icon next to the movement icons.



Pivot

8.5 Feed

The popularity of RSS Feeds means that many common applications will already support theconsumption and display of these web feeds. For example, Internet Explorer 7+, Microsoft Outlook2007+ and Windows desktop gadgets all give users access to feed data.

97Web Channel


RSS Feed

8.6 Excel

Excel published reports can have filters that refresh the worksheet content on Submit.

Excel Publish

Part

IX

99Dashboard


9 Dashboard

Sharperlight has several different techniques for constructing dashboards. For example, they can beprepared in Publisher using a Report Layout but this method precludes the end users from changingand adding to the layout, directly in the web browser. To overcome this limitation, Dashboard Pagesare a full browser interface that allows end users to create their own web pages that combinetogether web content. Dashboard Pages can contain multiple rectangular frames, called Tiles andthese can be resized and positioned directly in a web browser. Each Tile is a container for webcontent and it references its source through a saved Content URL.

Dashboard Pages are created, cloned and deleted through the Dashboard List Page. ExistingDashboard Pages can be opened from the List Page by double clicking on the document icon orDashboard Page Title. Every Dashboard Page has a unique web address referencing the SharperlightWeb Service. So Dashboards can be accessed directly from a shortcut, hyperlink and they can beembedded in other web interfaces.

Dashboard List Page

On a Dashboard Page the Tiles can be repositioned by left hand mouse clicking on the Tile Title barand holding the mouse click down during the movement. The other Tiles on the page will beautomatically adjust there position to allow room for the movement. Hovering the mouse pointerover the Tile Title bar will display in the right corner a Properties icon, a Maximize icon and theExpanded View icon.

Note Dashboard Pages use HTML 5 to allow Tile repositioning and resizing. Any web browserthat supports HTML 5 should be able to render and interact with these web pages.



Dashboard Page

In the Properties settings for a Tile, it can have an Expanded URL that displays an alternative view ofthe information. This Expanded View will open another web page and display the specified webcontent.

Expanded View

9.1 Properties

Dashboard Pages and Tiles have Properties that define their look and feel. To edit these Properties,the user needs to select the Properties icon on the Tile Title bar. This will switch the Dashboardpane into an editor interface with tabs for the Page and Tile.

Item Description

Page Properties

Code The unique alpha numeric dashboard code.

101Dashboard


Item Description

Group

Title The Title becomes the web page title for the dashboard.

Image URL An image file can be loaded into the background of theDashboard Page. This setting requires a URL address for animage file.

Margin Top

Margin Left

Grid Spacing Vertical

Grid Spacing Horizontal

Tile Properties

Code The unique alpha numeric tile code.

Title The title description that will be displayed in the Tile Titlebar

Content URL The web address to the source web content that will bedisplayed within the Tile.

Expanded URL The web address to the source web content that will bedisplayed when the Expanded View is selected.

Title Background Colour HTML colour codes for the title background.

Tile Background Colour

Contents

Sharperlight 2.9 Training Manual102


Index

- C -Client Setup 15

- D -Dashboard 99

Properties 100

DataModel

Installer 11, 14

Drill Through 11, 63

Drilldown 11, 41

- E -Excel Add-in 46

Detach Formulas 69

Query Manager 51

Stacking Tables 68

Table Formula 66

Table Totals 70

Value Formula 65

Explorer 72

Design 72

Filter 73

Settings 73

- L -Login 17

- P -Publisher 77

Caching 88

Chart 84

Feed 85

General 78

Links 88

Pivot 85

Report 80

Security 79

Table 83

- Q -Query

Data Types 31

Import 37

Mode 20, 32

Preview 35

Product 33

Save 37

Superfield 33

Table 34

- S -Service 16

Sub Query 61

- W -Web Channel 91

Write-back 11

Back Cover

sharperlight 2.9 training manual

Documents