Salesforce Certified Sales Cloud Consultant – Sales Cloud Analytics Part 2

6. Report Filter Logic

Now all the reports that we have built so far have some kind of other filter in them. And filters really help you to filter out only the important details in your report. So if you have thousands or millions of records in Salesforce and you need to focus on a particular type of record, then filtering out the correct data is very important so that it saves you the hassle of trying to find the data that you are looking for. If you include multiple filters in your report, the and operation is automatically applied to each of them.

For example, if I put in that the opportunity owner should contain the name Yashad and simultaneously add another filter that the amount should be less than 500K, then the salesforce will by default give me all the opportunities where the opportunity owner is Yashad and those opportunities must have an amount less than 500K. But what if you want to customise your filter instead of using the default one? You can simply add an or operation here as well by selecting the operator in the filter logic option. So here, as you can see, instead of and, I have changed the operator to or, and here, the salesforce is now giving all the records where the owner is Yashad, regardless of who is the opportunity owner. If the opportunity amount is less than $500, then that is also included in this report. As you can see, this line item has a different opportunity owner, but since the amount is less than $500K, that’s why it’s been added to the report.

Similarly, you can build complex logic in your report builder to satisfy your requirements. For example, we can further customise this report by adding another filter so that I can get only the opportunities of those accounts where the account type is Customer Direct or Customer Channel. And here I can simply edit this logic further so that I get all of the previous opportunities where the owner is either Yasha or the amount of the opportunity is less than $500,000.

And along with this criteria, there’s also the operation of the account type. So all the related accounts for these opportunities should be of the type “customer direct” or “customer channel.” Now you will be able to see that we have a limited amount of opportunity records since we have added an operator here that is filtering out only those opportunities where the related account is of the type Direct, Customer, or Customer Channel. So this is how you can use the filtering option to build your own logic in your report so that you can get only the relevant information that you want from the Salesforce report builder. So here we can also change the filter logic, as I’ve removed the and operator, and instead all three filters are now getting executed under our operator, and once we run, we have a larger number of opportunity records. So this is how you can customise your filters according to your needs. And you can build complex filters so that only the relevant data is shown to you in Salesforce.

7. Creating Summary Level Formula

Summary formulas are a great way to add additional totals based on numerical values. In your report, you can add up to five summary formulas to summary and matrix reports to create calculated summaries of your numerical fields or probability or percentage fields. Just like we add some formulas to our Excel sheet to add some intelligence to our data, we can create summary formulas in a Salesforce report to make it more presentable and add some more intelligence in the report builder itself.

But for that, the main prerequisite here is that your report must have at least one grouping. So here I’m going to group my report by stage. And now, as you can see, the Create Formula option is active. Now simply click on it. And our motive here is that we are going to multiply the amount field on the opportunity with the probability percentage field so that we can get a weighted amount in our report only. So let’s try to create a formula. So let’s just enter the column name with the amounts for this, and this will appear as a separate column with the calculated amount. So let’s try to create a formula. So I’ve added the column name “weighted amount” and the description “probability percentage multiplied by the amount.” So this is good enough, as anyone looking at this formula can simply check the description, and we’ll be able to figure out what this formula is actually doing. So the formula output type is the number since we are going to multiply the amount field with this probability percentage, which in turn will give us a currency value. So let’s start building a formula.

So in the Fields tab, let’s search for “amount” and click on “insert,” and then let’s multiply this by the probability percentage. Then, once more, click Insert, followed by Validate. So our formula syntax is correct, as this has been validated. Now click on “Apply.” Now our report refreshes, and here you can see that this new column has been added with the name “weighted amount,” and at the very bottom, you can see that the subtotal rows are not filled with the weighted amount for each summary category. So this formula is simply taking the total amount of the opportunity for each summary and multiplying it with the sum of the probability percentage for each of the groupings. Here you also have the option to add this calculation to the grand total as well, or if you have any specific groupings in your report, you can select that as well. So let’s add this to the group total. And now our formula is only showing the grand total by taking the entire opportunity amount for this report and multiplying it by the total probability percentage. Now let’s change our formula a little bit. So what I want is to add an “if” clause to our formula. If the probability percentage is greater than 50, I only want to show the opportunity’s amount field.

