Take Your Event Reporting To The Next Level

Phil, the groundhog, may have seen his shadow a few weeks ago indicating that we have 6 more weeks of winter on the horizon, but in my mind the beginning of March signifies we are quickly approaching Spring. Flowers are starting to bloom, the weather is slowly making a turn toward warmer temperatures and Organizations are ramping up for their busiest season with Special Events to take place over the course of the Spring and Summer. Are you planning a Spring Golf tournament, maybe a Summer Gala, a Spring Fling? 

While you're dusting off your event reporting, you may be wondering how to account for donations, auction items, and registrations all in one report. While Altru does not have a canned report to show you all of these items at once, we can build a customized OData Dashboard to help you keep track of your event fundraising progress. Not only will OData help to track all of this revenue in one place, it also allows you to create an end report that is easy for your Executive Team and Board Members to view and digest without overwhelming them with multiple queries. Check out the example below! 
While building this dashboard may seem overwhelming, we can easily break this process down into a few easy steps:

1. Create your Queries in Altru
2. Retrieve the OData links from Altru and Connect them to Excel
3. Add a few key Calculated Fields in Excel
4. Customize the theme of your Dashboard and add any pictures
5. Refresh your OData Connection in Excel to retrieve the most up to date data

Step 1: Create your Queries in Altru
Before we can get started with building our reporting using OData, we will first need to create 4 queries in Altru. The first query, will be a list of registrants with registration options. Next, we will want to create a query for event registration revenue and balances due. We will also need a query to track donations made to a your special event designation. Last, but not least, if you are had any auction item sales associated with your event, we will to build a query of all auction items sold. To create an OData table, once the query is saved, click Get OData Link to generate the link you will be using in Excel.
We can then copy this link, and open a new workbook in Excel. Once you are in Excel go to the Data Tab > Select From Other Sources and Select OData Feed. 
In the Data Connection Wizard, paste the OData link from Altru into Step 1 and type your Altru Credentials into Step 2 > Click Next. 
In the Import Data Screen, choose what you would like Altru to do with this data. Make sure the box to Add this Data to the Data Model is marked and click OK.

1. Total Registrations Paid: =SUM([TotalPaid]) 
   
2. Total Balances Due: =SUM([Balance]) 
   
3. Total Registration Revenue: =[Total Balances Due] [Total Registrations Paid]
   
4. Total Donation Revenue: =SUM([Amount])
   
5. Total Auction Purchase Revenue: =SUM([Amount])
   
6. Grand Revenue Total: =[Total Auction Purchase Revenue] [Total Donation Revenue] [Total Registration Revenue]
   

Step 4: Customize your Dashboard
Now that we have configured our connections and calculated columns, we can combine these elements to create a customized dashboard for your organization. Below are a few suggestions.

• Registration Option
• Quantity
• Registration Fee
  

To view a Registration Option Chart, on the right side of the screen we will drag the following fields to a single table in the Power View:

• Registration Option
• Quantity and/or Total Registration Amount

On the Design Tab, select what type of chart you'd like to create with this data.
To view Total Raised in a chart, on the right side of the screen, we will drag the following fields to a single table view:

• Total Donation Amount
• Total Auction Items
• Total Registration Amount

On the Design tab, select what type of chart you'd like to create. In our example, we selected a Pie Chart.
Lastly, you may want to create a Key Performance Indicator (KPI) for Total Revenue in order to track how your organization is doing compared to your goals. You can then drag these fields into the Power View Report as desired.
On the Power View tab, we can spend some time focusing on the visual aspect of the report including:

• Theme
• Font
• Background
• Pictures
  

Step 5: Refresh your Feed
Once the dashboard has been customized, save your worksheet in a location that you can access again. The advantage to building an OData Report like this one is that you can quickly and easily update your data with the latest statistics as data is added to your Altru Database. To refresh your Excel Report, navigate to the Power View Tab, click Refresh and select Refresh All. You may be prompted to reenter your password for each query that has been connected to the dashboard.