Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Prompt for a file or a list at run time and use to filter a report
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Prompt for a file or a list at run time and use to filter a report
 Login/Join
 
Member
posted
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,


WebFOCUS 8009
 
Posts: 14 | Registered: March 27, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1915 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1849 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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)


WebFOCUS 8009
 
Posts: 14 | Registered: March 27, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1849 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I think Jason said:
quote:
This would not be using infoassist..just a standard report.


WebFOCUS 8206, Unix, Windows
 
Posts: 1849 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Guru
posted Hide Post
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:


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5684 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5684 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Prompt for a file or a list at run time and use to filter a report

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.