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
Crosstab... Live with it or suffer. That is the whole purpose of a crosstab, to get dynamic headers.
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)
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.
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