Dynamic Grouping by combining several fields 1

[RobNauticus]

Hello all,

I am attempting to create a report that can group on a set of 3 different fields. All three fields contain the same type of data (workers initals). Each record in the table is for a particular job. Within each record contains 6 pieces of info, Task1 Date, Task1 Foreman,Task2 Date, Task1 Foreman,Task3 Date, Task3 Foreman.

I want to be able to group all three Foreman fields together. I want details showing each completed date within a time range. I have created 3 detail sections showing only the info for each task.

I will also need to do this for the date fields as well to enable chronological sorting of all the records shown.

My parameter are start date and end date.




I was reading another post

http://www.tek-tips.com/viewthread.cfm?qid=753240

I am trying to think of a way to adapt this to my situation instead of actually creating a group.


I am using CR 8.5 and I cannot make any structural changes to the database.

Please help,

And Thank you all.

Rob M.

 

[lbass]

I'm unclear on what you are trying to achieve. Is your goal to have separate groups per task? Or do you mean you want to group on specific combinations of foremen? What should happen if two of the dates meet your criteria but the third one doesn't?

-LB

 

[RobNauticus]

Hey Lbass, I was hoping you would reply.

My ultimate goal is to create a report for the boss to do payroll for the Foremen for the week.

Each foreman can do one of three tasks per job in a given day.
Each task can be done by a different foreman.
If the task is not within the timeframe I would like it excluded.


I need to create a group that brings together each task a given foreman has completed within the parameters set at runtime.

Thanks lbass, I hope this helps.

 

[lbass]

If I am understanding you correctly, I think the best approach would be to use a union all to combine the foremen fields and the date fields. Then you can group on the foreman field and exclude dates in a simple way. The only problem is you would have to use the date parameters for suppression rather than selection, since parameters can't be built into unions in earlier versions.

In 8.5, you would have to first add {table.task1foreman} and {table.task1date} and it would help if you had a third field that you could add to help distinguish the tasks--since you haven't said whether there are any other fields available, let's call that third (string) field {table.extra}. Add these to the report canvas and also add any selection criteria to the report, omitting any selection criteria based on parameters. Then go to database->show SQL query.

First you would copy the existing query, then add "union all" and then paste the results after the union all. Add a second "union all" and then paste the initial query a second time. You now have three identical queries separated by union all. Next you need to change the field names in the second and third queries, so that it looks something like this:

Select
table.`task1foreman`, table.`task1date`, table.`extra`
From
`table` table
Union All
Select
table.`task2foreman`, table.`task2date`, 'Task 2'
From
`table` table
Union all
Select
table.`task3foreman`, table.`task3date`, 'Task 3'
From
`table` table

Now you can create a formula {@task}:

if not({table.extra} in ["2","3"]) then "Task 1" else
{table.extra}

Then insert a group on this. You would then go to the section expert->details->suppress->x+2 and enter:

not({table.task1date} in {?startdate} to {?enddate})

Since all date fields now fall under this field label, only the appropriate dates will be displayed.

Please note that once you have modified the SQL query, you should not enter the record selection formula area, as this can cause the SQL query to be modified and make your report error out.

-LB

 

[RobNauticus]

Thank you so much LB, I am moving right along putting this in.

I have a questions about the example. First off I have joins to two other tables used for pulling job descriptions. I believe that these need to stay at the top of the statement and out of the whole union situation, is that correct? If so how would I structure the statement from this standpoint, two select statements? Should I separate the Task1 and TaskDate1 from the inital select statement? Or should I leave that one intact and create a new select statement below following your guidelines?

I am going to try adding the new select and union statments below the inital code generated by CR and see how that works.

Thanks Again,
Rob M.

 

[lbass]

Create the report using all tables as you normally would, and adding the three fields I mentioned and the selection criteria. Then go into the Show SQL Query area and copy the entire statement. It doesn't matter that you are using other tables. You just aren't going to make any changes in those areas, but they will be repeated in each query segment.

-LB

 

[RobNauticus]

I tried and got an error "non unique table reference"

here is the code

SELECT
    MASTER_JCM_JOB."Description",
    MASTER_JCM_LOT_EXTRA."Description",
    MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Scratch_Date", MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Scratch_Foreman", MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."extra"
