Create a Stacked Bar with “Total” Value displaying on top of each Bar

Important Requirements :

1)      A Query with the required Total Values available as a Column.
2)      A Java Code to customize a Line Chart.

Procedure :

1) Create a Report in Jaspersoft Studio with a Query including the required total Value in the query.

I have used a sample database “MDX Step by Step” to create the below code.

SQL Snap Shot :

Img1

Query :

Select * from

(select (Left(DateName(month, [dbo]. [DimDate].[FullDateAlternateKey] ),3)+‘-‘+cast(right(YEAR([dbo]. [DimDate].[FullDateAlternateKey]),2) as CHAR(6))) AS YearMonth,

dbo.FactSalesQuota.CalendarYear,

dbo.FactSalesQuota.CalendarQuarter ,

dbo.FactSalesQuota.SalesAmountQuota,

[FirstName],

[LastName]

fromdbo.FactSalesQuota

inner join [dbo]. [DimDate] on [dbo]. [DimDate].DateKey = dbo.FactSalesQuota.DateKey

inner join [dbo].[DimEmployee] on [dbo].[DimEmployee].[EmployeeKey]=dbo.FactSalesQuota.EmployeeKey

 

where(Left(DateName(month,FullDateAlternateKey),3))
IN(‘Jan’,‘Mar’,‘Oct’)

andYEAR(FullDateAlternateKey) In(2001,2004)

andFirstName in(‘Linda’, ‘Jillian’))a

 

inner join

 

(select (Left(DateName(month, [dbo]. [DimDate].[FullDateAlternateKey]
),3)+‘-‘+cast(right(YEAR([dbo]. [DimDate].[FullDateAlternateKey]),2) as CHAR(6))) AS YearMonth,

sum(dbo.FactSalesQuota.SalesAmountQuota) total

fromdbo.FactSalesQuota

inner join [dbo]. [DimDate] on [dbo]. [DimDate].DateKey = dbo.FactSalesQuota.DateKey

inner join [dbo].[DimEmployee] on [dbo].[DimEmployee].[EmployeeKey]=dbo.FactSalesQuota.EmployeeKey

 

where(Left(DateName(month,FullDateAlternateKey),3))
IN(‘Jan’,‘Mar’,‘Oct’)

andYEAR(FullDateAlternateKey) In(2001,2004)

andFirstName in(‘Linda’, ‘Jillian’)

group by (Left(DateName(month, [dbo]. [DimDate].[FullDateAlternateKey] ),3)+‘-‘+cast(right(YEAR([dbo]. [DimDate].[FullDateAlternateKey]),2) as CHAR(6))))b

on a.YearMonth = b.YearMonth

Jasper Studio Snap Shot :

Img2

2)      Drag the Chart palette from palettes.
3)      Select Multi Axis Chart

Img3

4)      From the Chart Axis Wizard select Stacked Bar Chart and Click Finish

Img4

5)      A Chart Element gets created on the Report Layout. Now Right Click and select Create Chart Axis.

Img5

6)      This will reopen the Chart Axis Wizard. Now select a Line Chart
7)      Once you selected both these charts, they will appear in the “Outline” Section.

Img6

8)      Important info is to place it either in Title or Summary to avoid duplicate charts.
9)      Now we will create the Data Values for these reports.
10)      Click on the Bar Plot Expander until you reach “Category Series” level. Click on “Category Series” and Check the properties window of the same and click on “Chart Series”

Img7

11)      Now select the relative Category, Value and Series expression as per your Stacked Bar Chart requirement.
12)      Follow the same with Line Plot.

Img8

13)      Imp Info : Since you don’t need the Line Chart legend to show up, Keep only “” in your series expression.
14)      In Line Plot, The Value Expression should be the Total Field which you created at query level.
15)      Now when you Preview the report will look something like this.

Img9

16)      Looking at this, we have still lot more to accomplish.

  1. Display Labels inside the Stacked bar.
  2. Remove Axis Lines
  3. Display Values in the Line Chart.
  4. Remove the Line Color and Line Legend.

 

16.1) Display Labels inside the Stacked bar: Click on the “Bar Plot” from Outline. Go to Chart Plot in the Properties section and Make “Show Labels” to “True”

Img10

 

16.2) Remove Axis Lines. : If we click the Show Tick Labels as False, it will also remove X axis labels which we don’t want. Hence the best way to achieve it is in both the Plots, make the Value axis Label Color to White,

16.3) Display Values in the Line Chart : To achieve this there is no direct method in Jaspersoft and we need to write a Customizer query in java and add that to the Line Chart. (Will explain this in below section with the code).

16.4) Remove the Line Color and Line Legend : Click on the “Line Plot” from Outline and go to properties section. Click on Advanced and search for “Series Color” item.

Click on the browse button next to that and click add. This will add the default color (Green in this case). Change the color to White.

Img11Img12

Img13Img14

After changing all this, the report will look like this.

Img15

Good Thing is we are reaching our destination. Last Item to do is to create Custom Class to display Total Values on each Point. For this we need to create a custom Class.

For your ease, I have attached the JAR File required for the same here.

LineChartCustomizer Download

For information on how to use this in Jaspersoft Studio, Please check my previous blog “Customizer Class – Jaspersoft Studio

17)      Now Click on “Line Plot” from Outline.
18)      Click Advanced Tab and Expand the Chart Item.
19)      There we will find Customizer Class Item.
20)      Click on the Browse button, in the filter section Type Line, this will provide the new class we created using jar select that.

Img17Img18

Img19

Now when we run the report. It should look like this.

Img20

Hence We achieved the goal of Displaying the total value of each stacked bar on top of the column.