Forum Discussion
Report Builder connecting two data sources into a dataset
Hello,
In Power BI Report Builder I'm trying to connect two different data sources, AS/400 and Oracle. The AS/400 has the info I need to show district and facillity alignment. While the Oracle houses the data I'm trying to collect. The only column these two tables have in common is the facility number. I'm trying to create a parameter so people are able to search by district in the AS/400 and have that filter the facility in the oracle dataset. In a regular dashboard I would noramlly use either the merge function in transform data or the model view to connect the two table I'm needing.
Is there a way to do this in report builder by either in the dataset query, making another table, or another way?
The oracle dataset is too large to run in a power bi dashboard (has over 14 million lines per day) so I moved it to report builder. So creating this in a dashboard is not feasible.
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.
5 Replies
- Juan-Power-bi
Super User
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. 
sevenhillsFYI, this approach has some limitations but you can give a try:
SSRS = Power BI Report Builder = rdl reports.
- v-saisrao-msft
Community Support
Hi LSeitz,
Have you had a chance to review the solution we shared by Juan-Power-bi ,sevenhills? If the issue persists, feel free to reply so we can help further.
Thank you.
- cengizhanarslan
You cannot directly join two different data sources (AS/400 + Oracle) inside a single dataset in Report Builder.
Step 1) Dataset for District (AS/400)
Create a dataset (AS400 source):
Step 2) Dataset for Facility (AS/400, filtered by District)
SELECT FacilityNumber FROM AS400_Table WHERE District = districtCreate a Facility parameter:
- Available values → from this dataset
- This makes it dependent on District
Step 3) Main Dataset (Oracle)
SELECT * FROM Oracle_Table WHERE FacilityNumber IN (@Facility) - v-saisrao-msft
Hi LSeitz,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
