cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Tablix Groups ( I think)

Hi guys

I wonder if there is a Paginated report guru out there who can help me with the following.
I am writing a pbix to report accounting information (profit and loss, balance sheet)- and all is running well.
Fact table with financial data
Dim tables for departments and sites
Dim table for line structure in right order:

ie Report ID, Report Name,Line 1 Turnover, Line 2 COS, Line 3 Gross Profit, Line4 Expenses, Line 5 Dept Profit
Then a second reportname and Line6 , Line 7 etc picking up different amounts

Relationships created for the above work fine; pbix runs quickly on desktop and in service. So very pleased so far.

I am now trying to create a paginated report with the same data, but am struggling with the grouping. This is what I have so far:

A "fresh table" to manage the production of reports, (not all sites have all departments) with no relationships to the main model.
Columns are:
1. Recipient - (to create parameter for final running of report)
2. Site

3. Report Name

4. Department

5. Sub Dept

I have got the report running with group hierarchy of Site, Dept, Report name, Line Ref. and am linking the main SSAS dataset to a dataset of the "Fresh Table" with Lookup and LookupSet. Kinda works but hard to write!

Problems are:
1. We need the first few pages to report on total numbers for the site, but this doesn't seem to work with the above hierarchy.
2. We need also in some departments but not others to report on sub departments and dept totals too.

 

So question is how do I manage the grouping so that for certain reports we add up all departments, for others just departments and for some departments (totalled) and sub departments. Or do I need to approach it in a different manner.

One workaround  might be to create multiple tablixes; 1 for site summaries, 2 for depts, 3 for sub depts. but this seems a bit clunky, the rep will probably run slowly or fall over and there has to be a better solution.

Any help greatly appreciated.
Thanks
Pete













3. Report ID



Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors