Poweruser's blog

Blog about the Power Platform

Month: August 2019

OData feed deprecated? What? The solution! – Power BI

Reporting on Navision (now Business Central) or Dynamics 365 CRM? OData feed was the way to go! We called web services who we had initialized in our ERP packages. But lately, there are more and more problems with OData. Especially when we want to enter our login details in the Power BI Service.

It is no longer possible to enter your organization login as credentials in the Service when setting up the automatic refresh. Microsoft gives us a boost to take a closer look at the PowerPlatform. There is also a Business Central connector and a CDS connector for Power BI Reporting.

Business Central Connector

The Business Central connector contains all web services that have been added to the Webservices table in Business Central. The connector makes it much easier for the Power BI user. No more messing around with OData URLs, just select the tables that are needed. This can also be done multi-company! If your user has the correct rights to different companies in Business Central, he can address the tables across the different companies.

CDS Connector (Common Data Service)

The CDS connector is not as straightforward as the Business Central connector. To be able to use the CDS connector with the server URL in your possession. The server URL is the address where you surf to open your Dynamics 365 CRM. This usually looks like this: https://tenantname.crm4.dynamics.com. When you enter this and click on the “OK” button you will see all the entities in your CRM.

These are the entities for which your account in CRM has rights. Then the same way as the Business Central connector, select what you need.

Optional fields

tenantname = name of the tenant
crm4 = your region code for the tenant

Conclusion

So we can conclude that using certain connectors becomes more important than using the OData feed. There are two reasons for this:

  1. It is easier to retrieve the data from the sources. In order to collect the necessary data, less knowledge of “code” is needed.
  2. Logging in is much easier because there are no more choices to be made. Your office-account becomes very important for the connector.

An important point the data technicians have to take into account is the rights they have in the application itself, here Business Central and Dynamics 365 CRM. I hope you enjoyed reading this article and good luck building reports on BC and Dynamics 365 CRM!

A quick tip, to end the week with: play with dates. – Flow

There are several options in Microsoft Flow to start manipulating a date. You have the functions adddays(), addmonths(),… and then also the actions Add Time and so on. However, these don’t always give you the desired result. So here’s a quick fix to get your desired date.

We’re going to divide the date in the flow first into day, month and year. After that, we will edit these components separately and in the last step, we will merge them into the new date.

The Flow

Step 1 – Initialize the date

Enter your date in the ‘Value’ place. The advantage of using a variable is that you only need to initialize it once. After that, you can use the same name for each step. The date can come from any data source: SQL, CDS, SharePoint,…

Step 2 – Modify the date parts

As you can see we do 3 operations on the date. One of each segment of the date. If you only want to change the month or the year this is also possible. Then you only need to use the Flow block that applies to you.

Formula Year

add(int(formatDateTime( variables(‘date’) ,’yyyy’)), number_to_change)

Formula Month

add(int(formatDateTime( variables(‘date’) ,’MM’)), number_to_change )

Formula Day

add(int(formatDateTime( variables(‘date’) ,’dd’)), number_to_change )

The number_to_change is the parameter you need to change in the formula. this will take care of the number of days, weeks or years that will be added to or subtracted from the date.

Step 3 – Recombine the date parts

In the penultimate step, we are going to overwrite the variable date. this is done by means of the action ‘Set Variable’. The outputs are the outputs of the previous 3 compose steps.

At the latest, we are going to convert the date to a UTC Format. In this example, it is set up according to the European standards but you can change this to your own interest.

Formula Compose

convertToUtc(variables(‘date’),’W. Europe Standard Time’,’dd/MM/yyyy HH:mm’)

So this was my first quick tip for Flow. Hopefully you will be able to apply it as well. See you next time!

SQL SERVER Connector, how to filter the date? – Flow

The Problem

Last week I was asked to retrieve a number of rows from SQL Server (on-prem). These rows have to create a site on SharePoint in real-time. That’s where we bump into our first problem: getting real-time rows from an SQL Server. Unfortunately, it is impossible to use this trigger for a SQL Server on-prem. As explained in this blog post: https://flow.microsoft.com/en-us/blog/introducing-triggers-in-the-sql-connector/. In consultation with the end-users, we have agreed on a certain period of time during which we will pick up the rows.

After solving the retrieval of the rows, we collide with the next problem. Making a date filter to pick up the rows that were made during the last period. It was my very first time I implemented a date filter in flow. After a while, I managed to solve the puzzle. I want to share my solution in this blog post because I don’t think it’s the easiest solution to figure out.

The Flow

Step 1 – The Interval (here 5min)

To avoid the problem with the trigger, we will use the ‘Recurrence’ trigger in combination with the ‘Get paste time’ action.
This way we get every 5 minutes, the time of 5 minutes ago. The data in the ‘Created on’-field will have to be greater than the calculated time.

Step 2 – Get the rows out of SQL Server

In this step, we are going to retrieve the rows from SQL Server. These must be filtered based on the syscreated field which has DateTime as a datatype.

It is obvious that we are going to use the ‘utcNow()‘ function? This function is included in Flow by default and provides us today’s date. But here is the catch, the formatting of both DateTime fields does not match. I used different formatting for the ‘utcNow()‘, but none of the results were positive.

So I decided to change tack. Instead of using the full DateTime as a filter, take a look at both parts of the field itself, the date and the time part. This is the solution that worked for me:

Filter Formula:

date(syscreated) ge date(@{utcNow()}) and time(syscreated) ge time(@{body('Get_past_time')}))

Note: syscreated is the DateTime field from the SQL Database.

Step 3 – Create a SharePoint Site

In this last step, we are going to create a site with the data we extracted from SQL Server. The body statement, provided with parameters will fill in the following information on SharePoint:

  • displayName: This is going to be the site and group name.
  • alias: This will provide the Url of the site (example test.sharepoint.com/sites/alias
  • isPublic: This parameter will set the group visibility to ‘Public’ (if isPublic=true) or ‘Private’ (if isPublic=false)
  • Description: This will be the site description.
Afbeeldingsresultaat voor first post ever

So this was my first blog post ever! I hope you enjoyed reading. I’ll see you later for a new post!

© 2020 Poweruser's blog

Theme by Anders NorenUp ↑