Manual Cross-Tab of unknown columns

[MJRBIM]

CR-XI (R2)
Oracle 10g
============

I don't think this is possible, but I thought I would post it here to see if anyone has an idea.

Trying to create a Manual Cross-Tab of unknown columns from this sample data.

SAMPLE
========

USER_ID USER_NAME SPECIFIC_PERMISSON GRANTED_STATUS
1234 John Smith Permission 001 Y
1234 John Smith Permission 002 Y
1234 John Smith Permission 003 Y
1234 John Smith Permission 004 Y
|
|
v
(There could be 1,000 Permisson Records...)

They would like to see the results as one (1) row per USER.

USER_ID USER_NAME P_001 P_002 P_003 P_004
1234 John Smith Y Y Y Y

As stated, there could be 1,000 Permisson Records per user - and 1,000+ colums to this report...

Is there a way to do this in CR-XI (R2) using the cross-tab tool or a manual cross-tab?

Thanks in advance for the advice!

 

[lbass]

Insert a crosstab in the report header or footer, add user ID and user name as your row fields, specific permissions as your column field, and maximum of granted status as your summary field. Then go to the customize style tab and select the row fields and check "suppress subtotal" and also check "suppress row totals" and "suppress column totals".

If you want to, you can eliminate the "Permission" in that field by using a formula for your column field:

right({table.specific_permission},3)

-LB

 

[MJRBIM]

LB -

THANKS! I tried something like that before I posted which left me with
Two questions...

1.) Will that cross-tab be exportable to CSV...?

2.) I need the first couple of columns (eg.
USER_ID, USER_NAME) as regular columns before the PERMISSION columns start. Is that possible...?

MJRBIM

 

[lbass]

I don't have CSV dlls installed to test this, but why don't you give it a try? I believe the ID and name would appear in separate columns, as well as the inner cells.

If you wish, you can remove the gridlines by going to the customize style tab->format grid lines->uncheck "show grid lines".

-LB

 

[MJRBIM]

I did try the CSV export with DEFAULT options and only the Report Footer showing.

It returned a BLANK CSV.

 

[lbass]

I fooled around with this a bit, but decided I don't know enough about CSV files to be of any help. Sorry.

-LB

 

[MJRBIM]

..as stated in my first-post, I don't think it is possible...

Thanks for trying!

 

[lbass]

Inserted crosstabs are explicitly not allowed for exports CSV. You could use arrays or accumulate values for the header and the granted status in strings to display in the group footer, but when I gave this a try, the export didn't seem to honor the detail suppression, and the header appeared as a left hand column (if I remember correctly). There MIGHT be a way to do this, it's just that I personally don't know how.

-LB