As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
My client wants a Key Performance Indicators Excel Workbook created via WebFocus queries. The Excel Workbook should contain for Spreadsheets (tabs) for 4 Major Components of the Business with a 5th Sheet that contains Totals. Each of these sheets will contain Key Performance Queries with the output going to specific cell ranges. There could be 3 or more queries for each Sheet based on the Key Performance Indicators the client wants to see.
I have tried to generate this KPI Excel Workbook using several different methods: 1. Excel Plugin with Multiple Queries 2. Excel Plugin with Structured Ad Hoc Forms 3. Excel Compound Document query via InfoAssist
I have run into issues with each of these that prevent me from getting the results that I require.
1. Excel Plugin with Multiple Queries I have tried putting more than one query in a spreadsheet using the Excel Plugin Can queries generated via the Excel Plugin have Selection Criteria that is based on values in a cell? For example, if I create a query that will select data for a specified YEAR and MONTH, I drag the YEAR field to the Filter and InfoAssist requires me to enter a number. It does not give me a way to specify that I want the query to test the YEAR against the value in a cell. I would like to be able to change the value in the cell and rerun the query. I don't want users to have to "Change Every Query" in the spreadsheet. All queries will be executed for the same YEAR & MONTH. So I would like to have the user key the Year in one cell and the Month in a second cell and be able to click a Refresh button to rerun all of the queries. They should not have to go to each query to change the selection criteria to the new Year & Month that they want to run.
2. Excel Plugin with Structure Ad Hoc Forms I have tried doing this with Structured Ad Hoc Forms but again, you can't run multiple queries from one Structured Form and have the outputs go on the sheets where I need them to go.
3. Excel Compound Document via Info Assist I have also tried to create a Compound Document FEX to generate run all of the queries and get the results to the correct places on the different sheets. However, every query that is run in the Compound Document goes to a separate sheet which is not what I want. I wants the output of several queries to go on the first sheet, then the output of several more queries on the second sheet, etc.
Ideally, an Excel Compound Document should allow you to specify a Sheet# and Cell Range (or Anchor Cell) for the target output for each query that is run or at least. With all of the emphasis in recent years on Dashboards and Key Performance Indicators, I would have thought that this would be a simple thing to do in WebFocus. Not only is it not simple, it appears impossible to do what my client has requested.
Any suggestions would be appreciated.
Eric J. SongyThis message has been edited. Last edited by: EJSongy,
WebFOCUS 8.0.09 IBM i HTML,PDF,Excel
Posts: 8 | Location: New Orleans, La | Registered: March 12, 2010
It is simple - just not simple with the GUI tools and the new "Composer" tool for creating Compound reports.
You want to look at the "Creating Reports With WebFOCUS Language" manual, specifically the "Creating a Compound Excel Report" -> "Guidelines for Using the OPEN, CLOSE, and NOBREAK Keywords and SET COMPOUND" in the "Linking a Report to Other Resources" chapter