Export crosstab with no row indent to Excel

[Janezz]

Hello,

I have the following problem:
If I design crosstab with e.g. 3 groups (rows) and 'Indent row labels' option on 'Customize Style' tab in crosstab expert is set to off (default), the crosstab is exported to Excel in a way that each row (group) value is in its own Excel column, for example:
Country
City
Street
What I want is to have all rows in the same column (first), for example:
Country
City
Street
..
and summary values to be displayed in a subsequent columns.

When dispaying report in a viewer I was able to achieve this by turning 'Indent row labels' option to on and setting the value of an indent to 0. Now the viewer displays all groups in the first column.

But if I export such report to Excel (I use option to export only data without any style), the export is the same as with the first example, row values are not all in the first column (as with viewer), but each in its own column.

Is it somehow possible to export data to Excel so that all row values will be in the first column?

Thanks
Janez

 

[IanWaterman]

I think you will need to produce a manual cross tab.

ie for your columns produce formula which are placed in details, supress details. eg If columns are months
@January
If month(DateField) = 1 then ValueField

@February
If month(DateField) = 2 then ValueField

etc

Add groups as per your Crosstab groups and then do relevant summaries in Group footers.

This will then expor to excel without indenting.

Ian

 

[Janezz]

Hi,

I think this is not an option for me, because columns in my crosstab are weeks and so the number of columns is "very" dynamic.
The number of columns is based on the date period the user selected. From this selected period, stored procedure in SQL Server calculates all weeks that fit the period (e.g Week 1, Week 2, ...) and the data from this stored procedure is then sent to the report (crosstab) which generates the correct number of columns. So the report does not "know" the exact number of columns until it receives the actual dataset.

Janez

 

[IanWaterman]

In that case I think you are stuck with the indents.

If try the data only option for excel export it is not so bad, and should be easy to tidy up.

Also right click on the Boxes with text total and suppress, at least those words do not get exported then.

Ian