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.
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.
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 pickThis message has been edited. Last edited by: FP Mod Chuck,
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...
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?
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: 2128 | Location: Customer Support | Registered: April 12, 2005
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.