Saturday, October 12, 2013

How to make text from query item (not layout calculation) appear on a crosstab

Please see sample below, where text was put in crosstab. The product line is the product line that generates the most of revenue for that year. In this case, it is personal accessories. Please note top 1 product line can be different year by year. please see report specification (version 10.2) for review

The query is listed below

Max: maximum ([Sales (analysis)].[Sales].[Revenue] within set  [Sales (analysis)].[Products].[Products].[Product line])

Top 1 Product Line: topCount ([Sales (analysis)].[Products].[Products].[Product line],1,[Sales (analysis)].[Sales].[Revenue])

Avg: average([Sales (analysis)].[Sales].[Revenue] within set  [Sales (analysis)].[Products].[Products].[Product line])

Variance:  ([Max]-[Avg])/[Avg]

There are two options to resolve this issue:

Option 1

Bring top 1 product line as second headings under year,
Add a cross space as separated node,
Override contents as YES
Unlock cell, bring query item 1 product line into cell

This option is somehow not working with conditional style. The most flexible way to stitch all crosstabs together

Option 2

Three crosstab are vertically stitched together, by hiding rows not support to show. Please see detail below

If we want to display dimension properties, such as Product code in this case, then simply drag dimension property under Top 1 product line, then hide both year and Top 1 product line.

No comments:

Post a Comment