Category Archives: Blog
Cash Position Report for a Professional Services Organization
In this post, I will talk about the importance of the Cash Position Report for any Professional Services Organization. The Cash Position is your current and forecasted ‘Cash in Hand’. The importance of having Cash in your bank account cannot be emphasized enough. It is important to understand the difference between Cash Flow and Cash in Hand as they are not the same. An organization can have a solid cash flow (which is revenue tied up in Receivables), but without money in the bank, it cannot operate. Let me go into this further as it’s the foundation of our conversation here. For any Professional Services organization, anywhere from 60 to 80% of the Monthly expenses are tied to Employee salaries. Paying salaries on time, every month is key to the organization’s credibility. Salaries need to happen on time, month after month, irrespective of your cash flow. They require cash on hand. In addition, any business planning, from HR Activities (a team lunch, an offsite meet) to Investing in the organization’s growth (new areas of business, developing expertise) also requires cash on hand. One more topic that I need to mention is the current pandemic. Many Projects are delayed, cancelled or on hold. From a business standpoint, this kind of unforeseen event can put a tremendous strain on its ability to continue operations. But while the current pandemic is an extreme scenario, businesses constantly face challenges as we operate on a global level (2004 Tsunami, 2008 Financial Meltdown are some recently examples). Cash on Hand can mean the difference between survival and going out of business. So we see that Cash on Hand is a Key Metric and hence should be reviewed regularly, ideally during a scheduled Management meeting on a recurring basis. At CloudFronts, we do this review every Monday morning. Our two and a half hour Monday morning Management meetings are literally run by numbers. Some of the Key Reports we review are Project Overview Report, Team Billable & Non-Billable Allocation Report, Support Metrics, Sales Pipeline, Outstanding Accounts Receivable Report, Forecasted AR Report, Cash Position Report. Each one of these is a PowerBI Report embedded into our Microsoft Teams Leadership Team Group. So what is the Cash Position Report? I will start with a simple equation and then delve into the details – Cash Position = Cash + AR – AP + Projected AR Now let’s walk through each of the Items in there and how we get that data – Cash on Hand – This is what is in your Bank Account when you run this Report (our report is updated every Monday morning before our weekly Management Meeting). We manually enter this data into the Report. AR – AR is your Accounts Receivable which includes all Customer Invoices that are due this month and have not yet been paid. This includes overdue invoices carried forward from the previous month as well. If an Invoice is Paid, then it goes into Cash on Hand. Our AR data comes from Dynamics 365 PSA Contract Invoicing Schedule for both Fixed Bid and Time & Materials Projects. Our Contract Invoicing Schedule is the Single source of truth for all our Project Services Invoicing. AP – AP is all Payables this month, including Salaries. It comes from our Accounting System which is currently QuickBooks Online. Projected AR – Projected Accounts Receivable are any Invoices that we will generate this month and that will be due this month. This data also becomes from our Contract Invoicing Schedule. For Fixed Bid, this is based on the Contract Billing Milestones and for Time & Materials, this data comes from the Team Member Allocations. At the top of the Report, you will see that the report is month wise. Hence as you move into the future months, you have less visibility into AR data, but rely on your Projected AR data. As you move into a month, the Projected AR starts becoming AR, which then becomes cash on hand and typically by the end of the month, Projected AR should be zero. This Report gives the Management Team a clear visibility of the company’s Cash Position over a period of time. For us, we are looking at six months into the future, but organizations can easily set this up for longer periods. By reviewing it regularly, in a scheduled meeting, you ensure there are no surprises and you have time to react well in advance. The benefits of such a report are further amplified when you consider its impact on our behaviour. During the pandemic, our No.1 priority has been to ensure we are a stable organization with the ability to operate through this pandemic. This report can give you that stability and allow you to stay productive and impact your team with a positive state of mind. If you are keen on understanding more about our Internal Systems that keep us running and growing, our Internal Systems page is a wonderful resource – https://www.cloudfronts.com/our-internal-systems/ CloudFronts is one of the leading Microsoft Partners globally on implementing Professional Services Automation on the Microsoft Dynamics 365 Platform. You can reach me on LinkedIn for any questions/queries or email me at ashah@cloudfronts.com
Share Story :
Form Validation in Power Apps — Part 2
In this blog, we will implement the following validation on our Sign Up Form in PowerApps: Disable Sign Up button if any invalid input is present in Form. Change Border or Fill Color Input which is invalid. Before moving forward please check out my previous blog because this blog is a continuation of my previous blog. Click Here Disable Sign Up button if any invalid input is present in Form Select the Sign-Up button in the form. 2. Select the property DisplayMode so that we can change Display Mode to disable if any invalid input is entered on Form 3. Add the following to in DisplayMode Formula Bar If(ErrorText.HtmlText = “”,Edit,Disabled) It means that when Error Text is empty it will enable the submit button or else it will disable it. Change Border or Fill Color Input which is invalid. Now we will add function in Fill and border to input if that input is Invalid. Email Input Select the Email Input and choice BorderColor Property. 2. Now, In BorderColor we will add the following code in the formula bar, so if Email Input is invalid or not in the format it will change the border color of input to red or you can add whatever color you want. 3. Code : 4. You can see in the following screenshot when an email is invalid or empty then the input border is red. Password and Confirm Password Input Password and Confirm Password Input 2. Now, In BorderColor we will add the following code in the formula bar, so if Password value doesn’t match with Confirm Password Input it will change the border color of input to red. 3. Code: 4. You can see in the following screenshot when password / confirm password does not match or empty then the input border is red 5. For Confirm password Field you can follow the same step as Password Field Phone Number Input Select the Phone Number Input and choice BorderColor Property 2. Now, In BorderColor we will add the following code in the formula bar, so if Phone Number Input is invalid or not in the format it will change the border color of input to red. 3. Code: 4. You can see in the following screenshot when phone no is invalid or empty then the input border is red. Here we finish with our blog, I hope this helps a lot and stay tuned for more blog like this. Thank you
Share Story :
Load Option set from D365 CRM to Power BI data model
Steps: Open Xrm Toolbox and add Tool “PowerBI option-Set Assistant” Open the tool and Click on load entities, it will load all the entities. Select the entity and field name from which we want to retrieve data, click on “Create records for selected option sets”. It will prompt that it will create new entity, click on Yes and proceed.it will create a new entity for option set. Now go to power bi and create connection using OData feed. Now we can see,”gap_powerbioptionsetrefs” entity created and we can load this into power bi model. Hope this helps!
Share Story :
Set Name Format In D365 CRM
Introduction: In some scenarios, we might need that Full name of Contact Person should be Last Name and then First Name. In this blog we will see how to change the Format of Full Name for Contact Entity. Step 1: Go to Advanced settings -> Administration -> System settings Step 2: Open System settings -> you will see Option to select different Name format -> Select Format you want and click OK. Step 3: here is the short Demo: I have created a Contact with First Name as Jon and last Name as Paul Below is the Output: Note : This can not be exported into Solution. I hope this blog will help you to format Contact’s Name
Share Story :
How to delete workspace from TFS Visual Studio
Introduction: In this blog, we will see how we can delete any of the TFS workspace which is assigned to different user Even if tried to remove/delete the workspace from Visual Studio, We’re unable to map existing workspace to new user. In such scenario, It is necessary to delete the workspace explicitly while getting the error as below “The working folder ‘Workspace_Folder_Local_Path’ is already in use by the workspace : on computer Solution: 1. Open Developer Command Prompt for VS2015 from Start menu 2. For getting the list of workspaces associated with user, run below command tf workspaces /server:https://{TFS}.visualstudio.com/{CollectionName} /owner:”{Owner}” Explanation, in my case {TFS} is xxxx.visualstudio.com/defaultcollection and {Owner} as Jagdish Solanki, Eventually it will look something like this, tf workspaces /server:https://xxxx.visualstudio.com/{CollectionName} /owner:”Jagdish Solanki” Reference: 3. To delete the workspace, run below command tf workspace /server:https://{TFS}.visualstudio.com/defaultcollection /delete “{Workspace};{Owner}” It will looks something like this, tf workspace /server:https://xxxx.visualstudio.com/defaultcollection /delete “SCM-DEV-1;Jagdish Solanki” Once the above command is executed, system will prompt you if user has any pending change(s). Are you sure want to delete the workspace? (Yes/No). Enter yes Reference:
Share Story :
How do you get a latest Refresh Date in Microsoft Power BI report
In this blog we will learn how to get a last refresh date in Microsoft Power BI report. So, using this practice, we will know last data refresh and also knows the problem with dataset refresh. In order to get latest refresh dates, you have to follow the below steps. Step 1: Open your report in “Power BI Desktop”. Step 2: Click on Transform data, it will open Power query Step 3: Click on New Source, select Blank query. Step 4: Enter DAX expression: = DateTime.LocalNow() and also change the name Step 5: Before use its need to convert into To Table. Step 6: Rename the column name like DateTime Step 7: Click on Close & Apply Step 8: Drag the Datetime field in report and apply card level visualization. It will work when you refresh the whole model, it will not refresh the date when individual table refresh. Hope this helps!
Share Story :
“Cannot delete a record in batch job(BatchJob). The corresponding AOS validation failed “while deleting batch job
while you want to delete batch job in Finance and operation you may have faced error as follows “Cannot delete a record in batch job(BatchJob). The corresponding AOS validation failed”. This blog will be helpful to resolve this issue, just follow mentioned steps. Go to batch job(system administation >> inquiries >> batch jobs) select batch jobs which you want to delete and press button change status now change its status to canceling as follows after which jobs status will change to canceling and after that it will automatically change to waiting state now try to delete it by pressing delete button. Now select yes to delete record And you have successfully deleted the record. Hope this blog was helpful to you.
Share Story :
Hiding Filter Pane in Power BI Reports
While viewing reports in Power BI service/ Power BI Desktop, Power BI provides a feature to eliminate the filter pane completely in order to provide a professional look. Here are the ways to Hiding Filter Pane in Power BI reports. 1.Show/Hide the filter pane from report readers. By default, the Filters pane is visible to the end-users. If we don’t want them to see it, we can select the eye icon next to Filters and hide it. 2. Turning on/off the filters from Settings. Steps: Open the Power BI Report in the desired workspace and go to the content section. Select More options (…), then select Settings for that report. Go to the Filtering Experience and disable both the options to hide the filter pane. 3. Showing the desired filters in a user-friendly manner: We can select the filters which we want to display to the end-user depending on the users requirement. Hover-over the filter icon on the right-hand corner to view the read-only filters. Whichever filter we do not want to display to the user, we can hide them in the filter section of the report. Hope this helps!
Share Story :
Why Custom Filter JS code doesn’t work on Lookup field? [Fixed]
One of the major pet-peeve is not understanding why the code isn’t working. And you for sure know you’ve written the correct code. But, thing just don’t work. One such tricky situation is that of applying custom filter to fields using JavaScript in Dynamics 365 Customer Engagement apps. Scenario Let’s say you have a custom filter to be applied to a field and you’ve written your JS code on Load to apply the filter and everything (you know what you need to do!) Example: But the above is just not working. Why??? Reason The reason is pretty simple! Because, the Lookup field is still using the one set on the field itself. Check that – The above should be turned off to make your code work since the field’s default OOB filtering takes precedence. And now, your code should work (Provided everything in it correct) Hope this quick tip helps!
Share Story :
Develop D365 Finance, SCM and Retail Reports using Azure Data Lake Gen2
The BYOD feature for the Dynamics 365 for finance and Operations was release in 2016, it’s provide feature for the customer to bring their own Azure SQL Database, but the drawback through this was Entity Store only accessible for the inbuilt data warehouse only which means it is not accessible to outside D365. The new feature for the Data Lake Gen2 makes the Entity Store get Sync with Data Lake. Following are the steps to setup the environment. Let’s create the Storage Account for the Data Lake Gen2 Click on the Create Resource and search for Storage accounts and go to the Storage Account and click on Add. Choose the Subscription and Resource group, resource group is the container that holds the resource. Here, we are creating the new resource for our Data Lake. Make sure you select the Location same as your Power BI dataset environment and set the other option as shown below. Once storage account gets created, we can access it from the resource group open the storage account Now let’s take a copy of the connection string since it will be required later, the connection string can be access by going to setting under the storage account. Let’s create the Key vault resource to store the secret and create the secret for the connection string. Secret can be created by going to the key vault by going to the Key under the Settings tab, click on the generate the key. We are creating the secret for the connection string that we have copied earlier, set the value for the connection string as the key that we have copied and click on the create. Once we have created the app secret for the connection the next step is to authorize the user and resource to the request. Here we are going to register app for the authorization of the D365FO environment. To register the app, go to the Azure Active Directory and Select the app registration and click on the new registration. Fill the application name of your choice and redirect URI is set to WEB and select the D365 environment URI as shown below. Once we register the app the next step is to grant the API permission as a part of consent process. Grant all the permission that application should require. From Azure Key Vault select the Permission as user_impersonation which provide full access to Azure Key Vault service. The next step is to create client secret and make sure to note down the value of the app generated since we are going to use that in the D365FO environment. Next is to add the D365FO in access policy list and select the Key and Secret permission to Get and List from the drop down. Once we have added D365FO in access policy list the next step is to add the Application ID and Application Secret of Azure Key Vault in Data connection in D365FO environment, which can be access by going to Module > System administration > Setup > System parameters > Data connections tab. Note:- Make sure if data connection tab is not visible to you which means you are missing some configuration setting during environment setup, to make the data connection tab available we need to check the Value of CDSAMaster in SQL Studio Management Studio. Open SSMS go to the AxDW database and check the value in SYSFLIGHTING for CDSAMaster if not present then insert the value in table. SELECT * FROM SYSFLIGHTING /*To check the Flightname values*/ INSERT INTO SYSFLIGHTING VALUES (‘CDSAMaster’,1,12719367,5637144576,5637144589,1) /* CDSAMaster FlightName 1 Enabled 12719367 FlightserviceID 5637144576 Partition 5637144589 RecID 1 Recversion */ Before Update After Updating Once Data connections is added fill the Application ID, Application Secret, DNS name and connection secret name as shown below and make sure to enable the Data Lake Integration. Test the Azure Key Vault and Azure Storage. Note:- If you are getting error 401 which means Azure user in which data lake is hosted has no access to D365FO environment in that case you need to import the user in environment and assign role as Administrator. Create Reports using Azure Data Lake Gen2 Once done with above steps the next step is to configure the storage account, following are the requirement for the Power BI. The storage account must be created in the same AAD tenant as your Power BI tenant. The storage account must be created in the same region as your Power BI tenant. The storage account must have the Hierarchical Name Space feature enabled (Make sure to enable this at time of storage account creation) Power BI service must be granted a Reader role on the storage account. You must have a Global Administrator account, this account is required to connect and configure Power BI to store the dataflow definition, and data, in your Azure Data Lake Storage Gen2 account As we have created storage account previously lets, grant the Reader role to the storage account, in Azure portal Go to the Storage account > Access control > Role Assignments then click on Add role. Once we assign role to the Storage Account the next step is to Assign the directory level permission to the Storage that we have created. Here we are granting Power BI permission to the file system. We need to get the collection of IDs of the Power BI. Which can get by navigating to the AAD > Enterprise Application > All Application, copy the Object IDs of the Power BI Premium, Power BI Service and Power Query Online. For each Power BI Object that we have collected in previous steps grant the below access for each of the object. Once we granted the access to the storage the next step is to connect your Azure Data Lake Storage Gen 2 to Power BI Go to your Power BI service > Click on Admin portal navigate to Dataflow settings then Select the Connect your Azure Data Lake Storage Gen2 button. The following … Continue reading Develop D365 Finance, SCM and Retail Reports using Azure Data Lake Gen2
