Showing row values in columns as query calculations?

[Bren312]

Hi guys, I've got report which is pulling three rows for each document (because there are three G/L accounts for each document).  All well and good, but I'd like to create query calculations to get these accounts displayed as columns, not rows.  I'd prefer to do this in a list (because I'm doo-doo with crosstabs at this point in my Report Studio life).

Example of the current display: 

Document   Account #       Value
266720031  3500148     ($4,234)
266720031  3100128      $2,005.24
266720031  1400046       $2,228.76


Example of what I'd like to see: 

Document         Acct. 3500148    Acct. 3100128    Acct. 1400046
266720031         ($4,234)               $2,005.24         $2,228.76

Sorry if I'm not explaining this well; I'm still trying to get used to the vernacular of Report Studio. 

Thanks guys!

Bren

[Invisi]

Crosstab... Live with it or suffer. That is the whole purpose of a crosstab, to get dynamic headers.

[bdbits]

While you could possibly write direct SQL (generally a bad idea) and use PIVOT statements, it is rather pointless since that is essentially what crosstabs do.

I am with Invisi ... learn to use crosstabs.   8)

[tjohnson3050]

Crosstab is definitely your best bet.  If you absolutely must have a list, you can create a separate data item for each account and use a case statement as a definition:

case
when [Account #] = 3500148
then [Value]
else 0
end

The downside to this is that it is not dynamic, and only works with the account numbers you 'hard code' into the report this way.

[Invisi]

I am one to prefer a list to a crosstab whenever possible, but this one screams C.R.O.S.S.T.A.B.

Or P.I.V.O.T. T.A.B.L.E. if you like.  :o