Best practice for end user to be able to run a report

[Sgligori]

I have a database that contains the entire universe of data.

I would like end users to have the ability to pull a report on only what they need. What is the best and most user friendly way that end users can limit the scope of the data? More specifically, the would be selecting specific customers, categories, and months.

 

[Matt Allington]

It depends what you want to do. If you want to "prevent" them pulling/seeing data, en you should implement row level security in the DB. If that is not an issue, then I guess it depends on the skills of the users. I am not a DB expert, but I pull data from a DB like yours all the time. I just write a query to limit what I need from my tool (eg power pivot). I think this works best as it is infinitely flexible, but of course you need the skills. I guess you could write some parameter queries in the DB that will prompt the user on refresh - I don't know if that would work between an external requesting resource like power pivot. You could use something like power query with a parameter table hat allows the user to set the parameters before refreshing a workbook.

My my view is that end users (people that just consume reports) shouldn't ever refresh reports. Only "power users" such as business analysts should pull and refresh reports, and hopefully they either have the skills or can learn them.