Creating Dashboards with CDE

This post shows the steps to get a customized CDE dashboard up and running on Pentaho BI Server. Starting from a mock-up sketch of a dashboard, this post shows the implementation steps all the way to the finished result, explaining CDE concepts as they appear along the way. CDE uses web technologies i.e. HTML/CSS/JS and SVG. The tutorial dashboard is tested on Firefox 3 and above and on Google Chrome 12. It may require some tweaks or hacks to get it to work on other browsers, especially older ones. So please make sure you have a modern browser when following the tutorial.

The Finished Dashboard

To whet your appetite, here’s what the final dashboard looks like. The select box at the top allows to choose the year to report, and all dashboard elements update accordingly.

Download the demo dashboard files. You can import them into a new folder on your server repository. It might not display properly when opened directly after the import. If that happens, open, preview and save the dashboard in your CDE. The dashboard will display properly then.

Before We Start

The sample dashboard is designed on Pentaho Community Server 3.8, but it should work down to and including 3.6.

You’ll need CDE a.k.a. Community Dashboard Editor, too. CDE is part of an entire set of open source plugins to the Pentaho BI Server, contributed and maintained by Pentaho Partner webdetails. There are several community plugins maintained by webdetails, some of which depend on or extend each other, and the best way to get the latest version of them is using the CTools installer. New features and bug fixes are put into the CTools on a regular basis, so it is a good idea to rerun the installer to update them regularly. Specifically, please make sure to update the CTools before following the tutorial. Older versions of CTools may not behave as described in this tutorial.

If you cannot use the CTools installer, or would like to start with a fresh server anyway, go with a freshly built Community BI Server with CTools and Saiku included: Sign up and login to the analytical labs continuous integration server at ci.analytical-labs.com and follow the instructions as outlined in Tom’s post. In fact, that’s what I did myself to work on this blog post.

From now on I’ll assume that you have a properly set up server with recent CTools installed. Let’s start!

A First Sketch

When working on a dashboard I find it very helpful to create a sketch of it up front and have it around during implementation. It need not be digital, and may just be a pen and paper drawing, but I’d recommend always having some mock-up reference before attempting the implementation. For the tutorial dashboard the reference looks like this.

The idea is to have 5 overall sections

  1. A title bar with a year selector
  2. A sales performance chart in the upper left area, the vertical marks show previous year’s performance
  3. A table of numbers showing the detailed sales numbers compared to previous year’s in the upper right area
  4. A top customers chart in the lower left area
  5. A top product lines chart in the lower right area

Let’s make it happen!

Starting with CDE: Layout, Components, and Data Sources

Before diving into the implementation, I’d like to briefly discuss some basic CDE concepts. A CDE dashboard is defined in terms of three distinct categories: Layout, Components, and Data Sources.

A CDE Layout defines static HTML blocks that the dynamic parts of the dashboard can hook into. Think of it as providing the underlying grid for the more interesting dashboard elements. CSS and general JavaScript are also maintained in the layout section.

CDE Components are entities that render and display data. Charts, selector boxes and data tables are components. If it’s a dashboard entity that displays data or requires data in order to work properly, it’s a component. In the example dashboard the year selector box, the charts, and the data table are components. Components are placed on the dashboard by first preparing a named space for them in the layout section, and then linking the component into that space.

CDE Data Sources provide data to components. They are usually parametrized SQL or MDX queries and they do not have direct visual representation on a dashboard. It’s just the data rows.

The following illustration shows the three concepts and how they relate.

A Screencast is Worth a Thousand Words

The entire process of creating and tweaking the dashboard is shown in the screencast below. The sections below the video explain the steps taken to build the dashboard. I recommend viewing the video in a new browser window, while reading through the explanations. Enjoy!

Starting with a Basic Layout

The layout provides the general structure of a dashboard. I like to think of it as the grid for the other elements to hook into. In CDE you create a layout by defining (possibly nested) rows and columns, while also placing custom HTML markup where necessary. CDE uses blueprint css to simplify this task: think of the dashboard to be 24 units wide in total, and assign column widths accordingly. If you want to fill the whole page, you may go for a single column of size 24, two columns of size 12 or three columns of the sizes 6, 10, and 8 for example.

