Forum Discussion
Create a report per Worker ID with different datasets feeding different tablix
I'm working for a client migrating from oracle reports to PowerBI reports. I took the queries already written for a specific report and created 3 datasets:
Header - includes info about the worker, including a WorkerID
Earnings - inludes info per worker, per day, per earning type, of sums of earnings. Also has worker ID.
Deductments - inludes info per worker, per day, per deductment type, of sums of deductments. Also has worker ID.
Now what i need should look like this, per worker:
Now i tried to make a list with textboxes and tables nested. However, since the header info comes from one dataset, the earnings from another and deductments from another, I was unable to make them repeat together. When i nest a table of earnings into the list where the header is already built, the dataset changes automatically from Earnings to Header.
This is a oracle DB and there is no connection between datasets.
Can someone share a workaround or a solution? I'm pretty sure its a common request but I can't seem to find much info online about it.
Hi vla2a ,
Thanks for reaching out to Microsoft Fabric Community.You are right that semantic models are a Power BI Desktop and Service concept and are not something you can create within Power BI Report Builder.
In Report Builder, each dataset is independent and a data region such as a tablix can be bound to only one dataset. Because of that, when you place a nested tablix inside a list or another tablix, it does not maintain a separate dataset context, which is why your Earnings tablix is switching to the Header dataset.
To work across datasets, you need to use supported patterns such as Lookup or LookupSet functions, or pass parameters into subreports.
Reference:
Report Builder functions - Lookup function in a paginated report (Report Builder) - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
LookupSet function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft LearnAnother option is to shape the data at the source so that Header, Earnings, and Deductions are returned in a single dataset. This is generally the most stable approach for paginated reports and aligns with Microsoft guidance on preparing data for reports:
Data retrieval guidance for paginated reports - Power BI | Microsoft LearnSo in your case, you can keep the Header dataset as the main grouping by WorkerID and use LookupSet to bring in related Earnings and Deductions rows, or use subreports with WorkerID as a parameter. If modifying the Oracle query is possible, combining the data into one dataset should simplify the layout and grouping.
Hope this helps. Please reach out for further assistance.
Thank you.
5 Replies
- lbendlin
Super User
Even if the data comes from different Oracle queries you can still join them in the semantic model if you have join columns. There are also things like COMBINEVALUES and TREATAS that can emulate composite keys.
I can't find anything about creating semantic models in PowerBI Report Builder. As far as I could find, semantic models usually come from PowerBI Service after you build them in PowerBI f.e., but i'm pulling the data from a Oracle DB directly.
Is there a way of creating connections between datasets in PowerBI Reports Builder? I couldn't find info on it. I can only find info about PowerBI Desktop.
v-veshwara-msftCommunity Support
Hi vla2a ,
Thanks for reaching out to Microsoft Fabric Community.You are right that semantic models are a Power BI Desktop and Service concept and are not something you can create within Power BI Report Builder.
In Report Builder, each dataset is independent and a data region such as a tablix can be bound to only one dataset. Because of that, when you place a nested tablix inside a list or another tablix, it does not maintain a separate dataset context, which is why your Earnings tablix is switching to the Header dataset.
To work across datasets, you need to use supported patterns such as Lookup or LookupSet functions, or pass parameters into subreports.
Reference:
Report Builder functions - Lookup function in a paginated report (Report Builder) - Microsoft Report Builder & Power BI Report Builder | Microsoft Learn
LookupSet function in a paginated report - Microsoft Report Builder & Power BI Report Builder | Microsoft LearnAnother option is to shape the data at the source so that Header, Earnings, and Deductions are returned in a single dataset. This is generally the most stable approach for paginated reports and aligns with Microsoft guidance on preparing data for reports:
Data retrieval guidance for paginated reports - Power BI | Microsoft LearnSo in your case, you can keep the Header dataset as the main grouping by WorkerID and use LookupSet to bring in related Earnings and Deductions rows, or use subreports with WorkerID as a parameter. If modifying the Oracle query is possible, combining the data into one dataset should simplify the layout and grouping.
Hope this helps. Please reach out for further assistance.
Thank you.- v-veshwara-msft
Hi vla2a ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.
