Crosstab Report Help 1

[Freefall27]

I am using CR 11 and pulling data by ODBC connection from an Oracle DB. I have been asked to write a report and think that a crosstab is the way to go but not positive. I am not sure if this can be accomplished using Crystal? The data is all pulled from one table in the following format:


ID LOC NAME TYPE ATTR GROUP USER ATTR USER VALUE
1001 WRHSE Host Bldg Details Bldg_size 2000
1001 WRHSE Host Bldg Details Own\Lease Own
1001 WRHSE Host Bldg Interior TotSqFt 5000
1065 YARD Fence Bldg Details Name Pond
1065 YARD Fence Bldg Details Size 11 X 17
1065 YARD Fence Bldg Interior TotSqFt 3000

The above could have many different records but all need to be displayed even if some are blank. The USER ATTR field will need to be the heading and then the USER VALUE will populate that heading. Desire result example

ID LOC NAME TYPE Bldg_Size Own\Lease TotSqFt Size Name
1001 WRHSE Host 2000 Own 5000
1065 YARD Fence 3000 11X17 Pond

I have explained the problem as I understand it. Please let me know if you need further information. I have read some about crosstabs but this seems to be more challenging than the contents of the book. Thank you in advance for any help provided.

 

[Freefall27]

Can anyone let me know if this is even possible in CR XI and possibly point me in the right direction. I need to get this done for my boss as soon as possible. If it cannot be done in CR XI then any other ideas would be greatly appreciated.

The new software that we purchased contains lots of these user-defined fields that will need to be layed out in the same manner.

 

[lbass]

Try creating a formula that concatenates the first three fields:

totext({table.ID},0,"") + " "+{table.locationname}+" "+{table.type}

Insert a crosstab, and use the formula for your row field, {table.user_attr} as the column field, and {table.user_value} as the summary field, with maximum as the type of summary.

-LB

 

[Freefall27]

That works great to pull all the data into a cross tab format. GREAT!! I appreciate all your help because I am starting to learn about Crystal and have more questions than answers at this point!

What I need to do is have the ability to further define the data for the report because I get all database records from the table.


Here are some challenges that I need to fix with the current format:

With the fields concatenated I have lost the ability to have the fields separate and only filter for certain (table. type) I have probably 200 types in the database for which I may need only 20.

I also need to filter for only certain (table.attr_group) and certain (table.user_attr) and then display the value of (table.user_value)

Should the cross tab be inserted into the PF section?

Thanks again for your help.

 

[lbass]

Place the crosstab in the report header or report footer. The concatenation has nothing to do with filtering, although if any of the three fields can be null, you'd have to account for that in the concatenation formula. To filter, go to report->selection formula->record and enter your criteria, like:

{table.type} = "Host" and
{table.attr_group} in ["Bldg Details","Bldg Interior"] and
{table.attr_user attrib} in ["Bldg_size","TotSqFt"]

The crosstab will be based on the filtered records.

-LB

 

[Freefall27]

1. How can I account for nulls in the first three fields in the formula?

2. I am not getting all records because some records have not been populated with data for all selected fields in the following.

Ex.

ID LOC NAME TYPE ATTR GROUP USER ATTR USER VALUE
1001 WRHSE Host Bldg Details Bldg_size
1001 WRHSE Host Bldg Details Own\Lease
1001 WRHSE Host Bldg Interior TotSqFt
1065 YARD Fence Bldg Details Name
1065 YARD Fence Bldg Details Size
1065 YARD Fence Bldg Interior TotSqFt

These records would not show on report because the (table.user_value)is not poulated for any of the missing records.Can I add a null statement to show all records when not populated as well?

Thanks for your help!

 

[lbass]

Are you saying that {table.ID}, {table.locationname}, and {table.type} each can be null?

You can change your selection formula to something like:

(
isnull({table.type}) or
{table.type} = "Host"
) and
(
isnull({table.attr_group}) or
{table.attr_group} in ["Bldg Details","Bldg Interior"]
) and
(
isnull({table.attr_user_attrib}) or
{table.attr_user attrib} in ["Bldg_size","TotSqFt"]
)

Are these fields all from one table?

Anyway, you can simmilarly change your concatenation formula to:

(
if isnull({table.ID}) then 0 else totext({table.ID},0,"")
)+
(
if isnull({table.locationname}) then "" else
" "+ {table.locationname}
)+
(
if isnull{table.type}) then "" else
" "+{table.type}
)

Try this and then see if the crosstab populates with the correct rows.

-LB

 

[Freefall27]

Are you saying that {table.ID}, {table.locationname}, and {table.type} each can be null?

**In this case they are populated. I dve some other reports where all would not be populated. So I asked out of curiousity.

The fields are all from one table and populated as above. The cross-tab worked well to break into one row.

I have used the formula and am still not getting the additional records where the first three fields are poulated but the user.value is blank.

 

[Freefall27]

The (table.attr_user_attrib) is in the data but if the (table user.value) is not populated then the row does not display.

I need the user_attrib to show as the column header even if all the user.values are blank.

I hope this makes sense? Thanks

 

[lbass]

If these fields are all from one table, then I think the rows should all appear. Maybe try using a formula for the summary:

if isnull({table.user_value)) then "" else
{table.user_value}

Then insert a maximum on this.

-LB

 

[Freefall27]

It appears that if only selected table.attr.group are null then the record does not show. If I include all table.attr.groups then I do get all records regardless if all table.attr.groups are populated or null. I can get around this though because I know one field that is a mandatory population and the missing records do show up when I add that field to the selection criteria.

For future reference since a large part of this database is setup this way can you provide some guidance.

1. In this report I concatenated fields

totext({table.ID},0,"") + " "+{table.locationname}+" "+{table.type}

Is there any way to get this information in separate fields in order to display on the report? ex. 1001WRHSEHost I will have to separate to show all three in different columns on the report. ex. 1001 WRHSE Host

Any additional guidance would be appreciated. I am learning more each day and certainly do thank you very much for all your help in getting this to work properly.

 

[lbass]

The concatenation was only for use in the crosstab because you seemed to want the fields to repeat in each row. You could add each field as a separate row in the crosstab and then suppress the subtotals.

-LB

 

[Freefall27]

I do want all the fields to repeat in each row. Is there a way to keep in the same row just like they are but separate into each field? I have to take the report and export to Excel in order for each user to pull out their data of interest on a spreadsheet. When I export to Excel I have to undo the concatenation in order for each user to have the ability to filter. Is there any way to separate or do the fields need to be concatenated for the report to work properly with the user attributes? Thanks!

 

[lbass]

If the fields are naturally nested, like:

E D A
E D B
E D C
E F H
E F I
E F J
E G K
E G L
E G M

Then if you reverse the order of the rows in the crosstab like:

A D E
B D E
C D E
H F E
I F E
J F E
K G E
L G E
M G E

...I think it will force the display of each row label for every row.

-LB