Conditional cross-tab

[xsw1971]

I have a cross-tab that looks like this:

emp1(org1) emp2(org1) emp3(org2) emp4(org3)
proj1 0 0 0 0
proj2 3 0 1 0
proj3 5 0 2 0
TOTAL 8 0 3 0

I want to show ALL employees in organization 1 regardless of whether or not they have time worked on one of the 3 projects. For all other organizations I want to show the employee ONLY if they have time worked on one of the 3 projects (in this case emp3), ie, if their grand total is greater than 0.

The report is grouped on employee then project. If I check the "suppress empty columns" box then I won't see emp2 who belongs to organization 1.

I have read the threads that mention creating empty values then using the "discard all others" option in the group format. I tried changing the employee group such that

if Sum({@hours_worked}, {USERS.LAST_NAME}) = 0 and  
not({USERS.ORG} in ["org1","org2"])
then emp=""
else emp={USERS.LAST_NAME}

This didn't work I assume because it needs to create the group before it can evaluate group summaries. I can't build the select into the query because I need all of the data elsewhere.

Does anyone have any innovative ideas to work around this or am I asking to move the earth?

Thank you!
Jen

 

[lbass]

I think you will need to create a manual crosstab. If the presentation can be changed so that projects are the columns, the job will much simpler. You could insert a group based on employee, and then create formulas like:

//{@Proj1}:
if {table.project} = "Project1" then {table.hours}

//{@Proj2):
if {table.project} = "Project2" then {table.hours}

...etc. Then place these formulas in the detail section, right click on each and insert a summary and then suppress the detail section.

In order to suppress the unwanted employees, go to the section expert->group footer->suppress->x+2 and enter:

{table.organization} <> 1 and //unclear whether you meant 1 and 2
sum({@proj1},{table.employee})+
sum({@proj2},{table.employee})+
sum({@proj3},{table.employee}) = 0

If the records in the report are limited to projects 1 to 3 then you could just use:

{table.organization} <> 1 and
sum({table.hours},{table.employee}) = 0

You could do the manual crosstab with one formula per employee if you only have a few employees, otherwise I think this approach makes sense and gives you the same information.

-LB

 

[xsw1971]

Thanks for the input LB. I have never created a manual crosstab, so bear with me here.

What I think you are saying is that I have to create a formula for each and every project I have, however these projects are dynamicly generated outside of my control. (Which employees track time is also dynamic) This report will be run by managers on the fly using Enterprise so it would not be feasible to try to keep up on the project formulas. I've read in other threads that I create column headers in the Page Header section, however again the projects are dynamic to I don't know what the headers are until I run the report.

Thanks!
Jen

 

[lbass]

In what sense are the projects dynamic? Are you using a parameter to determine what projects are included? You can create the detail formulas using parameters as in:

//{@proj3}:
if ubound({?proj}) >= 3 then
if {table.project} = {?proj}[3] then
{table.hours}

Remove the auto label that appears in the page header and replace it with a formula like:

//{@proj3colhdr}:
if ubound({?proj}) >= 3 then
{?proj}[3]

So you would create as many columns as the maximum expected, using two formulas for each--one for the detail and one for the column header. Then you would insert the summaries and suppress the detail formulas. You would need to format each column summary to "suppress if zero" by going to format field->number->customize->suppress if zero.

-LB

 

[xsw1971]

I work in an engineering R&D group. Each product in the company goes through an R&D phase and has its own project (we currently have 26.) Our database is set up so that employees (other than me) can add projects to the database as they arise, which is several times/year. I do not know when projects are added, unless that person tells me (which usually doesn't happen).

Based on what you have suggested, I would have to physically modify this report and redeploy it to Enterprise each time someone adds a project, otherwise we might not capture time tracking information. I have the same issue if I group on employee instead of project - we have interns and contractors that come and go frequently, so report maintenance would be contant.

The solution I am looking for is to have a report built that automatically pulls in new data without having to maintain the report. Am I completely out of luck with this option?

Thanks for your help,
Jen

 

[lbass]

You can use the inserted crosstab--but I don't think that employees will show up if they have not worked on the designated projects.

Otherwise, if you use the formulas-using-parameters approach, you can make the report dynamic, except for the parameter selection list. If you have CR XI, then this too can be dynamic; otherwise you would need to update the dropdown list regularly. Or if projects were identified by ID, you could require users to add exact IDs for desired projects instead of using a dropdown. More work for them, but the report wouldn't have to be updated, as long as you built in enough columns to reflect the maximum number of projects to be evaluated at any one time.

-LB

 

[xsw1971]

OK, thanks so much for your input! I will try to get creative in other ways...

Jen