To subreport or not to subreport? 1

[PAULCALLAGHAN]

I'm having this crystal report design problem and would greatly appreciate any input.

I'm using CR XI and ODBC connection to Pervasive data files.

For each production part there are standard operating steps. For example, part 1234 has the following steps,
0010 gather RM
0020 machine RM
0030 gather supplies
0040 put it altogether

When a part is placed into production, through a work order, the standard steps are the default steps used. However, production may decide to delete/add a step.

I'm attempting to create a report to show everytime we make part 1234 what are the actual steps versus the standard steps, and thereby show any new or added steps, as well as the effect of deleting a standard step.

I've tried a subreport approach containing the standard steps. The subreport links are part number and step number.

The container/main report shows the actual steps. But if a standard step is deleted, in the work order, then it doesn't show up at all in my container/main report because there isn't an actual step matching it.

If I reverse my subreport approach - use actual steps for a subreport - then I get the same output problem when an actual new step is made but there is no matching standard step.

 

[lbass]

Paul,

Are there separate fields for Actual and Standard steps? If not, how do you distinguish them?

-LB

 

[PAULCALLAGHAN]

Yes, they are distinct fields in separate data files.

 

[synapsevampire]

Successful posts tend to include example data (show what's in the tables), and the required output.

As you can see, text decriptions by themselves don't readily describe data nor convey requirements.

-k

 

[lbass]

I think using a union query might be the best solution. Go to database->database expert->your datasource->add command and enter something like:

Select table1.`partno`, table1.`stdstep`, 'Standard' as type
from `table1`table1
Union
Select table2.`partno`, table2.`actualstep`, 'Actual' as type
from `table2`table2

You could then insert a crosstab that used partno and {table1.stdstep} (which now contains both standard and actual steps) as rows, and use type as a column. This assumes that standard and actual steps have the same codes and/or descriptions except for newly added steps.

-LB

 

[PAULCALLAGHAN]

Thanks LB for your reply and assistance. However, I don't see in CR XI what you are saying. I don't see any 'add commands' button/feature. I see my datasources laid out on a screen though.

I'm not using SQL and I don't think it is available.

Am I missing something? Would you like me to post a screen print?

 

[synapsevampire]

If SQL isn't an option, then you won't have an Add Command.

I tried to suggest what you should post, and we do have a Progress database guru that pops in on occasion that should offer other insights, however posting technical information is the quickest way to resolve issues.

-k

 

[lbass]

If "Add Command" is available to you, it would be in the database expert for your datasource, right above where it says "tables". Try finding it for the Xtreme database to see what I mean. As you say, it might be that commands are not available for your datasource.

The other approach you might take is to use a table that has the relevant part numbers to link to two subreports, one for the standard steps, and one for the actual steps. You could probably use the table that contains the standard steps for the main report, group on partno, and then add two subreports. You could potentially use the underlay function on the section containing the first subreport so that you could have a side by side display.

-LB

 

[PAULCALLAGHAN]

Thanks again LB. I think you may be onto something. However now I'm having trouble lining up the output.

For example,
part 1234 has standard steps with hour factors
0010 gather RM 1.0
0020 machine RM 1.5
0030 gather supplies 2.2
0040 put it altogether 0.8

but on the first production run, with actual hours
0010 gather RM 1.1
0020 machine RM 1.2
0031 run around plant 0.4
0050 co-ordinate special supplies 0.2
0060 send outside for assembly 1.9

My desired output is,

0010 gather RM 1.0 1.1
0020 machine RM 1.5 1.2
0030 gather supplies 2.2
0031 run around plant 0.4
0040 put it altogether 0.8
0050 co-ordinate spec supplies 0.2
0060 send outside for assembly 1.9

How do I achieve this with two subreports on a single report? I'll probably need some form of grouping on the container/main report to place my subreports and maybe some conditional suppressions. Any ideas?

 

[lbass]

If you have a main report table that contains all possible steps and their descriptions, then you could group on partno, add the descriptions, and then place the subreports side by side with only the hours fields displayed. Each subreport would have to be linked to the main report on both the partno and the stepnumber.

If this is not possible, then I think the best you can do is a side by side display of subreports per part number.

-LB