Creating report from multiple lists

Question

I have a requirement to create report from multiple list. I was wondering if there is any out of the box features in SharePoint which will let you do that or do i have to create a custom webpart with code.

please see the below CAML query using one of the caml query generator with CSOM. with this i can only get the details of one list "Printers Consumable Requests". How do i modify it so that i can have a left join two other lists with columns of it?

ClientContext clientContext = new ClientContext("your site"); 
Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Printers Consumable Requests"); 
clientContext.Load(spList); 
clientContext.ExecuteQuery(); 

if (spList != null && spList.ItemCount > 0) 
{
   Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery(); 
   camlQuery.ViewXml = 
      @"<View>  
            <Query> 
               <Where><And><Eq><FieldRef Name='OutDateYear' /><Value Type='Calculated'>2017</Value></Eq><Eq><FieldRef Name='OutDateMonth' /><Value Type='Calculated'>01</Value></Eq></And></Where> 
            </Query> 
             <ViewFields><FieldRef Name='OutToPrinterID' /><FieldRef Name='OutDate' /><FieldRef Name='PartID' /></ViewFields> 
      </View>";  

   ListItemCollection listItems = spList.GetItems(camlQuery); 
   clientContext.Load(listItems); 
   clientContext.ExecuteQuery(); 
}

Answer 1

You can use Content Search WebPart to get data from multiple list, or else you can use Search REST API to get data from multiple list.

What you have to do is, just create a result source and configure the query to fetch data from multiple list.

Then after, just call a REST API to get the data from result source and you will get the data from multiple list from one rest call.

Answer 2

You can also use a data view web part to display the items from multiple lists.

1. Open the page in SharePoint Designer, in the Data Source Library pane, Click the "Create a new Linked Source..." link
2. Click the "Configure Linked Sources" button
3. Select the list you want to include and then click the "Add>>" button.
4. Choose "Merge" option to merge the results.
5. Click the "Finish" button. Then click the "Ok" button.
6. Open the page in SharePoint Designer.
7. In the Data View menu click the "Insert Data View" option. Select the data source.

A demo about using a Data View Web Part to display items from multiple lists with detailed steps.