I am looking for some sort of technical description of what ...

[robert fuschetto]

I am looking for some sort of technical description of what happens when an IBI fex is run. Right Click Run on the fex name.

What we experience:

Right Click Run

 

We wait, anywhere from a minute to 2 minutes for parameters to appear.

We select values.

We press the run button.

Data is returned in anywhere from 15s to 1minwhich we find acceptable.

*If the user picks a specific DEPT, data is returned much quicker than if they had chosen an Country. We are good with this part.

 

The fex hit a table with 11M rows. It prompts for 5 items. Selection is applied and data extracted into a HOLD file. Some Calculations are done off the hold file etcand a report pops up. Again form the time the user enters parameter values and hits run to when the report pops up varies but the return time is acceptable.

THE QUESTION iswhat is webfocus doing during the time I right click the fex name, click runand wait up 2 minutes for the parameters to appear

[Martin Yergeau]

It extrating data to create the filters choices.

So, depending on how you have built your parameters filters and from which source of data, it may have to query your table with 11M rows.

And since its a parameters list of choices, need to display only distinct values.

At the end, if you are missing indexes on the 11M rows table, it may takes a while to have the data returned.

A lot of things may occurs and differents reasons may explain that, but its the main one that I can see.

[Evan Brown 2]

I got this in an IBI presentation once:

What Happens When You Run A Report

Locate The File Description(s) And Data Files

Determine Access Method (SUFFIX=)

Select Records Based On Database Values

Determine Values Of Defined Fields

Select Records Based On Defined Fields

Sort The Data

Prepare Individual And/ Or Aggregated Values

Resolve Computed Fields

Select Records Based On Total Tests

Process BY TOTAL

Format The Report

[robert fuschetto]

MartinY,

that is what I feared. On the HTML page I can redirect parameters to Dictionarybut not in the fex. To be honest, on the HTML page, either way I must wait for the reports to load it would seem, the only value I have found in redirecting parameters on the HTML page to dict table is that chaining is faster.

I guess I should see if the table is Indexed.

[Martin Yergeau]

robertf:

 

I guess I should see if the table is Indexed.

 

 

The above is one option and one tthing to check.

Another is to create specific tables for parameters list only from which youll go against.

E.g. with an overnight process (using Report Caster as an example) you can read your 11M rows table and extract the values against which you will create filters/parameters and hold that.

Then use the hold table to feed your list box/parameters.

Assume that in my below sample GGSALES change everyday.

Let say that i would like to create a list of product per city.

It may take a while to extract the list directly from GGSALES meaning that each time that I need that list of city/product I need to go against the 4317 rows of the table (actual since).

But if I, over night, I extract that list and hold it I then have a table of only 117 rows.

Using that hold table to create and feed my parameters list will be much more faster than reading each time GGSALES.

And I can even create an index on that hold file.

TABLE FILE GGSALES

BY CITY

BY PRODUCT

ON TABLE HOLD AS MySavedPath/MyParamList FORMAT XFOCUS INDEX CITY PRODUCT

END

I normally dont use the source table as the source for the parameters list, when possible, I hold the parameters values in a seperate table. Especially for large table as yours.

[robert fuschetto]

MartinY,

What you describe I already have in place and use with the HTML page. Say I have 5 parameters on the HTML page all pointing to the 11M row table. Chaining gets very sloooooow. So I redirect the parameters to, wellthe exact type of table you describe. Now chaining is fast!

Are you saying in the report fex, NOT HTML PAGE, I can somehow redirect the parameters away from the 11M rows of data instead to this small table How so

I dont follow this at all:

ON TABLE HOLD AS MySavedPath/MyParamList FORMAT XFOCUS INDEX CITY PRODUCT

So lets say we hit our 11M row table

TABLE FILE BIGTABLE

SUM Amt1, Amt2 Amt3

By Country, State, Provence, Country

WHERE

Country MultiSelct any country value in BIGTABLE

State MultiSelct any State values in BIGTABLE

etc

ect.

When I right Click run, it goes through the 11M rows to populate the dropdowns for the user. I wait a minute or two then run the report.

So, what would I do in my fex to make those drop downs get populated by my smaller table that I already have containing just the 1000 or so rows of Country,State,Province etc values

you have me excited here!

[David Beagan]

Just like you redirected the HTML pages parameters to new tables, you can do so with your report.fex. I assume you have created the report in InfoAssist which doesnt seem to offer you the option to redirect to new tables. But you can go into the code editor and find where it has, for example,

WHRE STATE EQ &STATE.(FIND STATE IN BIGTABLE).

 

and change it to

WHERE STATE EQ &STATE.(FIND STATE IN STATETABLE).

[robert fuschetto]

If I can get this to work it would be amazingnever knew of this.

Is the syntax that simple Further, if I do this will I lose the GUI interface. We are working in App Studioinfoassist for charts.

So when I start the report in the GUI I reference BIGTABLE. Must i make some sort of reference to, say SMALLTABLE before I adjust the WHERE

[Martin Yergeau]

Robert, your best guest is : try it.

Make a copy of your fex then from the copy try the new process

[robert fuschetto]

IT WORKED! Question, since I can now NO LONGER get into the GUI (unless I first go in to text mode and comment out the WHERE conditions)is there a way around thisnot complaining mind you!

[Martin Yergeau]

I dont think so. In a sense, GUI is very limited and not that flexible compared to what we can do when coding.

This is the greatest reason why a hate GUIs : lack of flexibilities

When using GUI you normaly end up with a robust result (because you have not been able to add something that does not respect the rules) but often loosing performance and flexibilities as you encounter.

As you realized, the problem do not rely on the report itself that goes against the 11M rows table, but the parameters prompting that is not efficient reading 11M rows table.

[Walter Brengel]

Hi Evan,

What you shared is the behind the scenes of what happens from the Server point of view after all the client processing has happened.

Walter

[Debra Waybright]

@RobertF, did you try putting your WHERE clause in an Expression in the GUI (Choose new expression in the filter window) This should let you type in the WHERE clause you need without breaking your ability to edit the report in InfoAssist.

[john cullen]

Hello Martin,

I think I have a way you can get this to work in the GUI.

Normally, this is used for Modify, but I think you can leverage it for TABLE.

In the describing data manual, there is an attribute called ACCEPT, go find that, research it.

If you put the accept attribute in your master file, then InfoAssist / autoprompt should use the file in the accept declaration, to do the lookup.

so, build your crossreference files that you use for chaining, and make them the accept attribute for that field in your main master file InfoAssist / Autoprompt will then use the crossreference file instead of a table scan.

My confidence in this is not 100%, but it is very high that this will work. I have not used it recently, but I used it in the past. It is subtle, and not often thought about, because ACCEPT is usually used for modify, but it is can be used (at least I think it can be used) this way for autoprompt and InfoAssist WHERE statements.

[robert fuschetto]

I repointed the WHERE conditions in the fex to another table as was suggested. Open right up now. THANKS!!!