So, if the opportunity probability percentage is greater than 50, I want to display only the amount field on the opportunity. But if the probability percentage is less than 50%, then in that case I want to show the weighted amount, which is the amount multiplied by the probability of the opportunity. So here I’ve added the if condition, and the first condition checks if the probability percentage is greater than 50; if it is, then it will show only the amount field, and if not, then the weighted amount. Let’s click on “Apply” and run our report. Click on Apply, and you can see that the calculation is now based on this condition, and accordingly, the column has been updated. Now there are many functions here that you can use, and you can simply click on them to see the descriptions of these formula functions that you can use in your reports. And these come in very handy, as you don’t necessarily then have to create formula fields just for the categorization of data or just add another column in your reports. You.

8. Row Level Formulas

In the previous class, we saw how to use the Summary Level formulas in your report, but that’s not always useful, especially when we are seeing a huge chunk of data and a huge number of records in your report. And if you want to add formulas to individual line items, then summary level formulas are not useful, as they only add formulas to the summary level. So if you want to add a formula for all the line items to calculate a different value for each line item in your report, then you should use the Row Level Summary Formulas. And this feature came out in the Spring 2019 release of Salesforce, where you can use this feature to add formulas that will take into account different values in each row of your report.

So here I am creating a case report where I’m simply gathering all the case records that are currently present in my Salesforce.org, and we are going to apply the role level formula to this report. And as you can see, I’ve added some columns, such as “Date Time Open” and “Date Time Closed.” So what I want is to see what the average closing time of my cases is using the Row Level Formulas by simply subtracting the close date from the open date so that I can get the average duration for my case objects in my Salesforce.org. So, first, let me demonstrate how to activate the Role Level Formula feature. Simply go to the setup page and search for reports and dashboard settings. Here you will be able to see a checkbox by the name of “Enable row level,” formula Lightning. Experience only. Simply check this box and press the Save button. Now refresh your report page and make sure that you have saved your report; otherwise, it will be lost. So now, once you’ve refreshed, simply click on Edit. And here you’ll see a button that says Add Row Level Formula.

Simply click on it, and similar to the Summary Level Surface, it appears. And here I’m going to create the cadence by simply subtracting these two columns. So I’ve added the column name, “CaseAge,” and the formal output type, “number.” To calculate the ageing of my case, I subtract the closed date and time from the closed open date and time. Let’s simply validate this and click on “Apply.” Now in the end, you can see that the CaseAge column has been added, and it’s showing us the data for each of the report line atoms, and each row has a different value as it’s taking the closed date and subtracting it from the open date. And you can then simply export this report and use this formula in your Excel report as well. So this is how you can create a simple role-level Level Formula.So this feature really helps your company’s admins and data analysts to ease some of their work and some of the manual efforts that they make when they export data from Salesforce and then add some formulas in Excel sheets. And so instead of that, you can create the formulas in Salesforce itself to reduce the manual efforts of the users.

9. Dashboards

Dashboards are an important means of visualizing data for management activities, and they also provide a quick estimation of the progress of your data in Salesforce. The prerequisites to building a dashboard in Salesforce are simply reports. Any report that is either in summary or matrix format can be created as a dashboard of. To create a dashboard, simply navigate to the Dashboard tab, which appears in the navigation bar of your Salesforce app, and click on it.

You will be presented with an interface that is very similar to Report Builder. screen similar to the report builder interface where you can see a list of previously created dashboards and dashboard folders, private folders, and so on. You can create your own folders and set the fields on this page to see more relevant details. So without any further delay, let’s start creating our first dashboard, and let’s click on the new Dashboard button that is appearing on the page. And once you click on it, you’ll see that there is a dialogue box that is asking for us to name our new dashboard. So here I’m going to create a dashboard on all the sales data, which is all the opportunity data that we have in our salesforce based on the reports that we have built so far.

