Column based report options

[ebutter]

V10
XML data with ADO.NET driver

I have to build a cross-tab style report like the following:

2005 2006 2007 etc. for 30 years

Asset1 value value value
Asset2 value value value

The report will also be grouped in various ways. I have tried the native cross-tab capability, but it is too restrictive to meet design requirements.

1) Is there a way to use "Format with Multiple Columns" in the Section Expert to do this? It seems like it will only do one horizontal page.
2) I've read about a "three-formula" strategy in help. Is this my only other alternative?

Thanks!

 

[lbass]

Can you explain what you find too restrictive about an inserted crosstab? It is the most logical approach especially when you anticipate so many columns and appear to want the option of virtual pages.

-LB

 

[ebutter]

I agree that the Crosstab object would be easiest. My biggest problem is simple that I can't make it separate group level titles from subtotals. My work around used 9 crosstabs for this one section and it still wasn't right because there is white space between crosstabs that I can't control. The complete structure is like this:

2004 2005 2006 etc. 6 pages, 5 columns/pg
Asset Category Label
Asset Class Label subtotal at Class level
Asset Description Label values for each year
subtotal at Category level

I cannot find a way to separate Label from Subtotal.

Thanks!

 

[lbass]

I'm not sure what you mean by:

I can't make it separate group level titles from subtotals.

Are Asset Category, Asset Class, and Asset Description separate row fields? What are you trying to achieve in terms of the presentation? Are you trying to set off the subtotals more clearly?

-LB

 

[ebutter]

Yes. I have a rigid requirement that says that my Asset Category Group should be labeled at the top of the group and the subtotal for that group should be at the bottom of the group. For instance :

Yr1 Yr2 Yr3
INVESTABLE ASSETS (asset category group)
Cash and Equivalents 20,000 22,000 23,000
Stock Fund 110,000 112,000 115,000
Hedge Fund 55,000 57,000 etc.
INVESTABLE ASSET SUBTOTAL 185,000 191,000

Using the Crosstab object I seem to be limited to displaying the subtotal amounts on the same line as the label.

Thanks.

 

[lbass]

And the display with the outer heading (if you uncheck "indent row labels") is not okay? You can even rotate the label so that it saves space (although I'm not sure this would be printable).

Otherwise, you could add a second summary and suppress the topmost one to create an empty space, and add a return to the inner group name in the customize group name area of group options. Then create a second crosstab with the same fields (again with two summaries) where everything but the outermost group label is suppressed and the grid lines are removed. Then overlay this on the first crosstab. You would still have the extra space per other rows, though.

Otherwise, a manual crosstab is the way to go, where you create a formula for each year and then insert summaries on it at all of your group levels, e.g.,

if year({table.date}) = 2004 then {table.amt}

-LB