How to convert result to graph

First step in tutorial, tranform result to graph.

Navigate presentation using arrow keys. [o] opens "overview".

Sales data

Two columns are used from table SALES.
Saledate is used for date.
Amount for sales.

First step - sum sales for each month and create columns for each year.

To pivot table

Pivot table is used for data summarization to visualize information.

- Calculate year from Saledate.
- Insert sales data query into pivot query.
- Define columns in pivot query using field values from Year column

SQL code

Query for sales data


SELECT 
   YEAR(Saledate) "Year",  -- Get year from Saledate
   "Amount"                -- Amount will be Summed for each month
FROM dbo."SALES"
WHERE
   -- Filter out values from two years old to current year
   DATEDIFF( year, GETDATE(), "Saledate" ) > -3 AND DATEDIFF( year, GETDATE(), "Saledate" ) <= 0
            

Wrap query taking out sales data into pivot query


SELECT * FROM (
   SELECT YEAR(Saledate) "Year", "Amount" 
   FROM dbo."SALES"
   WHERE DATEDIFF( year, GETDATE(), "Saledate" ) > -3 AND DATEDIFF( year, GETDATE(), "Saledate" ) <= 0
) AS "RawData"
PIVOT (
   SUM( "Amount" )
   FOR "Year" IN ([2013],[2014],[2015],[2016],[2017])
) AS "PivotData"
            

Add month to pivot

Adding month enables summarization for each month.

Pivot columns for year 2013 and 2017 is removed.

SQL code

Month is added and sorted on


SELECT * FROM (
   SELECT YEAR(Saledate) "Year", MONTH(Saledate) "Month" /* insert month column */, Amount 
   FROM dbo.SALES
   WHERE DATEDIFF( year, '20160101', Saledate ) > -3 AND DATEDIFF( year, '20160101', Saledate ) <= 0
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2014],[2015],[2016]) -- 2013 and 2017 is removed, result here only shows 2014 - 2016
) AS PivotData
ORDER BY "Month" -- Sort on Month
            

Convert to Graph

To Graph, add AGGREGATE

Insert SQL into Selection query as AGGREGATE.


<QUERY ID="Sales">
   <AGGREGATES>
      <AGGREGATE NAME="sales" TYPENAME="graph" SERVER-CHARTTYPE="xy" DESCRIPTION="Sales each year compared to current year">
         <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
            <EXPRESSION>
SELECT * FROM (
   SELECT YEAR(Saledate) "Year", MONTH(Saledate) "Month", Amount 
   FROM dbo.SALES
   WHERE Saledate BETWEEN '20140101' AND '20161231'
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2014],[2015],[2016])
) AS PivotData
ORDER BY Month
            </EXPRESSION>
         </SQL>
      </AGGREGATE>
   </AGGREGATES>
</QUERY>
               

Add SQL to query

Add query in Selection query file.

How selection queries work is not addressed in this tutorial. See tutorials for Selection introduction.


<QUERY ID="Sales">
   <AGGREGATES>
      <AGGREGATE NAME="sales" TYPENAME="graph" SERVER-CHARTTYPE="xy" DESCRIPTION="Sales each year to current year">
         <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
            <EXPRESSION>
... Insert SQL here ...            
            </EXPRESSION>
         </SQL>
      </AGGREGATE>
   </AGGREGATES>
</QUERY>
               

SQL inserted

SQL code for pivot is inserted to selection query. See EXPRESSION


<QUERY ID="Sales">
   <AGGREGATES>
      <AGGREGATE NAME="sales" TYPENAME="graph" SERVER-CHARTTYPE="xy" DESCRIPTION="Sales each year to current year">
         <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
            <EXPRESSION>
            
-- Pivot is here !!!            
SELECT * FROM (
   SELECT YEAR(Saledate) "Year", MONTH(Saledate) "Month", Amount 
   FROM dbo.SALES
   WHERE Saledate BETWEEN '20140101' AND '20161231'
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2014],[2015],[2016])
) AS PivotData
ORDER BY "Month"

            </EXPRESSION>
         </SQL>
      </AGGREGATE>
   </AGGREGATES>
