Salesforce Certified Sales Cloud Consultant – Sales Cloud Analytics

1. Creating Reports

Your business has questions; your Salesforce data has the answers. When you get asked to build a report, the request typically comes in the form of a question. The question might be something like, “Which products are my top sellers?” Or who are my highest-value prospects? Or which marketing campaigns have been more successful? Or how satisfied are my customers? We can all answer these questions through salesforce.com lightning reports. As you can see on my screen, this is the home page, and these are some of the dashboards. And each of these dashboards has an underlying report that gets the data from the salesforce records. I’m going to show you in the course. Also covered is how to design these interactive and visually appealing dashboards. But first, we are going to go straight ahead and check out some of the objects we are going to use to create our reports. So let’s start off with the leads. So leads are basically any future prospects in your system.

So we are just going to quickly look at some of these records so that we can familiarize ourselves with the lead object. Let’s start off by opening up any lead record here, so you can see that these are all the open leads that are currently present in the system. So let’s start off by opening this anything lead, which has just demo data and is not in production. So as you can see, some of the main fields on this lead are lead owner and lead source, which have multiple picklist values.

So, what we’re going to do is create a lead report to get a sense of how many leads with the lead source “web” are in salesforce. So basically, I want to check how many leads are in the system that are coming from our website or through any web portal. So simply click on the report tab and let’s start building our report. First, let me show you some of the options that are present in the reports. So as you can see, when you click on Reports, by default, you’ll be able to see a recently accessed report. And here, you can search for any report that you want. Then you have the option to create a new folder. When you click on the “new folder” button, you can select which fields to display on this page. You can add and remove fields as per your convenience.

Then there are some more options on the left side. Now public reports are the reports that your users have access to and that are publicly available for them to see, get intelligence from, and export as well. By clicking on “all reports,” you are basically checking all the reports that are present in your salesforce.org account, including your private reports. And once you click on all folders, you can see all the folders that are created, and each folder can contain multiple reports. So just to better segregate your reports, you can create a folder in your Salesforce reports and add individual reports to each folder. And also, you can simply add a report to your favourites so that if you have a report that you access frequently, instead of going to the Reports tab and then searching for that report, you can simply add that to your favorites. And when you click on this favorite, you’ll simply have access to that report. Now let’s start building up the report. Let’s click on the new “Report” button that is appearing on the screen. And so we want to create our reports on the lead object. So simply search for the lead, select this lead, and click on “Continue.” Now, this user interface is very intuitive and will help you create your report.

Now in the middle of the page, this is the report canvas, where you will keep seeing all the columns that you keep adding to your report. And just beside this, you have a section where you can see and control which columns you need to add and what filters you need to add to this report. On the left-hand side, in the most column, these are all the fields that are available to be added to your report and include all the lead fields that are in Salesforce. So, in the Filters tab, you can select the types of reports you want and add any conditions to your report. For example, I want to show all the leads that are present in the system. So I selected all leads, and here you can control the created date aspect as well. So, for example, if you want to see only the leads that were created in this month, this fiscal year, the previous fiscal year, or this quarter, you can add all these conditions in here and the salesforce will automatically give you the results accordingly. So, here’s the created date, which I’ve also marked as all time. So basically, I’m getting all the lead data that is currently present in my system into this report. And in the outline here, you can remove and add multiple fields, and you can simply drag and drop to add or remove new columns here, or you can simply choose the Remove All Fields option and remove all the fields in one go.

So let’s start building a report. First of all, let’s drag and drop the lead owner to our column section. Then let’s add the first name and title; simply double-click on those fields on the left-hand side, and they’ll be added to your report canvas automatically. You can simply drag and drop your fields in any order you like. As you can see, I’ve changed the order here. I’ve rearranged the title and first name so that the first name is in the second column and the title is in the third. After that, simply click the “Run” button that appears. And now, once you run, you’ll be able to see the report that you have built using the Lightning interface. So you have the lead owner, first name, title, lead status, rating, industry, lead source, and company. And you can check and edit the filters as well, directly from here. And this is the refresh button. You can simply click on it and refresh your data. Now our objective was to only get web leads. So let’s edit our report, and in the filters, let’s add a filter on lead source. So I’m just going to simply drag and drop the lead source, and lead source equals web. You can select multiple options also, but in our case, we are just going to go ahead with Web. Now that you have selected this filter, let’s click on “Run.” And now you can see that we have twelve records that are coming up, and these are all the web leads that are currently present in my salesforce.org.

