How to create a chart inside a crosstab with summary

Check list of all posts

Business requirement
Please see screenshot below


The key requirement is to make product line chart at summary level, marked as yellow. In addition, it is also requested to generate all pages for all product lines.

Simple summary (report specification)

It is easy to make summary by aggregate all product types as below

The report generated as

However, there are two issues with this approach:

You need to make summary title as ‘Camping Equipment', either you create a page level title or you hardcode the title.

To make this page to be generic based on product line is difficult, as you cannot make master detail relationship at summary level

You’ll experience problem below

Actually, it is very reasonable because the crosstab node member is aggregated based on product types

There is no product line specified for that node, therefore the master detail relationship doesn’t work in runtime.  

Use parent (Product line) (report specification)

The better idea is to use the shared query to make the Crosstab node member as product line, unfortunately, you can’t change crosstab node member by simply changing data item. Instead, you need to remove aggregate and replace it with product line.


The generated report is below

We can further extend this idea for by looping through parents (Product line), which will make this report more useful.

Loop through parents (Product line)  (report specification)

Without changing query and layout, this report can be made as a report with all product lines

Use existing query (no filter)

Create page set with group by product line

Add page level query and build relationship between page set and page

Build relationship between page and crosstab

Now the whole structure can be illustrated below

What interesting is that only a single query is used. Below is the generated report