The CALCULATE function in DAX is the magic key for many calculations we can do in PowerPivot. Below is the syntax: CALCULATE( <expression>, <filter1>, <filter2>… ) The expression that we put in the first parameter has to be evaluated to return the result (i.e. a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on. This expression is evaluated in a context that is modified by the filters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following data model we have imported in PowerPivot named ‘Contract’ & ‘Project’ Scenario 1 Compare Contract & Project data model on YearMonth Column and take sum of multiple records of revenue column of Project data model into Contract data model Project data model has StartYM & StartRevenue Column as shown below And Contract data model has YM column, using Project data model StartYM, StartRevenue columns & Contract data model YM column, here we have derived StartR column with the help of Calculate() DAX function as shown below Formula is =calculate(sum(Project[StartRevenue]),filter(project,Project[StartYM]=Contract[YM])) Scenario 2 Calculate running total of ToDo column in ‘Contract-ToDo’ data model on basis of YearMonth column as shown below Formula is =calculate(sum(‘Contract-ToDo'[ToDo]),filter(‘Contract-ToDo’,’Contract-ToDo'[YearMonth]

Key Technologies: CRM Online, Scribe Insight Note: This article assumes understanding of CRM Online entities and relationships. Knowledge of Scribe insight tool for data integration. For more information on Scribe Insight refer https://www.cloudfronts.com/scribe-integrationsmigrations/ Connections can be used to define the relation between the two entities. You can have an account related to any contacts. With the help of connection you can define that one of the contact is actually the employee in the company and another an employer. 1. Before connections, you should have connection roles and association between the roles defined. 2. Connection Roles A role should have a role category. Consider, a role ‘Client’ which is part of ‘Business’ category, ‘Sibling’ which is under ‘Family’ category. 3. Connection Role Association After defining a role, we should associate different roles. This entity stores guid of the main role and the associated role. Role ‘Client’ can have other roles associated with it. See below example. 4. Select record type ‘All’ to avoid restriction on any entity. 5. Connections Map the following fields for connections between records of different entities. • Record1id • Record2id • Record1objectTypecode (ex: account) • Record2objectTypecode (ex: contact) • Record1roleid (ex: Client) • Record2roleid (ex: Employee) • Description • Status • Ownerid Below is a sample of a contact with role ‘Client’ connected to another with role ‘Attorney’.

Inventory dimension are used to determine physical characteristics of product, storage of products well as track inventory transactions for product. There are three type of inventory dimension group 1. Product Dimensions: Product dimensions are characteristics that serve to identify a product variant. The following table shows the storage dimensions that are available. > Size > Color > Configuration > Style For open Product dimension group form navigate to Product information management > Setup > Dimension groups > Product Dimension Group 2. Storage Dimensions: A Storage Dimensions determine where and how a product is stored. > Site > Warehouse > Location > Pallet ID > Inventory status ( New dimension in AX 2012 R3) > License Plate ( New dimension in AX 2012 R3) To open Storage Dimension Group form navigate to Product information management > Setup >Dimension groups > Product Dimension Group 3. Tracking Dimensions: Tracking Dimensions provide companies with the ability to trace their products. > Batch > Serial Number To open Tracking Dimension Group navigate to Product information management > Setup > Dimension groups > Tracking Dimension Group The following information provides descriptions for the controls Storage Dimension Group and for Tracking Dimension Group.  Mandatory: If the Mandatory check box is selected, you want to have dimension as a mandatory dimension for products that are associated with the dimension group. The Site dimension is always mandatory. However, the Location and the Pallet ID dimensions cannot be set to mandatory. (Note: This option is only available for Storage Dimension Group) Primary Stocking: If the Primary stocking check box is selected, it is mandatory that the dimension be entered before any physical updates. (Note: This option is only available for Storage Dimension Group and for Tracking Dimension Group) Active: If the Active checkbox is selected, the dimension is enabled for any products that are associated with the dimension group. Blank Receipt Allowed: If the Blank receipt allowed check box is selected, Microsoft Dynamics AX 2012 accepts a blank value for the dimension at the physical update of a receipt. This option only applies if the Primary stocking check box is cleared. (Note: This option is only available for Storage Dimension Group and for Tracking Dimension Group) Blank Issue Allowed: If the Blank issue allowed check box is selected, Microsoft Dynamics AX 2012 accepts a blank value for the dimension at the physical update of an issue. This option only applies if the Primary stocking check box is cleared. (Note: This option is only available for Storage Dimension Group and for Tracking Dimension Group) Physical Inventory: If the Physical inventory checkbox is selected the dimension must be included in physical inventory. (Note: This option is only available for Storage Dimension Group and for Tracking Dimension Group) Financial Inventory: Select the Financial inventory check box if the dimension is to be included in calculating the financial cost of a product sold. (Note: This option is only available for Storage Dimension Group and for Tracking Dimension Group) (Example of Physical and Financial Inventory: If you booked an item into Warehouse 1 and Site 1 at $600 and the same item into Warehouse 2 site 1 at $400. (Cost FIFO) If you unticked physical inventory from warehouse you could pick 2 from warehouse 1 because the check is at site where you have 2. At the same time if you looked at the cost and unticked financial inventory from warehouse the cost of each would be $500.) Coverage plan by dimension: Select the Coverage plan by dimension check box if this dimension is to be included in coverage plans that are created through master planning. (Note: This option is only available for Storage Dimension Group and for Tracking Dimension Group) For Purchase Price: Select the for purchase price check box if you want to include the dimension as a criterion to determine a product’s purchase price. In the Journal lines, price/discount agreement form, you can create a trade agreement where you associate a purchase price with a specific dimension that is set up for a product. For Sales Price: Select the for sales price check box if you want to include the dimension as a criterion to determine a product’s sales price. In the Journal lines, price/discount agreement form, you can create a trade agreement where you can associate a sales price with a dimension that is set up for a product. Now you can use Inventory Dimension for Purchase Order for Sales order and also for Inventory Journals like Movement, Adjustment, Transfer, Counting, Item arrival, Counting etc. After Inventory transaction you can track the inventory by Inventory dimensions in On hand inventory form.

A CRM project of 3 to 6 months’ time is a good medium sized project, a medium scale organization looks for. It allows them to do proper resource allocation and extend their horizon to showcase their talent to bring out the best from the venture. But, this needs to be handled well with a full time PM dedicated for the project. Along with this the entire project cycle has to be ethically followed. Below is a step by step quick guide that can help a successful CRM Implementation for such projects to be done. 1. Prepare a project proposal and get the Sign – off/Approval on the hours estimated for the bifurcated tasks from client. 2. Create a Project Charter to assign a Project Manager to the project. 3. Allocate the resources needed. Here we carefully do a right mix of senior resources vs junior resources to develop the project. 4. Once the above is fixed the PM needs to prepare a project plan. Allocate the resources at task level. (Use MS Project for creating project plan). 5. Get the environments ready to start the work. Ideally we need 3 environments for the entire project life cycle: a. Dev Environment – Where developer carry there development activity. b. UAT Environment – Where Client performs test for the development done. c. Production Environment – Where the final product shall be deployed. 6. Since the development happens in Phases for a CRM project, we can follow “Agile Methodology” for development of CRM application. Following are the phases in which the development is carried out: 1. Design Entities. 2. Design Views. 3. Design Forms. 4. Create users in CRM. 5. Assign Business Units and Security Roles to the users. 6. Write Scripts/Business logic. 7. Write Plug-in’s if required. 8. Write workflows. 9. Do any Third Party integration – If Required. 7. With the above phases defined we can split then into 4 phases. Phase 1: (CRM Look and Feel) > Design Entities. > Design Views. > Design Forms. Phase 2: (User Security Roles & Business Unit) > Create users in CRM. > Assign Business Units and Security Roles to the users. Phase 3: (Business Rules) > Write Scripts/Business logic. > Write Plug-in’s if required. > Write workflows. Phase 4: (Any Third Party Integration) 8. For all the 4 phases defined above schedule UAT’s as and when they are finished. They should be defined as milestones in the project. 9. Each phase should be minimum of 1 week’s duration, which includes bug fix if any. (Depending upon the complexity and size of the project). They should also be reflecting in the Project Plan. 10. With all of the above set follow the Project development methodology defined. 11. After we are done with the Requirement Gathering Phase of the project which might be a littles exhaustive as we need to have call with the client for more than 3 days a week. 12. Post the requirement gathering phase is complete and we are in actual development phase we need to do a couple of things in order to proceed: a. Document the Function Requirements and prepare a “FRD (Functional Requirement Document)”. b. Simultaneously with project development prepare a “TSD (Technical Specification document)”. c. Schedule a bi-weekly call with the client to provide status update and also discuss on other requirements if any. 13. Ask the client to create User Test cases for all the entities. Hand over the same to us 1 week in advance before each phase is pushed to UAT. 14. After each Client meeting do make it a point to send out precise Minutes of meeting. 15. Every week once send a status update and work review report to the client. 16. At any point of time the project should have a following set of updated documents to refer to: a. FRD – Attached with CR’s. b. TSD c. Project Plan (MS – Project) d. One Note: This contains the following other project related information like i. Environment Details/ Credentials. ii. Agenda’s for each project meeting schedules. iii. Minutes of Meeting. iv. TO DO’s/ Tasks for the team. e. Issue Tracker Sheet. (Shared with the client) 17. At the closure of the project we should have the following documented and preserved as companies knowledge base: a. Learnings from project. b. Issues/ Challenges faced.

Introduction Adding sub-domain to UCC SSL 5 certificate and revoking existing SSL Certificate. Problem Statement: We have sub-domain called salesportal.mywebsite.com hosted on windows azure. Salesportal.mywebsite.com associated with SSL binding but that SSL is expired and we want to revoke that certificate and add it to UCC SSL 5 certificate from Go-daddy. => We have salesportal.mywesite.com hosted on windows azure. => SSL certificate purchase from Go-Daddy. In our case we want to remove standard certificate and add it to UCC 5 Certificate. Please follow below steps to revoke existing SSL and add it to New SSL certificate Installation To proceeds with SSL, first step is to download Open SSL. This is Open source software that will help us to create CSR request. Download Open SSL from following link: http://indy.fulgan.com/SSL/ Generate CSR Create CNF file for CSR For more detail visit following URL. http://azure.microsoft.com/en-in/documentation/articles/web-sites-configure-ssl-certificate/ Let’s understand how to apply SSL Certificate to multiple Domain. Download Sample code from http://azure.microsoft.com/en-in/documentation/articles/web-sites-configure-ssl-certificate/#bkmk_subjectaltname Example: # ————– BEGIN custom sancert.cnf —– HOME = . oid_section = new_oids [ new_oids ] [ req ] default_days = 730 distinguished_name = req_distinguished_name encrypt_key = no string_mask = nombstr req_extensions = v3_req # Extensions to add to certificate request [ req_distinguished_name ] countryName = Country Name (2 letter code) countryName_default = stateOrProvinceName = State or Province Name (full name) stateOrProvinceName_default = localityName = Locality Name (eg, city) localityName_default = organizationalUnitName = Organizational Unit Name (eg, section) organizationalUnitName_default = organization = Organization Name organization__default= commonName = Your common name (eg, domain name) commonName_default = www.mydomain.com commonName_max = 64 [ v3_req ] subjectAltName=DNS:payments. mydomain.com, DNS:blog.mydomain.com # ————– END custom sancert.cnf —– Note: => subjectAltName contains sub domain name. It can also contains main domain name as well. Example. www.crmonline.com File start with # ————– BEGIN custom sancert.cnf —– Above example can be tricky Problem statement 1: We have UCC SSL 5 certificate connected to payments.mywebsite.com and its running. We are adding salesportal.mywebsite.com domain to UCC SSL 5 certificate. In that case your Common Name will be payments.mywebsite.com and subjectAltName will be salesportal.mywebsite.com. Note: common name need to add from console window and subjectAltName in the cnf file. Example: # ————– BEGIN custom sancert.cnf —– HOME = . oid_section = new_oids [ new_oids ] [ req ] default_days = 730 distinguished_name = req_distinguished_name encrypt_key = no string_mask = nombstr req_extensions = v3_req # Extensions to add to certificate request [ req_distinguished_name ] countryName = Country Name (2 letter code) countryName_default = stateOrProvinceName = State or Province Name (full name) stateOrProvinceName_default = localityName = Locality Name (eg, city) localityName_default = organizationalUnitName = Organizational Unit Name (eg, section) organizationalUnitName_default = organization = Organization Name organization__default= commonName = Your common name (eg, domain name) commonName_default = www.mydomain.com commonName_max = 64 [ v3_req ] subjectAltName=DNS:salesportal.mywebsite.com, # ————– END custom sancert.cnf —– Problem Statement 2: We have UCC SSL 5 certificate connected to www.mywebsite.com and its running. We are adding salesportal.mywebsite.com domain to UCC SSL 5 certificate. In that case your Common Name will be www.mywebsite.com and subjectAltName will be salesportal.mywebsite.com. Please note: your common name you have to add from console window and subjectAltName in the cnf file. Example: # ————– BEGIN custom sancert.cnf —– HOME = . oid_section = new_oids [ new_oids ] [ req ] default_days = 730 distinguished_name = req_distinguished_name encrypt_key = no string_mask = nombstr req_extensions = v3_req # Extensions to add to certificate request [ req_distinguished_name ] countryName = Country Name (2 letter code) countryName_default = stateOrProvinceName = State or Province Name (full name) stateOrProvinceName_default = localityName = Locality Name (eg, city) localityName_default = organizationalUnitName = Organizational Unit Name (eg, section) organizationalUnitName_default = organization = Organization Name organization__default= commonName = Your common name (eg, domain name) commonName_default = www.mydomain.com commonName_max = 64 [ v3_req ] subjectAltName=DNS:salesportal.mywebsite.com, # ————– END custom sancert.cnf —– CSR Generation Write following command in Open SSL Console window. req -new -nodes -keyout myserver.key -out server.csr -newkey rsa:2048 -config sancert.cnf Note: sancert.cnf is above sample. myserver.key file is used to generate .pfx file. Steps 1: => Please fill basic information like Country name, State etc. => I have entered common name like payments.mywebsite.com because first time UCC SSL certificate is associate with payments.mywebsite.com. => If you are first time creating csr request then common name will be www.mywebsite.com If you want to check your CSR request then open CSR file in notepad and copy all code paste in following website. https://www.sslshopper.com/csr-decoder.html Revoke Expired SSL Certificate (GoDaddy) Always it’s better to revoke SSL certificate after it is expired. Please refer following URL for more support. https://support.godaddy.com/help/article/4747/revoking-an-ssl-certificate?countrysite=in Login to GoDaddy account: 1. Click on SSL Certificate: 2. Click on manage You can see we have one sub-domain Standard SSL is expired and another sub-domain has standard UCC SSL 5 certificate. We are going to add expired sub domain to standard UCC SSL certificate. 3. Click view status of expired domain. 4. Click on conform after that following window will appeared. 5. Wait for few minutes and refresh page. Certificate is expired successfully. 6. Following is status of certificate Add Domain to UCC 5 Certificate Click on domain which has UCC 5 certificate. If you have just purchase UUC 5 certificate then open that certificate and paste csr request. (Which also include # ————– BEGIN custom sancert.cnf —–# and # ————– END custom sancert.cnf —–#). Then click on manage. To add domain to UCC 5 Certificate, first we need re key that certificate Copy and paste your CSR in a text box Then Click on submit changes that will submit your New CSR request to GoDaddy Verification window will appeared. Refresh this page, you can see new Domain is successfully added to this certificate. Install certificate to Azure Download certificate from GoDaddy => Select Server type IIS and download zip file. => Important file is .crt extension and Ignore other files contain in zip => Open OpenSSl and click run as administrator(Run as administrator is IMP) Type following command in command line pkcs12 -export -out salesportal.pfx -inkey myserver.key -in 889d0fa6641ee566.crt myserver.key is … Continue reading UCC SAN SSL Certificate–GoDaddy/Azure →

Key Technologies: Power BI, PowerPivot, Business Intelligence Note: This article assumes experience on PowerPivot for Excel 2013/2010 Many a time data needs to be imported and processed from systems belonging to different locations and hence having different time zone. For example, consider the following scenario. A user residing in India having IST time zone might need to import data into PowerPivot model from CRM Online System having CEST as time zone required for Power BI Dashboards or for performing Data Analysis. There might be critical business data related to revenue or sales that matters the most. If date time is not properly converted or processed, end user might see incorrect output. When working with PowerPivot, after importing data into model, new calculated columns needs to be created before working on revenue reports or any business related critical data so that proper reports/results are displayed to end user. Here, we are converting to match data for CEST time zone. Similarly for other time zone, we can add/subtract UTC offset as shown below: Central European Summer Time (CEST). Offset UTC +2:00 hours Eastern Standard Time (EST). Offset UTC -5:00 hours Central Standard Time (CST). Offset UTC -6:00 hours Mountain Standard Time (MST). Offset UTC -7:00 hours Pacific Daylight Time (PDT). Offset UTC -7:00 hours

Key Technologies: Scribe Insight, CRM Online Note: This article assumes experience on Scribe Insight for Data Integration/Migration and understanding of CRM Online Sources: CRM, Salesforce TASK Activities like Tasks need to be migrated and linked to an entity (Account, Contact, Lead, Opportunity and so on). For storing the GUID from CRM, perform a seek step on the entity and then insert the task associated with it. Store the Guid of the Entity from seek step in a Variable (Leadid/Contactid/Accountid/Opportunityid) and then use it in the next step. Mappings required for Tasks from source to CRM NOTES Notes and Attachments are stored in Annotation entity in CRM Mappings required for Notes Attachment Mappings required for Attachments In Scribe, configure source and and select ‘annotation’ entity in the target(CRM). 1. Seek step will lookup ‘accountid’ and if it finds one, will continue to next step else the record will be skipped 2. Note/attachment will be inserted to the respective Id found. Similarly design for contacts, leads and opportunities by selecting the respective entity.

Key Technologies: Scribe Insight, CRM Online Note: This article assumes experience on Scribe Insight for Data Integration/Migration and understanding of CRM Online For all the Activities like Email, Letter ,Fax ,Phone Call etc. we need two packages to be set up a: Activity Entity to Activity Entity (Eg: Email to Email) [with Status= Open] b: Activity Party to Activity Party (For Party List fields of Email like bcc, cc, from, to) c: Status Update for the Activity Entity 1. For Activity Entity to Activity Entity (Eg: Email to Email) There are three field mappings which are mandatory to map ° RegardingObjecttypecode ° RegardingObjectId ° ActivityId (Guid of the Entity) RegardingObjecttypecode indicates to which entity is the record related Refer to following article for the Object Type Codes: https://msdn.microsoft.com/en-in/library/bb887791.aspx As per the RegardingObjectTypeCode we need to Setup the RegardingObjectId If(S70=1,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “account”, “tam_accountguid”, “accountid” ), If(S70=2,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “contact”, “tam_contactguid”, “contactid” ), If(S70=3,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “opportunity”, “tam_opportunityguid”, “opportunityid” ), If(S70=4,DBLOOKUP(S67, “Adapter for Dynamics CRM”, “lead”, “tam_leadguid”, “leadid” ),#null!)))) Where S70=RegardingObjectTypeCode S67=RegardingObjectId Apart from these three fields the other required fields can be mapped [NOTE: The Status of the activities should be set to Open for the Initial Migration so that the activity party are set correctly. Once the Party list attributes are set up after running Activity Party for that Activity we can then update the activities for the Status again] 2. For Activity Party to Activity Party Perform a seek on Activity Entity (eg: Email) [Note: We should lookup on activityid as well as activitytypecode so that all the activity entities are not processed and filtering is done] ActivitytypeCode indicates which Activity Entity is it related to Eg: Appointment=4201 Email=4202 Store the Guid of the Activity Entity in a variable for Step 2 Mapping of the following fields is required: ° ActivityTypeCode needs to be hardcoded   [Eg: If we are migrating Activity Party for Email it should be 4202, For Letter 4207 etc.] ° ActivityId to Variable created in Step 1 ° ActivityPartyid to ActivityPartyid ° PartyObjectTypeCode to PartyObjectTypeCode PartyObjectTypeCode indicates to which entity is the record related. As per the PartyObjectTypeCode we need to Setup the PartyId as: If(S26=1,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “account”, “tam_accountguid”, “accountid” ), If(S26=2,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “contact”, “tam_contactguid”, “contactid” ), If(S26=3,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “opportunity”, “tam_opportunityguid”, “opportunityid” ), If(S26=4,DBLOOKUP(S24, “Adapter for Dynamics CRM”, “lead”, “tam_leadguid”, “leadid” ), If(S26=8,DBLOOKUP2(FILELOOKUP(S25, “XREFUsers.INI”, “Users” ),”0″, “Adapter for Dynamics CRM”, “systemuser”, “fullname”,”isdisabled”,”systemuserid” ),#null!))))) Where S26=PartyObjectTypeCode S24=PartyId S25=PartyIdName ° ParticipationMaskName to ActivityFieldName So the Mapping can be mentioned as: Where S23= ParticipationMaskName IF(S23=”Optional attendee”,”optionalattendees”,(IF(S23=”Organizer”,”organizer”,(IF(S23=”Required attendee”,”requiredattendees”,””))))) Where S23=ParticipationMaskName Refer to following article for the Activity Party Entity: https://msdn.microsoft.com/en-us/library/gg328549.aspx List of ParticipationTypeMask and ParticipationTypeMaskName: 3. Status Update for the Activity entity We have to lookup on the ActivityId Update the Status and Status Reason of the Activity as specified in the source

1. To create an AIF service using Wizard, we are supposed to prepare an AOT query containing the tables with proper relations. 2. Once we generate the AIF Service using Wizard, the AxBC classes gets generated for each table present in the AOT Query. 3. AxBC classes contains the parm methods for each field as per table. 4. Now if we want to have a filed in the XSD/XML from a table that is not the part of the Query, we will need to do the below changes: a: In the AxBC class i.e. Ax[TableName] class, create a parm method for the field you want to include in the XSD/XML for the corresponding AIF Service. b: Perform Incremental CIL c: Refresh AIF Services d: Re-Activate the Inbound port. 5. By doing the above steps, the required field will be the part of the XSD/XML for the corresponding AIF Service.

Office 365 groups are available with Dynamics CRM Online who don’t use CRM. In simple words, Sales team don’t have access to input important input from various people. Office 365 in this case provided a collaborative location to share documents, conversations, meetings and notes. Office 365 groups can be enabled for any entity. Important: Users must have an Exchange mailbox setup. Also, Server-based SharePoint Integration needs to be enabled to be able to see the documents. Provision Office 365 Groups Preview Navigate to Office 365 admin center, choose CRM as shown below. Then choose, Solutions. Then choose, Office 365 Groups and then choose Install. Review the terms of service and then choose install. Once installation is complete, you can configure Office 365 Groups. Check Required Privileges The security privilege, ISV Extensions, is required to use Office 365 Groups. You can add or remove this privilege from custom or default security roles to meet your business needs. If a user does not have this privilege, they will not be able to see the Office 365 Groups item in a record’s navigation menu. Configure Office 365 Groups In CRM Online, navigate to Settings > Office 365 Groups. On the settings page, choose Add Configuration and choose the entity. Auto-create can also be chosen if you wish to create O365 Group to be automatically created. Finally, choose Publish All. Hope this was helpful. Thank you.