From what I can see, there is no way to use hold files in a reporting object.

Ideally I'd like users to be able to be prompted for a parameter, which would limit the population of ids we get back, then do our joins and then do our output. This is the most efficient way to report.

The way webfocus forces you to do it we have to join potentially huge populations of data, then start filtering which results in very slow reports.

Has anyone succesfully used reporting objects with a hold file? Any help would be appreciated.

We are running 5.2.6 looking to upgrade to "Nexus" very soon.

George,

I have not tried it, but what about setting up a selection form which will be the starting point for creating the hold file. Set the APP HOLD to a permanent dir, run the query which will create a hold file in that dir and create your Reporting Object on the hold file.

I must admit that I'm very new to reporting objects. In theory they sound great but I cannot use them if we have to join first.

That being said, I thought of the app hold idea but am not sure how I'd tie the 'launch page' to the reporting object. And even if I did, we'd have to hand code the connection from our web launchpage to the correct domain, folder, report or we'd have to use a drilldown on that launchpage which would have to change every time someone wanted to create an 'ad hoc' report. Seems a bit cumbersome.

Maybe a hold file isn't exactly what I need. All I really need to do is filter before doing a join in a reporting object. If there is some other way I'd love to know about it.

Thanks

JOINS can be set up in the OTHER window of a reporting object;
if for some reason you don't want to do that, then build a datamart from your warehouse for your users to hit. Make your datamarts specific to each MRE domain...so retrievals are faster.

George, When you say filter before doing the join, Do you just want to as another where statement to the RO that the user cannot change?

Yes...

I would like to narrow down the search before the join happens.

Basically all of our reports are driven by academic term. Fall 2005 being one such term.

Usually, we'll have users select a particular term, we will then do a quick lookup for all people who are registered for that term, then we'll start joining to whatever tables we need to get our information on those students.

If we join first, we start with a population of every single student who has ever enrolled in a course at the university. Try joining a table of half-a-million records to another table of the same size... it's not pretty

George - I have a very similar issue.
Did you ever get a suitable solution?

George, while I'm not sure if this is exactly what you are looking for, this is how we do it at the college I work for.

TABLE FILE STUDENTS<br />SUM<br />STUDENTID<br />BY STUDENTID NOPRINT<br />WHERE TERM EQ '&TERM'<br />ON TABLE HOLD AS CUR_STUS<br />END<br /><br />JOIN<br />STUDENTID IN CUR_STUS TO<br />PERSONID IN PEOPLE AS J_STUS_PEPS<br />END<br /><br />TABLE FILE CUR_STUS<br />PRINT<br />STUDENTID<br />FIRST_NAME<br />LAST_NAME<br />END

I hope this helps some.

I was still never able to get a satisfactory solution to this question. The real question isn't about how to do a hold file, it's how to limit a join or how to do a conditional join in conjuction with a reporting object.

George,

The WebFOCUS world only brings back the answer set based on your selection no matter how large the join might be. I'm assuming when you say reporting object you mean a reporting object you have created in the domain to do reports against, which don't lend themselves to more than an answer needed. A reporting object is a 'fex'.

I can relate to you volume issue. I have found sometimes the fastest way is to pull data from the sources and create multiple hold files if needed and join them. We have many reports that pull currently enrolled and then go back to the mainframe and get the rest of the information. I have joined hold files back to the mainframe, some times it goes fast others not depending on the key. Sometimes you can do it all in one, but not always. Here is a fex I use to get Graduate College Admissions 'census' statistics;
I started with report assistant and then edited to complete the processing.

JOIN
MMAPTBL_UNO_PROD.MM301 AND MMAPTBL_UNO_PROD.MM005_MMAP AND
MMAPTBL_UNO_PROD.MM320 AND MMAPTBL_UNO_PROD.MM325 AND
MMAPTBL_UNO_PROD.MM330
IN MMAPTBL_UNO_PROD TO ALL RTPGTBL_UNO_PROD.RT005_RTPG AND
RTPGTBL_UNO_PROD.RT010_RTPG AND RTPGTBL_UNO_PROD.RT105_RTPG AND
RTPGTBL_UNO_PROD.RT115_RTPG AND RTPGTBL_UNO_PROD.RT120_RTPG
IN RTPGTBL_UNO_PROD AS J1
END
JOIN
RTPGTBL_UNO_PROD.RT005_RTPG AND RTPGTBL_UNO_PROD.RT010_RTPG
IN MMAPTBL_UNO_PROD TO ALL RTRTTBL_UNO_PROD.RT005_RTRT AND
RTRTTBL_UNO_PROD.RT010_RTRT
IN RTRTTBL_UNO_PROD AS J2
END
JOIN
MMAPTBL_UNO_PROD.MM005_MMAP
IN MMAPTBL_UNO_PROD TO AARTTBL_UNO_PROD.AA002_AART
IN AARTTBL_UNO_PROD AS J3
END
TABLE FILE MMAPTBL_UNO_PROD
HEADING CENTER
"College of &COLLEGE Student Applications"
"Admissions and Enrollment Status for &TERM"
"Reported on &DATE"
PRINT ALL.MM005_MMAP MM325 MM330 MM305 MM375 MM450 MM496/MDY MM515/MDY RT090
COMPUTE ENROLLED/A1= IF RT090 EQ 'N' THEN 'N' ELSE
IF RT090 GT ' ' THEN 'Y' ELSE 'N'; AS 'ENR'
COMPUTE FULLPART/A1= IF RT090 EQ 'F' OR 'S' THEN 'F' ELSE
IF RT090 EQ 'N' THEN ' '
ELSE IF RT090 GT ' ' THEN 'P' ELSE ' '; AS 'F/P'
AA011 AA013
-*BY MM005_MMAP
WHERE MM301 EQ '&TERM'
WHERE MM320 EQ '&COLLEGE'
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
TYPE=HEADING, COLOR=NAVY, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END

George,

There is a way to use hold files in reporting objects (at least in 5.3.x). Put your hold file syntax in the "Others" section of the reporting object (or do an ex fexname or whatever you like). Then base your reporting object on the hold file name. This works very well for hold files database stored procedures and such.

The only thing you have to do, is ensure that the hold file master is part of your application. If you change the process for creating the hold file, then you'll have to resave the master as well.

Also, if your hold file and master are different names, put a filedef after the hold request.

if all your reports are driven by term, then have a reporting object for each term. if your universe is driven by term, then your dbs are probably viewable by term. the joins in each RO are for the specific view represeting that term.
We have ro's for each trading partner...and multiple ro's for each individual, or for each of a specified set.