Question... I want to put together what would normally be a quite simple report, just returning a couple of records from a table.

It gets a little more complicated, because the end user has a spreadsheet with a list of roughly 20 or so ID's that they would like to use to filter the report at run time. So a couple of options came to mind:

1) Is it possible to prompt the user for a FILE (maybe XLS, CSV, etc) at run time and have the contents of that file be used to filter the report? It would be a single column with a list of ID's to filter on ("ID1", "ID2", "ID3", etc)

2) Is it possible to do a prompt where a single column from a spreadsheet, like this:
ID1
ID2
ID3
...etc
could be copied and pasted and used to filter?

Any other thoughts of how to approach this or pointers?

Any help or direction would be much appreciated.
Thanks,
Jason

This message has been edited. Last edited by: FP Mod Chuck,

Hi Jason

First of all welcome to Focal Point, I see this is your first post. You will find this very useful for collaborating ideas.

I am not aware of a technique for uploading a file to be used by a pre-existing report. With InfoAssist it allows you to upload data and then report against it. I hope someone else knows a trick to do this for you.

Regards,

Keep in mind that all data including prompt values have to be present on the reporting server to be accessible to WebFOCUS. You can't really mix and match data that's sitting on someone's laptop with Server data without uploading it.

Assuming you want to do all of this via InfoAssist, you could click Data, JOIN, UPLOAD, Join the uploaded table to the existing table and use the value in that structure as a dynamic filter for a prompt.

This assumes a power user who's not easily intimidated by JOIN, Upload et cetera.

This would not be using infoassist..just a standard report. In report painter you can setup a where statement to filter on the contents of a file...but was curious about prompting for the file at run time. (or, a prompt where a list can be pasted in at run time)

I'm pretty sure, that list of values for your prompt has to be accessible via the Reporting Server. If the data your user has to pass to the prompt is on his/her laptop, an upload is inevitable.

if you hop in your time machine.... Report Assistant had this functionality back in version 7.6.7. I don't know whatever happened to that feature. I doubt it was ever rolled into InfoAssist. Maybe Chuck can shed some light on what happened with that feature and whether IBI plans to bring that back into InfoAssist at any time in the future.

I think Jason said:

quote:

This would not be using infoassist..just a standard report.

This is very easy. Create a Launch Page that display a list of as many values as needed. I use the following as an eexample

Enter Parameters

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter aParticipant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

Enter a Participant ID to Search by:

If you can get your user to create a simple text file of the required IDs (educate them on how and what format etc.), you should easily be able to prompt for a file name that exists on a "share" and then just write you code to make basic checks etc.

This is an example for Windows based code.

-DEFAULT &USRFILE = 'FOC_NONE'
-SET &USRFILE = &USRFILE.File containing list of IDs (\\\\server\\share\\filename).;

-IF &USRFILE EQ 'FOC_NONE' THEN GOTO FILEOK;
DOS STATE &USRFILE
-RUN

-IF &RETCODE EQ 0 GOTO FILEOK;
-TYPE File &USRFILE does not exist or cannot be accessed.
-EXIT

-FILEOK
-SET &USRFILEF = IF &USRFILE EQ 'FOC_NONE' THEN 'FOC_NONE' ELSE 'USERLIST';
-* FILEDEF the provided file to allow a WHERE clause to be used against it.
FILEDEF &USRFILEF DISK &USRFILE
-RUN

TABLE FILE filename
....
WHERE id_column EQ (&USRFILEF) -* If the value of &USRFILE is 'FOC_NONE' this line is effectively ignored

T

One thing to remember is that if you are using this method against MS SQL or Oracle then you should not have more than 1000 entries in your file - otherwise you will receive an SQL error!

T