Issue: In SSRS if we are using an aggregate function in a group at the Tablix level, then you may realize that the simple row numbering function does not give a current sequential ranking. Using the function RowNumber(Nothing) gives something like this. Using RowNumber(“GroupName”) would also give an incorrect row numbering and look something like this. This is because RowNumber does not actually give the row count. Rather it counts the incidences of the data in the group and returns that value. Solution: We can use the “RunningValue” function in SSRS. The format for the expression would be. =RunningValue(<Grouped field>,CountDistinct,”<DataSet>”) Eg: =RunningValue(Fields!Name.Value,CountDistinct,”Accounts”) This would return something like this.   This should fix your issue!

Introduction: In this article, we will learn how to dynamically use Dynamics 365 CRM option set/ CRM picklist in Power BI. As you may be aware that CRM picklist stores Integer value in the backend and not the actual display name. So, whenever you query an entity with picklist fields in Power BI you will get picklist Integer value and not the display names. Pre-requisite: The option set/picklist should be a global. You can check the IsGlobal property for the picklist field in the Metadata Browser. In Metadata Browser, when you expand Get Options for OptionSet there is a (+) button. Click on that button and option set properties will appear. Steps to get Option Set Values: Login in to Power BI Click Get data Select Odata Feed as the connector Enter the OData URL for querying the Global option sets and click Ok. URL Format: https://orgname.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(OptionSetMetadataId) Example: https://contoso.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(ae2a3b9d-395a-4dbd-9abc-c32aeb10888b)Note: OptionSetMetadataId is highlighted in the Pre-requisite screen capture Once the query is loaded, the Edit Query window will pop up.  Click on Advance Editor and modify the code as below (Add null and MoreColumns parameter) and then click Done: let Source = OData.Feed(“https://fasttrackleasingllc.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(ae2a3b9d-395a-4dbd-9abc-c32aeb10888b)”, null, [MoreColumns = true]) in    Source After loading, an additional row will be added to the table named More Columns. Click on the Record field beside More Columns. A new view of the table will be loaded as shown in the below screenshot. Right Click on List and Select ‘Into Table’ Once the table is loaded, expand the Value column by clicking on ‘Expand to New Rows’ Expand the Value Column and select ‘Value’ and ‘Label’ Columns. Click Ok. Expand the Value.Label Column and select ‘UserLocalizedLabel’ Column. Click OK. Expand the Value Column and select ‘Value’ and ‘Label’ Columns. Click Ok. Finally, we get the Option set Code (Value.Value) and Option Set Value (Value.Label.UserLocalizedLabel.Label) from CRM. Conclusion: As the above method creates a separate table for option set, you have to add relationship (1-*) between the newly created Option Set table and the Main table.

Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: BasicHttpBinding_ItemService_find Every Find block has 2 components and they are, Query Criteria – Used for specifying the Table Name, Field Name and the Fields Value for finding the record Return Value – Used to fetch the needed value back If for any reason either of the component is missing, then you cannot lookup in AX and search the required value back. Consider the following Scenario where we have a SalesOrderService Find block with only the Query Criteria component but without the Return Value component. Following are the steps to resolve the issue. Steps: Go to your AX Web Service Connection and click on Edit Click on change connection Proceed further by clicking OK and then click on your connection and click on Edit Select the Configuration Tab on top Select the Find Block under Method that is not showing the required Return Value. Here that would be the BasicHttpBinding_SalesOrderService_find Method. Make sure that the value of QueryCriteria_CriteriaElement and ReturnValue_SalesTable both are 1. Note: If you want to look up with additional parameters then increase the QueryCriteria value. Validate your Web Service Connection and restart your DTS Your issue should be resolved and the Return Values should be visible Conclusion Now you should successfully be able to look up and find a value from any of the Return Values in the Sales Order successfully.

