Hiding columns in crosstabs
Users want to see sales figures by periods and order method. We need to show monthly sales and the yearly total sales. The year should be shown in the Year total row and not as a separate column.
Getting ready
Create a crosstab report with Sales fact / Quantity as a measure. Drag Time/Year and Month on rows, Order method / Order method type on column as shown in the following screenshot, and create aggregation on measure:
Add a total for the Month and Order method type then define appropriate sorting if required.
How to do it...
In this recipe, we want to hide the year from the crosstab and show it only in the report as a year total. To do this, perform the following steps:
First, let's identify the issue. If you run the report as it is, you will notice that the year is shown to the left of the months. This consumes one extra column. Also, the yearly total doesn't have a user friendly title as shown in the following screenshot:
We will start by updating the title for the yearly total row. Select the <Total(Month)> crosstab node. Change its Source Type to Data Item Value instead of Data Item Label and choose Year as the Data Item Value.
Run the report and check that the yearly total is shown with the appropriate year as shown in the following screenshot:
Now we need to get rid of the year column on the left edge. For that, click on the Unlock button in the Report Studio toolbar. The icon should change to an open lock (unlocked).
Now select the <#Year#> text item (not the whole cell) and delete it.
Select the empty crosstab node left after deleting the text. Change its Padding to 0 pixels in all directions.
Run the report and you will see the following screenshot:
As you can see the year column on the left is now successfully hidden.
How it works...
When we want to hide an object in Report Studio, we often set its Box Type property to None. However, in this case, that was not possible.
Try setting the Box Type of the year column to None and run the report. It will look like the following screenshot:
As you can see, the cells have shifted to the left leaving the titles out of sync. This is most often the problem when Report Studio creates some merged cells (in our case, for the aggregations).
The solution to this is to format the column in such a way that it is hidden in the report as we have seen in this recipe.
There's more...
This solution works best in HTML output. The Excel output still has a column on the left with no data in it.
You might need to define the background color and bordering as well so as to blend the empty column with either the page background on the left or the month column on the right.
