Hello,

I am trying to build a report to replace a manual process - currently, the team runs bunch of queries and pastes the results in a spreadsheet. They have a set of queries looking for oldest 100 rows and newest 100 rows from a table, looks like this:
Select * from table_name where rownum < 101
order by date_column asc (and separate sql with order by as desc for latest 100 rows)

They are going to build a new table with all the table_names and corresponding date_column_names so that I can use that table to populate a dropdown list - I am thinking of building an HTML launch page where they will be prompted for table_name (I will also design a hidden date_column_name parameter so that I can pass that value to the queries. Based on the table they select, I need to run that query and populate results in a spreadsheet.

I tried doing this by creating a SQL pass thru report and using 2 parameters which I can pass: &table_name and &date_column. How would I control all the columns which need to be printed as output? I am stuck! Please help with any ideas.

Thanks in advance!


P.S.: I am using WF 7.7.03

This message has been edited. Last edited by: Tamra,

What you seem to be asking about is what some call an "adhoc" or "self-serve" report.

Try this fex file to see how WebFOCUS can handle dynamic field names being passed in for processing:

-DEFAULT &PRINT_OR_SUM_FIELDS='''COUNTRY'' AND ''CAR''';
-DEFAULT &BY_FIELDS='''BODYTYPE'' NOPRINT BY ''MODEL''';
-DEFAULT &ACROSS_FIELDS='''SEATS'' ACROSS ''BODYTYPE''';
-DEFAULT &WHERE_FIELDS='''ENGLAND'' OR ''JAPAN''';

TABLE FILE ibisamp/car
PRINT
  &PRINT_OR_SUM_FIELDS.(AND()).PRINT_OR_SUM_FIELDS.
BY
  &BY_FIELDS.(BY()).BY_FIELDS.
ACROSS
  &ACROSS_FIELDS.(ACROSS()).ACROSS_FIELDS.
WHERE
  COUNTRY EQ &WHERE_FIELDS.(OR()).WHERE_FIELDS:. ;
ON TABLE SUBFOOT
""
"Field names and filter criteria used in this report:"
""
"PRINT or SUM fields:  &PRINT_OR_SUM_FIELDS "
"BY fields:  &BY_FIELDS "
"ACROSS fields:  &ACROSS_FIELDS "
"WHERE filter criteria: &WHERE_FIELDS "
END

I am using App Studio and WebFOCUS 8.1.05M, so I cannot say if this will help you or not.

This message has been edited. Last edited by: Squatch,

Thanks for your response, I think I got what I was looking for.