It may help to have a look at the blueprint general and grid test pages to get the idea. Also, when you’re trying to get the layout right, feel free to assign color to the rows and columns so their extent clearly stands out in the preview.

Placing the first Component: The Year Selector

The selector box at the top allows choosing the year of interest. To place it on the dashboard, the layout, the component and a data source must work together. Here’s the steps to successfully place a component:

  1. create a new data source that provides the required data and name it properly i.e. “get_all_years”
  2. create a new layout column in the appropriate spot, and name it properly i.e. “year_select”
  3. create a new component of the desired type and assign the data source (“get_all_years”) and the HTML object (“year_select”)

The SQL query for the year selector is as follows:

SELECT DISTINCT YEAR_ID as YEAR FROM ORDERFACT ORDER BY YEAR_ID
A Side Note On Data Access»

Placing a Chart: The Top Customers Section

Let’s skip the entire upper section of the dashboard for the moment and continue with the top customers section to the lower left. It should display the top 5 customers for the selected year. The query to start with may look something like this:

SELECT 
	'Revenue' AS SERIES,
	CUSTOMERNAME,
	SUM(ORDERFACT.TOTALPRICE) AS REVENUE
FROM 
	ORDERFACT
	INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERNUMBER = ORDERFACT.CUSTOMERNUMBER
WHERE
	YEAR_ID = 2004
GROUP BY 
	SERIES, CUSTOMERNAME
ORDER BY
	REVENUE DESC LIMIT 5

The result set is something like this:

Series Customername Revenue
Revenue Euro+ Shopping Channel 375268.36
Revenue Mini Gifts Distributors Ltd. 256474.26
Revenue Australian Collectors, Co. 140859.57
Revenue Land of Toys Inc. 138285.68
Revenue Vida Sport, Ltd 117713.56

The dashboard puts that into a bar chart component which is configured to show horizontal bars.

The chart looks quite reasonable. But the chart should update whenever a new year is chosen in the selector box. By default when a new year is chosen, nothing happens. To fix that, dashboard parameters are required.

Observing Year Changes

Dashboard parameters are essentially variables living on the dashboard. Select components can alter their values using their “Parameter” property. All components can listen to changes of parameter values using the “Listeners” property. The demo dashboard should have a parameter holding the currently selected year. The parameter should have a default value of 2004, the selector box should be able to alter that value, and all other components should listen to changes of that value, and update themselves accordingly.

When a component, such as the “Top 5 Customers” chart, detects a change in the year parameter, it should rerun its underlying query passing the newly selected year into it. In order for that to work the query itself must be parametrized, and the chart component must map the dashboard parameter to a query parameter before running the query. The “Parameters” property is used for that. Query parameters are declared on the data source, and can be referenced within the query using ${parameter_name} syntax.

Dashboard parameters and query parameters often share names (i.e. both are called year), but since they are distinct entities, the executing component always has to map dashboard parameters to query parameters explicitly.

Tuning a Chart

After the chart reacts to year selections properly, its appearance is tweaked a bit.

Reversing the Order of Bars

I’d like the longest bar to be at the top of the chart, which is at the bottom by default. That’s solved by sorting the data in SQL. The adjusted query reverses the order of the result rows.

SELECT * FROM 
(
	SELECT 
		'Revenue' AS SERIES,
		CUSTOMERNAME,
		SUM(ORDERFACT.TOTALPRICE) AS REVENUE
	FROM 
		ORDERFACT
		INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERNUMBER = ORDERFACT.CUSTOMERNUMBER
	WHERE 
		YEAR_ID = ${year}
	GROUP BY 
		SERIES, CUSTOMERNAME
	ORDER BY 
		REVENUE DESC
	LIMIT 5
)
ORDER BY REVENUE ASC

Removing the Vertical Axis