</QUERY>
               

Set bar color to green


<COLUMN INDEX="1" SERVER-COLOR="0x1F6435" />
<COLUMN INDEX="2" SERVER-COLOR="0x4A9586" />
<COLUMN INDEX="3" SERVER-COLOR="0x5EAE9E" />
            

Add COLUMN element to AGGREGATE.
In COLUMN it is possible to specify how column is displayed.
Set bar to green

XML with green color


<QUERY ID="Sales">
   <AGGREGATES>
      <AGGREGATE NAME="sales" TYPENAME="graph" SERVER-CHARTTYPE="xy" DESCRIPTION="Sales each year to current year">
         <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
            <EXPRESSION>
SELECT * FROM (
   SELECT YEAR(Saledate) "Year", MONTH(Saledate) "Month", Amount 
   FROM dbo.SALES
   WHERE DATEDIFF( year, GETDATE(), Saledate ) BETWEEN  -3 AND 0
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2014],[2015],[2016])
) AS PivotData
ORDER BY "Month"
            </EXPRESSION>
         </SQL>
         <COLUMN INDEX="1" SERVER-COLOR="0x1F6435" />
         <COLUMN INDEX="2" SERVER-COLOR="0x4A9586" />
         <COLUMN INDEX="3" SERVER-COLOR="0x5EAE9E" />
      </AGGREGATE>
   </AGGREGATES>
</QUERY>
               

Add 2012 and 2013 and reverse

Add years 2012 and 2013 to chart and reverse order for year columns in pivot result.

Thin lines, change to something better...

         <AGGREGATE NAME="sales" TYPENAME="graph" 
                    SERVER-CHARTTYPE="xy">
            <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
               <EXPRESSION>
                  <![CDATA[
SELECT * FROM (
   SELECT YEAR(Saledate) "Year", MONTH(Saledate) "Month", Amount 
   FROM dbo.SALES
   WHERE DATEDIFF( year, '20160101', Saledate ) BETWEEN  -4 AND 0 -- filter year 2016 and 4 previous years
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2016],[2015],[2014],[2013],[2012]) -- reverse order, start with 2016
) AS PivotData
ORDER BY "Month"
               ]]>
               </EXPRESSION>
            </SQL>
            <COLUMN INDEX="1" SERVER-COLOR="0x1F6435" />
            <COLUMN INDEX="2" SERVER-COLOR="0x4A9586" />
            <COLUMN INDEX="3" SERVER-COLOR="0x5EAE9E" />
            <COLUMN INDEX="4" SERVER-COLOR="0x74BAAC" />
            <COLUMN INDEX="5" SERVER-COLOR="0x8DC7BB" />
         </AGGREGATE>

How about using whisker?

Using whisker boxes is one way to pack more data in small spaces.

Also, adding legend and change format for x-axis showing months. Continue down to see how that is done.

Modify COLUMN for whisker

Setting layer type to whisker will insert column data as whisker box.


<COLUMN INDEX="1" SERVER-COLOR="0x1F6435" />
<COLUMN INDEX="2" SERVER-COLOR="0x4A9586" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="5" SERVER-DATAGAP="-0.1" SERVER-TOP="1" />
<COLUMN INDEX="3" SERVER-COLOR="0x5EAE9E" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="3" SERVER-DATAGAP="0.0" SERVER-TOP="1" />
<COLUMN INDEX="4" SERVER-COLOR="0x74BAAC" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="2" SERVER-DATAGAP="0.1" SERVER-TOP="1" />
<COLUMN INDEX="5" SERVER-COLOR="0x8DC7BB" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="1" SERVER-DATAGAP="0.2" SERVER-TOP="1" />
            

SERVER-LINEWIDTH = whisker box height.
SERVER-DATAGAP = gap between bars for whisker boxes.
SERVER-TOP = Bring layer to top, default is to write next layer at below other layers.

Adding legend

Adding legend is a bit more complicated and require LUA coding. Legends have so much possibilities it's better writing the code.

            <PROPERTY NAME="LUA-END">
               <![CDATA[
return function( chart, aggregate, tAttr )
   local box = chart:addLegend( chart:getWidth() - 340, chart:getHeight() - 5, false, "arial.ttf", 8 );
   box:setRoundedCorners( 3 )

   local R = tAttr.Result
   box:addKey(R:GetColumnName(1), 0x1F6435, 10);
   box:addKey(R:GetColumnName(2), 0x4A9586, 5);
   box:addKey(R:GetColumnName(3), 0x5EAE9E, 3);
   box:addKey(R:GetColumnName(4), 0x74BAAC, 2);
   box:addKey(R:GetColumnName(5), 0x8DC7BB, 1);

   box:setLineStyleKey();
   box:setAlignment("BottomCenter");
end
            ]]>
            </PROPERTY>

Adding PROPERTY (next page will show all code) with NAME = "LUA-END" it is possible to modify block before it is rendered. You have access to user as global variable. Parameters to function has all data for rendering chart.

All code for chart

All code for rendering chart with 2016 and previous 4 years of sales.


   <QUERY ID="sales";>
      <AGGREGATES>
         <AGGREGATE NAME="sales" SIMPLE="Compare sales" TYPENAME="graph" DESCRIPTION="Sales each year to current year"
                    SERVER-CHARTTYPE="xy" SERVER-LAYERTYPE="xy" SERVER-MARGIN="60,10,10,75">
            <PROPERTY NAME="LUA-END">
               <![CDATA[
return function( chart, aggregate, tAttr )
   local box = chart:addLegend( chart:getWidth() - 340, chart:getHeight() - 5, false, "arial.ttf", 8 );
   box:setRoundedCorners( 3 )

   local R = tAttr.Result
   box:addKey(R:GetColumnName(1), 0x1F6435, 10);
   box:addKey(R:GetColumnName(2), 0x4A9586, 5);
   box:addKey(R:GetColumnName(3), 0x5EAE9E, 3);
   box:addKey(R:GetColumnName(4), 0x74BAAC, 2);
   box:addKey(R:GetColumnName(5), 0x8DC7BB, 1);

   box:setLineStyleKey();
   box:setAlignment("BottomCenter");
end
            ]]>
            </PROPERTY>

            <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
               <EXPRESSION>
                  <![CDATA[
SELECT * FROM (
   SELECT YEAR(Saledate) "Year", RIGHT(N'00' + CAST( (YEAR(GETDATE()) % 100) AS NVARCHAR(2) ), 2 ) + '-' + RIGHT(N'00' + CAST( MONTH(Saledate) AS NVARCHAR(2)), 2 ) "Month", Amount 
   FROM dbo.SALES
   WHERE DATEDIFF( year, '20160101', Saledate ) BETWEEN  -4 AND 0
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2016],[2015],[2014],[2013],[2012])
) AS PivotData
ORDER BY "Month"
               ]]>
               </EXPRESSION>
            </SQL>
            <COLUMN INDEX="1" SERVER-COLOR="0x1F6435" />
            <COLUMN INDEX="2" SERVER-COLOR="0x4A9586" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="5" SERVER-DATAGAP="-0.1" SERVER-TOP="1" />
            <COLUMN INDEX="3" SERVER-COLOR="0x5EAE9E" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="3" SERVER-DATAGAP="0.0" SERVER-TOP="1" />
            <COLUMN INDEX="4" SERVER-COLOR="0x74BAAC" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="2" SERVER-DATAGAP="0.1" SERVER-TOP="1" />
            <COLUMN INDEX="5" SERVER-COLOR="0x8DC7BB" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="1" SERVER-DATAGAP="0.2" SERVER-TOP="1" />
         </AGGREGATE>
      </AGGREGATES>
   </QUERY>
            

Marked code shows how to present month with year and pad year and month number with 0

Integrate Selection query engine

Result is static, that is very limited flexibility. Using Selection query engine will make the chart much more flexible. Next part in tutorial will show how to do this

- Add Selection fields to generate query.
- Insert Selection query to AGGREGATE.
- Pad result with missing months.