So I’m naming our dashboard “Sales Dashboards for FY 20” and going to add a very small description, and we can again save this dashboard in any folder that we want. So I’m going to save this only in my private Dashboards. Once that’s done, you’ll notice a grid-like interface here. From here, you can rename your dashboard if you want, and in the properties button lets of this window, you have many other options. If you do not want to have many charts in this dashboard, then you can choose the nine-column option appearing here for the grid, as it allows you to expand your individual charts on this dashboard page. And the twelve columns are mostly recommended in those dashboards where you want to add more charts and you want your chart size to be small and crisp so that a lot of information can be seen on the screen without scrolling down or without side scrolling. And then you also have the option to choose a dashboard theme. Now you can experiment with all these dashboard palettes and see what you like; you can build individual dashboards with each of these dashboard palette. You also have the option to select the dashboard theme to be light or dark. N

ow let’s start creating our dashboard. So here I’m going to create a dashboard on this report, which we have previously created for Open Optic’s current effort. Now you can see that we are not able to see any chart options here, and that’s simply because this report is a tablet report. And as I mentioned earlier, the reports should either be a summary report or a matrix report to be added in a dashboard, so here I’m going to simply edit this report, and we are going to make it a summary report here, and I’ve grouped this report by stage and opportunity owner. Let me just quickly save and run this report. Make sure that if you are changing a report to a summary or a matrix report that needs to be added to the dashboard, then you save the report. As you can see, we are now able to choose the chart options here. So the first chart is a simple bar chart, and you can control what data you want to see on which axis. So here you can select that if you want to have a bifurcated view of the opportunity and the stage on the axis, you can choose the same, and the preview will automatically refresh. This will help you decide what type of chart you want to add. There are so many more options here. As you scroll down, you can also choose the type of number to display in the x axis. So here we are adding the sum of the amount, the amount field on the opportunities, and the decimal places I have selected as automatic.

And here you have the option to sort by opportunity owner or stage. The maximum groups displayed are by default set at 100, and you can rename the title here as well or add a subtitle or a footer here. So, for example, I can add a footer here for sales performance, and here you can see that in the preview the same has been added at the bottom of the chart. Similarly, the subtitle is just below the name of the chart. Then you have the option of viewing the legend at the bottom or from the side. And here are the dark and light themes, which you can switch between as you please. Now that we have added this, you can simply make the dashboard size as per your liking. So I’ve just expanded a little bit, and you can see that this is a very nice-looking dashboard that shows the sales performance by the opportunity owner and how much money is in each stage of the opportunity for all these opportunity owners. So you can again change the type of chart or any other setting by clicking on this pencil button. And let me just make it dark, and this is how it looks. And once you hover over the bar chart, you’ll be able to see some more details. Just move the legend to the left and make the theme visible. Again, you can change the chart type here.

This is the vertical bar graph, and depending on your preference, you can either choose a vertical or a horizontal bar graph. Now that we have saved this dashboard, let’s see the next chart type. So the next graph is the stacked bar graph. So you can use the stacked bar chart if your report has multiple groupings and you are interested in the proportions between the values in each grouping as well as each grouping’s total. So you can use that stack bar graph to see the bifurcation in the bar graph itself, bifurcated with your groupings, and no additional grouping gets added in your report, unlike the simple bar graph where each grouping has a different bar on your x axis. Now let’s add another chart to our dashboard. So let’s click on the component button on the screen. And here we can add another chart type here.So we can add another dashboard chart using the same report to show the data in a different view. We can add a line graph to show the trend of the opportunities and the amount for each stage of the opportunity owner-wise. So here you can see that on the x-axis, all the stages of the opportunities are there. And the line graph shows the amount that is currently present in each stage of the opportunities in our sales force.

So once you scroll down, you have additional options here to either show value or sort the report by owner or amount as you like. Let’s add another chart here, and this time let’s add a pie chart of the same report. And here, a pie chart can be used to show the total and the segregated view of the groupings of your data. So here you can see that our pie chart shows the total amount of the opportunity by opportunity owner. And also in the middle, you can see what the sum total of all the opportunity amounts is. So here you can also change the colour palette of our dashboard. So here I have changed the dashboard palette, and you can see that the colour scheme has also changed. Now, just as we previously bifurcated a pie chart for the opportunity owner, we can edit and make the same bifurcation on the opportunity stage as well. So here I’ve changed the “sliced by” option from owner to stage. And here you can see that now these two pie charts paint a different picture altogether, where one tells us about the bifurcation by stages and the other shows the bifurcation by opportunity owner. Then here is another report, which is a case dump. And now, this report is divided into two sections based on the standard case field: open and closed, displaying the number of records that are currently open in our salesforce and the number of records of the case object that are closed.

