Hello,

I am fairly new to WebFocus and not aware of all that it can do.. Currently using WF 7.7.03 Developer Studio where I created a SQL pass thru report.. For this report I need a user input parameter in the form of drop-down list which is dynamic or obtained from a field from one of the table's from my SQL query... How do I build a drop-down list for a SQL pass thru report?

Example:

SELECT A, B, C
FROM TABLE A
Where date = &Date (user selected from drop-down list of dates)

The drop-down should look like 01/01/2016, 01/02/2016, etc.

I tried using this: '&variable.(FIND fieldname IN datasource).[description.]' .. but it throws an error since SQL pass thru reports don't have a master file...

Please help.. Thanks!

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

I tried this on the Northwind Orders table and it seems to work.

  
ENGINE SQLMSS SET DEFAULT_CONNECTION MY_SQL_CONN
SQL SQLMSS PREPARE SQLOUT FOR
SELECT * FROM ORDERS
END
TABLE FILE SQLOUT
PRINT
     Freight
     ShipName
     ShipAddress
     ShipCity
     ShipRegion
     ShipPostalCode
     ShipCountry
BY  LOWEST OrderID
BY  LOWEST CustomerID
BY  LOWEST EmployeeID
BY  LOWEST OrderDate
BY  LOWEST ShipVia
WHERE ShipCountry EQ &ShipCountry.(FIND ORDERS.ORDERS.SHIPCOUNTRY,ORDERS.ORDERS.SHIPCOUNTRY IN orders|FORMAT=A15V).ShipCountry:.QUOTEDSTRING;
END

I think you'll need to have a synonym for orders to be used in the WHERE test.

I can't do this because I am not displaying the date on the report, just need it to be an input parameter and eventually filter the report..

Thanks.

I'm not sure if I understand. Could you post an example of what you're doing and the error it causes? The code above is an example. Specifically, look at the WHERE test. It is doing a FIND off of the WebFOCUS synonym for orders not the actual table.

There is no need to display your date field for it to work. Just using it in your filter will be good enough.

Here's an example of the WHERE test using the Orderdate field

TABLE FILE SQLOUT
PRINT 
     Freight
     ShipName
     ShipAddress
     ShipCity
     ShipRegion
     ShipPostalCode
     ShipCountry
BY  LOWEST OrderID
BY  LOWEST CustomerID
BY  LOWEST EmployeeID
BY  LOWEST ShipVia
WHERE OrderDate EQ DT(&OrderDate.(FIND ORDERS.ORDERS.ORDERDATE,ORDERS.ORDERS.ORDERDATE IN orders|FORMAT=HYYMDs).OrderDate:.);
ON TABLE PCHOLD FORMAT HTML

END

I still get the same error: (FOC205) The description cannot be found for file named: "DW"

Here's my code:
ENGINE SQLORA SET DEFAULT_CONNECTION DWQA
SQL SQLORA PREPARE SQLOUT FOR
SELECT
A.ABC AS "ABC"
,A.XYZ AS "XYZ"
...........
...........
FROM REPORTS.TABLE1 A
WHERE EXISTS (SELECT 1 FROM DW.RUN_PERIOD B WHERE B.PERIOD = A.PERIOD)
AND A.PERIOD = '&B_PERIOD'
END
TABLE FILE SQLOUT
PRINT
ABC
XYZ
.......
.......
WHERE PERIOD EQ &B_PERIOD.(FIND DW.RUN_PERIOD.PERIOD, DW.RUN_PERIOD.PERIOD IN DW.RUN_PERIOD).PERIOD
ON TABLE SUBHEAD
............
............
END

Hey Nova,

Ensure you have created a synonym/master file for all tables/views being used; and ensure that they are accessible to the domain they are being called from if working in the managed reporting area (check if they are in the app path for that domain).

If you're just working in the reporting server area, then ensure your qualifiers are correct.

SQL:
[server].[database].[schema].tableName.columnName

WebFocus:
[table].[segment].fieldName

Good luck!

Hello,

Thanks for your response.. But I am working on a SQL pass thru report which does not use a master file and hence faced this problem with creating a dynamic drop down list for user-input parameters...

Any thoughts if I don't have a master file to use for this?

Thanks!

You can run a SQL Passthru to create a list of values for the where test, HOLD the output to a temp file and then use that list of values for your actual request. You'll still have to use the synonym for a drop down list but the synonym is for the HOLD file which is created dynamically. Something like this:

SQL SQLMSS PREPARE SQLOUT FOR
select orderdate from orders
END
TABLE FILE SQLOUT
BY  orderdate
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS 'baseapp/od_lov' FORMAT BINARY
END
SQL SQLMSS PREPARE SQLOUT FOR
SELECT * FROM ORDERS
END
TABLE FILE SQLOUT
PRINT 
     Freight
     ShipName
     ShipAddress
     ShipCity
     ShipRegion
     ShipPostalCode
     ShipCountry
BY  LOWEST OrderID
BY  LOWEST CustomerID
BY  LOWEST EmployeeID
BY  LOWEST OrderDate
BY  LOWEST ShipVia
WHERE OrderDate EQ DT(&OrderDate.(FIND OD_LOV.OD_LOV.orderdate,OD_LOV.OD_LOV.orderdate IN od_lov|FORMAT=HYYMDs).OrderDate:.);
END

Nova27,

Please see my suggestion to your other post .