Hi,
When I create a query calculation in my first column that concatenates all the columns in my report I want to hide it when I run it for Excel 2007. Setting the column property Box = None, removes the column from my report, however the fact cells then mis-align. I tried conditional formating, setting padding to 0 and nothing works. There was some suggestions to use Crosstab space, but I couldn't get this to work. Could someone provide a step by step on how to re-align fact cells after hiding a crosstab column. This has to work with output to Excel 2007.
thanks,
Paul B.
Try this:
1. Define a boolean variable based on ReportOutput() function (where the variable gets "yes" when the output type where you want to hide the column appears).
2. In the crosstab, click on the title of the column you want to hide. Over the properties box the name of the highlighted object will appear, it should be "crosstab node member". make the variable style variable.
3. Click on the little arrow on top of the properties box ("select ancestor") and pick "crosstab member fact cells". make the variable style variable.
4. Now, from the explorer bar, hover over conditions, and select "yes" under the boolean variable you created. The bar will become green.
5. click on the header of the column you want to hide again. Over the properties box the name of the highlighted object will appear, it should be "crosstab node member". Set boxtype to none.
6. Click on the little arrow on top of the properties box ("select ancestor") and pick "crosstab member fact cells". Set boxtype to none.
Double click on explorer bar to make it gray again and run the report for verification.
Hi Nimrod - thanks very much for replying. I tried it with a slight mod, I created a boolean variable '0=1' and then used the No condition with the below steps, I figured this would work with all output types. It does remove the column I want, but by setting Fact cells to None it also removes the measure.
I couldn't get the ReportOutput() function to work. My varialbe was 'ReportOutput()='XLS'.
any further help would be much appreciated.
Paul B.
I think it's because you selected all crosstab fact cells. You need to select "crosstab member fact cells" - which will only remove the fact cells of the current culumn.
Hi Nimrod - thanks again for your reply, though when I click the ancestor button after selecting the first column the only option is 'crosstab member fact cells' and this selects all fact cells. I have attached an example of the output.
cheers,
Paul B.
I am experiencing the same issue; my measures are not showing when I render the Crosstab Member Fact Cells to the variable. To solve the issue, I removed the variable from the Crosstab Member Fact Cells. This caused the measures to shift to the left. To solve this issue, I added a table to the top of my page with the number of columns that will display in my crosstab when I run the report in Excel 2007. I added text items; one for each of the column headings in my crosstab. Then I set the box type to none on the Column Member Captions for each of my facts.
This option requires additional formatting on the page, but seems to work well.