In this article, we will create a Gantt Chart or Progress bar inside a table in SSRS . In SQL Server Reporting Service does not allow a report programmer to modify a “width” property at run time. The challenging part of this task is how to create a scale and how to create a progress bar that spans a start date and end date. But SSRS have a range bar chart that will help you to make a Gantt chart or progress bar. And display diamond shape if start date and end date is same. For creating Gantt chart, we need a Data source, a Dataset, a Table and a Range Bar chart. Steps Step 1: First create a new data source. Step 2:  Before creating a data set, we need to write a Fetch XML query that created the data we’re going to work with. Fetch XML Query: <fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”> <entity name=”milestone”> <attribute name=”createdon” /> <attribute name=”item” /> <attribute name=”projectid” /> <attribute name=”contractorid” /> <attribute name=”packageid” /> <attribute name=”startdatemilestone” /> <attribute name=”remarks” /> <attribute name=”finishdatemilestone” /> <attribute name=”startdateforecast” /> <attribute name=”finishdateforecast” /> <attribute name=”startdateactual” /> <attribute name=”finishdateactual” /> <attribute name=”progress” /> <attribute name=”status” /> <attribute name=”durationdays” /> <attribute name=”sequencenumber” /> <attribute name=”milestoneid” /> <order attribute=”item” descending=”false” /> <filter type=”and”> <condition attribute=”projectid” operator=”eq” uiname=”ABC” uitype=”pmtracker” value=”{86AF05D9-6962-E911-A834-000D3A07F3D7}” /> </filter> </entity> </fetch> This gives us a result set like this : Sequence Number Item Package Start Date(Milestone) Finish Date(Milestone) Start Date(Forecast) Finish Date(Forecast) Start Date(Actual) Finish Date(Actual) Duration(Days) Status 1 Concept Design Lighting 02-01-2019 06-02-2019 03-01-2019 09-02-2019 03-01-2019 09-02-2019 37.00 Completed 2 Concept Design approval Lighting 13-02-2019 13-02-2019 07-02-2019 14-02-2019 07-02-2019 13-02-2019 6.00 Completed 3 Detail Design Lighting 14-02-2019 17-04-2019 14-02-2019 17-02-2019 14-02-2019 18-02-2019 4.00 Completed 4 Detail Design Approval Lighting 17-04-2019 23-04-2019 17-04-2019 24-04-2019 17-04-2019 22-04-2019 Delay 5 Tender Issue Lighting 29-05-2019 31-05-2019 01-06-2019 03-06-2019 01-06-2019 03-06-2019 Delay 6 Tender Return Lighting 31-05-2019 22-06-2019 03-06-2019 03-06-2019 03-06-2019 24-06-2019 21.00 Completed Step 3: Now we create a Data set from this query: If start date and end date is same then need to display diamond shape, for that we have to add three calculated field. Need to find number of days between two dates. DayDiff_MileStone=DateDiff(“d”,Fields!startdatemilestoneValue.Value,Fields!finishdatemilestoneValue.Value) we are going to set the following properties: We’re going to create a table within the report that has a column for each of the columns in the data set, plus a column for a graph. We just have to bind the Table to the data set “MilstoneData” Then drag the columns of the data set to the columns of the table. Step 4 : Build the Gantt Chart SSRS have the Range Bar Chart option. Just drop the chart onto a blank area of the report. We’ll fit it into table later. Now we are going to take fewer part of the chart. We’re throw away of: The chart title The legend The vertical Axis title The vertical axis The horizontal axis title When we’re done, the chart should look like this: Let’s start setting up the chart. The first thing we need to do is set the Minimums, Maximums, Intervals and Interval Types. The client wants minimum date must be from “startdatemilestone” date and maximum date should be “finishdateforecast” date. On the Horizontal Axis, we are going to set the following properties: Minimum =DateSerial(Year(Min(Fields!startdatemilestoneValue.Value, “MilestoneData”)), Month(Min(Fields!startdatemilestoneValue.Value, “MilestoneData”)), -1) Maximum =DateSerial(Year(Max(Fields!finishdateforecastValue.Value, “MilestoneData”)), Month(Max(Fields!finishdateforecastValue.Value, “MilestoneData”)) , 0) Interval 1 Interval Type Months Also going to set the format the of items along the Horizontal Axis to MM-yyyy Go to chart series properties, set Marker properties. Let’s get a preview of the report and see how we’re doing. It’s correct, but it doesn’t provide the milestone data. Now cut the chart and paste it into the details row of the Table, in the right most column. Now we’re going to run a preview. OOPS! We receive the error “The chart has a detail member with inner members. Detail members can only contain static members. What this means is that chart cannot live in a detail row. We’re going to create a Group that contains only a single Item. After selecting the Table, at the bottom left of the screen, we find “Row Groups”, Just click on preview shows us: Nice-looking but do we really need a timeline on every now? Now we are going to need a timeline. So, let’s take a copy of the Chart and put it right next to the header “Duration” Now we can hide the Axis Labels on the chart in the footer. We can also change the Axis line style to none. Click on preview Wow! Hope this helps you! Check out my other blog here https://www.cloudfronts.com/performing-update-operation-in-microsoft-dynamics-nav-through-integration-in-scribe-online/