Introduction: The Variable Connector, created as part of the Scribe Labs initiative, adds a much-needed feature to TIBCO Cloud Integration i.e. to store and retrieve variables in a Scribe Map. However, keep in mind, these variables cannot be shared between maps or solutions. Steps For Installation: To begin using the connector, install it from the Scribe Marketplace. Go to Marketplace. Search for ‘Variables’. Select Scribe Labs – Variables and click ‘Install’. The Connector will install for your Organisation in a few minutes. Steps To Create A Variable Connection: From the ‘More’ dropdown menu, click on ‘Connections’. Click on the plus sign (+) on the right of the page to add a new connection. Select your Connector Type, input the name of the variable connection and select the Agent. Click OK. Steps to use in a Map: Add the variable connection to your map. To store a value in the variable, select the Upsert block. In the General Tab, select the data type of the variable you want to store from the Entity dropdown menu. In the Fields Tab, input the name of the variable in the ‘name’ field and the data you want to store in the variable in the ‘val’ field. Click OK. To retrieve a variable, use the Lookup Block from the Variable Connection. Select the data type of the variable in the Entity tab. In the Lookup Criteria tab, lookup the name of the variable you had set. Select the ‘val’ field in the Field List tab. Click OK. You can now use the data stored in the ‘val’ field of the variable in your map. Conclusion: I hope this helps understand the usage of the Variable Connector in TIBCO Cloud Integration. This feature is very useful when one needs the functionality of a variable while using TIBCO Cloud Integration.

In Power BI, Dynamics 365 is connected using its Web API i.e [organization URI]/api/data/v9.0 Dynamics 365 Web API does not support GET Request for calendarrule entity; because of which Power BI cannot retrieve calendar rule entity details. You can verify by browsing to the URL: [organization URI]/api/data/v9.0/calendarrules Please refer to the screen capture below for the error details in browser. Also, you will not be able to load the calendar rule entity in Power BI. Below screen capture displays an error received in Power BI.

Introduction: In this article, we will walk through steps on how to authenticate Business Central API using Basic Authentication in Postman Pre-Requisite: Business Central account Business Central API Basic authentication in Postman: In Postman, make a GET request to Business Central base API URL. Base URL: https://api.businesscentral.dynamics.com/v1.0/api/beta For using Basic Authentication, we need to add domain as well. Domain URL: https://api.businesscentral.dynamics.com/v1.0/myusersdomain.com/api/beta In Business Central, generate Web Service Access Key for your user. Now in Postman authorization tab, select Basic Auth in Type. Provide Username and Web Service Access Key Click Send

Introduction: In this article, we will walk through steps on how to authenticate Business Central API using AAD Authentication in Postman Pre-Requisite: Business Central account Admin Access in Azure Portal Business Central API AAD authentication in Postman: In Postman, make a GET request to Business Central base API URL. Base URL: https://api.businesscentral.dynamics.com/v1.0/api/beta For using successful authentication, we need to add domain name or tenant ID Domain URL: https://api.businesscentral.dynamics.com/v1.0/myusersdomain.com/api/beta In Azure Active Directory, create a new Web API Type application registration. Sign On URL: https://www.getpostman.com/oauth2/callback Reply URL: https://www.getpostman.com/oauth2/callback Required Permission: Dynamics 365 (Note: If you are not able to find Dynamics 365 under the required permission, type Dynamics in Search bar on required permission section. Also, remember to Grant Permission to Application(requires Admin permission)) Under the Keys section, add new Key and Save. Copy the Generated Key as it will not be visible later. Now in Postman authorization tab, select OAuth 2.0 in Type. Provide following details Give a Token Name Call back URL: https://www.getpostman.com/oauth2/callback Auth URL: https://login.windows.net/myuserdomain.com/oauth2/authorize?resource=996def3d-b36c-4153-8607-a6fd3c01b89f Access Token URL: https://login.windows.net/myuserdomain.com/oauth2/token?resource=996def3d-b36c-4153-8607-a6fd3c01b89f Client ID: (Application ID of Azure Application) Client Secret Key: Paste the Key copied in step 2.d Grant Type: Authorization Code Untick Request Token locally Click Request Token and then Use Token. If the token is successfully generated, then you will see Authorization Code under Header section in Postman Now Click send.