I’d like to remove the vertical axis on the chart, so the bars and the labels are connected more clearly visually. One way to do it is to use extension points. Extension points allow direct JavaScript access to protovis properties of CCC charts. And yes, CCC a.k.a. Community Charting Components is another member of the CTools family.

To remove the vertical axis, I set the yAxisRule_lineWidth extension point to 0.

A Side Note On CCC and Protovis»

Adding a Currency Symbol to the Axis Labels

The xAxisLabel_text extension point allows manipulating the text of each label using a function. The original value of each label is conveniently passed into the function as an argument. I’d like to prepend the $ sign to each of the labels.

xAxisLabel_text = function(d){return '$ '+ sprintf('%d',d/1000)+'k'}

The incoming value is the number that goes on the label, say 1.000.000, and the function returns a short and neatly formatted “$ 1,000k”. The sprintf() function is a nice formatting helper available in CDE. It implements a formatting scheme compatible with other sprintf() implementations. Look up the docs on the C++ or Perl implementation.

Adjusting the Rollover Tooltips

The CCC chart accepts “Value format” and “Tooltip format” functions, which I’d like to use to get nicely formatted tooltip texts too.

Value Format = function f(value){return '$'+sprintf("%d", value)}
Tooltip Format = function f(series, name, value){return name + " " + series + ": "+ value}

Adjusting the Font Sizes on the Labels

Finally, I’d like to adjust the font sizes on the x and y axis a bit. I’m using the following extension points.

xAxisLabel_font = "13px Arial"
yAxisLabel_font = "13px Arial"

Protovis allows a CSS font shorthand declaration for the font property. Go creative :)

Tweaking Comparison

Now that the chart has been tweaked a bit, let’s compare the before and after:

Creating the Top Product Lines Chart

The top product lines chart is created exactly as the top customers chart. The only difference being the different placement and the different data source to go with it.

Show the SQL»

Creating the Financial Performance Chart

The financial performance chart at the top of the dashboard shows two measures per metric, the current measure, and last year’s measure as a line mark. CDE does not support this kind of chart directly, and to a first approximation the chart should display two bars per metric. Last year’s performance and current performance. In an additional customization step, the set of bars for last year is going to be converted to lines.

This is a sample result set for 2004; the target series carries last year’s measure in this case:

Category Series Measure
Profit Measure 2284552.67
Profit Target 1680658.55
Cost Measure 2703187.17
Cost Target 1996725.45
Revenue Measure 4987739.84
Revenue Target 3677384.00

The SQL to get this result set looks more complex than it should be, and in real life situations it is probably best to get this kind of data from a MDX query, but in this tutorial I’d like to stick to plain old SQL.

Show the SQL»

The two-bar version of the chart looks like this:

CCC charts are implemented using the protovis library, and if you are JS savvy enough, and know how to use the JS development tools of your browser, you may actually tap into the chart definition and change it to your liking. The following PostExection function (an event fired after the chart has finished drawing) changes the rendering of the chart to remove last year’s bars, resize the current value bars, and draw the appropriate line marks on top of them. Using the PostExecution like this may introduce a slight flicker on the screen when the dashboard is built, depending on the browser. But having a customized chart, may well be worth it.

function f(){
    
    // define some handy shortcut variables
    var bar = this.chart.barChartPanel.pvBar;
    var chart = this.chart;
    var resultset = this.query.lastResults().resultset;
    
    // drop the prev year bars (give them size 0)
    // and resize the others
    bar.data(
            function(d){
                return [0,resultset[d*2][2]];
            }
        )
        .height(27)
        .bottom(2);
    
    // implement last year markers as lines
    bar.add(pv.Line)
        .data(function(d){
            var x = chart.xScale(resultset[d*2+1][2])-chart.xScale(0)-2;
            return [{x:x,y:0},{x:x,y:31}];}
        )
        .left(function f(d){return d.x;})
        .bottom(function f(d){return d.y;})
        .lineWidth(3)
        .strokeStyle("#b08c58")
        .root.render();   
}

