I have been trying to figure out for a while how to display a report prompt to a report consumer that only displays a distinct subset of the items from the prompt's (dynamic) data source.

This is a common occurence in Web-based reporting. For example- a dimension such as "Store" exists for the purpose of query and retreival and it may contain both current and old stores. In the case at hand the preferred outcome is to be able to use this dimension as the data source of a 'Select a store' prompt yet filter out all of the irrelvant stores such as those that are no longer active or do not meet some other criteria.

I assume there must be a way to accomplish this rather basic capability- can anyone please enlighten me?

Thanks!

This message has been edited. Last edited by: <Michael Goshey>,

Yes it can.

If you are a user of WF I would ask you to update your signature with the installed systems (version etc).
What you want is indeed one of the basic functions that you will learn during a 2-3 days course.
It is however a bit to complex to explain the whole procedure here.

Thanks Frank. I'll certainly update the signature. However, forgive me for not accepting on blind faith that this is simple (or can even be done) in WebFocus. I've taken a number of courses, studied the app pretty thoroughly, scroured the helps and documentation for days before submitting this...and I work with a couple of long-time WebFocus/Focus users. Frankly, we are stumped. We still hold out hope it can be done but you would make me (and our 1000 report consumers) very happy if you could identify a 'how to' for this....I remain skeptical.

This message has been edited. Last edited by: <Michael Goshey>,

The method that gives you the most flexibility is to create a procedure that gives you the values you want. Next, you can use the GUI to create your HTML page for your main fex. Make the parameter you want to populate dynamic and tell it to use a procedure instead of a table. You can then choose the procedure you created to select the values you want. As Frank mentions, there are additional details about how the first procedure must be held to get it to work properly, and more in-depth information. It's all in the documentation. It's also a specific topic covered in training courses.

Thanks Darin- that gives me some specific direction to fish in. We've already built the reports in the HTML layout painter and all other prompts work great. Its just that there are certain prompts for which a limited subset of data will always be the norm.

Thanks for the tip- will investigate.

This message has been edited. Last edited by: <Michael Goshey>,

Darin, Frank, all:

Updated my sig.

Both of you mentioned this capability exists in the documentation but I am relatively certain it does not- at least, not in any of the materials I have (and I have the full/most recent CD of all help documentation).

I will go ahead and contact IBI directly but would still love to have someone chime in that has actually done it and can cite an example or a specific area of the documentation where it exist. Have also never seen it addressed on this or any other forum.

In kindergarten, I learned that when you ask for people's help, you should at least try their suggestions.
Instead of spending your time telling people they don't know what they're talking about, maybe you should spend a little more time in the docs. I (and I can probably speak for Frank as well) have done this many times. I took five minutes and quick searched manuals and Dev Studio help and found so many examples of what you're trying to do that it would take pages to paste them all here. So instead of doing that, I'll just say (again) it's all in the documentation. Try a little harder.

This message has been edited. Last edited by: Darin Lee,

Darin:

I'm convinced from your comments that we're talking about two different things but since no specifics or references were provided I can't say for sure.

Not sure what I said that caused offense but I apologize.

Michael,

From your post I gather that you are using an HTML launch page created in the painter, therefore you are half the way there.

Within the JavaScripts provided by IB (ibirls.js to be precise) there is a function that loads a combo box with the result set from an XML formatted WF report or with some fields from a table.

If I take what happens during a loading for a master sourced combo box we can extrapolate the requirements for a focexec sourced operation.

On the combo box TAG line you will note the attributes for SOURCENAME, SOURCETYPE, DATAFIELD and DISPLAYFIELD. These are used within the JS to build an adhoc focexec to supply the details to be loaded.

The JS builds a request line that would go something like this -
IBIF_adhocfex=TABLE FILE [sourcename]%0D%0ASUM FST.[displayfield]%0D%0ABY [datafield]%0D%0AON TABLE PCHOLD FORMAT XML%0D%0AEND

This produces an XML file that contains TD tags with "colnum" attributes of C0 and C1. These are used within a filter to populate the combo box.

To be able to use your own focexec to populate the combo box all you need to do is reproduce what the JS does (and allows for) by writing a basic focexec that returns two columns in XML format -

TABLE FILE CAR
SUM FST.CAR
BY CAR
WHERE COUNTRY EQ 'ITALY'
ON TABLE PCHOLD FORMAT XML
END

Run that and you will get -

  <?xml version="1.0" encoding="ISO-8859-1" ?> 
- <fxf version="1.0" data="hold">
- <report records="2" lines="2" columns="2" rows="2">
  <target format="" version="" type="" destination="HOLD" /> 
- <column_desc>
  <col colnum="c0" fieldname="CAR" alias="CARS" datatype="char" width="16" focus_format="A16" description=""
       accept="" help_message="" title="" within="" property="" reference="" valign="left" /> 
  <col colnum="c1" fieldname="CAR" alias="CARS" datatype="char" width="16" focus_format="A16" description=""
       accept="" help_message="" title="" within="" property="" reference="" valign="left" /> 
  </column_desc>