So let’s save this report for future reference so that we don’t necessarily have to create the same report over and over again. So I’ll just save this in my private reports. And the “Private Reports” folder is a folder where only you will have access to this report and no one else can. And if a user also creates a report in the private folders, even the system administrator won’t have access to that report. And private reports are usually used to store those reports that are only relevant to the user that created this report. So this is how you can create a simple report in Salesforce using just a drag-and-drop interface in Lightning Report Builder. And you can simply go through each of the records by clicking on the hyperlinks that are coming in your report. For example, I can simply click on this lead name, and I can simply go to Lead Page directly.

2. Sales Report

In a previous video, we created a lead report and filtered out only those lead records where the lead source was marked as well. So in our case today, we are going to do a lot more hands-on work on an opportunity report, and we’ll see how to fetch out only the relevant information that we want from the opportunities. So, if you have thousands of records of opportunities in Salesforce.org, you can get a lot of information and intelligence from your Salesforce lightning reports on Opportunities. So here, let me just show you some of the opportunities that I feel I have. So we have the type, the forecast category, and the close date. So, based on the close date, I can filter out the opportunities by fiscal year or fiscal quarter, and then I can see if I have any open opportunities or simply create a report on the closed opportunities. So let’s go to our reports and start building this report.

But first, I’d like to create a folder to which we will specifically add all of the reports that we create. So I’m just going to call it FY 20 Reports and save it. Creating a folder in Salesforce really helps you to segregate your reports, and it just keeps your report section of Salesforce a bit more clean. Let’s add our web leads report to our FY 20 reports folder. And as you can see, the Web Leads Report is now contained under FY 20 Reports. So let’s get started on our opportunities report. So simply click on the new button and select Opportunities. So these are some of the columns that Salesforce automatically shows on the canvas when you are creating reports. So you can simply remove all columns from here.

And now let’s build our report right from the ground up. So, first, let me add the opportunity owner, then the account name, and finally the opportunity name. And it’s always a good idea to have some unique identification in your report, like an opportunity ID or an account ID, to segregate your records. If you want to have a little bit more detail, let’s add Stage Create and the Close Date as well. And we have the option to simply drag and drop and rearrange our columns as we see fit. Then let’s also add the lead source. I’m just going to enter all the necessary opportunity details right at the start so we can simply drag and drop all these columns. Let’s also include account type. Now, with the filters, we could filter out only the relevant opportunities that we wanted. So in our case, I want to create an opportunity report where we can see only the open opportunities and only those opportunities where the close date is marked in the current fiscal year. So that’s why I have selected the current FY in the Closed Date filter, and in the Opportunity Status filter, I’ve chosen the option “Open,” since we only want to see the Open Opportunities report.

So this filter returns all opportunities with a close date in the current fiscal year, as well as only open opportunities. Now let’s save our report and see our result. So I’m just going to name it “Open Options: Current FY” and you can select the folder right from the bottom, and we are going to save in our newly created folder FY 20 Reports and Save. So we successfully created and saved our report on the opportunity in this fiscal year. Now if I edit the filter and make the close date all time, I’m going to see a larger number of records since we have expanded our filter, and now you can see all the opportunities in my salesforce where the opportunities are still open. That is, no opportunities are marked as closed or lost. And if you want to remove any column, you can simply edit your report, and in our case, I’m going to remove a lead source column here. So once done, you can simply run and save your report again, and all the changes that you made will be saved.

