Focal Point Banner


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 myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Solved: Excel Plugin - Multiple Queries in 1 Excel Workbook

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Solved: Excel Plugin - Multiple Queries in 1 Excel Workbook
 Login/Join
 
Member
posted
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. Songy

This 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, 2010Report This Post
Guru
posted Hide Post
Eric,

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

http://infocenter.ibi.com/wf80...ge/source/opener.htm

Also if you search thru the forums for NOBREAK you'll find plenty of examples

Cheers

Stu


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Member
posted Hide Post
StuBouyer

Thanks for pointing me in the right direction.

I will do some experimenting and report back my results.

Eric J. Songy


WebFOCUS 8.0.09
IBM i
HTML,PDF,Excel
 
Posts: 8 | Location: New Orleans, La | Registered: March 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Solved: Excel Plugin - Multiple Queries in 1 Excel Workbook

Copyright © 1996-2020 Information Builders