SpagoBI Lesson 5: Creating a dashboard with BIRT and SpagoBI
Creating a dashboard with BIRT and SpagoBI
In this chapter, we will be creating a dashboard using the BIRT reporting engine and then we will host it in the SpagoBI server.
Assume that the bank Shemma Global Limited gave out loans to 10 individuals with the account numbers 1-34-100-100-100 to 1-34-100-100-109. The loan was to be paid after duration of 48 months and was taken on December 2010. The first repayment period therefore was on 31st January 2011. We will follow the payment for these individuals for duration of one year.
1. January 2011 – On the month of January 2011, everybody made their loan repayment which for illustration purposes we will assume is 0.19% of the total loan amount per month.
From the figure above, we can see that the column not_paid is zero for everyone for the month of January. If this column have a figure, then the loan will be in arrears.
2. On the month of February, some of our clients had difficulty paying their loans as illustrated below.
For subsequent months, we had several defaults. We need to create a dashboard using the BIRT reporting engine and SpagoBI that will show the following details.
1. The monthly loan arrears trends in a line chart.
2. The top 5 defaulters.
3. The total loan defaults per month.
4. Listing of all defaulters.
Follow these steps to create the BIRT dashboard outlined above.
1. Start your SpagoBI Studio and create a new report project. Call it Dashboard.
a. Click on File -> New -> Project.
b. Select Report Project under Business Intelligence and Reporting Tools.
c. Under project name, enter Dashboard and click on use default location.
d. Click on Finish.
2. Next, create a new report document.
a. Click on File -> New -> Report.
b. For the parent folder, select Dashboard and enter non_performing_loans. rptdesign as the file name.
c. Click Next.
d. Under report template, select blank report.
e. Click Finish.
3. Now let us add a grid with four rows and two columns to our report. A grid is a row/column layout that helps you organize items in your report. Under the pallete tab, click on report items, click on the grid icon and drag it to your report.
4. For number of rows select two and number of columns select four.
5. We will insert the company logo on the first cell. Click on the image icon under pallete and drag it to the first cell. Under “Select image from”, check the radio button next to “Embedded Image” and click on “Add Image”. On the CD that came with this book, you will find the folder images. Inside it you will get shemmalogo.jpg. Select this image and click on insert.
6. Click on the Shift key as you drag the image to resize it proportionately. Now we will a heading on the report. Click on the second cell on the right and drag the Text icon to it from the Pallete.
7. Click on OK.
8. Click on the first cell of the second row then press down shift key and click on second cell on second row. All the cells should be highlighted. With the cells in this state, right click and select merge cells.
9. Click on background and change the background color of the merged cells and reduce the size to five pixels.
10. Once we are done with the heading, it should look like this.
Now we will add the first item that pulls data from our table on the chart. This will be the “total monthly loan repayments that have not been paid expresses as a line chart to show trends for the entire year “.
Note: In case you are reading this at spagolabs blog, then you can get the mysql dump and other files needed for this tutorial by writing the author at the email xogutu at gmail dot com for a small fee.
11. First though we need to create a connection to the database.
a. Under Data Explorer tab, right click on Data Sources and select New Data source.
b. Under Data Source Type, select JDBC data source.
c. Under Data Source name insert “LocalMysql”
Click Next.
d. Under Driver Class, insert “com.mysql.jdbc.Driver (v3.1)”
e. Under database URL, insert “jdbc:mysql://localhost:3306/bank”
f. Put a username and password and test your connection.
g. Next we need to create a data set. Right click on Data Sets then select New Dataset.
h. Under Data Source Connection, select the datasource we created above.
i. For the Datasource name, enter “RepaymentTrend”
j. Click Next.
k.
Under Query Text, enter the following sql and click on Finish.
l. A dialog box should come up. Click on preview results.
12. We will create a chart item using this data set.
13. Merge the cells on row three and drag the chart icon from the pallete to the mergerd cells.
14. Select line chart and click Next.
15. Under Select Data, click on the radio button next to “Use Data From” and select “RepaymentTrend” data set.
16. Click on loan_month, drag and drop it on category (X) series.
17. Click on total_monthly_unpaid, drag and drop it on value (Y) series.
18. Now if you check keenly, you will notice on the chart preview the chart is nor ordered properly by month. To order click on “Edit Group and sorting button” next to “Category (X) series”
19. Under “Data sorting”, select Ascending.
20. Under “Sort On”, select row["month_no"] and click on OK.
21. Click on Next. Under format chart tab, click on series and remove the text “Series 1” and type “Loan Arrears”
22. Click on Title and replace the text “Line Chart Title” with “Monthly loan arrears”
23. Click on Finish and resize the chart accordingly.
24. Now click on Run -> View Report -> In Web Viewer, so that we can see how our dashboard looks so far.
We can see from this that the month of SEP had the greatest amount of loan arrears. Now let us add other components to our dashboard.
25. Next we will create a chart to show the top five defaulters as at current month which is December.
a.
We will use the query below to calculate the top five defaulters.
b. Create a new data set using the query above and call it “Top5Defaulters”
Expand the row below the one that contains the monthly loan arrears chart and insert a bar chart on the cell labeled top 5 below.
c. Under use Data from, select Top5Defaulters. Drag the months_defaulted to Value (Y) series and acctno to Category (X) Series a shown below. Click on Next.
d. On the format chart Tab under legend, uncheck the visible check box.
e. Under title, replace the text with “Top 5 Defaulters”
f. Under X-Axis, click on the icon below to invoke the font editor.
g. Change the rotation to -42 degrees.
h. Click on Y-Axis and select the check box under title. For the title insert the text “No of defaults last 12 months”. For the title of the X axis, insert “Account No.”
i.
Next we will add the the total loan defaults per month on our chart using the query below.
Create a new data set and call it “Monthly Defaulters” using the query above.
j. Drag the data set “Monthly Defaulters “ to the cell on the right of the one with the Top 5 defaulters chart. Modify it to look like the one shown below.
k.
Create a new data set and call it “ArrearsList” using the sql below.
l. Drag the dataset “ArrearsList” just below the datagrid we used above.
m. Modify the List to look like the one below.
n. Our dashboard should now be complete. Click on Run -> View Report -> As PDF to export your report to PDF.
Final non performing loans dashboard.
Follow the steps in chapter four to add the birt report to SpagoBI. Now you should have enough knowledge to follow the SpagoBI documentation to create any object.