So, just to demonstrate this bifurcation, I can add a bar graph to show how many records in a salesforce for the case object are closed and how many are still open. So here you can see that the first bar is all the case records that are closed, and the second bar is the number of records that are still open. You can change the view as per your needs. So this is the number graph here, which simply shows the total number of records that are appearing in our source report. And you can change the colour of this number depending on your needs. So here I set this option for segment ranges so that if the total number of records is greater than 28, then the number gets shown in the amber color. Once updated, you can simply add this chart wherever you like. Let me just change the title a little bit. So this displays the total number of cases so that the dashboard viewer knows what number this dashboard is displaying. As a result, this report is no longer an accessible report of Open Opti’s current FQ. Let me just make this a summary report by grouping this report by opportunity owner so that we can again add a dashboard chart for this report. So here we can add a metre chart or a funnel chart, but I think it’s better if I can just group by stage as well for this report. Let me just add this component again, and here you can see that the funnel chart can be added here. This shows the opportunity amount by owner or by opportunity stage, just so that you know the volume of the opportunities that are in each of your stages so that you can accordingly forecast and plan ahead for your sales targets. As you can see, the majority of the opportunities are currently in the needs analysis stage for this FQ.

We can add a metre graph here as well for the same report. So this metre graph or gate chart is used to track progress along a single measure, such as how much revenue or how many opportunities there are versus your targets in your sales force. Using the gate chart, we can simply report on the opportunity amount, so here I can segment our opportunity amount so that the sum of the opportunity amount. If this lies below 3 million, then the chart is in red. If it’s between 3 million and 5 million, then it’s in amber, and anything above 5 million gets shown in the green color. So similarly, you can use the same method to report on the target as well. For example, if your organisation has a target of 50 million, you can simply use the chart ranges accordingly. For example, if the opportunity sum is below $25 million, then it’s in red. If it’s between 25 and 50, then it’s in amber, and anything above 50 is in green. Now, this is the scatter chart, and you can use scatter charts to show meaningful information using one or two groupings of report data plus the summaries. And because a scatter chart shows datagroups by summarised values, you need at least one grouping in your report. And scatter charts automatically show data from the source report, or you can manually choose what information to display for groupings and summaries. As seen in this open optics chart, we can simply show the sum of the amounts on the axis, which has been grouped by stage. So if I choose the y axis as record count, you can see the number of opportunities that are in each of these stages, and simultaneously, you can see the total number of amounts of these opportunities in each stage as our x axis shows the amount field. The table chart, which shows a simple table that you can display in your dashboards, is the final chart. So you can simply add more columns, and you can add group rows in your table to show the values or record count. If you want, you can also add conditional highlighting to your table chart to make it a little bit more aesthetic.

For example, here I can add conditional highlighting such as if the amount is less than one hundred k, then the line item should be shown in red, and anything between one hundred k and one million, then it should show in amber, and so on. Anything above 1 million is shown in green. So this is a dashboard that we have created; let’s save this and run to see how this looks now. So once you’ve saved your dashboard, simply rearrange and save it again so that the most important chart gets shown at the top. And always try to make the dashboard more compact so that your users can see the data on one screen without much scrolling, and always try to create more dashboards yourself. And as you keep on creating more dashboards, you’ll be able to intuitively know beforehand what type of chart suits best for the type of data that you want to show in your dashboard.

10. Dashboard Filters

Just like we used to filter out our reports so that we could get only the relevant data from Salesforce, we can also use filters in our dashboards. So once you’ve created your dashboard, click on the filter button that is appearing on the canvas. And as you can see, this is the filter field. These are all the fields that are on your source data reports. And since all of our dashboard charts are based on the opportunity reports, you’ll be able to see all the opportunity fields in this filter field.