The appearance of the chart changed accordingly:

Filling the Data Table

The data table component is useful for displaying data in a HTML table. It is set up exactly like a chart, except that it also accepts header titles and sprintf() compatible format strings for each column. It usually also requires some additional CSS styling to fit into the general design of the dashboard. It also gets a separate SQL query to generate the data.

Show the SQL»

Done!

If you’ve been following along, you should have an operational dashboard by now, congrats!

You’re Taking Dashboards Seriously?

If you believe that BI dashboards should communicate intuitively, clearly and without unnecessary distractions, you’ll find that CDE’s customization mechanisms give you the total control you need for well designed implementations. Check out the designs data visualization pros are proposing. See for example, Stephen Few’s books on the matter:


Edit: Jos van Dongen mentions another great source:

Edit: Alex Meadows adds a classic:

Good dashboard designs strive to minimize visual distraction. Unfortunately distraction is often generated by dashboarding and charting packages by default. CDE excels at giving you minute control over each dashboard element, thus allowing you to fully optimize the information-to-noise ratio of your dashboard.

Where to go from here?

This post introduces the basics of CDE, and shows some customization techniques. But there’s more! Be sure to investigate at least the following topics to get the most out of CDE:

  • interactivity, i.e. drill downs on charts etc.
  • overriding the “draw” function of the table component to gain total control over how the cells are rendered
  • implementing custom components using your favorite JS library like Highcharts or Raphael

With CDE the sky is the limit. So if you’re embarking on a project in which flexibility is king, be sure to look into CDE!

Cheers

Slawo

A Side Note On Data Access

Accessing the SampleData Database

The tutorial dashboard uses the Steel-Wheels database that comes with Pentaho BI server. It is available inside the server by the JNDI name “SampleData”. If you’d like to connect with an external program like SquirrelSQL to prototype and verify queries, you can use the following JDBC connection:

driver org.hsqldb.jdbcDriver
url jdbc:hsqldb:hsql://localhost/sampledata
user pentaho_admin
password password

CDE Data Sources

A CDE dashboard keeps its data sources in a separate .cda file. The CDA project (Community Data Access) is a member of the CTools family. It provides an abstraction for querying different data sources as well as result caching, which dramatically improves dashboard responsiveness. CDA files can be edited and tested separately. Just make sure that you don’t edit your data sources in CDA and in CDE simultaneously.

Query Result Formats

You may be wondering how to tell the roles of the query result fields for a component and the answer is: just throw a test query with multiple result columns at the chart, and see what ends up where; adjust the amount and order of fields as necessary. Some components have settings that influence the interpretation of the data columns, like “Series in rows”, which alters the interpretation of series and “Crosstab mode” which is useful for interpreting MDX result sets. Look out for them when determining the query formats you need.

Powered by Hackadelic Sliding Notes 1.6.5

CCC and Protovis

Extension point names are constructed by taking the CCC extension point prefix exposed by a chart, and joining a protovis property name using an underscore. So if a chart exposes the “xAxisLabel” extension point, and you know that a protovis label supports the textAngle property, you know that there is an extension point “xAxisLabel_textAngle” and that it can be set to influence the text angle. Extension points can be set to any value supported by protovis, so feel free to even use function expressions where appropriate.

How to tell which extension points are supported by a given CCC chart? An official documentation for the CTools family is in the making. For those of you who would not believe it: here’s a sneak preview screenshot ;)

But the docs are still a work in progress, and until the launch of the official CTools site, you’d have to either check the dashboarding community forums, the ##pentaho IRC channel on freenode.net, or look for the extension points in the sources. A quick grep for ‘this.extend’ in pentaho-solutions/system/pentaho-cdf-dd/resources/custom/components/CCC/ gets you started.

Powered by Hackadelic Sliding Notes 1.6.5

