Bullet Graph Dashboard: PDI-MDX-SQL DataSource + BI Server + CDE

To Do

The 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 published a Bullet Graph widget from the Protovis Library that is easier to use and customize.

Dashboard PDI Final

Final Dashboard


We’ll need:

  1. CDE Editor. Install or update to the latest version [Instructions]
    Used to create and edit the parameter, datasource and mappings to the graph.
  2. Pentaho PDI/Kettle ETL tool [Download].
    Used for data retrieval by selected parameters, normalization and PKI process.
  3. The Pentaho BI Sever
    To excecute the dashboard via its menu and see the html result.

Building The Layout

Lets start a new CDE dashboard by a click on the CDE icon in the top toolbar: CDE icon

Header Rows

The CDE Editor will open with ‘Layout’ selected. This is the asociated toolbar:
cde layout toolbar

  • Click on the row icon (=)
    Select Row

    • Name=header
    • BackgroundColor=99a5de
      (in the bottom text field, close clicking on the color ball)
    • In the ‘Corners’ attribute. Use the down-arrow to scroll to Round
  • Click on the row icon (=) three more times to have a total of 4 rows.
    Be carefull, the toolbar is dynamic and the icons will change.
  • On the first and second rows add one ‘Html’ element (<>)
  • On the third row, set Name=ParamRow
  • Type in the first html type a title:
    <h2>Sales Dashboard By Product Line</h2>
  • On the second Html type:
    <div class="topRow1">Sales YTD</div>
    <div class="topRow2">Actual amount (horizontal bar) and Previuos Year (blue mark)</div>
    <hr class="topRowHR"/>

It should look like this.

Dash 00

Save in your development folder with a meaningfull ttitle (used by the BI menu system)  and click Preview to see the headers. Close the dialog.

Parameter Row

  • The third row will need four columns.
  • The first and the third column will have also an Html element.
  • Put in the first one the text Year, and in next Month.
  • Select the second column and look for the name attribute set it to nYear (node for year select).
    Name the forth one nMonth.

Result Row

  • Add two columns
  • Name the first one nResultGraph
    and the other nResultTable
  • Set for each column span size=12 (there’s a total of 24 columns in the template)

Save the final layout, that should look like this.

Dash n

Important: If the message “Dashboard saved successfully” doesn’t appear, check your mofifications for errors. If you can’t fix it, exit and resume from the latest point saved.
To edit a dashboard, select it on the browse panel in the BI Server. Right click on it and then select Edit in the context menu.

Building Datasources and its Widgets

Three types of datasources:

  1. Two SQL queries are needed for Year and Month.
  2. The table will be  a simple MDX.
  3. The Graph will need additional columns for ranges, we’ll use an ETL transformation for this one.


Click on Data Sources ‘button’ in your top-right. The left side of the CDE will list all the connections the CDE can use to get data.

  • Click on SQL Queries, then on sql over sqlJndi
    The group SQL Queries will be added
  • Set:

Click on Components ‘button’ in your top-right.

  • We need a parameter to hold the Year selection
    • Click on the Generic button on the left list
    • Click now on Simple parameter
    • Set Name=pYear
      Set de default to Property value=2005
    • Save
  • Drop Down
    • Click on Selects button on the left list
    • Click now on Select Component
    • Set Name=csYear
      Parameter=pYear (the one defined above)
      HtmlObject=nYear (attach point, that is the column we named that way in the layout)
    • Save
  • Click Preview. Check the select box. Close the preview dialog.

For the Month SQL, the set up is a little diferent as we need to set up a listener for changes on the Year select box and then pass that parameter to the Component and then the SQL datasource.

  • Click on Data Sources
    • Click SQL Queries, then sql over sqlJndi.
    • Set:
      Click on Parameters, click on the Add Button.
      in the dialog: Name=pYear, value=2005, Type=String

  • Click on Components
    • Click on the Generic button
    • Click on Simple parameter
    • Set Name=pMonth
      Set de default to Property value=May
    • Click on Selects button on the left list
    • Click now on Select Component
    • Set Name=csMonth
      Click on Listeners, on the select arrow choose pYear
      Click on Parameters, click on the Add button,
      in the dialog set Arg=pYear, Value=pYear (you can select with the “…” button)
    • Save

