Developing a report in App Studio and the report requirement involves presenting 3 summary rows of data in a single grid.
For example: the First row is All customer sales over time, the Second row is for sales related to Top 20 customers over time and the Third row is sales for All Other customers over time. The data is not a straight pull from the database so 3 separate HOLD Files were built for each of the above cases. All 3 Hold files have the same fields/columns and are the same format. The plan is to pull all this data back together in another Hold file and build the final version of the report.

If I was writing this in SQL stored proc I would do a UNION of the 3 datasets since each contain the same fields.
My question; what is the proper menthod to use in WebFOCUS. I have tried Join and Match and neither give me one table with all three datasets in one union at the same level.

Suggestions or examples appreciated.

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Dave,

Perhaps one of the easiest methods would be to FILEDEF ddname DISK filename.ext (APPEND.

If you use ON TABLE HOLD AS ddname FORMAT ALPHA for each hold file you should be able to perform a single table request on the resultant file.

Alternatively you could use MORE with your existing 3 hold files.

T

quote:

loping a report in App Studio and the report requirement involves presenting 3 summary rows of data in a single grid.

-----------------------------------
After generating your all hold file use more command for UNION :-

TABLE FILE hold1
PRINT field1
BY field2
ON TABLE HOLD AS final_hold
MORE
FILE hold2
MORE
FILE hold3
END
-RUN
TABLE FILE final_hold
PRINT field1
BY field2
END
-EXIT

Another option is to concatenate the hold files via the USE statement, as long as each file has identical columns and column sizes (i.e. master files are identical) and they are held as FOCUS or XFOCUS files.

For example:

USE hold1 AS hold1
    hold2 AS hold1
    hold3 AS hold1
END

TABLE FILE hold1
PRINT field1
      field2
.
.
.
END

Thanks everyone. One additional item I found out is the use wants the report when finished in Report Caster.
Are are any expected issues with using any of these solutions?

Hi Dave,
What do you mean by

quote:

the use wants the report when finished in Report Caster

?

The above technics shows how to "merge" data files together to be able to use that new file for something else such as producing a report.

If your goal is to create a new file that users will then be able to use by themselves, you'll have to save (APP HOLD ...) this file somewhere on the server and make it available.

You may need to do more research about this and have some WF training.

Hi dave,

i faced the similar requirement in one of my project. Please try as below

TABLE FILE FINAL_HOLD_GRP
PRINT *
ON TABLE HOLD AS FINAL
MORE
FILE ONE_FILE
MORE
FILE SECOND_FILE
END


The final hold file will give the union set of al the three file. You can pull the data and use it for further reporting.

Thanks everyone.
I was able to use the 'MORE' command and successfully joined the data I needed together.
The report also appears to run just fine in Report Caster.

Update the subject of your first post by adding [SOLVED] at the beginning.