Click below to watch “What is a living report and why would I create one?”
You can create a “live” report in Excel that pulls your Opportunities or Activity Journals from your Repfabric instance. Once the report is created and formatted how you want it, every month (or as often as you need) you can “Refresh” the data to get the most recent information from Repfabric. There are 4 basic steps to creating the living report.
What is a living report and why would I create one?
Part 1: How to Create a Basic Living Report
I. Create a custom URL
II. Enter URL and token into Excel
III. Format report in Excel Power Query
IV. Save the file
WARNING: if you have multiple living reports to create, DO NOT COPY or attempt to copy and then edit. It will not work well. For the most efficient process with the best results (and least frustration!) please start each report from scratch following the instructions outlined here.
Construct a URL
You will need to construct a string that “asks” for your data from Repfabric in a format you want. The first part of the URL is the same. There is a separate one for Opportunities and one for Activity Journals. Your company name will need to appear in the URL so it knows where to look for records. You can add modifiers to the end of an opportunity URL using an ampersand &
Copy and paste one from below and make changes or add to it.
https://acmeexplosives.repfabric.com/api/public/v1.0/opportunity?principal=Dyno
https://acmeexplosives.repfabric.com/api/public/v1.0/activity_journals?principal=Dyno&forDuration={days}&includeChild=true
https://acmeexplosives.repfabric.com/api/public/v1.0/quotes?fromDate=2020-09-01&toDate=2020-09-20&showAll=false
https://acmeExplosives.repfabric.com/pbi_api/v1.1/jobs
https://acmeexplosives.repfabric.com/api/public/v1.0/planner?upcoming=TRUE
You can use the following modifiers:
fromDate=2021-03-01&toDate=2021-06-30
Principal=
salesTeam=
oppType=
closeStatus=
forDuration=90
Planner only: ?upcoming=TRUE
How to Enter URL Into Excel
Now we will link the URL to Excel and we will use your email and password that you use to login to Repfabric.
1. Open new Excel file.
2. Left click “Data” tab.
3. Left click “From Web.”
4. Copy a URL from previous page and paste a URL into the field.
5. Make and changes and additions to your URL.
6. Left click “OK.”
7. Left click on “Basic.”
8. Use the drop down and select the last one on the list.
9. Enter username : your email address
10. Enter your Repfabric password in “Password” field.
11. Left click “Connect.”
12. RIGHT click on “List.”
13. Select “Drill Down.”
14. RIGHT click “List” again.
15. Left click “To Table.”
16. Left click “OK.”
17. Left click arrows & remove check marks from columns you do NOT want to see (if you can’t decide, you can leave check marks in them all for now. We can delete them in the next page).
18. Remove check mark from “Use original column names as prefix.”
19. Left click “OK.”
Change Column format:
A. Left click format button.
B. Left click desired format.
Delete a Column:
a) RIGHT click on a column header.
b) Left click “Remove.”
To Move a Column:
1. Drag column to its new position. Look for the green line while dragging.
Close & Load to Excel Spreadsheet
When you have finished deleting, formatting and moving your columns
2. Left click “Close & Load.”
Now you can sort & filter your numbers any way you like. The next time you refresh, it will bring in the most recent data. Instructions are on the next page.
How to set your Living Report to “Auto-refresh”
- Left click the “Data” tab.
- Left click “Queries & Connections.”
- Go over to the panel on right side of screen and right click on your connection.
- Left click “Properties.”
- Put a check mark next to “Refresh Every” and move the counter to the desired number of minutes you’d like between refreshing.
Click the play arrow to see a quick demo.
How to Refresh the Data in your Living Report Each Month
You can refresh your report as often as you need to. Open your saved Excel file.
1. Left click “Data” tab.
2. Left click “Refresh All” button.
Time to make it fancy!
Once you have created the living report with the columns you want, you can make it fit your color schemes an add your company and manufacturer’s company logo’s for example.
Change table color scheme
- Click on cell A1 to have the table selected.
- Click on the Table Design tab, and select the color scheme you want from the Design tool bar.
Add additional rows, clear gridlines, and logos
- Select the top row of the table, and Right Click > Insert
- Repeat this process to add additional rows
- To clear gridlines, select the widget Page Layout, and deselect “Gridlines” View checkbox.
- Add your company logo and line images by either using the Insert > Picture or from another pasted in source (as shown below).