SELECT * FROM 
(
SELECT 
	'Revenue' AS SERIES,
	PRODUCTLINE,
	SUM(ORDERFACT.TOTALPRICE) AS REVENUE

FROM ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
WHERE
	YEAR_ID = ${year}
GROUP BY
	SERIES, PRODUCTLINE
ORDER BY
	REVENUE DESC LIMIT 5
)
ORDER BY REVENUE ASC
Powered by Hackadelic Sliding Notes 1.6.5

(
    SELECT
    	'Profit' as CATEGORY,
    	'Measure' as SERIES,
    	SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}
)
UNION ALL
(
	SELECT
    	'Profit' as CATEGORY,
    	'Target' as SERIES,
    	SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}-1
)
UNION ALL
(
	SELECT
    	'Cost' as CATEGORY,
    	'Measure' as SERIES,
    	SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}
)
UNION ALL
(
	SELECT
    	'Cost' as CATEGORY,
    	'Target' as SERIES,
    	SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
	WHERE
    	YEAR_ID = ${year}-1
)
UNION ALL
(
	SELECT
    	'Revenue' as CATEGORY,
    	'Measure' as SERIES,
    	SUM(ORDERFACT.TOTALPRICE) AS MEASURE    
	FROM 
    	ORDERFACT
	WHERE
    	YEAR_ID = ${year}
)
UNION ALL
(
	SELECT
	    'Revenue' as CATEGORY,
	    'Target' as SERIES,
	    SUM(ORDERFACT.TOTALPRICE) AS MEASURE    
	FROM 
	    ORDERFACT
	WHERE
	    YEAR_ID = ${year}-1
)

Powered by Hackadelic Sliding Notes 1.6.5

SELECT
    MEASURE,
    CASE WHEN MEASURE_TARGET > 0 THEN MEASURE_TARGET ELSE 0 END,
    CASE WHEN MEASURE_TARGET > 0 THEN (MEASURE/MEASURE_TARGET-1)*100 ELSE 0 END
FROM
(
SELECT 
    SUM(ORDERFACT.TOTALPRICE) AS MEASURE    
FROM 
    ORDERFACT
WHERE
    YEAR_ID = ${year}
) measure
INNER JOIN (
    SELECT 
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE_TARGET 
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = ${year}-1
) target ON TRUE 

UNION ALL

SELECT
    MEASURE,
    CASE WHEN MEASURE_TARGET > 0 THEN MEASURE_TARGET ELSE 0 END,
    CASE WHEN MEASURE_TARGET > 0 THEN (MEASURE/MEASURE_TARGET-1)*100 ELSE 0 END
FROM
(
SELECT 
    SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
FROM 
    ORDERFACT
INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
WHERE
    YEAR_ID = ${year}
) measure
INNER JOIN (
    SELECT 
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE_TARGET 
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = ${year}-1
) target ON TRUE 

UNION ALL

SELECT
    MEASURE,
    CASE WHEN MEASURE_TARGET > 0 THEN MEASURE_TARGET ELSE 0 END,
    CASE WHEN MEASURE_TARGET > 0 THEN (MEASURE/MEASURE_TARGET-1)*100 ELSE 0 END
FROM
(
SELECT 
    SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE    
FROM 
    ORDERFACT
INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
WHERE
    YEAR_ID = ${year}
) measure
INNER JOIN (
    SELECT 
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE_TARGET 
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = ${year}-1
) target ON TRUE 

Powered by Hackadelic Sliding Notes 1.6.5

