TABLE FILE GGSALES
PRINT DST.PRODUCT
BY PRODUCT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XML
END

TABLE FILE tablename SUM FST.displayfield BY datafield ON TABLE PCHOLD FORMAT XML
END

-* First proc -
DEFINE FILE account_codes
  ACC_PREFIX/A2 = EDIT(ACCOUNT_CODE,'99$');
END
TABLE FILE account_codes
SUM FST.ACC_PREFIX
 BY ACC_PREFIX
ON TABLE PCHOLD FORMAT XML
END

-* Second proc -
-DEFAULT &Acc_Prefix = 'AA'
DEFINE FILE account_codes
  ACC_PREFIX/A2 = EDIT(ACCOUNT_CODE,'99$');
END
TABLE FILE account_codes
SUM FST.ACCOUNT_CODE
 BY ACCOUNT_CODE
WHERE ACC_PREFIX EQ '&Acc_Prefix'
ON TABLE PCHOLD FORMAT XML
END

quote:

Originally posted by GinnyJakes:
Check the "Customizing Dynamic Parameters" section in the wf761appsgt.pdf manual.

TABLE FILE GGSALES
PRINT DST.PRODUCT
BY PRODUCT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XML
END

Starting in 7.6.10, this is documented in the new HTML Composer manual.

Another thought. Even though your data changes freqently, maybe you could run a Report Caster job to create the 'distinct' file once or several times a day. Why don't you try this at least once to see if it helps the load time for your box.

quote:

Originally posted by GinnyJakes:
Check the "Customizing Dynamic Parameters" section in the wf761appsgt.pdf manual.

TABLE FILE GGSALES
PRINT DST.PRODUCT
BY PRODUCT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XML
END

Starting in 7.6.10, this is documented in the new HTML Composer manual.

Another thought. Even though your data changes freqently, maybe you could run a Report Caster job to create the 'distinct' file once or several times a day. Why don't you try this at least once to see if it helps the load time for your box.

quote:

I don't believe the DST. prefix can be translated into SQL, so all detail records must be returned so WebFOCUS can perform the aggregation and sorting (which is why DST. only works with TABLE).

TABLE FILE CWX_LEGAL0_VLITIGATION_MSTR
PRINT DST.BNSF_ATTY_NME
BY BNSF_ATTY_NME
WHERE BNSF_ATTY_NME NE '';
ON TABLE SET HOLDLIST PRINTONLY
-*ON TABLE PCHOLD FORMAT XML
END

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT  DISTINCT T1."BNSF_ATTY_NME"(CHAR( 22)) FROM
CWX_LEGAL0.VLITIGATION_MSTR T1 WHERE (T1."BNSF_ATTY_NME" <> '
') ORDER BY T1."BNSF_ATTY_NME";
...RETRIEVAL KILLED

? SET AUTOTABLEF

quote:

AUTOTABLEF can optimize all TABLE, EMR, and GRAPH requests except those that include the following syntax:

ACROSS
FOR or BY sort field values value1 over value2...
PCT.
PCT.CNT.
TOT.
Within
Multiple display commands (PRINT, LIST, SUM, COUNT)
Multiple display paths (display fields from multiple paths)

quote:

Originally posted by Tony A:
Dan,

Wasn't there a similar presentation last year by David Glick?

Umun,

To move forward, I would suggest analysing the individual processes that are used to give the end result of your combo box.

Within the WF processing, an AJAX call is made to retrieve the data for use in populating your control. This is a distinct call to your reporting server to get the data, which is then returned to the browser in XML format. Javascript then flushes the existing combo box and repopultaes it with each pair of data items held within the returned XML file.

The first part of this process (the AJAX) can differ depending upon your attributes on the HTML control in question. If the "source" attribute is "typeMas" then the other two attributes "displayfield" and "datafield" are used to create a simple adhoc request of the form -

TABLE FILE tablename SUM FST.displayfield BY datafield ON TABLE PCHOLD FORMAT XML
END

You will see that most people follow that form when they create their own procedure for the XML feed ("source" attribute of "typeFex")

I would suggest that your first analysis is actioned around this process. Check that the data retrieval using a fex of the above form both returns the data that you expect and that it achieves the retrieval in an efficient manner. If the source data is on an RDBMS then ensure that the SQL is as efficient as it can be (e.g. uses indexing when possible and not a table space scan - check with your DBAs). If possible can you use views or even build subset tables (as per Ginny)?

Once you have that process as efficient as possible, then turn your attention to the amount of data being returned (e.g. network traffic) - limit it if you can by using a couple of combo boxes instead of one to reduce the ultimate selection (and therefore the amount of data that the Javascript has to handle).

i.e. If you have account codes that are 12 characters long, then identify whether they can be "ranged". Do some begin with "AA" and others "AB" etc.? If so then make your first combo the account prefix and restrict the second one (by using chaining) to account numbers beginning with the chosen prefix.

-* First proc -
DEFINE FILE account_codes
  ACC_PREFIX/A2 = EDIT(ACCOUNT_CODE,'99$');
END
TABLE FILE account_codes
SUM FST.ACC_PREFIX
 BY ACC_PREFIX
ON TABLE PCHOLD FORMAT XML
END

-* Second proc -
-DEFAULT &Acc_Prefix = 'AA'
DEFINE FILE account_codes
  ACC_PREFIX/A2 = EDIT(ACCOUNT_CODE,'99$');
END
TABLE FILE account_codes
SUM FST.ACCOUNT_CODE
 BY ACCOUNT_CODE
WHERE ACC_PREFIX EQ '&Acc_Prefix'
ON TABLE PCHOLD FORMAT XML
END

As you will find when creating reactive web pages, there are many things to consider and it requires a deal of lateral thinking to overcome some of the obstacles that you will encounter. There is no right or wrong way of achieving it, providing the process is as efficient as you can make it.

T