Analytics

 View Only
  • 1.  Using Power BI for Analytics

    Adopter
    Posted 09-21-2021 18:33
    Edited by Pat Cooney 02-17-2022 11:31
    Wanted to start a discussion here about Power BI as the analytics tool for ProntoForms data. We are early in our journey using Power BI and would love to gain insight from companies who have already gone through trial and error to find the best way to pair ProntoForms and Power BI together.

    We believe that our end state will involve having SharePoint Custom List Data Destinations set up for most of our forms. The forms we have live with SharePoint List destinations have been really useful as a tool to give our teams real-time data visualization by using the SharePoint List destination as a data source for Power BI. One major hurdle we are facing is that repeatable sections are not currently supported by the SharePoint Custom List destination type. A lot of our most-used forms utilize repeatable sections.

    Has anyone else out there found a creative workaround to address this? Some combination of a scheduled ProntoForms data export and a Power Automate flow to add rows to an Excel file? Hoping to hear from our peers who might have solved this challenge already.

    #PowerBI

    ------------------------------
    Matt Lambert
    Vice President of Operations
    PrimeLine Utility Services
    mlambert@primelineus.com
    ------------------------------


  • 2.  RE: Using Power BI for Analytics

    Posted 09-27-2021 08:23

    Hey Matt, 

    If you have the ability to define the number of rows that will be allowed to be input into the repeat section, what you can do is create 1 destination per row. The main "gotcha" when doing this is that Sharepoint Lists depend on a Unique ID to determine if it will be updating an existing row or creating a new one. When dealing with a repeat, your Unique ID needs to be something that is unique per row of that repeat, otherwise it will just overwrite the previous.

    For example, if you have a repeat section gathering an employee name and a date for timekeeping, use a String Concatenation question to combine those 2 data points so that you will have unique data per row (like "Ian Chamberlain - 09-27-2021") and use this CONCAT question as your Unique ID because it will then see that each row is different than the last which will create a new row in Lists. The Name and Date will not work in every situation, sometimes you can use an Asset number or something else, but a Date is always good to include in the Unique ID. You can also you more than 2 questions to come up with the Unique ID. 

    Once you have figured out your Unique ID, you will need to define the number of Rows that will be allowed to go into the repeat. Limit the number of rows in your repeat section to what you have defined. In each one of your destinations, you will need to specify which row that destination will be for using DREL. %a[QuestionLabel][0] would refer to the answer to "QuestionLabel" in the first row of repeat. replacing the 0 with the next number will indicate the next row and so on. 

    Using this, you can write each row of the repeat to its own row in your List. Your first destination will reference all the questions in the first row with [0], the second destination will use [1] and keep on adding destinations until you have them all.

    Now when your form has multiple repeats all needing to be on their own row, managing that many destinations might become a little bit more complicated.

    I hope that this works in your workflow! Let me know if you have any questions. 



    ------------------------------
    Ian Chamberlain
    Implementation Specialist
    ProntoForms
    ------------------------------



  • 3.  RE: Using Power BI for Analytics

    Posted 11-18-2021 11:36
    Hello Matt,

    We wanted to let you know that we are working on improvements to our SharePoint List destination that will add support for repeating sections.  Expect some news on this in the next few months.

    /Glenn

    ------------------------------
    Glenn Chenier
    Chief Product Officer
    ProntoForms
    ------------------------------



  • 4.  RE: Using Power BI for Analytics

    Posted 11-19-2021 05:54
    Matt

    Depending on what type of data you are collecting in your repeatable sections, this may be a workaround...or maybe not:
    - I use a summary of data from my repeatable sections (aggregation and/or calculation function) which I then bring in to SharePoint and thus avoid the multiple lines (when brought into Excel etc).

    In my use case I am collecting numbers so this works well even for a nested repeatable section (one section is wall area data, the next section is the aggregate of walls within a room, and finally the m2 for all the rooms in the property).

    If I need to drill down into the specific data in part of a job, I would just open the individual job files (which are all stored in Dropbox).

    Just a thought.

    Stephen

    ------------------------------
    Stephen Morrison
    Business Information and Performance Director
    BCA Insulation
    ------------------------------



  • 5.  RE: Using Power BI for Analytics

    Adopter
    Posted 11-19-2021 10:25
    Thanks, Stephen. We actually have a few forms where this solution would work well. Appreciate the insight!

    ------------------------------
    Matt Lambert
    Vice President of Operations
    PrimeLine Utility Services
    mlambert@primelineus.com
    ------------------------------



Reminder: Content posted to our Community is public content.  Please be careful not to post Intellectual Property that you do not have permission to share.  For more information please refer to our Terms Of Use