79 comments to Creating Dashboards with CDE

  • [...] idea to build this dashboard comes from joining Stephen Few’s spec [pdf], an excelent implementation from Slawomir Chodnicki and finding out that the C*Tools (Webdetails dashboard project) has [...]

  • hi, i’m trying to create dashboard with month selector which parameter will be used to change another parameter value and used in data source
    to be simple, i will make it like this
    select month -> month parameter value changed -> date parameter value changed, depends on month parameter -> send date parameter value to datasource used
    i put a function to change date parameter value in post change property of month selector. but what exactly happen is
    select month -> month parameter changed -> send date parameter that has been changed by post function to datasource
    select month -> month parameter changed -> date parameter changed

    is there any way to make sent date parameter is the value that has been changed after month selected by user?

  • Carey

    Nice article – thanks. I have been revisiting the webdetails CDE and after much research have two challenges:
    1) I am trying to turnoff or change the busy gears
    2) Refresh a chart without doing a firechange which updates the entire dashboard and seems to cause a redraw. Some components like table has a refresh attribute that is what i am trying to achive. I dont see a refresh attribute in the CCC charts. I am looking at the ajax code but dont see an example of its use in populating a ccc chart.

    This has come about as I am using trying to feed a continuous Twitter feed to a line chart.

  • Carey

    Figured out how to solve the above referenced challenge – the call is to Dashboards.update with the object name render_”name assigned to the component from cde”.
    Just redraws the component and not the page. Great. Now i want to just redraw the line in the line chart and not the entire component. Any tips? My goal is a moving line graph without a redraw of the entire component.

  • Slawomir Chodnicki

    Carey,

    that’s in-depth stuff :)
    I would suggest you get yourself on ##pentaho IRC on freenode and ask for a CDE dashboard guru. The CTools forum is a good place to post in-depth questions too. It is moderated by the creators of CDE :)

    http://forums.pentaho.com/forumdisplay.php?80-Community-Tools-CTools

    Cheers
    Slawo

  • Sergio

    Hi Slawo,
    Could you see this post http://forums.pentaho.com/showthread.php?90990-Cde-create-for-me-bad-file-cda&highlight= ?
    I’d like to know how to create a dashboard that uses a mondrian cube and not simple SQL queries.
    Your istance is really well done but I’m looking for how to do by OLAP queries.
    Is it also possible in MDX queries use a paramater as for SQL queries?

    Thank you really much for your time.

    P.S.

    I posted 4 threads in pentaho forum about different problems and anyone answers to me. I just found alone a few of workarounds for that problems but unfortunately I didn’t solve this prooblem.

    Is there any guide or instance by using OLAP CUBE with CDE dashboard?

  • Omayma

    Hi!
    Thank you for the tutorial it has really helped me test CDE.
    I would have a concern and was wondering if you had the answer. When I create a query, the cda I get is empty. I have tried reinstalling the C** tools, refreshing the cda cache, clearing the log…it is still empty.

    Thank you

  • jamal

    Please whene i use a paremeter as date type, it not work (for example to build chart depending on periode where colum_xxx is between twoo date passed as paremeters)

    But whene I us a string parameter or integer it work fine.

    Can some one help please.

  • Andre Curvello

    Thanks for the tutorial!
    Please, post more tutorials, showing the use of the other CDF componentes (table, autocomplete, dial, etc)!

  • Hi Slawo, an in-depth but very clear walk through on the CDE. Thank you.
    What tool did you use to create the dashboard mock up? That’s pretty.

  • Slawomir Chodnicki

    Hey Will, I think I used http://www.swordsoft.idv.tw/ for the mock up.

    Cheers
    Slawo

  • Ali NZM

    Hi,
    Is there any manual for designing interactive dashboards using cde available? for example drillable dashboards?
    Regards.

  • Rob

    I am using your tutorial to learn to build dashboards. I am on 3.8 and use the latest Ctools from the install script. All of the examples that come with BI Server and CTools install work.

    I am having an issue with partially rendering your example.

    1) a minor issue with font size causing a weird justification issue with sales performance table (resolved with a font-size reduction in the css).

    2) Top Customer and Top Product Lines charts do not render. I have firebug console messages and pentaho log records that might indicate to a more experienced pentaho user what is wrong.

    I used CDE editor and the video above to see if I can figure out what is causing the charts to not render but I am stumped.

    I could send you the pentaho logs and screen capture from firebug if you care to look at it.

    Rob

  • Rob

    Sorry I think I found it. There may be a problem with the example’s SQL when used with MySQL. Let me test it.

  • Rob

    Yep the problem is with the SQL in the Top Customer and Top Product Lines query using MySQL. Using a derived table you have to make an alias.

    ELECT * FROM
    (
    SELECT
    ‘Revenue’ AS SERIES,
    CUSTOMERNAME,
    SUM(ORDERFACT.TOTALPRICE) AS REVENUE
    FROM
    ORDERFACT
    INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERNUMBER = ORDERFACT.CUSTOMERNUMBER
    WHERE
    YEAR_ID = ${year}
    GROUP BY
    SERIES, CUSTOMERNAME
    ORDER BY
    REVENUE DESC
    LIMIT 5
    )
    AS XXX
    ORDER BY REVENUE ASC

  • Nice Blog. I fallow it, every thing is fine. But, i want to do some change, how can i send some Parameter from Sales Performance Chart to other two charts?
    Means, instead of referring the selection parameter, i want to get some parameter from first chart to send chart, how can i achieve it?

    Thankx & Reagrds

  • Arturo Juárez

    Hi, I have a cuestion, if I want do click in a char, and this click make changes in other char, how is the process??

    thanks

  • gustavoacm

    please, How can I implement custom components using Highcharts please step by step.

    thanks in advance

  • Nico Nagel

    Thanks for this nice blog. But I still have 2 problems : 1) The function xAxisLabel_text = function(d){return ‘$ ‘+ sprintf(‘%d’,d/1000)+’k'} does not have any effect.
    and 2) the function for sales_chart in the PostExecution field : prevyear function throws this error “Error processing component (sales chart)”.
    I don’t know where to look for some log: in pentaho.log there was nothing about this incident.

    The sales_chart with the two bar for actual and previous year is rendered correctly.

  • Russell

    Hi Slawomir – It may be me, but I don’t believe that this tutorial works correctly with the updated version of CDE. If you tell me that it does, I will presume that I made some coding errors.

    Thanks and keep up the great work
    - Russell

  • Hi… It’s a nice artical for begineers like me.

    I’ve the follwoing issues in practicing the post you have given.
    They are
    1) For every grap I’m getting border.. How to remove border for every grap ? What is the property to remove the graph ?
    2) I’m not getting the categories in first chart(Revinew,profit & cost). Instead of these I’m getting “Measure” in middle ? How to get the categories correctly ? Where I need to check out ?
    3) The tabular data displaying correctly but not dynamic ? How to make this tabular data as dynamic ?
    Last but not the least.. Where can I see the genereal output of the queries ?? I guess in CDA ?? How to see in CDA ?

    Could you please help me in it… ?

    Thanks,
    Sadakar

  • [...] artical you can find working with databases) Source of the Example taken from the following blog http://type-exit.org/adventures-with-open-source-bi/2011/06/creating-dashboards-with-cde/comment-pag… * This example was based upon CCC-Version1. The current Example which I’m going to explain is [...]

  • Alex

    wonderful tutorial!

    While I have two questions:
    1. Does CDE support other language in MDX query? (e.g. I found Chinese characters are mistaken) ? if not, how can I solve the problem?

    2. how can I setup [drill down] function?

    Thank you!

  • João Ciocca

    Hey Slawo! =) Awesome example, helped me a lot too!

    Just wanted to thank and up Rob’s suggestion. The SQL code without the “as ” won’t work not only on MySQL, but also on SQL Server (at least here with 2000… we’re still struggling with the IT dept to upgrade, lol).

  • [...] – L’excellent tutoriel de Slawomir Chodnicki: « Creating Dashboards with CDE » [...]

  • [...] of the Example taken from the following blog http://type-exit.org/adventures-with-open-source-bi/2011/06/creating-dashboards-with-cde/comment-pag… * This example was based upon CCC-Version1. The current Example which I’m going to explain     [...]

  • Anureddy

    please help me to create dynamically height for table component..
    if i load one record i am getting more space in table.how i can reduce the height. based on records how i can show the height.
    if i set height in css i have a subtable in row click. so subtable getting out of the table hegiht.please help me.

  • [...] – L’excellent tutoriel de Slawomir Chodnicki: “Creating Dashboards with CDE” [...]

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>