Reporting on and viewing related SharePoint Lists

[guyinazo]

I am sure I am not alone in this question, but what is the best practice to show and end user data from several related SharePoint lists?  So if I have a list of projects, then a separate list of to-do items with a foreign key to the project ID of the project list, etc, what is the best way/tool to deliver something to a project manager who can click on the parent record and see any child records.  I know I probably can use Power BI as a reporting tool, but not everyone in my org has a Power BI pro license, and if I were to use a separate reporting tool, what would the query look like?

Thanks for the information

[#2 · Accepted solution]

@guyinazo the best way to do this is with an app in Power Apps as you can have multiple data sources which can be related, cascading dropdowns, cascading galleries and a lot more.

In this simple example I have a list of projects as follows:

And then a list of tasks where the project column is a lookup to the Projects list Title column:

In Power Apps you could then have a dropdown which displays the Title from the projects list and filters a gallery to show the relevant data from the tasks list:

You could embed the app in a SharePoint page with the Power Apps web part, or you could give your manager the web link to the app.

There could be other screens with more data, and if this was a live app instead of a 30 minute example I would make a lot of improvements to the lists and the app. But it might give you something to consider that can be implemented quickly. If you haven't used Power Apps before there are many very good videos on YouTube by Shane Young which will get you started.

Rob
Los Gallardos
Intranet, SharePoint, Website and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#3]

@RobElliott Thanks for the response.  Yes, I am very proficient in Power Apps and I didnt think of that as a solution.  This will work if they need to look up a project, but what if they want to print the project with all of the related records?  I know Power Apps now has a print option, but it isnt too viable.

[#4]

@guyinazo that's different to what your original question wanted. If the Print() function doesn't given you the results you need then you probably would need to look at Power BI.

Rob
Los Gallardos
Intranet, SharePoint, Website and Power Platform Manager (and classic 1967 Morris Traveller driver)