Excel integration can provide integrated access to all of your Docketbook data
Setting Up Excel Integration with OData
Integrating Docketbook with Excel offers a valuable reporting tool to extract real-time data from your company's Docketbook. You can utilize formulas, lookups, and charting to gain deeper insights into the collected docket data.
This article guides you through setting up the Excel integration, which involves using a secret key to address Excel's password storage vulnerability. The process, demonstrated in the video below, includes:
- Generate a Security Key using THIS LINK
- Authenticate your copy of Excel using the security key
- Establish Excel privacy levels
- Extract the data.
Getting Access
To get started, you'll need to generate a secret to use instead of your normal password. In order to do this, click the button below and you will be prompted for a username and password. Use the same credentials that you would otherwise use to login to Docketbook:
Once you login, you will see a screen like this that contains your secret
Copy and paste the secret (the string between the quotes) into a safe location - We will use it later.
Getting a Template
Customers with Accounting Integration included can access a basic template that extracts all docket data in a structured format. If you wish to obtain a copy of this template, simply create a ticket, and our team will assist you in getting started.
On a security point, this template contains data connections and macros, both of which Excel is going to present warnings about. We use macros to trigger data to reload as other data changes (as shown in the video) and the data connections to talk to Docketbook. These warnings are shown because macros can be potentially harmful if constructed as such. If you have concerns or your organisation requires it, it is best to verify with a trusted professional that our macros are harmless or otherwise contact us directly.
Starting with the Template
The video below illustrates the process most users will encounter if they're starting with our template.
The steps that the video works through are:
- Open the downloaded template file and hit the "Enable Editing" button
- Once in editing mode, hit the "Enable Content" button
- Go into the "Data" tab and open the "Queries & Connections" panel
- Starting with the "Organisations" query, right click and select "Refresh"
- Because this is the first time with this query in this workbook, you should receive a prompt like this. When you do, select the "Basic" option on the left. Your username is your Docketbook username and the password is the secret that we created earlier.
- Once thats done, you should see your organisations listed under the "Orgs" worksheet and also selectable under the drop-down in the "Settings" worksheet.
- Select your organisation from the drop down list, which will trigger another query to fetch the related organisation groups. Another pop-up will likely appear the first time. When presented, we select "Organisational" from the drop-down. Your organisation may want you to use another option, so it is best to check with them
- Once the query is complete, you should be able to select your organisation group from the drop down as well as see the results in the "Org Groups" worksheet.
- Select one of your groups and the list of dockets within that org group should be fetched and populated into the "Dockets" worksheet.
Once you've got docket data being loaded into the "Dockets" worksheet, you can perform any analytical work you would otherwise do. Each column on the Dockets tab is also filterable.
Where To From Here?
- You can see how we write our queries by opening the Data -> Queries & Connections panel, right clicking on a particular query and clicking "Edit". With the "Query Editor" window open, select the "Advanced Editor" option along the top. This will show you the query steps involved in obtaining data from Docketbook and were required, how we extract parameters from the worksheet to make it interactive.
- At the moment, we only support Docket and Checklist querying based on organisation, organisation group and date range. A date range is mandatory and the absence of one will result in an empty response. In the future, we plan to support additional query parameters for Dockets and expose an even wider array of our data.
General Tips
- Try to keep any queries you run confined to a particular date range. Our API's won't prevent you from fetching every docket you've got, but its going to be a very long running query and generally slow down Excel.