KeyStat Layout

A KeyStat layout is the elementary figure or amount on a report.

Each number / count / sum is separately calculated and has a number of settings.

1 Title

This is the name to help you remember what KeyStat this is.

2 Type

This has three options

Value = Which will just put the number on screen
Hidden = Which will put the number on screen AND create a hidden input type with the Query - Field name and the value. (Used for JavaScript processing on the page)
Graphic = The visual representation of the data in some form of chart

3 Query - Field(s)

This is the SQL field or fields you are looking to extract.

The field is used in conjunction with the query, as the data is returned by "selecting" this field.

If multiple fields are specified, their data is concatenated using spaces.

4 Query - SQL

The custom SQL query. This is a complicated field which can take many combinations of queries. Here are some simple examples to get you started.

Our form Id is 44:

All records which are in Dublin : Select count (intCDA_44_Id) as intTotal from CDA_44 Where strCDA_44_field_0_9 = 'Dublin'

All records which are in Dublin we had contact with in the first quarter of 2014: Select count (intCDA_44_Id) as intTotal from CDA_44,CCL_44 Where intCCL_44_CDAId = intCDA_44_Id and strCDA_44_field_0_9 = 'Dublin' and dteCCL_44_EndDateTime >= '2014-01-01 00:00:00' and dteCCL_44_EndDateTime <= '2014-04-01 00:00:00' Group By intCDA_44_Id

To save a lot of queries it is possible to use dynamic parameters as passed by the Tab, rather than the KeyStat.

e.g. In the Tab we put --3[##SEP##]10--

in the KeyStat we put

All records which are in Dublin we had contact with in the first quarter of 2014 which were last interacted with by staff X: Select count (intCDA_44_Id) as intTotal from CDA_44,CCL_44 Where intCCL_44_CDAId = intCDA_44_Id and strCDA_44_field_0_9 = 'Dublin' and dteCCL_44_EndDateTime >= '2014-01-01 00:00:00' and dteCCL_44_EndDateTime <= '2014-04-01 00:00:00' and intCDA_44_StaffId = --parameter0-- Group By intCDA_44_Id

The number after the [##SEP##] is paramater 0 for as many parameters are as needed.

The custom parameters above can be manual entered in or can be apart of a query string or a form variable, in order to reference the dynamic parameter simply add a query string parameter or form variable with the following notation KeyStatsCustomX (Replace the X with the number of the parameter you wish to pass i.e. Two parameters being passes A and B -- KeyStatsCustom1=A/KeyStatsCustom2=B

In your key stats tab the KeyStatsCustoms can be used on any of the key stats that you have added to the tab, to reference them simply add the following to you key stats reference --14[KeyStatsCustomX]--

5 Layout

If there are further HTML wrapping requirements, they can be added to the layout here. This wrapping is usually best left done in the Tab, as the simpler the KeyStat is, the more reusable the KeyStat is.