Now, if I want to have a field on the closure date of the opportunities so that I can filter out my dashboard based on the close date of the opportunity, then I can simply use the close date field here and here. I can name the display name for this particular filter. And in the filter values, I have the option to either select a particular date in the calendar or use the relative option. I can simply add a text, for example, this quarter, so that the salesforce automatically sets the range of the current quarter in this filter. So once I have added this and saved, I’m able to see this filter option here and there’s. This option, called Close Date, equals this quarter. And once I choose this, our dashboard automatically refreshes to show us all the data pertaining to this current quarter only.

Similarly, we can add other filters as well. For example, now I can add a different filter, so I can simply add a new filter here by the opportunity owner, and the field value I have added is Yoshid, and the second value is Samantha. I now have the owner-wise opportunity dashboard filter after adding this and saving it. And once I change the filter here, my report gets automatically refreshed to show only the data pertaining to this Opportunity owner. So as you can see, all the data now belongs to the user Yashad. And similarly, once I changed this, you can see all the data for Samantha’s opportunities. So this is how you can add various filters to your dashboard to see only the relevant information. Whenever you are viewing the charts in your dashboards, you can also subscribe to your dashboards just like the reports by clicking on the subscribe button. And a snapshot of this dashboard gets sent to you via email depending upon the time and day you set in the subscription option.

11. Define a Reporting Snapshot

Let’s talk about reporting snapshots. So, a reporting snapshot lets you report on historical data. Users can save tableau or summary report results to fields on a custom object, then map those fields to corresponding fields on a target object. Then you can schedule when to run the reporting snapshot to load the custom objects with the report data. Basically, reporting snapshots enables you to work with report data similar to how you work with other records in Salesforce. Now, let’s take an example. I have this simple tabular report that gives me a view of the opportunity details in my salesforce. Now obviously, with time, these records will change, and the users will keep on updating them. But if I have a use case where I need to store the historical data of Salesforce so that I can maybe later on give the management or someone else a view of the historical trend that’s been going on in the organisation with these opportunities or maybe some other reports on history, then I can simply save these columns of the report into a custom object. Then, perhaps monthly or weekly, I can schedule a reporting snapshot to automatically run and save the updated values of these reports into the various fields of custom object records.

So let me just create a custom object where we can save these fields and these data records separately. So I have created a new object, a new custom object by the name of “Op Snapshot,” where we are going to save an opportunity’s ID, amount, close date, and all the columns that are added into this report that we have made so that we can then later on schedule that automatically in our salesforce. So these are all the fields that I have created to store the ID opportunity. Name Amount Close Date Account Name. So, once your report is ready and you have created a custom object, simply go to the settings and search for the Reporting Snapshot. First, let me just add this custom object again to the navigation bar so that I can show you that right now this is a newly made object and there are no records associated with this custom object. As you can see, once I click on all listviews, there are no records currently in this object. So let’s go to the setup and search for “reporting snapshot.” Once you click on Reporting Snapshots, you’ll be able to see some details about what this Reporting Snapshot is all about. You can simply read it, and you’ll be able to better understand what this is all about. Now click on “continue.” Click on the new reporting snapshot. And here you will need to map the fields of your source report to your target object, which in our case is the custom object that we have just created by the name of Op Snapshot.

So give this reporting snapshot a name and the name of the user whose ID will be used to automatically run this snapshot. The majority of that would be your administrator; that user will have access to all Salesforce reports. Now, once you scroll down, you’ll have the option to select a source report and a target object. So our source report is for op snapshot, and our target object is op snapshot. So here we have to do the mapping of our source report to our custom object fields. So in our source report option, you will be able to see a drop-down mentioning all the columns that we have added in the report. So you can map these columns one by one to the custom object field value. So, for example, I want to add the account name of my source report to the accountname field of my OP snapshot object. Similarly, you must map each field of the columns and each field of the custom object to a field of your source report. Now, once you have mapped all the fields and all the columns of your source report with the fields, simply click on Save, and then you have the option to schedule it so that the column values of this report will automatically be created as a new record in this salesforce object. So here I have assigned this, I have scheduled it on a daily basis, and I have added a start and end date and a preferred start time as well. And again, you have the option to email the reporting snapshot to yourself or to others. Once you have completed your scheduling, simply click on Save, and your reporting snapshot has been scheduled successfully. After this dual reporting has completed, you will be able to see records on your custom object for each line item in your source report; each line item will constitute a single record in your custom object.