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,

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.

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. .

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...

perhaps the control can call a query...

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?

still stuck.

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....

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.

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.

Robert

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

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 .