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 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):

[Pentaho/biserver-ce/pentaho-solutions/]
bi-developer/reporting/report-charts/bubble-charting.xaction

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">  
  <name>bubble-charting.xaction</name>
  <title>%title</title>
  <version>1</version>
  <documentation>
    <author/>  
    <description>%description</description>  
    <icon>PentahoReporting.png</icon>  
    <help>This action-sequence was generated by the Report Design Wizard. To edit this action-sequence use the Pentaho Design Studio.</help>  
    <result-type>report</result-type>
  </documentation>

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

  <inputs>
    <output-type type="string">
      <default-value>html</default-value>  
      <sources>
        <request>type</request>
      </sources>
    </output-type>
  </inputs>

  <outputs>
    <report type="content">
      <destinations>
        <response>content</response>
      </destinations>
    </report>
  </outputs>

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

  <resources>
    <!-- use this section to identify any files that the component needs to execute the report -->  
    <report-definition>
      <solution-file>
        <location>bubble-charting.xml</location>  
        <mime-type>text/xml</mime-type>
      </solution-file>
    </report-definition>
  </resources>

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%’.

  <actions>
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>rule</action-type>
      <action-inputs/>
      <action-outputs>
        <rule-result type="result-set"/>
      </action-outputs>
      <component-definition>
        <jndi>SampleData</jndi>  
        <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>
      </component-definition>
    </action-definition>

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.

    <action-definition>
      <component-name>JFreeReportComponent</component-name>
      <action-type>report</action-type>
      <action-inputs>
        <output-type type="string"/>  
        <data type="result-set" mapping="rule-result"/>
      </action-inputs>
      <action-resources>
        <report-definition type="resource"/>
      </action-resources>
      <action-outputs>
        <report type="content"/>
      </action-outputs>
      <component-definition/>
    </action-definition>

  </actions>
</action-sequence>

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
  ./report-designer.sh
• 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.

Report

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
   

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
    from QUADRANT_ACTUALS
    WHERE NOT department like 'Prof%'
    GROUP BY department

5. Click on the Preview button. You should see a 6×4 grid with the 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.
   

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:
   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
   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)
   

Bands

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.
   
   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 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.
   
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),
   legend-font=SansSerif-PLAIN-6
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:

  SELECT DISTINCT DEPARTMENT
  FROM QUADRANT_ACTUALS
  ORDER BY DEPARTMENT;

• 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:
   output-format=table/html;page-mode=stream
   lock-output-format=true
3. To avoid the auto-submit:
   Scroll to Parameter and set:
   auto-submit=false
   autosubmit-default=true
   parameter-layout=flow

This is how it looks:

Bubble Chart Report in the BI Server

Advertisement