Custom report builder Coming soon
Relate lists, pick fields, export
For the report nobody built for you. Pick lists on a site, relate them to each other, choose the fields you want from each, filter, and export the joined result as a single Excel sheet. It is the answer to reporting across a list and its lookup lists without exporting three sheets and writing a VLOOKUP.
What it tells you
- Anything you can express as lists plus relationships plus fields plus a filter.
- Data from several related lists on one row, rather than one export per list.
- The same question again next month, because the definition is saved and re-runnable.
How it runs
- Sign in and pick the lists. Clicking the tile asks which account to use and how to connect, signs you in if you are not already, and drops you straight on the site list. No feature page asks you to connect a second time. Choose the lists you want to report across.
- Relate them. Lookup columns are detected and offered automatically. Where there is no lookup, you can relate two lists by hand.
- Choose fields and filters. Pick the columns you actually want, in the order you want them, and filter the rows down.
- Export, and save the definition. The workbook is the output. The definition goes to Saved reports so the next run is one click.
How it works
Running a report reads each list it uses in pages, which works past the 5,000 item threshold, and reads only the columns the report needs: the ones you show, the ones you filter on, and the ones the relationships join on. SharePoint cannot join lists itself, so everything after reading happens on this computer.
Lists are joined along your relationships, starting from the base list. A lookup column joins on the item it points to, and a relationship you set by hand joins on matching values, compared the same way on both sides. Each relationship either keeps base rows that have no match, leaving the other list's columns blank, or drops them. Rows are joined, filtered and grouped one at a time rather than all held at once, so a wide join does not exhaust memory. Filters combine with AND binding tighter than OR. Output stops at Excel's row limit, with a warning saying so.
If one list cannot be read completely, it is left out entirely, with a warning in the workbook and on the completion dialog, because partial rows joined and filtered would look plausible and be wrong. When no rows can be trusted, nothing is exported. The workbook goes into History before the save dialog appears, so cancelling the save does not lose the run.
What you need
- Read access to the lists you report on.
What it will not do
- Not released yet. The tile shows a clock rather than a padlock, because this is us not being finished rather than a licence you could buy.
- It reports across lists on a site. It is not a cross-tenant query tool.