If you click Preview you should see something like this.

Preview 01


Let’s define the query against the ‘Steel Wheels Sales’ cube as our Datasource. There are some links at the bottom of the article about MDX and sumary functions.

  • Click on
    • Data Sources
    • MDX Queries
    • mdx over mondrianJndi.
  • Set:
    • name=dsTable
    • Mondrian squema=SteelWheels
      (jndi will be set to SampleData)
    • Query=
      member [Measures].[Sales_Curr] as 'Sum(Ytd([Time].CurrentMember), [Measures].[Sales])'
      member [Measures].[Sales_Prev] as 'Sum(Ytd(ParallelPeriod([Time].CurrentMember,1)), [Measures].[Sales])'
      {[Measures].[Sales_Prev], [Measures].[Sales_Curr]} ON COLUMNS,
      {[Product].Children} ON ROWS
      from [SteelWheelsSales]
      where [Time].[2005].[QTR2].[May]

And now the grid element to display our data.

  • Click on:
    • Components
    • Others
    • Table Component
  • Set:
    • Name=ctTable
    • HtmlObject=nResultTable
    • Datasource=dsTable
  • Save & Preview
  • Now click on Advanced Properties (below the Component button)
  • Click on:
    • Column Headers
      add button for: Sales, Previous, Actual
    • Column Formats
      add button for: %s, $%.2f , $%.2f
      Those are for: string (%s) and currency with two decimals ($%.2f)
  • Set:
    • Paginate=False
    • Show Filter=False
    • Sort Data=False
  • Save & Preview

For testing and format purposes the used query doesn’t change when the month or year are modified. Now we need to add listeners, the parameters in the component and the datasource to calculate the ‘quarter’ depending on the month.

Click on Components (you may be already there)

  • Click on:
    • (left) Generic button
    • Simple parameter
  • Set:
    • Name=pQtr
    • Property value=QTR2

Click on Data Sources

  • Click on:
    • (group triangle) MDX Queries
      select dsTable
    • Parameters
  • Add three lines
    • Name=pYear, Value=2005, Type=String
    • Name=pQtr, Value=QTR2, Type=String
    • Name=pMonth, Value=May, Type=String
  • Click on Query
    change last line from:

    where [Time].[2005].[QTR2].[May]


    where [Time].[${pYear}].[${pQtr}].[${pMonth}]
  • Save & Preview.
    You should see the same image than before. Close the dialog.