Once you open the SSRS Mobile Report Publisher, you’ll see a blank layout grid and available visualization types as navigators, gauges, charts, maps, and data grids: To start designing your report, simply drag-and-drop the visualizations onto the layout grid: By default, you’re designing the “Master” layout, but you can also design layouts for Tablet and Phone: After switching to the Phone layout, you simply drag-and-drop the visualizations you have already created to optimize the layout for phones: You can also switch to Preview to see how your report looks in Master, Tablet, or Phone layout: Phone Layout: You can also switch to Preview to see how your report looks.

Introduction: While migrating Email Activities from D365 v8.2 to v9, I came across the following error: “Operation failed. Label: Create email, Name: emailCreate2, Message: -2147220969: ActivityPointer With Id = XXXXXXXXXXXX Does Not Exist” This can cause some confusion as activitypointers do not need to be migrated before you begin migrating emails. What are ActivityPointers? The activity pointer (activity) entity represents any activity or task that is performed, or to be performed by a user. An activity is any action for which an entry can be made on a calendar. Whenever you create an activity record in Microsoft Dynamics 365, a corresponding activity pointer record is created. This indicates that the activity record and the corresponding activity pointer record have the same value for the ActivityId attribute. For example, if you create an Email record, the attribute values of Email.ActivityId and the corresponding ActivityPointer.ActivityId will be the same. This should not be confused with ActivityParties which represent a person or group associated with an activity. An activity can have multiple activity parties. Cause: Upon inspection, I discovered that those emails are linked to themselves, having the ‘parentactivityid’ field populated with its own activity GUID. The error was being caused because Scribe could not find the parent activity in the Target environment (since it had not yet been created). Solution: Remove the ‘parentactivityid’ mapping from the ‘Create Email’ block. Add an If-Else Block below the Create block to check if the Email has been created, and if true, use an Update block to update the Email with the ‘parentactivityid’. Now, the Email activity will be created with the correct GUID and once created, will be updated with the required GUID as ‘parentactivityid’. This solution resolved the above issue and email activities  can now be created without this error occurring.

Introduction: PowerBI introduced the feature to format line styles and legends for line charts a while ago. However, these features were not applicable to lines in combination charts like ‘Line and Stacked Column Chart’ and ‘Line and Clustered Column Chart’. With the April update of PowerBI Desktop, we are now able to customise the format for lines in combo charts to different styles, which then reflect in the legend, making the chart easier to read and understand. Steps: While using a combo chart, all line series are in the ‘Solid’ format with just the defined colours differentiating them and the legend only displays their respective colours as shown below. To format the line or line series, go to the ‘Format’ pane and under the ‘Shapes’ card, you will find several options to customise the format of the lines. Shade Area can be switched On to shade the area each line encompasses. ‘Stroke Width’ allows you to adjust the thickness of all the lines. ‘Line Style’ allows you to select whether the line will be displayed as a solid line, dashed or dotted line as shown below. The next option is ‘Show marker’, which when switched On allows you to select the Marker shape, size and colour. To customise each line in a series individually, switch On ‘Customize series’. You will get a dropdown to select from the line values and options to customise the format of each line based on the line value selected from the dropdown. Coming to the customisation of the legend, you can do so by going to the ‘Legend’ card under the ‘Format’ pane. In the Style option, you have three options in the dropdown menu – Markers only: This will display only the respective markers in the legend. Line and markers: This will display both the respective lines and markers in the legend. Line only: This will display only the respective line style in the legend (shown below). Conclusion: As we can see, the new April update allows us to completely customise the lines in a combo chart, which not only add more visual appeal but also enables the user to easily read and understand the chart and the data it represents.