11 Connection Campaign Reporting Dashboard Shared by Leah Jennings, Northside Christian Church 3 years ago 10.3 General, Operations Intermediate PurposeWhen we started our calling campaign, we had 30+ volunteers and 30+ staff organized to help make the calls to our congregation. We created this dashboard for those who were overseeing the effort to give them eyes on overall progress and individual progress. Features of the dashboard:Shows overall campaign connection creation progress (indicated by the green boxes at the top of the page)The green background will fill the background from left to right reflecting the progress percentage100% created means they are all created but that doesn't mean that 100% of the requests have been connected; these boxes track creation onlyIndividual connector progress indicating the following:Number of current requests assigned to that connector (calculated by subtracting the requests completed from the total number assigned)Total connection requests completed (completed means the request state is either Inactive or Connected)Overall total number of requests assignedNumber of days since each connector last modified any of their requestsDate/time of the last update of their oldest request (looks at the oldest ModifiedDateTime for each connector)Here's what the dashboard looks like (note: we did three separate connection campaigns for performance and to allow volunteers from each area to call people within that area):PrerequisitesWe used a separate connection opportunity type specifically for the calling campaign. If you haven't done that, some of the reporting could be off as it may include requests outside of the calling campaign.The code provided supports 3 campaigns, so if you have more than that, be prepared to copy/paste some to get up to the number of campaigns you have!ProcessCreate a new page dedicated to the campaign progressAdd two new blocks:HTML Content block (this will be for the green blocks at the top)Dynamic Data block (this will be for the individual progress tracking)Edit the block properties of the HTML Content blockCheck Sql under the Enabled Lava Commands sectionEdit the HTML content of the HTML Content blockAdd the content of the HTMLContentBlock.txt file to the blockHere's what you'll need to modify that content:EntitySetId(s) - connection campaigns are stored as Entity Sets, so you'll need the EntitySetId of each connection campaign SELECT es.Id, es.[Name] FROM EntitySet es WHERE es.[Name] LIKE '%KEYWORD%'Where KEYWORD is a word or the full name of your connection campaignDo that query until you get the Id of each connection campaignConnectionOpportunityId(s) - the Id(s) of each connection opportunity type used in your campaign(s)These can be found by clicking on an existing request of the type you need the Id for and look in the address bar: For my example, the Id I would need from the Url is 49Once you have these Ids, replace the Ids in the code with your Ids in the SQL:You'll need to also change the when clauses in the for loop to assign each variable that then gets calculated later. We only have three campaigns, so if you have three or less, you're only needing to modify the condition of the when statements. If you have more than three campaigns, you'll need to add a when condition for each additional campaign. If you have 6 campaigns, you'll have 6 when conditions. Also the condition needs an exact match of the campaign name (one of our campaigns is literally called Adults)Each <div class="col-lg-4"> is one green box at the top. If you only have one campaign, remove the last two <div class="col-lg-4"> sectionsHit Save on the block and you should now have a green progress box at the top of the page for each campaignEdit the Criteria of the Dynamic Data blockAdd the content of the DynamicDataBlock.txt file to the Query section of the blockUpdate the SQL in the 4 indicated places to reflect your ConnectionOpportunityId(s)Example: Here are the rest of the Dynamic Data block settings, but these you can feel free to adapt to your organization's needs:Hit Save on that blockAnd your dashboard should be complete! Download File