Hiding column in crosstab
Users want to see sales figures by periods and order method. We need to show monthly sales and year's totals. 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 dimension | Current year and Month on rows, Order method | Order method on column as shown in the following screenshot. Create aggregation on measure.
Define appropriate sorting.
How to do it...
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 user friendly title.
We will start by updating the title for yearly total row. Select <Total(Month)> crosstab node. Change its Source type to Data item value and choose Current year as the Data item.
Run the report and check that the yearly total is shown with the appropriate year.
-
Now, we need to get rid of the year column on left edge. For that, click the Unlock button
on Report Studio toolbar. The icon should change to an open lock (unlocked).
Now select the <#Current Year#> text item and delete it.
Select the empty crosstab node left after deleting the text. Change its padding to 0 pixels and font to 0.001 pt size.
Run the report.
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 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.
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 colour and bordering as well, so as to blend the empty column with either the page background on left or the month column on right.