- <table>
- <tr linetype="data" linenum="1">
  <td colnum="c0">ALFA ROMEO</td> 
  <td colnum="c1">ALFA ROMEO</td> 
  </tr>
- <tr linetype="data" linenum="2">
  <td colnum="c0">MASERATI</td> 
  <td colnum="c1">MASERATI</td> 
  </tr>
  </table>
  </report>
  </fxf>

Now, all you have to do is tell the JS module that you want it to use the results from a focexec instead of a master. So, within the parameter tab, choose DYNAMIC in the control values, Procedure and then choose the fex that you want to execute to populate the combo box.

This is a simplistic view of what happens as it would take an age to go through a blow by blow of what exactly happens, but this should give you the basic understanding to assist you through your task.

Good luck

T

Now that's interesting.

In 7.1, for Francis hated autoprompt, you could use syntax like:

TABLE FILE CAR
.
WHERE COUNTRY EQ &COUNTRY.(OR(FIND COUNTRY IN CAR WHERE COUNTRY NE ‘FRANCE’)).Select a Country. ;
END

to restrict the data returned.

In 7.6 this does not work, the WHERE clause is ignored. I've raised this with IB TS.

quote:

I assume there must be away to accomplish this basic rather basic capability- can anyone please enlighten me?

All you need to do is select 'Procedure' instead of 'Data source' for your parameter

Create a fex such as CLIST.fex containing something like

TABLE FILE STORELIST
BY STORE
WHERE STORE_STATUS EQ 'OPEN'
ON TABLE PCHOLD FORMAT XML
END

use CLIST.fex as the procedure name

If thats what you need then it is fully documented in the devstudio online help
under
Contents
Developing Reporting Applications with Graphical tools
Designing a User Interface for a Web Application with the HTML Layout Painter

One thing to add John is that if the site profile or previous processing contains SET HOLDLIST = PRINTONLY the output will only have one column in the XML and therefore the combo box will not populate as expected.

T

Tony correct yet again, and unless you know your profile settings not an obvious
place to look for a trouble maker as even with ECHO=ALL in your fex you would not see it.

lets change the request to

TABLE FILE STORELIST
SUM STORE
BY STORE
WHERE STORE_STATUS EQ 'OPEN'
ON TABLE PCHOLD FORMAT XML
END

That should cover the little begger.

Alan,

What's the hottrack number for your case?
I'd like to cross ref. it with another one and
I have not been able to locate it via the advanced search.

Alan, Tony and John:

I'm indebted to you all- and to Darin and Frank as well for their help. This is precisely what I was hoping for- a few specific comments that reflect a precise understanding of what we're trying to do and a concrete suggestion of how to resolve.

I had seen and played extensively with the dynamic 'Procedure' choice (in the HTML Painter) for populating a form control in contrast to using a 'Data Source', however the specifics of how to format the fex were not explained in the sparse documentation of the feature. As a result, when I tried it all it would do is fire the fex in a separate window rather than use the resultset from the fex to populate the control. I'll include the documentation reference here in case others are interested but it only tells the 'beginning' of the story.

quote:

Using Form Controls to Supply Parameter Values
Select the Dynamic option in the Control Values section. To find the desired source file you want to retrieve values from, select the Data source option to retrieve values from a .mas file, or select the Procedure option to retrieve values from a .fex file. Click the browse (...) button, select a source file from the Get source file dialog box that opens, and click Open. For the Data source option only, select the data field that has the desired values from the tree structure displayed at the bottom of the Parameters tab. The data field you select will be populated in the Value field.

I am out of the office until next week but look forward to implementing your suggestions and will update the topic then with more info then.

Thank you both! I'm extremely grateful for the help.

This message has been edited. Last edited by: <Michael Goshey>,

JG

Not got a case number yet, went through Raoul Sabbagh and awaiting a response. Will update when I know.

Alan, I wanted the info for Raoul, I did not realize it was the same case.

He's done a repro and I've told him to raise the case as it's either a very clear bug or a totally undocumented change in functionality.

Here's the exact reference in the docs that talks about how to create the fex.
Under
Developing Reporting Applications with Graphical Tools> Designing a User Interface for a Web Application With the HTML Layout Painter >
Using Form Controls to Supply Parameter Values > Customizing Dynamic Parameters

You have the option to allow customized retrieval of dynamic parameter values. By using a custom procedure, you can use the FOCUS language to utilize temporary HOLD files, filtering, etc. This allows the HTML Layout Painter to better integrate with a multitude of large and/or proprietary data sources that may require unique retrieval methods. The procedure must meet the following criteria:

The procedure must return a name and value pair for each parameter value that will populate the list. The first value is the submission value which is passed to the form control when the user clicks the Submit button. The second value is the display value which is what the user will see in the control. These two values can be the same or different.

The procedure must return the two data values on a single data line in XML format (PCHOLD FORMAT XML).

Thanks Darin- I really appreciate the help!

Michael