Report Builder connecting two data sources into a dataset

Good news — Report Builder is actually well-suited for this exact pattern using cascading parameters. You don't need to join the two sources directly; you let the parameters do the bridging.
Here's how to set it up:
1. Create a dataset from AS/400 for the district list — a simple query that returns distinct district values. This feeds your District parameter dropdown.
2. Create a second dataset from AS/400 that returns the facility numbers for the selected district — something like SELECT facility_number FROM ... WHERE district = district. This feeds your Facility parameter, which gets automatically filtered based on what the user picked for District.
3. Create your main Oracle dataset with the facility parameter in the WHERE clause — WHERE facility_number IN (@Facility) or = @Facility depending on whether it's single or multi-select.
Report Builder handles the dependency chain automatically — when District changes, it re-queries the AS/400 for facilities, and when the user confirms, it fires the Oracle query with only the relevant facility numbers. The Oracle query never runs until the user has made their selections, which is exactly what you need for a 14M row/day table.
The key thing to configure is the parameter order and dependencies in the Parameters panel — make sure District is listed before Facility, and that the Facility dataset's query references district. Report Builder will figure out the rest.