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