Add fields to query

Adding fields to query. Add either by typing XML directly in file or create a query in Selection, view XML code, copy it to the query file.

Fields inserted to query file


<FIELD ID="SALES1SalesK" ALIAS="Salename" TABLE="SALES1"/>
<FIELD ID="SALES1Name" ALIAS="Salename" TABLE="SALES1"/>
<FIELD ID="SALES1Saledate" ALIAS="Saledate" TABLE="SALES1"/>
<FIELD ID="SALES1Year" ALIAS="Year" TABLE="SALES1"/>
<FIELD ID="SALES1Amount" ALIAS="Amount" TABLE="SALES1"/>
            

Database description for Sales table, how to write description for database isn't addressed in this tutorial.


<TABLE PREFIX="SALES1" NAME="SALES">
   <FIELD NAME="SalesK" ALIAS="Sales ID" KEY="1" TYPE="I4" CONDITION="1" DESCRIPTION="Primary key" />
   <FIELD NAME="Name" ALIAS="Salename" TYPE="STR" CONDITION="1" DESCRIPTION="Sales description" />
   <FIELD NAME="Saledate" ALIAS="Saledate" TYPE="DATE" CONDITION="1" DESCRIPTION="Sales date" />
   <FIELD ID="Year" NAME="Saledate" ALIAS="Year" TYPE="R8" CONDITION="expression" DESCRIPTION="Sales year">
      <EXPRESSION>YEAR($T.$F)</EXPRESSION>
   </FIELD>
   <FIELD NAME="Amount" ALIAS="Amount" TYPE="R8" CONDITION="1" DESCRIPTION="Sales value" />
</TABLE>
            

"Down" shows how to add fields creating query in Selection and paste generated XML into query in query file

New query and adding fields

"Down" to view all operations

New query and adding fields

Operations to take out XML for query when it has been created.

Fields inserted to query

Fields are pasted into query

Aggregate data is removed for visibility


<QUERY ID="sales">
   <FIELD ID="SALES1SalesK" ALIAS="Salename" TABLE="SALES1"/>
   <FIELD ID="SALES1Name" ALIAS="Salename" TABLE="SALES1"/>
   <FIELD ID="SALES1Saledate" ALIAS="Saledate" TABLE="SALES1"/>
   <FIELD ID="SALES1Year" ALIAS="Year" TABLE="SALES1"/>
   <FIELD ID="SALES1Amount" ALIAS="Amount" TABLE="SALES1"/>
   <AGGREGATES>
      <AGGREGATE NAME="sales" TYPENAME="graph"
                 SERVER-CHARTTYPE="xy" SERVER-MARGIN="60,10,10,75">
                 ... removed for visibility ...
      </AGGREGATE>
   </AGGREGATES>
</QUERY>
         

Next - Merge static and dynamic query parts

Parts merged

