1) Create a simple report using the Sample DB and the below Query.

To understand how to create a simple report, please check my previous blog “Creating Your First Report !!!


SELECT DISTINCT ORDERS.SHIPCOUNTRY,
                                          ORDERS.SHIPCITY,
                                          COUNT(ORDERID) AS TOTALORDERS
FROM ORDERS
WHERE SHIPCOUNTRY IN (‘ARGENTINA’,’AUSTRIA’,’BELGIUM’,’BRAZIL’)
GROUP BY ORDERS.SHIPCOUNTRY,
                         ORDERS.SHIPCITY
ORDER BY ORDERS.SHIPCOUNTRY,
                         ORDERS.SHIPCITY

2) Once the layout is ready delete all other Band except Detail, Title and Summary Bands.
3) in the Outline Menu, Select the Report Name and Right Click and select “Create Group”

Img21

4) In the Create Group Wizard, Name the Group. I have left it with “Group 1” and select the tick box
“Create Group from a report Object” and In the Report Objects list, select ORDERCOUNTRY.

Img22

5) Click Next and Select the Radio Button to add the Group Header and Footer. Click Finish.

Img23

—-To Test The Data we will follow the below steps—–

6) Drag and Drop SHIPCITY and TOTALORDERS Fields from “Fields” Tab into the detail band. Also Drag the field “SHIPCOUNTRY” into Group Header band.
7) Preview the Report and the Report Should look like below.

Img24

8) Now From the Fields section, drag and drop TOTALORDER Field into Group Footer section. This will open a wizard asking to select a Function. Select “SUM” function and it will create a Variable as “TOTALORDER1”. Place it below the TOTALORDER Field in the Detail Band.

Img25

9) Preview the report. and it should look like below.

Img26

The display shows TOTALORDER for each city under each country and the TOTALORDER1 shows the sum of TOTALORDER for Each Country. So this data is sufficient to create stacked bar with Total display.

——-In this section i will show how to create stacked bar with Total value displayed on the column———

1) Expand the Summary Tab.
2) From the Palette select Charts and select “MultiAxis Chart”.
3) In the First Chart selector window, select Stacked Bar Chart.
4) In the Outline section expand the Multi Axis under Summary Band upto “Category Series”

Img27

5) On the Property Window, select the Chart Series section and select the variables as follows.

Img28

6) Now, Click on Chart Element in the summary Band, right click and select “Create Chart Axis”
7) Select “Bar Chart” as the secondary chart.
8) This will create another Chart element under “Multi Axis” in the Summary band. Follow the same step as we did for the stacked bar chart above and select the Chart variables as follows.

Img29

9) Two things to note in the above image are
a) The series Expression is just two colons “”. This displays blank series expression.
b) The Value expression is the Variable which we created (TOTALORDERS1)

10) In the “Chart Plot” section of both Charts, make Show Label section as “True”

Img30

11) Click Preview to see the Chart. The Chart would look like this.

Img31

In this Chart, there are two Y axis lines and the Total is not displayed properly on the Column. This can be corrected as follows.

12) Click on the Bar Chart from Summary band, go to properties and then “Chart Plot”. Under this go to Value Axis and Color the Value Axis Line, Tick to “White (#000000)
13) Also change the “Range Axis MaxValue Expression” to 100 for both the charts.

Img32

14) Now Preview the Chart and we have the Stacked Bar Chart with Total Value displayed on the Column.

Img33

Download the report file here