Hello!
I'm posting here in the hope of some advise/direction with a business problem.
As an Analyst at an Automotive Training Centre, I use Excel and Power Query to collate a report about training attendance. This report is used by internal employees but I then use VBA to break this full Excel report up into pieces which are emailed each week to each Company who sends delegates for training with us (i.e. each external company listed on the full report, usually around 200).
So, I would like to find a better way for our external customers to view this information without having to use VBA and emailing.
I have so far explored and rejected the following:
1. Power BI - Our company doesn't encourage use of Power BI, not fully sure why. Also, licencing seems to be a nightmare when external customers need to view.
2. Tableau - Again licencing costs due to external customers needing to view.
Ideally, i'd like external customers to be able to view the report data only applicable to their company (data protection!) maybe via web browser? but without having to embed 200 separate reports.
Can anyone please help with suggestions/help?
Many thanks in advance!!
Mike
Hi Michael,
Welcome to our forum!
Power BI is the tool designed for sharing specific data with different users as you describe above. The only alternative that I know of is to use Power Automate to automatically email subsets of the report to users. However, I'm not sure there's any advantage to this as you're already doing this with VBA.
Mynda
Many Thanks Mynda,
What type of Power BI would I need? Could I achieve this with Power BI Desktop without publishing public? I think our company wouldn't allow that due to data protection as my report contains personal information.
I have the option to embed the report in our Learning Management system and would need to research in Power BI how to apply views based on user viewing the report.
Many Thanks
Mike
Hi Michael,
You and each of the customers accessing the reports would need a Power BI Pro license (which is US$10/month), so that you can use the Row Level Security feature.
If you're willing to create 200 separate Excel files, you could use Excel workbooks connected to the master data source file with Power Query filtering the data for each customer. Then create a sharing link for each Excel file allowing the customers to read their specific workbook. You would have to run a VBA routine or Power Automate script to open each file and refresh the queries as required.
Mynda
Many thanks Mynda! 🙂