Click on Components

  • Click on:
    • (group triangle) Others
      select ctTable
    • Listeners
      Select in the drop down: pYear, pMonth. Click OK
    • Parameters
  • Add three lines:
    • Arg=pYear, Value=pYear
      (you can use the three dots (…) button to select  value)
    • Arg=pQtr, Value=pQr
    • Arg=pMonth, Value=pMonth
  • Click on:
    • Advanced Properties (at the top link on your right)
    • preExecution
  • Paste this javascript function to set Quarter based on month selected:
    • function Q (){
      var vQtr='';
      switch (pMonth) {
      case 'Jan':
      case 'Feb':
      case 'Mar':
      vQtr='QTR1'; break;
      case 'Apr':
      case 'May':
      case 'Jun':
      vQtr='QTR2'; break;
      case 'Jul':
      case 'Ago':
      case 'Sep':
      vQtr='QTR3'; break;
      case 'Oct':
      case 'Nov':
      case 'Dec':
      vQtr='QTR4'; break;
  • Save & Preview.
    Try changing the month to ‘Jan’. You should see something like this.Table Preview

Close the preview dialog.


In he last part we’ll use an ETL process and the Bullet Graph widget.

Start the PDI. Create a new transformation and save it in the demo/development folder with the name SalesDashboard.ktr.

This is the PDI transformation

Dashboard PDI

Dashboard PDI Transformation

  1. Prepare the default parameters for testing.
    On the menu click Edit ->Parameters
    Select Parameters Tab, Define pYear, pQtr, pMonth.
    Dash PDI 00
  2. Set the MDX
    Add Input->Mondrian Input
    Dashboard PDI 01
    Check the ‘Replace variable’  checkbox so the parameters are used.
    The query is:
member [Measures].[Sales_Curr] as 'Sum(Ytd([Time].CurrentMember), [Measures].[Sales])/1000'
member [Measures].[Sales_Prev] as 'Sum(Ytd(ParallelPeriod([Time].CurrentMember,1)), [Measures].[Sales])/1000'
{[Measures].[Sales_Prev], [Measures].[Sales_Curr]} ON COLUMNS,
{[Product].Children} ON ROWS
from [SteelWheelsSales]
where [Time].[${pYear}].[${pQtr}].[${pMonth}]
  • Rename columns
    Here we copy columns to new ones but set the new names. This is necesary if you want to remove the dynamic nature of column names in MDX results, for example when you use time in columns then you may get [Time].[2005].[QTR2].[May] or [Time].[2005].[QTR1].[Jan] then you better use ‘CurrentMonth’ to use in PDI steps.
    Add Scrpting->Modified Java Script
    Dashboard PDI 02
    The script is:

    var rowOut = getOutputRowMeta();
    newRow = createRowCopy(rowOut.size());
    var ProductLine = rowMeta.getString(newRow, 0);
    var Actual   = rowMeta.getNumber(newRow, 1);
    var Previous = rowMeta.getNumber(newRow, 2);
  • Get the maximum value for each column.
    Here max value for Actual is renamed to Max1 and Previous o Max2.
    Add Statistics->Group by
    Dashboard PDI 03
  • Calculate max values and two ranges.
    Add Scrpting->Modified Java Script
    This is an oversimplified ETL, with ranges set on maximum values for all rows. In any case you could use KPIs from tables and a lookup step.
    – maximum value from both columns
    – round it up
    – I set two marks one at 70% other at 90%, for grade of color in the graph bar.
    Dashboard PDI 04
    – The sample script use is:

    var Maximum=0.0;
    var Mark1=0.0;
    var Mark2=0.0;
    if ( (Max1!=null) || (Max2!=null) )
    if ( Max1 > Max2 )
    Maximum = Max1;
    Maximum = Max2;
    if (Maximum!=0.0) {
    if (Maximum>=1.0) {
    Maximum = Maximum / 10.0;
    Maximum = ceil(Maximum) * 10.0;
    } else {
    Maximum = Maximum / 1.0;
    Maximum = ceil(Maximum) * 1.0;
  • Process measures
    Each row may need processing, if KPI is set for each measure then it should be calculated on this thread. Here we just add a subtitle string (k=thousand).
    Add Transform->Add constants
    Dashboard PDI 05
  • Remove excesive text from product line description
    The first colum has text like “[Product].[Ships]”, we can remove the fixed part with a regular expression to replace the unwanted and fixed text
    Add Transform->.Replace in String Step.
    Type in Search:  ^\[Product\]\.\[(.*)\]$
    Notice that no output field is specified so ProductLine will be used, that the dot and squared brackets are ‘escaped’ with backslash and that the replace string is (.*)=$1
    Dash PDI Regex
    For using this step: Reference1, Reference2
  • Join the data and its ranges.
    Add Joins->Join Rows (cartesian product)
    Dashboard PDI 06
    The important thing is to state the step from where is the data coming from, if you don’t do this you end up missing data or even complete rows: Add constants.
  • Sort as you like.
    Add Transform->Sort rows
    Dashboard PDI 07
  • Output
    Make available only the relevant columns:
    – Measure Title
    – Measure Subtitle
    – Value of Bar
    – Value of Mark
    – Min Range (darker shade in bar)
    – Max Range (lighter shade in bar)
    – Max value
    Add Transform->Select values
    Dashboard PDI 08

Selecting this last step and previewing the output you get.

Dashboard PDI Result

You can close the PDI.

Bullet Graph

Back to the CDE Editor.

Click on Data Sources

  • Click on:
    • MDXKettle Queries button
    • ketle over kettleTransFormFile
    • Select the ketle over kettleTransFormFile Component
  • Set
    • Name=dsBulletGraph
    • Kettle Transformation File=SalesDashboard.ktr
    • Click on Variables, a dialog will open
      add three lines for:
      – Arg=pYear
      – Arg=pQtr
      – Arg=pMonth
    • Click on Parameters, in the dialog add three lines:
      – Name=pYear, Value=2005, Type=String
      – Name=pQtr, Value=QTR2, Type=String
      – Name=pMonth, Value=May, Type=String
    • Click on Output Options, in the dialog add six lines:
      – Index=0, Index=1, Index=2, Index=3, Index=4
      Index=5, Index=6
    • Click on Columns, in the dialog add six lines:
      – Index=0, Name=Product Line
      – Index=1, Name=Subtitle
      – Index=2, Name=Actual
      – Index=3, Name=Previous
      – Index=4, Name=Mark1
      – Index=5, Name=Mark2
      – Index=6, Name=Maximum
    • Click on Query=OutputBullet
      (last activity)
    • Save CDE

Click on Components

  • Click on:
    • Charts
    • CCC Bullet Chart
      a group will be added
    • Select the new CCC Bullet Chart Component.
  • Set
    • Name=cgBulletGraph
    • Width=420
    • Height=220
    • Datasource=dsBulletGraph
    • Clear Title and Subtitle
    • Click Bullet Ranges
      click on the minus button (-) to delete each line
      Close the dialog
    • Click on Parameters, in the dialog add three lines:
      – Arg=pYear, value=pYear
      – Arg=pQtr, value=pQtr
      – Arg=pMonth, value=pMonth
    • Set HtmlObject=nResultGraph
    • Click on Listeners
      Select pYear, pMonth

Save and preview. We are almost finished

Dashboard PDI Almost

Fine Formatting

There are three formating options in the CDE

  1. Format options in CDE, either in the component advanced properties or html or column layout
  2. CSS class from the web page
  3. Some properties of the components

To work with the CSS or DOM elements we add a resource file:

  • Create a text file and type:
    That is mainly header fonts and colors, parameter style and table formatting.

    #header { margin-top:1em; }
    h2 { font-size:1.4em; padding-top:7px; padding-left:1em; height:1.6em; }
    .topRow1 { font-size:1.2em; font-weight:700; margin-left:1em; }
    .topRow2 { font-size:1em; margin-left:1em; }
    hr.topRowHR { height:2px; width:900px; color:red; }
    #ParamRow { font-size:0.9em; vertical-align:bottom; margin-bottom:2em; }
    #ParamRow select { font-size:0.9em; vertical-align:top; }
    #ParamRow input { font-size:0.9em; vertical-align:top; }
    #nResultTable { margin-top:2em; }
    table tr.even, table tr.odd{ background-color: white; }
    table thead th { border-bottom: 1px solid #CCC; background-color: #fff; }
    table tbody td.number, th.number{ text-align: right; }
    table tbody td.string, th.string{ text-align: left; }
    table tfooter td { background-color: white; }
    .ui-widget-header { background: white; color: white; border-bottom:0px none; border-top:1px grey solid; border-left:0px none; border-right:0px none;}

    Save it in the demo/development folder as Dashboard.css

  • On the CDE, click on Layout option
  • Add a css resource file, select from the options:
  • Set:
    Resource file=Dasboard.css

This will be the result

Dash Final 1

We can modify the Graph if we access its properties, in this case the ‘Extension Points’. Lets change the Marker shape and color.

Click on Components

  • Click on the triangle of the Group Charts
  • CCC Bullet Chart
  • click on Advanced Properties.
  • Click on Extension points a dialog will open.
    Add Three lines. Fill with:

    • Arg:bulletMarker_shape  Value:bar
    • Arg:bulletMarker_lineWidth  Value:3
    • Arg:bulletMarker_strokeStyle  Value:#2e559c

Check posible values for Extension Points at CCC (Bullet Chart) and Protovis sites.
In the CCC (Bullet Chart) page check the keyword to access: base, mark, measure, title, rule, range. In the rest of the document and examples look for the second part: font, shape. Join them with an undescore: bulletMarker_lineWidth.
Or if you want to change the color of the inner bar, you could try:
Arg: bulletMeasure_fillStyle, Color: green or #0F0

You can use other properties to change the appearence, for example the tooltip to show the value of a value can ve modified.

Click in the Advanced Properties of the Graph, in Value format:

function f(value){return ('$'+sprintf("%.1f", value)+'m')}

The final product should be the initial dashboard image.


Additional Links

  • Bullet Chart on webdetails: here.
  • Class, Field, Method and Extension Points on webdetails: here.
  • MDX function YTD youtube video by phiintegration.
  • MDX Time Series Functions Part I. Series by William Pearson.
  • Another fine example about building CDE dasboards OSBI.
    Its in French, but the code and diagrams are easy to understand.

Bubble Report: BI Server + SQL Query + Report Designer

To Do

The Pentaho BI demo has an example of what will be built. In the ‘Browse’ panel click on bi-developer and in ‘Files’ panel  open the Bubble chart report.

Bubble Report

Bubble Report in the BI demo

Report Specification

[If you’re just interested in learning to build the report, you can skip this section]

The report is defined by an .xaction (you can right-click on the file and check properties):


To find out our data source, data, graph size, etc. we’ll browse the .xaction file. Then we’ll use the Pentaho Report Designer (PRD) to build a more interactive version that will prompt for parameters. And is easier to build.

We  have at the top, the documentation ‘section’:

<?xml version="1.0" encoding="UTF-8"?>
<action-sequence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
    <help>This action-sequence was generated by the Report Design Wizard. To edit this action-sequence use the Pentaho Design Studio.</help>  

Define an input string variable output-type with value ‘html’ and output report of type content

    <output-type type="string">

    <report type="content">

In the resources section there is a reference to a file of type ‘solution’:  bubble-charting.xml that will define the chart.

    <!-- use this section to identify any files that the component needs to execute the report -->  

Then a sequence of actions: the SQL-lookup gets the data from a SQL query using a jndi connection to SampleData that puts the result in the object result-set with four columns: department, actual, budget and variance (its really just the difference of actual-budget), all are sums of columns from the table cuadrant-actuals that do not have departments that  start with ‘Prof%’.

        <rule-result type="result-set"/>
        <query><![CDATA[select DEPARTMENT, sum(actual) as ACTUAL, sum(budget) as BUDGET, sum(variance) as VARIANCE from QUADRANT_ACTUALS WHERE NOT department like 'Prof%'  GROUP BY department]]></query>

Then the output is used in the chart component. The output-type was defined as ‘html’  and the data is in the object ‘result-set’. The chart  was also specified in the .xml file.

        <output-type type="string"/>  
        <data type="result-set" mapping="rule-result"/>
        <report-definition type="resource"/>
        <report type="content"/>


If you read the .xml graph file you’ll see specification for several elements: headers, footers, functions, series definitions, fonts, axes ranges, groupings, chart sizes, colors and data grid. If you want to use the fancy graphic capabilities of the library JFreeChart library check: Chart usage and  Bubble chart specs.

Using the Pentaho Report Designer

Start the PRD and a new report:

  • Open a terminal or command window.
    In Ubuntu click start on the left bar, type TER. Click on the icon ‘Terminal’
  • Change to PRD folder.
    cd /Pentaho/report-designer
  • Start the aplication
  • Click on File -> New

Lets take an incremental way to build the report that let us test and see how the report grows and how it becomes beautiful.


Just an SQL is needed for the report so well select a JDBC connection but you here can choose from MDX o PDI (ETL transformatoion) or several other sources.

  1. In the upper right, select the data tab.
  2. Right-click on Data Sets and click on JDBC
    Data Tab

We’ll just need the recods for our report but you can define also parameter queries and subreport sources.

  1. Choose the SampeData connection on the left panel (see the image below)
  2. Click on the plus sign on the upper right of ‘Available queries’
  3. On Query Name type someting like ‘sqlBudgets’ (use prefixes and mnemonics)
  4. Copy and paste the original query:
    select DEPARTMENT, sum(actual) as ACTUAL, sum(budget) as BUDGET, sum(variance) as VARIANCE
     WHERE NOT department like 'Prof%'
     GROUP BY department
  5. Click on the Preview button. You should see a 6×4 grid with the data.
    SQL Data
  6. When you close the dialogs (Close and OK) the columns will appear in the right panel.
  7. Drag and Drop the DEPARTMENT, ACTUAL, BUDGET VARIANCE columns into the Details Band in the middle of the page. Don’t Worry about Aligment now.
    Drag Columns

It’s a good time to save the report, it will let you go back to a known point in case you don’t like a change or save progress. The PRD is stable but you can never be too cautious.

  1. In the top menu: File -> Report Properties.
  2. In the dialog click on Description.
  3. Fill the data required: (Author, description)
    The Title is important as is the string that will be used in the BI server.
  4. Now save it into the bi-server/Pentaho Solutions/[Your folder].
    In the top menu File->Save As.

Lets see the data in the actual output

  1. Click the Run button (green arrow) in the toolbar: Run Button
    Select output: html
  2. After checking the data, close the browser window and return to the PRD.

Set Style for the output columns

  1. Select all columns: click on each one and press at the same time the shift key.
  2. Check that the Structure tab is selected in the upper panel.
  3. In the bottom panel in the Style tab, scroll down to size & position section, set:
    y=0,  Height=12, font-size=8, v-align=MIDDLE, width=25% (you can use pixels or percentages)

Set Format for the currency columns

  1. Deselect all items (click on other area) so you can click again only on ACTUAL, BUDGET VARIANCE.
  2. In the bottom panel click on Attributes tab.
  3. In the common section make the format equal to $#,###.00.
  4. Now align the columns so there is no overlapping selecting.
    Click on each one and move them with the arrow keys on your keyboard.

Add text labels for titles, column headers, etc.

  1. Use the left toolbar icon for labels Text Area
    Drag it into the top left area in the Header.
  2. In the Attributes tab (or in the same text box) in value (as it appears on the original report), type:
    Variance By Department
  3. Set: font-size=10, x=0, y=0, height=12.
  4. Put another label to the right with this text:
    $(report.date, date, MMMM dd, yyyy @ hh:mm )
  5. Set: font-size=8, x=50%, y=0, width=50%, height=12, align=RIGHT
  6. Add 4 more labels with text in the report header: DEPARTMENT, ACTUAL, BUDGET VARIANCE.
  7. Set font-size=10, width=25%, height=18, bg-color=#9eaa36, text-color=white, v-align=MIDDLE. Then align=LEFT for Department, align=RIGHT for the others.
  8. Alignment:
    Tip 1: To align object vertically, select them, right click on one and choose Alignment->top. Then use the arrows to put them when you want them.
    Tip 2: To align them horizontally, select a column on the detail then shift-click on the corresponding label on the header. Click on x and press enter, then on width, the values will be the same.
  9. Drag a horizontal line from the left toolbar into the header.
    Set text-color=#9eaa36, height=3, width=100%, x=0, y=24
  10. For the grid effect, select the four data columns (Detail band) and set their top, bottom, right and left borders to size=0.5, style=solid, color=#bbb

Preview the report

  1. Use the Preview button:
    (click on the pencil or edit button in the same poition to return to this view)
    Data Report


Now that you are familiar with objects and its attributes. An additional concept you need to know is the band-structure of the report.

Select the Structure tab in the upper panel.


Example 1: The ‘No Data’ Band.

  1. In the tree structure click on No Data.
  2. Click Attributes Tab in the bottom right panel
  3. Set hide-on-canvas=false.
    Visible Band
    now you can set message or elements on the report band in case there is no data.

Example 2: Alternating colors on Details.

  1. Click on the Details Band in the tree structure.
  2. Click in the top menu: Data -> Row Banding
  3. Select ‘White’ for visible color
  4. Any other for invisible.
  5. Click OK to close the dialog.

Lets check the function that was added and change color

  • In the upper panel click on Data tab.
  • Select Row Banding on Functions.
  • In the bottom panel setinactive color=#e7e7e7

Preview your report and save.

The Chart

The JFreeChart library is used in the PRD, but we can use the properties to set options, thats is settings. We just need to add the chart object.

  1. Drag from the left toolbar the Chart Icon Chart Icon into the Header band.
  2. Give it the size you like
  3. Move the text with the arrow keys after selecting them on the structure right panel and clicking on one of them (That is more easy to me).
  4. Right click on the chart, select Chart.
    The following dialog will appear.
    Chart Definition
  5. In the top toolbar select bubble chart.
  6. In the left panel you can set fonts, lablels, backgrounds (bg-color=#9eaa36), series colors, and legend options.
    Set: max-bubble-size=125, plot-fg-alpha=0.5 (transparency),
  7. On the right panel you set the datasource.
    First click on x-value-columns, then on the three dots. In the drop down select click on the data column ACTUAL.
    Now set y=BUDGET, z=VARIANCE, series-bby-field=DEPARTMENT.
  8. Save it an click preview or run.
    Notice how the axes are dynamic as you didn’t set a maximum value.

You have compleated your report. If you reload the solutions on the BI server you will be able to run it from the menu.

Add a Parameter

To add a parameter for the user to filter data we need do three things:

1. Add a query that list the available departments

  • In the right-top panel, check that Data tab is selected.
    Double click on JDBC: SampleData to open de Data Source Dialog with the ‘SampleData’ selected.
  • Click on the plus sign in the Available Queries panel and name it sqlDept.
    Copy and paste this SQL:

  • Close the dialog.

2. Add the parameter for the user selection

  • In the right-top panel, check that Data tab is selected.
    Right-click on Parameters, select Add Parameters
  • Set: name=pDept, label=Department to exclude:, type=string, Mandatory.
  • For using the query: Display=Drop Down, Query=sqlDept
  • Close the dialog.

3. Modify the query to use the parameter

  • Double click on ‘JDBC: SampleData’ in sqlData.
  • Change: WHERE NOT department like ‘Prof%’
    to: WHERE NOT department like ${pDept}
  • Close the dialog

Finishing Touches

Lets add the department in the report so you can see what was the query if printed. Just drag de pDept parameter to the Report Header. Add a Label ‘Parameter Excluded:’

If you test the report in the BI server you’ll see that a parameter for output was added as well as an auto-submit checkbox. Lets asume we just want one html page:

  1. Click in the upper panel: Structure Tab.
    Click on Master Report.
    Click on Attributes tab in the bottom panel.
  2. To avoid the option select drop down:
    Scroll to common section and set:
  3. To avoid the auto-submit:
    Scroll to Parameter and set:

This is how it looks:

Bubble Chart Report in the BI Server

Bubble Chart Report in the BI Server