Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Chaining Question ApStudio 8206
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Chaining Question ApStudio 8206
 Login/Join
 
Master
posted
Let me try to describe as simply as possible. We have reports / charts. They execute from HTML Pages. As is the case with 90% of our reporting, going down the left side of the given dashboard are chained controls:

Country
State
Town
Office
Salesman

These are multi selects.
They have the option of selecting ALL at any point.
The user picks the desired combination.
The reports / charts etc generate.
The data source is a single SQL table: tblMASTERDATA.

We will soon acquire data from THREE *NEW* countries but they are from new 'locales' considered to be in new REGIONS for reporting purposes. Over time REGION will need to be added to our reporting structure and all reports/charts etc. HTML pages would need a 'Region control' added that would sit above the 'Country control' in the chained list of controls...UGH!

Is there a better way?

My thought was to add a sql table: tblREGIONCOUNTRY.
We then populate it with Region and Countries therein.

We then add a single control to each HTML page, which prompts the user for Region; the data source being tblREGIONCOUNTRY. This should get us the 'countries' with in the region.

Here is the trick....since our chaining starts with: country (again from tblMASTERDATA), is there a way to feed the Countries to the Country control from tblREGIONCOUNTRY rather than tblMASTDATA but still have the country control chained to State then Town etc.


On the parameters screen I see Options where you bind stuff together but I have had no luck. Again, I'd like the user to pick a region (from tblREGIONCOUNTRY) to obtain the Country list and have that feed existing Control: Country...and still have the chaining work from country down.

Any thoughts?







The desire is to report those Country on there own..never combined with anything else.
They ideally want the Country dropdown to contain
They don't like that idea. Instead they want to be able to pick

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Robert

How volatile is the data. I am thinking on a daily basis you could use ReportCaster to create a FOCUS HOLD file that extracts the data in the hierarchy and use that data for the filters.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2003 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Master
posted Hide Post
That would work except different reports use different tables...it's not really just tblMASTERDATA.....Charges may come from one data set, Pmts another etc...I guess I simplified it too much. .


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
further, I don't think that would really work..though I like the idea..one table drives all selection everywhere. Trouble is we may be budgeting 'future' countries but there are no actuals yet so they would not want those countries in the dropdown etc. Again I oversimplified...I really want to know if one control from one table can feed another control that was using another table and then have chaining work from there down...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
perhaps the control can call a query...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
so if I take my country control, the one pointing to tblMASTERDATA, and instead redirect it to a query (fex) against tblREGIONCOUNTRY (select Region, Country from tblREGIONCOUNTRY) .....I can set the display to show Region but value to be country.....so I see dropdown values for Country like
NorthAmercia,
NorthAmercia,
NorthAmercia,
SouthAmerica,
SouthAmerica,
Europe

Each one of course is associated with a specific country. Interestingly the chaining works. I know the first table entry is NorthAmerica / Canada. If I pick that one, the chaining below works..I see the values in state control as populated for Canada.

What I really want is to have the Regions appear once and the countries therein passed to the Country control....possible?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
still stuck.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
for anyone who can actually follow this, I got close and will likely open a case with IBI.

I added a REGION input control to my form.
It gets its data from tblREGIONCOUNTY.REGION.

I created a fex against tblREGIONCOUNTRY. It prompts for: Region and returns the countties it finds.

On my existing Country input, I pointed it the above fex. The fex prompts for: REGION. It picks the value up from the REGION input control.

I linked the Region parameter to the Country parameter.

THIS PART WORKS FINE!!!

However, my State control now says: -Make Selection-, in other words the chaining from Company to State no longer works....


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Robert

I will have time later to see if I can replicate this. I have always had my hierarchies in one table so I don't know what happens when one of them isn't. In the mean time one other suggestion I have is to use a procedure that selects the values instead of using the tables directly.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2003 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Master
posted Hide Post
Appreciate it. A procedure will not work 100%. We would end up with all sorts of stuff the user might select that would not be in the final table. Using the table insures what ever they select will yield results. If we moved towards something generic they might be selecting items that were put in for testing, or are for future use or have not been used in a while etc etc.

HOWEVER..what I now have set up is half working and it is a procedure.

I added REGION as a control to the HTML page. It points NOT to the data table (since REGION is not in there) but rather to a new table containing the region and Country...one entry for each Region/County combination.

I added a fex that when run prompts the user for Region, queries this new table and returns the countries therein.

I redirected the COUNTRY control away from the main table, to this new query.

I linked the RGION to the COUNTRY parameter.

When the form is run, the user select a REGION..which feeds the query that is run in the background. It populates the Company control in the exact manner we want. All this runs seamlessly...

The trouble is the chaining after Country no longer works...I do not know why.

We have a few reports, where the COUNTRY is derivative of a procedure and the chaining DOES work.

The only thing different hear I can see is we added Region to the top of the chain as a delimiter.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 580 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Robert

Sorry.. I wish I had the answer to that!


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2003 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
I would break all bindings and add them again if I were you, check the binding settings carefully, check the ptocedures carefully if they contain the right columns for chaining .


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 441 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Chaining Question ApStudio 8206

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.