One of the advantages of Power BI “V2” over “V1” is the ability to refresh from data sources more frequently than once per day. Daily was the maximum in the first version of the service, and continues to be the maximum for the free version. If you have a Pro license however, refreshes can be performed more frequently. Unfortunately, it isn’t immediately obvious as to how this is done.
If we navigate to one of our datasets in Power BI, and select “Schedule Refresh” we’re presented with the user interface for setting this up (assuming that it’s a refreshable source). First we turn on scheduled refresh, and then next, we select our frequency.
Wait – there’s no “hourly” option in the dropdown. Didn’t I just say that it can be more frequent than daily? It can. You just need to be more explicit about it. The way to enable refreshes more granularly than daily is by explicitly adding the times for the refresh using the “Add another time” link.
Therefore, in order to have true hourly refresh, you will need to add 24 refresh times for the data source. That’s not all that appealing, is it? Unfortunately, 8 is all you’re allowed. I can only assume that this was by design in order to dissuade people from overtaxing the system when it isn’t required. You do get refreshes more frequently than daily, but hourly isn’t an option either.
Therefore, the minimum amount of data latency that can be achieved by using refresh with Power BI is, as of this writing 24/8 = 3 hours. Of course, this does not apply to direct connected sources.
A common customer requirement that we have come across involves periodic reporting requirements. Basically a number of departments, or individuals are required to fill out a form on a periodic basis, and that form must be run through an approval workflow of some sort. This scenario can easily be accommodated with Nintex Workflow and SharePoint.
The solution will require at least two lists:
Reporters – this list will contain all of the entities that are required to do the reporting, along with parameters that will drive the destination approval workflow. We will use a custom SharePoint list for this purpose.
Reports – this will be a forms library that will contain the submitted forms, along with the approval workflow itself. Technically, this does not need to be a forms library with InfoPath 2010, but if the form is to contain embedded sections, it will need to be.
In summary, a workflow will be scheduled to run on all of the items in the Reporters list on a periodic basis. This workflow will create new items in the Reports library, and set a number of their values. The creation of these forms will initiate another workflow that will notify the reporter that the report is due, allow them to enter form data, and then move the form through an approval process. This walkthrough will cover only the basics, but all of the constituent items can be elaborated upon.
Step 1 – Create and publish a form
I’m going to assume that you know how to work with InfoPath forms in SharePoint already, but if not, there is a good walkthrough on this topic here. There are a few differences that you need to take into account when you want workflows (or data views) to be able to populate form data, so I’ll outline the steps required here. We’ll be working with the following simple form:
Not only will we want to promote the two highlighted fields into the SharePoint library, but we’ll want our automated workflow to fill them out for us ahead of time. The expense detail fields are repeating fields, and they can’t be promoted, except in aggregate. To do this, we’ll first need to publish our form to our SharePoint library. We’ll want to add or change our promoted fields, so we can’t use Quick Publish at this point. From the File menu, click Publish, and select SharePoint Server.
Follow the prompts for publishing, but stop at the field promotion screen. click add to add in a field to be promoted.
From here, you can select the fields that you want to promote. The critical part is that you must select the “Allow users to edit data in this field by using a datasheet or properties page” option. This allows direct editing of form data from SharePoint, and consequently, workflow.
Not all InfoPath field types support this capability, so you will need to work within these limits.
Complete the publishing wizard and test the form. Once you are happy with the result, it’s time to move to step 2.
Step 2 – Modify the form template to allow automated creation
Workflow processes, including Nintex workflows can create list items, documents, etc. This is also true of InfoPath forms. However, due to the way that an InfoPath form works, it’s not as simple as creating a new item using the built in template. With InfoPath, the standard template that gets published by the Designer is an XSN file, whereas the finished product is an XML file. Basically, we need to create a finished product, and use it as the template instead of the Designer generated XSN. We will want to keep the XSN around for future modifications though.
This step is also only pertinent if you’re using a forms library. If you’re just using InfoPath as a form for a SharePoint list, you can skip this step.
First, create a new item using your form. Immediately save, or submit your form, depending on how you’ve built it. In my case, I save it, and call it “ExpenseReportBlank”. Once the form is closed, you will see a new entry in the library. Now, you want to download it to a local file system. To do so, hover over the form, select the dropdown, and from the “Send To” item, select download.
The next step is to make this file available to the library as a template. To do that, we need to access the “Forms” folder for the library. The easiest way to do that is by opening the library in the Explorer view, navigating to the forms library, and copying the file to it.
Once it’s there, we can set it as the template for the library. To do so, click on Library settings, in the ribbon, and then Advanced settings. From there, you edit the Template URL to point to your new template. Once you do that, you need to click OK, and then come back into Advanced settings. Doing so “registers” the template. Once that’s done, you need to set the “Allow management of content types” option to yes, otherwise the form will not open in the browser.
Click OK, and return to the library. Test out your new template to make sure that all is well. If you’re happy with your form, we’re ready for the automation.
Step 3 – Add the workflow specific columns
At this point, we want to add in all of the columns that will drive our workflow. In a simple approval, you might want to use submitter, approver, and status. The submitter and approver fields will be “Person or Group” fields, and status will be a choice field consisting of submitted, approved, and rejected as possible choices.
The final list of columns in our example can be seen below:
Of course, the exact columns that you will need will vary with your workflow. We won’t be developing the actual approval workflow here today, but these are typically what you’d want at a minimum, and what we’ll be populating automatically.
Step 4 – Create the “Reporters” List
For the next step, we will create the list that contains the workflow to create the scheduled reports using the custom list template. The list will contain fields for submitter and approver. The title field will be renamed to employee in order to get the full display name of the employee as opposed to the account name (there are other ways to accomplish this, but this is simple for our purposes).
Next, create at least one entry in the list. We’ll use this for testing.
Step 5 – Build the Form Creation Workflow
The workflow to create the for is relatively straightforward. It needs to accomplish 3 things in our example.
Get the current date to be used for our Week Ending field
Build a form title that will be unique (Concatenate Employee and Date)
Create a new Report in the reports library
The complete workflow appears below:
The first three actions are just variable assignments and text manipulation, but the last action is the one that creates the report and is worth opening:
Once the workflow is built, go ahead and run it. If everything is working, you will see a new report in the Expense Reports library.
If you have an approval workflow defined to start when items are created in the destination library, it will be running. The last thing we need to do now is to schedule the workflows.
Step 6 – Schedule the Workflows
Nintex supports the scheduling of workflows. Essentially, you pick a time for workflows to start on individual list items. This is done on an item by item basis, and needs to be performed for each item in the source list.
From the “Reporters” list, hover over one of the configuration items, click the dropdown and select “Schedule Workflows”.
You will presented with a screen showing you the scheduled workflows for that item (likely empty). Click the “Add Schedule” button, fill out the schedule form, and click save.
In this case, our workflow “Create New Report” is scheduled to run every Saturday at 2PM indefinitely. This will create a new weekly report for each employee that is configured, and if configured will kick off a review and approval cycle.