Please refer to my previous blog better understand why the same records are required in two different Organizations or Environments. Click Here Import Phase: Login to Destination Environment in which you want to import the Data with the same GUID. In Destination Environment, Open the Entity View and click on right three dots for import options. Click on the > which is present on the right side of “Import from Excel” Click on the Import from CSV You will get the Screen like shown in below Screen and now you have to select the file and click on Next   You will get the Screen as shown in the below Screenshot. Now, click on the Review mapping. After Clicking on the Review Mapping you must map the column header to fields of Entity. Now, here comes the most important of this blog. Here you have a map the column which has the GUID of the entity to Entity name present in the Destination Environment. Here I have mapped Customer Header (having GUID) to Customer (Entity Name). You can ignore the mapping of “Created On” Date or else you can map it will “Record Created On”.   Click on the Finish Import and see the magic. All the Data will be imported with the same GUID as of source Environment. You can see the imported data in the following screenshot. To check whether the GUID of records present on both the Environment is the same or not. Just export data from both the Environment and match the GUID of each record. Below you can see the GUID of imported and exported records are the same. Following is exported Data from Source Environment. Following is exported Data from Destination Environment.

Why it is required? Let’s discuss the scenario where we will be required to have some records in the Multiple Environment. Let’s say we have workflow or flow which is configured or running with specific records. So, if we move the flow or workflow to another organization or Instance so the same record is not available with the same GUID. To run the flow or workflow we must change the flow or workflow. Now if we have 10 – 20 flow / Workflow like the same, then it will be so much time consuming So, the best solution to have the Same records with the same GUID in multiple instances. Prerequisite: The system must have the same entity and fields present in both the system in which you must transfer records. Because it will through an error while import to destination Environment Solution: Exporting Phase: Login to Source Environment from where you want to export the Data. Open the Entity of which data needs to be migrated. You can see I have an Entity called Customer which is having a few numbers of records present in the system. Now You need to export the Data from the source Environment. Export Data from the advance find or direct from the View. After Exporting the Data from the system, an excel file will be downloaded with the same columns as in the views. Now, open the exported Data file. The following is the screenshot of the file. You can see there will hidden Columns in Excel A, B, and C. Now to Unhide the Hidden Columns, select all Data Or Press Ctrl + A and Go to Format in Home Section > Under the Visibility Section Click on Hide & Unhide > Unhide Columns. Please see the following Screenshot for more reference. Now You can see all the three Columns as (Do Not Modify) Entity Name, (Do Not Modify) Row Checksum & (Do Not Modify) Modified On. You can see the Hidden Column in the screenshot after unhiding. Let’s move forward now, we must delete (Do Not Modify) Row Checksum & (Do Not Modify) Modified on Columns because we are not required those columns while importing to another environment. If we will import with those columns it will through the error while import Operation. Change the Header from (Do not Modify) Entity_name to Entity_name for convenience while mapping during importing and Save as CSV. Now Your File is ready to import in another system. Let’s go towards the importing phase.

Introduction: Converting Date/Time values according to a time zone is quite challenging task in SSRS Reporting. For D365 CRM online we can achieve this using CDate Function. I will demonstrate how to use CDate function with a dynamic time-zone parameter. Step 1: Add this reference to the report properties: Click on “References” and then click on “Add” button under “Add or remove assemblies”. Browse the following file from your BIDS folder. Microsoft.Crm.Reporting.RdlHelper, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 Click on Ok button to save the changes. Step 2: Create the parameter in the report data section in the “Parameters” folder as shown below: In the “Default Values” section check the click on “Add” Button. Enter the User Time Zone in the Formula and click on OK to Save the Changes. Step 3: To use this functionality is simply as follows: DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value))) Example: If the Expression or Formula is as follow: Last 30 =WeekdayName(Weekday(DateSerial(Year(Fields!msdyn_dateValue.Value), Month(Fields!msdyn_dateValue.Value),”1″).AddMonths(1).AddDays(-30))) The Replace it with: Last 30= WeekdayName(Weekday(DateSerial(Year(DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value)))), Month(DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value)))),”1″).AddMonths(1).AddDays(-30))) Note: This is an easy way of dynamically converting a time zone from UTC to the user’s local time using CRM Online. We have to replace all the formulae which contain the DateTime field value which is retrieved directly from FetchXML with the above mentioned Function to avoid issues in SSRS reports due to Time Zone Conversion.

Introduction: Handling Errors is a best practice in Integration when integrating to keep a track of the Errors that occurred during Integration. The Customized Error Handling is Handy to find quick Errors to understand them and resolve as required. This approach can be extended to any Source or Destination as required. Our Scenario: Integration from SalesForce – NAV. Different Approaches to Error Handling: No. Approach Description Customizations Required Comments 1. Entity Level All errors will be logged in an ‘Error Log’ entity in Salesforce and NAV along with the error description and date & time the error occurred. 1. Custom entity ‘Error Log’ 2. Fields for ‘Error Log’ entity: i. Name [String(100)] ii. From Entity [String(100)] iii. Error Message [Note] iv. Record ID [String(100)] v. Created On Date [DateTime]   For instance, i. Name: Error while creating ‘Customer’ in NAV ii. From Entity: Customer iii. Error Message: The specified Country is not valid iv. Record ID: Cust123 v. Created On Date: 08/07/2018   Example:    2. Field Level If an error occurs for a record during integration, the error description will be stored in a custom field on the record in Salesforce or NAV. A custom field ‘Error Description’ will have to be created on the form of all required entities. Field: Error Description: [Note]   For instance, when you open a Customer ‘Cust123’, Error Description: The specified Country is not valid   Example:

Phase 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM: The Map after Completion will look as follows. Step 1: Add an OLEDB Source and configure it as follows: Click on Parameters and configure it as follows: Step 2: Add a derived column transformation to specify the Customer Type i.e 2 which indicates that Customer type is an Account. Also, we will map the Invoice Number to name, so we will create a copy of it. There is a single PriceList for all Invoices in my case so I have hardcoding for Lookup Purpose. Step 3: Add a CRM destination Block and configure it as follows: We will use Upsert Criteria specified on Invoice Number for Integration. The fields mapped are as following. Step 4: Add the remaining blocks for Custom Error Handling as explained in the Blog in Link: https://www.cloudfronts.com/creating-custom-error-log-table-in-ssis-for-oledb-block-and-kingwaysoft-dynamics-crm-block/ Note: This is the second part of the 4 part blog series. Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM. Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM. Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic.  

Part 3/4 SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Step 1: Add a DFT to Integrate the Invoice Details in the Control Flow. The Map after Completion looks as follows: Step 2: Add an OLEDB Source and configure it as following: In our case we are Inner Joining Invoice and Invoice details Table based on OrderNumber and  Invoice Details and Products Table based on ProductID. This will help us to retrieve the columns which are not provided in a single Table and are required for Integration of Invoice Details. (Note: this might vary according to your scenario) Click on Parameters and configure it as follows: Step 3: Add a derived column transformation to create a unique name for Invoice Details as an upsert criteria. Step 4: Add a CRM destination Block and configure it as follows: We will use Upsert Criteria specified on Invoice Detail Unique Name for Integration of Invoice Details. The fields mapped are as following. Step 5: Add an OLE DB command transformation to update the Ported status to 1 indicating the Invoice details have been integrated. Configure it as follows: Step 6: Add the remaining blocks for Custom Error Handling as explained in the Blog in Link: https://www.cloudfronts.com/creating-custom-error-log-table-in-ssis-for-oledb-block-and-kingwaysoft-dynamics-crm-block/ Note: This is the third part of the 4 part blog series. Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic:

Part 4/4 Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic Introduction: To handle the scenario where the Invoice Details fail to get Integrated or Incomplete Integration of Invoice Lines the Invoice itself should get deleted from CRM as we don’t want to Integrate Incomplete records. Step 1: Add a DFT to Delete Invoice in the Control Flow as follows: The Map after Completion looks like the following: Step 2: In our case, we are Inner Joining Invoice and Invoice details Table based on OrderNumber and Invoice Details and Products Table based on ProductID. This will help us to retrieve the columns which are not provided in a single Table and are required for Integration of Invoice Details. Add an OLEDB Source block with the required query and configure it as follows: Click on Parameters to add a Parameter as follows: Step 3: We have a Ported field in our Data Base which set to 1 once Integrated with CRM. Add a Conditional Split Block to Split into two categories based on the sum of ported and count of ported as follows: Count of Ported = Sum of Ported This indicates that all the Invoice Header and all its Details have been Integrated Properly. In this case, we will add an OLE DB Command Block to update the status of Invoice Header to 1 as follows: Count of Ported != Sum of Ported This indicates that all the Invoice Header and all its Details have not been Integrated Properly. In this case, we will add an OLE DB Command Block to update the status of Invoice Header to 0 just as above where we update it to 1: Once the Status has been Updated we have to Delete the Faulty Invoice Record from CRM. We have performed Lookup based on Invoice Number as follows: Output: Source (SQL Server) Invoice Header- Invoice Details- Execution of Package – Destination (CRM) We can see that one Invoice is Integrated which had all proper data. The Other Invoice which failed to Integrate in some aspects has been automatically deleted from CRM. Note: This is the fourth part of the 4 part blog series. Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic

Customers are crucial to the success of any business. When you build your business focusing primarily on giving the best experience to your customer, your business is going to bloom. Your profits are going to multiply each year. Dynamics 365 for customer service is one of the best ERP solutions that you can find in the world.  It will help in streamlining the processes in all the departments to make your business customer-friendly and customer biased. When customers know that you value their association, they are going to stay with you for a long time becoming your loyal customers.  People who never used this ERP solution may not know how to find what they want. Here is a topic that you should learn as it will be quite helpful to you in the long run. Data Integration project support two types of executions Manual and auto-refresh i.e. schedule-based refresh. For a manual refresh, we have to select the project and click on run. In this blog, we will learn how to create a schedule to run an Integration project at a specific date and/or time. First, go to Admin Center Go to the Data Integration -> Projects and click on the ellipses of the project to be scheduled and click on Schedule We will get two options a. Run Manually b. Run on specific day and/or time  Now let’s schedule the project, in this example we going to schedule the project every hour starting at 17-Aug-2019 to 30-Aug-2019, and it should run form 12 AM to 2 AM. After scheduling click on save We can schedule the project in another way also, to do this  go to the scheduling after selecting on the Integration project After clicking on the project following window will open and then click on the Scheduling and same scheduling window will appear. In this way we can create schedule-based refresh for the Integration project

Error handling is very important part of any integration project, it is also very important to fix the issue when it is recognized, so it is very important to take action to resolve it earliest. In the Integration project of CDS we can setup email alert option in following way. First go to Admin Center Go to Data Integration -> Select the project -> Go to Scheduling Following window will appear Tick the checkbox and specify the email address on which we wanted to receive the alerts and click on save. In this way we can set up the email alert notification for Integration progress in CDS.