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.03This message has been edited. Last edited by: Tamra,
WF 7.7.03, Win 7
January 19, 2017, 01:55 PM
Squatch
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,
App Studio WebFOCUS 8.1.05M Windows, All Outputs
January 19, 2017, 03:24 PM
Nova27
Thanks for your response, I think I got what I was looking for.