Automate report generation in SharePoint

Introduction

Ultimate Forms is a no-code platform for SharePoint. We designed it to enhance and extend SharePoint's capabilities. It allows users to create dynamic business processes, forms, and workflows without requiring programming knowledge.

Ultimate Forms provides a comprehensive set of tools, including custom forms, automation, notifications, permissions control, and integration with external data sources. Organizations use Ultimate Forms to streamline operations such as document approvals, HR processes, project tracking, and purchase requests. It helps to make SharePoint more powerful and user-friendly.

Ultimate Forms makes it easy to create and maintain relationships between different lists in SharePoint. It allows you to manage the relationships and automates summary calculations. Based on this relational data, it's easy to create reports and other drilldown features.

Ultimate Forms also helps you configure automation in a way that is fast and easy. Using its Actions, you can implement automated SharePoint reports in a considerably shorter time than with any other tool.

In this article I will show you how easy it is to automate weekly report generation. The report will arrive in the managers' inboxes, without writing a single line of code. We can use SharePoint Online (Microsoft 365) or SharePoint on premises, the implementation steps are exactly the same.

We will be managing sales figures in a regular SharePoint list. Every week the system will generate a new report based on this data. Next it will email it report to a designed group of managers.

Implementation

We will be using the following components found in Ultimate Forms:

• Actions - will allow us to created a timer-based action, which will generate and email a PDF-based report
• Print - will allow us to design and implement the report template. This component has multiple configuration options and output format. You can print both individual items and lists.
• Associated Items - will allow us to automatically calculate summaries of sales figures by region. Associated Items column implements relationships between SharePoint lists. Once we add it to our parent list, it will display a list of child items. You can add and manage child items directly in the parent item forms.

We designed these components to work hand-in-hand in a complementary fashion.

Create Lists

In our demo we will have two lists:

1. Sales - this is where we enter our sales figures, either manually or through some automated import. The actual method of data entry is outside the scope of the demo. You might want to look into the Import capability of Ultimate Forms.
2. Regions - this is where we enter a list of the region names. It serves as lookup values for the sales figures. It also contains our Associated Items Summary columns, which will calculate the totals for us.

The following steps will ensure you have the lists you need:

1. Create a custom list named Regions.
2. Create a custom list named Sales, add the following columns:
   1. Region - lookup to Regions, required field
   2. Amount - currency, required field

Next lets add out special column types to the Regions list.

1. Create an Associated Items column, pointing to Sales, call it Sales.
   
2. Create an Associated Items Summary column, use it to Count the ID column in Sales. Call this column # of Sales
3. Create an Associated Items Summary column, use it to Sum the Amount column in Sales. Call this column Amount.
4. 

Summary column automate the summary calculations. You will notice that when you add new child items to the parent, the form will calculate the summary automatically.

Print Template

We are now ready to setup our report. First, we need to create our custom print template to define the way the report will look like.

1. Go to Regions list --> Design --> Print and export
2. Create a new template by specifying the following:
3. Title - Report (can be anything)
4. Type - List, as we want to print all regions in one report
5. Select Region, # of Sales and Amount columns
6. Add any text to Header and Footer sections. It could be the name of the report and any disclaimers
7. Save the template

Print Action

Actions allow you to quickly and easily implement automation in SharePoint lists. Unlike traditional workflows, they are simple to use and require no specialized knowledge.

With over 20 different action types, you can easily work with data both inside and outside SharePoint. You do not need any additional licenses and specialized connectors.

Let's create the action that will produce and send out the report:

1. Go to Regions list --> Design --> Actions
2. Create a new Print list items action
3. Enter Weekly Report as action name
4. Specify that the action runs on a weekly timer.

1. Under Action settings, specify which items to include in the report. Entering conditions that is always true (such as ID not equals 0) will include all items
2. Specify the print template and recipients (user or group)
3. Specify email subject and body
   
4. Under Conditions you need to specify a condition that always applies to a single item in the list. In my case I chose ID = 1. This ensure the action only runs once on this particular item. The report itself however will include the data for all items.
5. Save the action to enter it into the system. The system is now ready to function.

Testing

Now we can enter a number of regions into our Regions list. For each region we can enter multiple sales events.

Once done, proceed to clicking on the Print button in the toolbar of the Sales list. That will display a preview of the sales report. This is the report that you will receive in the mailbox every week.

This is it, you've successfully configured a weekly sales report!