FROM
    { oj ("MASTER_JCM_JOB" MASTER_JCM_JOB INNER JOIN "MASTER_JCM_LOT_EXTRA" MASTER_JCM_LOT_EXTRA ON
        MASTER_JCM_JOB."Job" = MASTER_JCM_LOT_EXTRA."Job")
     INNER JOIN "MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS" MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS ON
        MASTER_JCM_LOT_EXTRA."Job" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Job" AND
    MASTER_JCM_LOT_EXTRA."Lot_Extra" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Lot_Extra"}

UNION ALL

SELECT
    MASTER_JCM_JOB."Description",
    MASTER_JCM_LOT_EXTRA."Description",
    MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Brown_Date", MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Brown_Foreman", "Task 2"
FROM
    { oj ("MASTER_JCM_JOB" MASTER_JCM_JOB INNER JOIN "MASTER_JCM_LOT_EXTRA" MASTER_JCM_LOT_EXTRA ON
        MASTER_JCM_JOB."Job" = MASTER_JCM_LOT_EXTRA."Job")
     INNER JOIN "MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS" MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS ON
        MASTER_JCM_LOT_EXTRA."Job" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Job" AND
    MASTER_JCM_LOT_EXTRA."Lot_Extra" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Lot_Extra"}

UNION ALL

SELECT
    MASTER_JCM_JOB."Description",
    MASTER_JCM_LOT_EXTRA."Description",
    MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Finish_Date", MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Finish_Foreman", "Task 3"
FROM
    { oj ("MASTER_JCM_JOB" MASTER_JCM_JOB INNER JOIN "MASTER_JCM_LOT_EXTRA" MASTER_JCM_LOT_EXTRA ON
        MASTER_JCM_JOB."Job" = MASTER_JCM_LOT_EXTRA."Job")
     INNER JOIN "MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS" MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS ON
        MASTER_JCM_LOT_EXTRA."Job" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Job" AND
    MASTER_JCM_LOT_EXTRA."Lot_Extra" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Lot_Extra"}

Any thoughts?

BTW you are awesome LB!

 

[lbass]

Where did you get this field: MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."extra"

In 8.5, you can't create alias fields in the first part of the select as far as I know. Were you somehow able to create this field in the database?

What happens if you remove this field and "task 2" and "task 3" from the union statement? Does it work?

-LB

 

[RobNauticus]

The error is from the Master_JCM_JOB. This is the top level table used to define the Job.

I was able to create another custom field, I called it extra.

I tried removing the "task 2" and "task 3" and still got the same error.

Do I need to add the other fields to the canvas before I attempt to preview? All I have on there now is the job description from the JOB Table and description from the JOB_LOT_EXTRA, and the Scratch_Date, Scratch_Foreman, and extra.

Rob

 

[lbass]

I'm assuming that you removed your newly created field as well as task2 and task3 to troubleshoot the error?

I'm assuming you initially added the three tables to the visual linking expert and then added the three fields to the report canvas.

I honestly can't follow your From clause. Can you instead explain how you linked the tables? The error seems related to that.

-LB

 

[RobNauticus]

I simply used the visual linking expert to create the relationship. That is the code generated, I have not modified anything within that block. Can I clean it up?

 

[lbass]

I'm saying I cannot read the code. How did you link the tables? Does the report run okay without the unions? Or is the same error generated?

-LB

 

[RobNauticus]

It runs fine prior to modification.

Here is what it looks like when I first started.

The linking is as follows

Master_JCM_Job -> linked by the field "JOB" to -> Master_JCM_Job_Lot_Extra -> linked by fields "Job" and "Lot_Extra" to -> Master_JCM_Job_Lot_Extra_Custom_Fields

I would say just dump all this extra linking but it would probably be extremely difficult to add it in later.

SELECT
    MASTER_JCM_JOB."Description",
    MASTER_JCM_LOT_EXTRA."Description",
    MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Scratch_Date", MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Scratch_Foreman", MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."extra"
FROM
    { oj ("MASTER_JCM_JOB" MASTER_JCM_JOB INNER JOIN "MASTER_JCM_LOT_EXTRA" MASTER_JCM_LOT_EXTRA ON
        MASTER_JCM_JOB."Job" = MASTER_JCM_LOT_EXTRA."Job")
     INNER JOIN "MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS" MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS ON
        MASTER_JCM_LOT_EXTRA."Job" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Job" AND
    MASTER_JCM_LOT_EXTRA."Lot_Extra" = MASTER_JCM_LOT_EXTRA_CUSTOM_FIELDS."Lot_Extra"}

 

[lbass]

Okay, a CR guide says that this is error is usually caused when a table name contains an underscore or is more than 15 characters long. I wonder what would happen if you used alias table names for your longer tables and in particular, if you abridged them so that the first 15 characters are different (since they are the same for two of your tables).

Of course, this doesn't explain why there is no problem without the union statement...

-LB

 

[lbass]

I cannot recreate your problem, even using aliases with long table names and underlines. I wonder if it has something to do with the new field you added. To remove the "extra" field, you removed it from the report canvas, and then corrected the query by removing "Task 1" and "task 2", right?

-LB

 

[RobNauticus]

I am going to recreate the report and try again. I will see if this helps. I seem to be getting an error with aliased tables still. I have removed all extra and related fields.

 

[lbass]

You could also try placing a paren after the first union all and then at the end of the query. I remember that this worked in one case with multiple queries, although I can't recreate the issue. Nor do I recall seeing your particular error message.

-LB