I have a standard P&L report that users can drill through to transaction detail by clicking on the total in the report. I don't have all the detail required in the cube to populate the transaction list as I'm trying to keep the cube as small as possible to reduce refresh times.
At the moment this works by using a macro to dynamically pass the cost centre, GL number, month and company to a SQL query against the data warehouse. This has been working fine, but I want to start using the JET hub and I can't upload .xlsm files.
Is anyone doing this another way, without giving users access to Nav? I also don't want to attach the full transaction listing in another workshet. Maybe linking out to another JET report or passing a dynamic URL to SSRS?
Any ideas appreciated.