Hi guys
Been a while since I’ve been on site. Too much non dev financial work going on in the CFO world!!
And also thanks to Sam I got the inspiration I needed earlier in the year to create a financial reporting tool for our business. All going well and now getting towards the end of the project.
However I am struggling to manage a relationship issue in a financial reporting model:
- Fact table with RepLine, Cost Centre, Stream(Bud or Act), Amount, dates
- Dim table for cost cent linking one to many to fact table
- Dim table for site and Dim table for operation (dept) each linking one to many with cost cent
- Dim Report Spec table with RepLine (linked back one to many with Fact table), Line description, Report Name etc
This all works beautifully for a 9 site motor group with multiple departments.
Most of my report pages have a 3 slicers, site, dept and Report name. - All working well.
Next steps are to bring manager lists in. So: - Dim Manager List with 2 columns - Manager and Cost Centre linked Many to one to CostCent table
All working well here but needed to set direction to both for Manager List, Dept and site tables.
When I bring in a slicer for manager the Site and Dept filters are behaving properly, ie only showing the sites and departments for which the manager has responsibility.
I am now trying to shrink down the selections on the Report Name Slicer to only those reports relevant to a given dept. But this is proving very difficult. My report Spec table has a column for dept name eg vehicle Sales gross, service/bodyshop gross, parts gross, expenses. There are also some summary depts. covering a whole site. The reason for shrinking down the slicer is not solely cosmetic however.
Once set up correctly the cube will ten lend itself to generating paginated reports more easily without the need for lots of fiddly LookUp functions
There is “kind of” a relationship between operations and the the dept name in the Spec table, which I have tried to set up and create relationships for but the slicer for report is not shrinking down when clicking on a manager???
Please see attached “watered down” version of my pbix with test data for a boat sales, repairs and chandlery business. The manager slicer is working correctly, but I am trying to reduce the MA report slicer when filtering Buzz only New Boat sales will appear; when filtering Combat Karl only Boat repairs will appear; when filtering Andy all the reports will appear.
Apologies for long preamble, sometimes it’s difficult to explain your problem without background and examples.
Hope you can help
PeteBoatTest.pbix (296.0 KB)