As you can see at the top, the total probability percentage is showing. Now that doesn’t look right. So let’s edit our report, and we only want to see the average probability percentage. So, in the column probability (percentage), simply click the downside button, and in the summarise section, select the option Average. Now this will show you the average probability percentage at the top of your report. Let’s simply run and see. Now this shows the average probability percentage as 23%, calculating the average probability of all the opportunities and records that our report has fetched. Salesforce created this user interface to make it easier to retrieve records from Salesforce. You can sort this report by any of the columns in ascending or descending order. For example, I can sort this to show me the data in descending order of probability percentage. Now, we have successfully created this report, which shows all the open opportunities that are currently present in our salesforce.org. To access the same report, simply click on the Reports tab, then in the all folders, select your folder FY 20 Reports, and then click on a newly created report. You can change the filters if you want. Now this report shows all the open opportunities. But what if I want to create another report that shows open opportunities but has a close date in the current fiscal quarter?

Now I don’t need to create another report from scratch; I can simply make changes in my already-created report, select the Save As option, and this will create a new report altogether without committing the changes in my original report. So this way, you can save a lot more time. If you want to create another report with minor changes and if you already have a report of the same kind, then you can simply use the Save As functionality to create almost the same report but with different filters or different columns as you like. So here we have created two reports: OpenOpties Current FQ and OpenOpties Current FY. As a result, you can create an unlimited number of reports as needed. You can use various filters, add and remove columns as needed, and don’t forget to save any changes you make because they will be lost the next time you open the report. So make sure you save any changes that you make in your reports.

3. Tabular Report

So there are basically four types of reports that you can create in Salesforce Tableau Reports: summary reports, matrix reports, and join reports. A Tableau report is a very basic type of report where you can tabulate data from objects and other related objects. So simply put, a simple row and column report is called a “Tableau report” without any summarization. The Opportunities report that we previously created was an example of a tablet report, where we are simply showing the field names in the columns and all the related data in the rows without any summarization of data.

Even the Web Leads report that we created was a tablet report, and usually tabular reports are created to get the data dump as quickly as possible from Salesforce, and then you can simply export these tablet reports and make your own engineering in your Msxl or any other tool. Tablet reports usually don’t give out much information, as they are simply showing us the raw data that is currently present in Salesforce. If you want to get some intelligence out of your reports, then you need to make sure that you convert your tablet report into a summary or matrix report. We are going to learn about a summary and matrix reports in the upcoming sessions as well, but it’s very important to first learn about the importance of tableau reports. To begin with, as a tableau report, reports can be created quickly by dragging and dropping fields in your report builder and simply running to create a whole data dump that can then be exported or used in any way. Usually, tabular reports are created to simply check a few records from a whole list.

You can simply search your data in your whole list of tablet reports, and then simply check the field values and compare them with each other. If you do not want to go ahead and search for each record in Salesforce, Tablet reports are the simplest and fastest way to look at data. similar to a spreadsheet. They consist simply of an ordered set of fields and columns, with each matching record listed in a row. Tablet reports are best for creating lists of records or a list of a single grand total. And another thing to notice here is that tablet reports cannot be used to create groups of data or charts, but they can be used in dashboards as well, because to create a dashboard, your report should have at least one group row. A simple example of a tablet report is an account report containing all the accounts that are currently present in your salesforce, a contact list report, or an opportunity report where you have all the opportunities in your salesforce.org and you want to simply download that into a spreadsheet.

4. Summary Report

Now let’s extend the tablet report that we have built so far into a summary report. All that is required is to begin grouping your report by stage or any other field relevant to the opportunity. We’re going to try to make this tablet report into a summary report now that I’ve opened Optic’s current Fireport. Now all that’s needed is to summarise this report by field. So here, let’s quickly edit this report, and I’m going to simply drag and drop the stage field from the columns list to the group rows list. Now, what this does is that it groups your report by stages, and you have a segregated report that shows you a lot more information than a tablet report. As the first column of your report shows, the stages and the whole report are then grouped by those stages. So summary reports are similar to tablet reports but also allow users to group rows of data, view subtotals, and create charts.

