Mass creating reports with same/similar data?

[alexlmiddleton]

Greetings!

I work in an organization with hundreds of departments, and each department has about a dozen reports.  The reports are all the same -- meaning, every department has an 'employee birthdays' report, and every department has a 'budget status' report, and every department has a list of their employees, etc.

In other words, despite the fact that we have thousands of separate reports, they're all using the same queries, just with a different department location code.  So, as an example, it'd be a query like:

SELECT * FROM emp_info WHERE location=8

SELECT * FROM emp_info WHERE location=9

SELECT * FROM emp_info WHERE location=10

SELECT * FROM emp_info WHERE location=11

SELECT * FROM emp_info WHERE location=12

...

The only thing that's changing is the location.  It seems ridiculous to manually create hundreds of variations on the same SQL query, or to go pull in ALL departments (SELECT * FROM emp_info) and then manually filter them to create hundreds of variations.

Is there an easier way to create reports for each separate location automatically based on one query?

Thanks in advance!

[edhans · Accepted solution]

Yes. It sounds like one report would do what you need.

1. Set up reports using Row Level Security. In the Modeling Tab, select Manage Roles.
2. Create a role for Location 8. In the fields, select the table, then type [emp_info] = 8
3. Repeat for all locations.
4. Consider creating a master role that has no location filter - executive for example. Don't add any filters to this one.
5. Do View as Role on the modeling tab to make sure it works.
6. Publish.
7. Make sure the workspace is set to Read Only, not Edit.
8. In the workspace, select the ellipses in the dataset section and select Security.
9. Here you enter either the emails of the users for each location, or better yet, a security group in Office 365. In the security group, you can add and remove people as needed. Remember to create a security group for any admins/executives for step 4 above.

That is about it. See this article for more info.