Example 3: Sparklines

SparkL

Type of Visualisation

The type of visualisations used in this example are sparklines and column charts.

Source Data For Visualisation

This is generated through an MDX Query.

CDA Name

The CDA file used for this example is Sparkline.

Link to the Example

http://millersoft.ltd.uk:8080/pentaho/content/publishintel/render?solution=Spreadsheets&path=%2FWindows&file=exSpark.xlsx

Techniques Used  

I generated my own CDA through Saiku Analytics and plugged it into the CDA and then opened the data through SheetLoom into a spreadsheet. I then applied a named range to keep the data running dynamically and added Sparklines and column charts to show the changes in data.

Code Used

Data Access Query:

<DataAccess id=”16″ connection=”2″ type=”mdx” access=”public” cache=”false”>
<Name>Sparkline</Name>
<Query>
SELECT
NON EMPTY Hierarchize(Union(CrossJoin({[Measures].[Quantity]}, [Time].[Years].Members), CrossJoin({[Measures].[Sales]}, [Time].[Years].Members))) ON COLUMNS,
NON EMPTY {Hierarchize({{[Markets].[All Markets]}, {[Markets].[Territory].Members}})} ON ROWS
FROM [SteelWheelsSales]
WHERE {[Product].[Line].Members}
</Query>
<BandedMode>compact</BandedMode>
</DataAccess>

 

Example 2: Named Sheets and Pivot Tables

Capture

Type of Visualisation

The types of visualisation used in this example are named sheets for generating the data and pivot tables applied on top of the data. The data has a bar char and a pie chart to show the difference in spending. The data also has slicers attached to help the user slice through the data and pull out specific pieces of information.

Source Data For Visualisation

The source behind this example is a SQL statement.

CDA Name

The CDA used in this example is AllOrderFacts.

Link to the Example

http://www.millersoft.ltd.uk:8080/pentaho/content/publishintel/render?solution=Spreadsheets&path=%2FWindows&file=Example6.xlsx

Techniques Used  

There are  several very important techniques used when generating your data into a Pivot Table.

Number 1- When selecting the data you want to use for your Pivot Table the data must be selected along the columns so that when new data is added into the flow it gets reflected in the chart.

ex2column

Number 2- Once your data is in your Pivot Table, click on the data in the table and go to Options in the Menu bar and then the Options button in the bottom left hand corner and select Pivot Table Options and Select Data in the pop up box and tick the Refresh data when opening the file button and click okay, as shown below. As this helps automatically refresh the data each time the file is opened.

ex2datarefresh

Also in this example instead of using the named range technique the Sheets are renamed on the Excel Spreadsheet which works quicker for larger data sets.

Code Used

<DataAccess id=”500″ connection=”1″ type=”sql” access=”public” cache=”false”>
<Name>AllOrderFacts</Name>
<Query>
select
ORDERNUMBER ,
PRODUCTCODE ,
QUANTITYORDERED ,
PRICEEACH ,
ORDERLINENUMBER ,
TOTALPRICE ,
ORDERDATE ,
REQUIREDDATE ,
SHIPPEDDATE ,
STATUS ,
COMMENTS ,
CUSTOMERNAME ,
COUNTRY as CUSTOMERCOUNTRY,
TIME_ID ,
QTR_ID ,
MONTH_ID ,
YEAR_ID FROM ORDERFACT o join CUSTOMERS c on (o.CUSTOMERNUMBER = c.CUSTOMERNUMBER)
where YEAR_ID <![CDATA[<]]> ${year}
</Query>
<Parameters>
<Parameter name=”year” type=”Integer” default=”2006″/>
</Parameters>
</DataAccess>

Example 1: Using Dynamic Ranges with Excel

Capture

Type of Visualisation

The chart used for this example is a 3D Bar Chart.

Source Data For Visualisation

This is generated through an MDX Query.

CDA Name

The CDA file used for this example is Sales Budget By Month.

Link to the Example

http://millersoft.ltd.uk:8080/pentaho/content/publishintel/render?solution=Spreadsheets&path=%2FWindows&file=Example.xlsx

Techniques Used  

Named Ranges are used to dynamically update the chart from the template to the Pentaho file. Each row in the file has a different named range which links to the data in the spreadsheet and once opened via Pentaho refreshes the chart with live data.

Capture1

Code Used

Data Access Query:

<DataAccess id="340" connection="2" type="mdx" access="public" cache="false">
 <Name>SalesBudgetByMonth</Name>
 <Query>
      WITH MEMBER [Measures].[Budget] as [Measures].[Sales]
      select           {[Measures].[Budget]} ON columns,
             NON EMPTY {Tail(Filter([Time].[Months].Members, (NOT IsEmpty([Time].CurrentMember))), 12.0)} ON rows
     from [SteelWheelsSales]
 </Query>
 <BandedMode>compact</BandedMode>
 </DataAccess>