They can be used as a source report for the dashboard components. As in a dashboard, the underlying report data should have at least one field as a group pro. You can use the summary report to show subtotals based on the value of a particular field or when you want to create a hierarchical list, such as all opportunities for your team subtotal by stage and owner. Now, summary reports without any grouping are basically a tabular report. So in this report that we have just created, this shows all the open opportunities grouped by stage, and you can simply see how many records are there in the prospecting stage, how many are in the qualification needs stage, analysis, and so on.

Now, with the subtotals added, it gives a whole lot more knowledge about how many deals are currently present in your system in each stage and gives you an idea about how many opportunities will be closed in the upcoming months as it gives you a clear idea of your opportunity funnel quality. You can simply click on the stage column to sort your report by stage as you like. I’ve now sorted the stage, and all the data is sorted as well. Now let’s edit this report and change our grouping. So we saw how our report looks when we group it by stage. Let’s choose some other field to group by two. So let’s choose an account name. So now this will show you the number of opportunities that are currently open and available for all the accounts that are currently present in your salesforce, and this can again give you a whole lot of information as you can check how much business is going to come in the future. from each of your accounts. You can also summarise a report by any date field, such as the close date.

So right now, this closed date looks very ugly in the report as it’s showing each individual date value, and you can simply drop this by calendar month, fiscal quarter, or fiscal year as per your liking. And when you run this, you have a very clear picture of how many opportunities are going to be closed in the upcoming months. And this is a very useful way to forecast using your opportunity data and simply creating a summary report. You now have all of the information you need to forecast how many and what types of opportunities will be closed in the next fiscal quarter or year. Now, at the bottom, there are some options. You can choose whether to display the grand total, the subtotals, or simply uncheck the detailed roles. Now, you will only be able to see the quantifying fields, such as the probability and the amount field. So this will give you a more compact view of the data than the whole set. You can also select the row counts if you want to see how many records are in this report. So this is how you can create a summary report and make your tablet report a little bit more intelligent, which gives you a whole lot more information compared to a tablet report.

5. Matrix Report

So previously, we created a summary report. Now let’s see what a matrix report is. So I’m on my reports tab now; let’s create a new report on the opportunities, and let me just remove all the columns. So what I want is to check how much business we have done so far in the current and previous fiscal years with our existing customers. I simply want to see the numbers, the amount fielded on the opportunities on the report, and nothing else summarised by the account name in the first column and the closure date of the opportunities. So let me just start off by entering the amount field into our report. So here I have added the amount column, and in the filters, let me change the close date here. So we want all the opportunities that were closed in the current and previous fiscal years, and I want to show only the closed one opportunities.

Now, you can also select the stage field as a filter, and you can simply add the closed one or multiple stages you want to add in your report. But for me, opportunity status is a standard option that comes in the filters, and it’s much easier. So we have all of the amount rows, each with its own amount for each individual opportunity record. Now let’s summarise our report. So in the grip rows, let’s add the account name. Let me remove the subtotals. Okay, so this looks a bit more promising. And for the closure date, let’s add the closure date in the group rows as well. Let’s see how this looks. Let’s summarise a close date by fiscal quarter. Let’s run this report and see how it looks. So this is actually a summary report, which consists of only two groupings in the rows only.

And though it’s giving me all the information, I’m still not getting a clear picture, and my report seems to be a bit longer than I would want. And just to make it a little bit easier to see, let’s change our report here and make this close date into a column grouping. Let’s summarise the close date for the fiscal quarter and run this report. So, this appears to be a lot nicer than what we’ve seen previously, as I can simply see that the Burlington Textiles unit did zero business in FY2020 quarter two and this much business in FY2020 quarter three. And if I choose the stacked summary, it becomes a lot more clear as this column sum of amount and record count is now removed and is now shown in the column, which makes it a bit easier to read for the viewer. So this is how you can create a matrix report, which is similar to a summary report but allows you to group and summarise data by both rows and columns. They can be used as the source report for dashboard components as well, and you should use this type of report if you have a large amount of data to summarise and you need to compare values in several different fields. Or we want to look at data by date and by product, person, geography, or anything in matrix reports without at least one row and one column grouping showing as a summary report on the report. Run page. So this is how you can create a matrix report in Salesforce.