Generated SQL will be mixed with static SQL

   <QUERY ID="Sales">
      <FIELD ID="SALES1SalesK" ALIAS="Salename" TABLE="SALES1"/>
      <FIELD ID="SALES1Name" ALIAS="Salename" TABLE="SALES1"/>
      <FIELD ID="SALES1Saledate" ALIAS="Saledate" TABLE="SALES1"/>
      <FIELD ID="SALES1Year" ALIAS="Year" TABLE="SALES1"/>
      <FIELD ID="SALES1Amount" ALIAS="Amount" TABLE="SALES1"/>
      <AGGREGATES>
         <AGGREGATE NAME="sales" TYPENAME="graph" DESCRIPTION="Sales each year to current year"
                    SERVER-CHARTTYPE="xy">
            <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
               <EXPRESSION>
                  <![CDATA[
SELECT * FROM (
   SELECT 
      YEAR(SALES1.Saledate) "Year", 
      RIGHT(N'00' + CAST( (YEAR(GETDATE()) % 100) AS NVARCHAR(2) ), 2 ) + '-' + RIGHT(N'00' + CAST( MONTH(SALES1.Saledate) AS NVARCHAR(2)), 2 ) "Month", 
      SALES1.Amount 
   {#V from}
   {#V where}
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN ([2016],[2015],[2014],[2013],[2012])
) AS PivotData
ORDER BY "Month"
               ]]>
               </EXPRESSION>
            </SQL>
            <COLUMN INDEX="1" SERVER-COLOR="0x1F6435" />
            <COLUMN INDEX="2" SERVER-COLOR="0x4A9586" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="5" SERVER-DATAGAP="-0.1" SERVER-TOP="1" />
            <COLUMN INDEX="3" SERVER-COLOR="0x5EAE9E" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="3" SERVER-DATAGAP="0.0" SERVER-TOP="1" />
            <COLUMN INDEX="4" SERVER-COLOR="0x74BAAC" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="2" SERVER-DATAGAP="0.1" SERVER-TOP="1" />
            <COLUMN INDEX="5" SERVER-COLOR="0x8DC7BB" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="1" SERVER-DATAGAP="0.2" SERVER-TOP="1" />
         </AGGREGATE>
      </AGGREGATES>
   </QUERY>

{#V from} and {#V where}

What does {#V from} and {#V where} do?

{#V ????} is a marker for something that is replaced. Selection scans string for these markers and if found it is interpreted and proper operation is done.
{#V from} = Inserts generated FROM part
{#V where} = Inserts generated WHERE part

Now you can select chart data

Select chart data

Time to select

Open selection for selected block. Condition fields attached to query is shown.

- Press search (select icon for block)
- Type value and set operator for value before adding filter to query

What happened?

Strange values show up setting condition

- This is a common problem calculating information based on time space
- If no value found for time space, result could "miss" spaces

We need to pad result to fix missing time spaces

Pad result

We can never be sure to get information setting filter. To make sure result is always going to be right for time spaces, pad it with fake data.

SQL for this is a bit advanced

Fake data

Generate fake data in temporary table. One entry for each month

-- Delete temporary table if it exists
if OBJECT_ID('tempdb..#tMonth') IS NOT NULL DROP TABLE #tMonth;
CREATE TABLE #tMonth (Saledate DATETIME, Amount FLOAT)

DECLARE @iMonth INT
DECLARE @i INT = (SELECT YEAR( GETDATE() ))
WHILE @i >= (SELECT YEAR( DATEADD( year, -4, GETDATE() ) ))
BEGIN
   SET @iMonth = 0
   WHILE @iMonth < 12
   BEGIN
      INSERT INTO #tMonth VALUES( DATEADD( mm, (@i - 1900) * 12 + @iMonth, '1900-01-01' ), 0.0 )
      SET @iMonth = @iMonth + 1
   END
   
   SET @i = @i - 1
END
SELECT * FROM #tMonth -- One entry for each month current year and previous four years
DROP TABLE #tMonth; -- Delete temporary table

Find years with values

Extract years that has values based on generated Selection query


DECLARE @i INT = (SELECT YEAR( GETDATE() ))
DECLARE @columns AS NVARCHAR(MAX) = '' -- @columns has pivot columns
WHILE @i >= (SELECT YEAR( DATEADD( year, -4, GETDATE() ) ))
BEGIN
   IF (SELECT COUNT(*) FROM (SELECT YEAR(SALES1.Saledate) "year" {#V from} {#V where}) t WHERE t."year" = @i) > 0
   BEGIN
      IF LEN(@columns) > 0 
         SET @columns = @columns + ', '
      
      SET @columns = @columns + '[' + CAST(@i AS NVARCHAR(4)) + ']' -- Add column 
   END
   
   SET @i = @i - 1
END

-- If no columns then make sure we have at least one
IF LEN( @columns )
   @columns += '[' + CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + ']'

If columns in pivot conforms to base query we need to build pivot query in a string. It can't be hardcoded. SQL code above shows how to extract years from base query.

Count hits for each year that chart has been set up to present values for, if values found then add column to @columns.
Note that {#V from} {#V where} is used to find values based on generated query with active filter.

Final SQL code for aggregate

Complete SQL code that merges with Selection query

SET NOCOUNT ON
if OBJECT_ID('tempdb..#tMonth') IS NOT NULL DROP TABLE #tMonth
CREATE TABLE #tMonth (Saledate DATETIME, Amount FLOAT)

DECLARE @i INT = (SELECT YEAR( GETDATE() )), @iMonth INT, @columns AS NVARCHAR(MAX) = ''

WHILE @i >= (SELECT YEAR( DATEADD( year, -4, GETDATE() ) ))
BEGIN
   IF (SELECT COUNT(*) FROM (SELECT YEAR(SALES1.Saledate) "year" {#V from} {#V where}) t WHERE t."year" = @i) > 0
   BEGIN
      IF LEN(@columns) > 0 
         SET @columns = @columns + ', '

      SET @columns = @columns + '[' + CAST(@i AS NVARCHAR(4)) + ']'
   END
   
   SET @iMonth = 0
   WHILE @iMonth < 12
   BEGIN
      INSERT INTO #tMonth VALUES( DATEADD( mm, (@i - 1900) * 12 + @iMonth, '1900-01-01' ), 0.0 )
      SET @iMonth = @iMonth + 1
   END
   
   SET @i = @i - 1
END

IF LEN( @columns )
   @columns += '[' + CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + ']'

DECLARE @Sql AS NVARCHAR(MAX) = N'
SELECT * FROM (
   SELECT YEAR(SALES1.Saledate) "Year", RIGHT(N''00'' + CAST( (YEAR(GETDATE()) % 100) AS NVARCHAR(2) ), 2 ) + ''-'' + RIGHT(N''00'' + CAST( MONTH(SALES1.Saledate) AS NVARCHAR(2)), 2 ) "Month", SALES1.Amount 
   {#V from --escape} {#V where --escape}
   UNION 
   SELECT YEAR(t.Saledate) "Year", RIGHT(N''00'' + CAST( (YEAR(GETDATE()) % 100) AS NVARCHAR(2) ), 2 ) + ''-'' + RIGHT(N''00'' + CAST( MONTH(t.Saledate) AS NVARCHAR(2)), 2 ) "Month", t.Amount 
   FROM #tMonth t
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN (' + @columns + ')
) AS PivotData
ORDER BY "Month"'

EXECUTE( @Sql ); DROP TABLE #tMonth;

Pivot query is executed using TSQL EXECUTE. Pivot query is placed in @Sql string, note columns taken from @columns.

{#V where --escape}

{} = Selection will try to understand whats between brackets
{#V } = Value from query
{#V where } = SQL WHERE from owning query
{#V where --escape } = Escape inserted string to work in quoted string.

Samples

Now the chart is much more flexible because it uses Selection functionality to filter data

Complete code

All code for chart, blue color is used


   <QUERY ID="Sales Blue">
      <FIELD ID="SALES1SalesK" ALIAS="Salename" TABLE="SALES1"/>
      <FIELD ID="SALES1Name" ALIAS="Salename" TABLE="SALES1"/>
      <FIELD ID="SALES1Saledate" ALIAS="Saledate" TABLE="SALES1"/>
      <FIELD ID="SALES1Year" ALIAS="Year" TABLE="SALES1"/>
      <FIELD ID="SALES1Amount" ALIAS="Amount" TABLE="SALES1"/>
      <AGGREGATES>
         <AGGREGATE NAME="sales" SIMPLE="Compare sales (selectable)" TYPENAME="graph" DESCRIPTION="Sales each year to current year"
                    SERVER-CHARTTYPE="xy" SERVER-MARGIN="60,10,10,75" OUTPUT-ORDER="101">
            <PROPERTY NAME="LUA-END">
               <![CDATA[
return function( chart, aggregate, tAttr )
   local box = chart:addLegend( chart:getWidth() - 255, chart:getHeight() - 5, false, "arial.ttf", 8 );
   box:setRoundedCorners( 3 )

   local R = tAttr.Result
   if R:GetColumnCount() > 1 then box:addKey(R:GetColumnName(1), 0x23819C, 10); end
   if R:GetColumnCount() > 2 then box:addKey(R:GetColumnName(2), 0x2FAACE, 5); end
   if R:GetColumnCount() > 3 then box:addKey(R:GetColumnName(3), 0x44B4D5, 3); end
   if R:GetColumnCount() > 4 then box:addKey(R:GetColumnName(4), 0x57BCD9, 2); end
   if R:GetColumnCount() > 5 then box:addKey(R:GetColumnName(5), 0x7BCAE1, 1); end

   box:setLineStyleKey();
   box:setKeySpacing( 3 );
   box:setAlignment("BottomCenter");
end
            ]]>
            </PROPERTY>

            <SQL COMMAND="sum_sales_per_month" TYPENAME="select">
               <EXPRESSION>
                  <![CDATA[
SET NOCOUNT ON
if OBJECT_ID('tempdb..#tMonth') IS NOT NULL DROP TABLE #tMonth
CREATE TABLE #tMonth (Saledate DATETIME, Amount FLOAT)

DECLARE @i INT = (SELECT YEAR( GETDATE() )), @iMonth INT, @columns AS NVARCHAR(MAX) = ''

WHILE @i >= (SELECT YEAR( DATEADD( year, -4, GETDATE() ) ))
BEGIN
   IF (SELECT COUNT(*) FROM (SELECT YEAR(SALES1.Saledate) "year" {#V from} {#V where}) t WHERE t."year" = @i) > 0
   BEGIN
      IF LEN(@columns) > 0 
         SET @columns = @columns + ', '

      SET @columns = @columns + '[' + CAST(@i AS NVARCHAR(4)) + ']'
   END
   
   SET @iMonth = 0
   WHILE @iMonth < 12
   BEGIN
      INSERT INTO #tMonth VALUES( DATEADD( mm, (@i - 1900) * 12 + @iMonth, '1900-01-01' ), 0.0 )
      SET @iMonth = @iMonth + 1
   END
   
   SET @i = @i - 1
END

IF LEN( @columns )
   @columns += '[' + CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + ']'

DECLARE @Sql AS NVARCHAR(MAX) = N'
SELECT * FROM (
   SELECT YEAR(SALES1.Saledate) "Year", RIGHT(N''00'' + CAST( (YEAR(GETDATE()) % 100) AS NVARCHAR(2) ), 2 ) + ''-'' + RIGHT(N''00'' + CAST( MONTH(SALES1.Saledate) AS NVARCHAR(2)), 2 ) "Month", SALES1.Amount 
   {#V from --escape} {#V where --escape}
   UNION 
   SELECT YEAR(t.Saledate) "Year", RIGHT(N''00'' + CAST( (YEAR(GETDATE()) % 100) AS NVARCHAR(2) ), 2 ) + ''-'' + RIGHT(N''00'' + CAST( MONTH(t.Saledate) AS NVARCHAR(2)), 2 ) "Month", t.Amount 
   FROM #tMonth t
) AS RawData
PIVOT (
   SUM( Amount )
   FOR "Year" IN (' + @columns + ')
) AS PivotData
ORDER BY "Month"'

EXECUTE( @Sql ); DROP TABLE #tMonth;]]>
               </EXPRESSION>
            </SQL>
            <COLUMN INDEX="1" SERVER-COLOR="0x23819C" />
            <COLUMN INDEX="2" SERVER-COLOR="0x2FAACE" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="5" SERVER-DATAGAP="-0.1" SERVER-TOP="1" />
            <COLUMN INDEX="3" SERVER-COLOR="0x44B4D5" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="3" SERVER-DATAGAP="0.0" SERVER-TOP="1" />
            <COLUMN INDEX="4" SERVER-COLOR="0x57BCD9" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="2" SERVER-DATAGAP="0.1" SERVER-TOP="1" />
            <COLUMN INDEX="5" SERVER-COLOR="0x7BCAE1" SERVER-LAYERTYPE="whisker" SERVER-LINEWIDTH="1" SERVER-DATAGAP="0.2" SERVER-TOP="1" />
         </AGGREGATE>
      </AGGREGATES>
   </QUERY>
            

END