Cross Tab and Grouping

[smmedeiros]

I'm working on a cross tab report where i have 4 variables I've placed in a crosstab
Manager
Name
Door
Access Flag


in the row section
Manager
Name

in the column section
Door

in the summarized Section
Mode of Access Flag

The results are what I expect, simple table showing

Manager --> name and population of data value "yes" if access flag=yes
The columns have 4 possible doors.

because I'm grouping by Manager (and this is a need to seperate them),
the first cross tab where I have team members with access to all 4 doors show all 4 columns.
I then move to the 2nd manager where the team members have access to only 1 door. so, only 1 column shows
i then move to the 3rd manager where team members have access to 3 doors, hence 3 columns
back to a manager who has team members with access to 4 doors, hence 4 columns.


the door names will be consistence for all manager combinations.

is there a way to have each cross-tab section display all 4 door columns regardless if the team member has access to it?

desired output
door names across top in columns
manager name in row followed by employee name
access flag in summarized field

door1 door2 door3 door4
Manager1
name1 Yes Yes Yes
name2 Yes Yes
name3 Yes Yes Yes Yes
name4 Yes Yes
name5 Yes Yes Yes Yes
name6 Yes Yes Yes Yes
name7 Yes Yes Yes


door1 door2 door3 door4
Manager2
name20 Yes
name21 Yes
name22 Yes
name23 Yes
name24 Yes
name25 Yes
name26 Yes


door1 door2 door3 door4 Response
Manager3
name30 Yes
name31 Yes
name32 Yes Yes
name33 Yes
name34 Yes Yes
name35 Yes
name36 Yes




in addition, is there a way to add a 'blank column' at the end to allow after export, someone to type in the field to track a response?
Thanks in advance for any help, ideas or suggestions.

 

[hilfy]

This is going to depend on how you join your data together. Do you have a table that just lists the doors? If so, that needs to be the "master" table in the Linking Expert. You would then create Left Outer joins from there to whatever tables it needs to link to (I assume to the table with all of the door accesses in it) and then Left Outer join from that table to the person table and from the person to the manager - ALL of the joins are left outer.

This should cause all of the doors to appear, regardless of whether a manager's people use the door.

If that doesn't work, I have some other thoughts about how to do this, but it would require creating a Command (SQL Select statement) to get the data for the report. For that I would need to know what type of database you're connecting to along with the table names and structure.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[CrystalizeCanada]

Another idea that might work is to use 4 summary fields in the cross-tab and just make them look like columns.
So you don't add a real column to the cross-tab but instead add 4 summarized fields based on 4 formulas that you create - one for each door.

Imagine I had sales for 4 sales people Tony Sue Pam Fred and I want to create a crosstab.
However I want Jeff to have a column too - Jeff doesn't really work for the company but I want him to appear in the cross-tab anyway.

I could create a formula for Tony, Sue, Pam, and the elusive Jeff.
@Tony's formula would look like this:
if {salesPerson} = "Tony" then {Sales} else 0
@Jeff's formula would look like this:
if {salesPerson} = "Jeff" then {Sales} else 0

Once I create the 4 formulas I add them as summary fields to my Cross-tab.
When adding more than one summary field to a cross-tab you have the option to summarize horizontally.
And you can add summarized labels that you can edit.
Formatted this way the four summary fields in the cross-tab end up looking like four columns.

All Jeff's sales will be zero unless he decides to come to work one day and then finally we will have some numbers show up in his column.

In your case you would be adding 4 formulas for each door and adding these to the summary area of the cross-tab.
You would remove any column fields you had added to the summary.

I would definitely try Dell's suggestion/s above first. This is just an interesting workaround if you can't find a better fix.



Gordon BOCP
Crystalize

 

[smmedeiros]

Thank you both for your suggestions.
Gordon - got your idea working. Only problem left would be the ability to have column headings. I have content in the row section, and content in the summarize section. Nothing in the column. Any thoughts on how I can get labels in place for the 4 new summarized formulas now placed in my 'summarized field' section of the pivot?

 

[CrystalizeCanada]

In the Cross Tab right click a summary and choose Summarize Field Labels, Show Summarized Field Labels and you should get the labels that you are after. That should give you the labels you want for each "column"

Gordon BOCP
Crystalize

 

[smmedeiros]

Thank you Gordon.