cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexlmiddleton
Frequent Visitor

Mass creating reports with same/similar data?

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Mass creating reports with same/similar data?

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
Super User II
Super User II

Re: Mass creating reports with same/similar data?

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors