In this blog, we will learn how to create blank Tabular model project. Pre-requisite: Visual Studio with Business Intelligence template. Data source to import data into Tabular model (in our case we are using SQL Server). Analysis Server to deploy Tabular model.   Go to Visual Studio, and create new project, go to Templates > Business Intelligence > Analysis Services > Analysis Services Tabular Project, give a name and click on OK. Once click on OK following window will appear to select Analysis services instance. Select the workspace server in our case workspace server name is “localhost”, Compatibility level as “SQL Server 2017/ Azure Analysis Services(1400)” and Click on Test connection to check whether, it is connected to workspace server or not. After clicking on OK project will be created and we can see the Tabular model Explorer, under that we can see the following sections: Data Sources, Expressions, KPI etc. as shown in below snapshot And in solution explorer we can see the solution details In this way we can create Tabular project, in next blog we will import data source in it.

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/

Introduction: One of the common Errors while attaching a .mdf file for restoring Data is as follows: An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details. The detailed message is as follows. Step 1: Right, Click on the .mdf that you want to attach and click on Properties. Step 2: Go to Security tab and give Full Control to all Users and Groups by clicking on Edit. Click on the Check Box under Allow section for Giving Full Control to all Users and Groups. Step 3: Right-click on the Main Folder containing your .mdf file and go to the properties section. Deselect the  “Read Only Option” in “General Tab” and click on Apply. Step 4: Go to the “Security Tab” and give “Full Control” to all “Groups and User Names” as shown in Step 2 and click on Apply. Select “Apply changes to this folder, subfolder and files” option and click on “Ok”. Click on “Ok” to apply all the changes. Step 5: Now go to SQL and Attach your .mdf file Again and your DB will be attached to the Databases folder and you can access it. Check out my other blog here https://www.cloudfronts.com/blog/dynamics-nav/performing-update-operation-in-microsoft-dynamics-nav-through-integration-in-tibco-cloud-integration/  

Introduction: Use the Update Block to update existing records in the source or target datastore. While integrating with Microsoft Dynamics NAV as a destination use the Update Merge Block to perform the Update Operation in Microsoft Dynamics NAV. The Update Merge block can configured as follows. General Tab Following Options are available under the General Tab. Change the Block Label Add Description Set Batch Processing Options Select the entity to use for this Operation. Matching Criteria Tab Following Options are available under the Matching Tab. Specify one or more fields in your source and target data to select records for this operation. Fields Tab Following Options are available under the Fields Tab. Map the required fields from your source to destination directly or based on some formulae. Note: The following fields must Hardcoded Error Handling Tab Following Options are available under the Error Handling Tab. When an operation fails — If the record cannot be updated, an error is logged. If no matches are found — If the record being processed does not match the criteria in the Matching Criteria Tab, it is not updated, and an error is logged. Errors and Warnings Tab Following Options are available under the Errors And Warnings Tab. Various Errors available are displayed. Once you have corrected the errors click on Validate button to update the Errors and Warnings Tab.

Introduction: Establishing a connection is an important step before developing a solution for Integration. While integrating with Microsoft Dynamics NAV as a source/destination a Connection is needed which can be established as follows. Step 1: Click on “More” tab and choose Connections. Step 2: Click on “Addition” Symbol to add a Connection. Step 3: Install On-Prem Agent on Nav server. Refer to the link below. https://help.scribesoft.com/scribe/en/index.htm#sol/agent/agentinstall.htm?Highlight=On%20prem%20agent Step 4: Add the details as follows. Connector Type: Microsoft Dynamics 365 Business Central/NAV. Name: Give a name for your connection. OData Service URL: Get the required Odata URL from NAV. Note: The URL should end with “Odata” at end. No additional forward slash must be added at the end of the URL which might result in errors. http://ftl-nav-dev:7048/FTL-DEVInstance/OData User: Find it after the date as follows. Password: Password for NAV Company Name: Find it before the date as follows. Agent: Select the On-Prem Agent which you had installed.

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.

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

Let’s say you need to create a new store with multiple registers on CPOS.   It is now possible to have a retail store with multiple registers each running CPOS and each having their own dedicated hardware.   For example: A store with two registers both running CPOS and both having their own receipt printer, scanner, etc. . Here comes the concept of a shared hardware station for CPOS, It may seem impractical to have multiple registers sharing the same scanner, pin pad, etc but it is actually possible.   You’ll need to install a hardware station per CPOS and then you should be good to go.   Sharing hardware can be handy in scenarios with limited space or lesser sales persons moving around taking the sales and so on and it’s valid for CPOS.   Note: However, for MPOS you need a dedicated hardware.   Please notice that you can’t have multiple hardware stations installed on the same machine. Please do correct me anyone if that has changed 🙂 This can be done absolutely and is perfectly possible. Just deploy a Hardware station on each device and it will work as discussed in the example above.