This chapter’s objectives are:
- 12.1- Describe the options available when creating or customizing a report (e.g., report type, report format, fields, summarizing data, filtering data, charting, scheduling, conditional highlighting)
- 12.2- Describe the impact of the sharing model on reports
- 12.3- Describe the capabilities of custom report types
- 12.4- Describe the options available when creating and modifying dashboards (e.g., dashboard components, data sources, chart types, scheduling, running user)
A report allows you to quickly summarize and chart record data. A dashboard can present summary data from multiple reports on a single page. It is a collection of Reports.
12.1- Describe the options available when creating or customizing a report (e.g., report type, report format, fields, summarizing data, filtering data, charting, scheduling, conditional highlighting)
First, to get the basics, let’s explore the different Reports Formats:
- Tabular Reports: most basic data listing. Data can only be summarized across all records in report (Grand Total only). No grouping of data, no charts, no dashboards – use for List of emails, accounts…
Summary reports: Tabular + group rows of data by, view summary (Total and subtotals), and create charts.
- Report Tab – New Report
- Page 1- Select the Report Type: for ex. Lead.
Page 2: Report builder: 3 panes:
- Fields: left pane, used to select the fields to display (columns)
- Filters: top, used to apply filters on the records (date, status, probability) and you can add fields as filters
Preview: main pane, live Record Preview. Can collapse the filter pane to give Preview room:
- Hold ctrl to select more than 1 fields
- Drag and drop fields from and to the main preview pane from the left pane
- Each column has a menu: sort, group by (report changes from tabular to summary)
- Summarize by the field you want. Ex. Sum of all Opty, summary on Amount column
- Add see the amounts only by hiding Records: Show – remove Details
- To see which fields can be summarized: click on # in left pane
- Add another summary (ex. average of probabilities): drag/drop the 2nd summary field
- Add another group by field, drag field into the group by drop-zone
- Click Run report – it will run and you go to page 3: you can show/hide records as needed
- Save As – if share, save in a folder that the other users have access to!
- When you create a report, the default columns are added to the preview pane automatically.
Matrix: comparison of related totals by both rows and columns like opty summary by month vertically, and account horizontally. Data can be grouped by and summarized along the X and Y axes (up to 2 levels each.
- Start with a summary report created previously (grouped by stage and closed date
- Easier to see the date in a Matrix format! Convert!
Change the format using the drop down – choose Matrix
- The Top level Group becomes the row
- The second level group becomes the column group
- If you want date across, and stage down
- Show – Dropzones, and drag close date to row dropzone, and stage to column dropzone
- Hide dropznes
- You can add Summaries also: drag and drop Expected Revenue sum in the yellow highlifghted column
- You can hide “Record Counts” by clicking show – hide record count
Joined Reports: add up to 5 Report Blocks of related info in a single report. It lets you view different types of info in a single report: Report showing accounts with open and closed opportunities, and open cases. Joined reports are available only in Enterprise, Performance, Unlimited, and Developer Editions.
- Start with a summary report displaying open opp. Change to Join Reports
Now the look changes, we have each report in a Block:
- Each Block has its own set of filters: treat each Block independently
Add the Cases report block. First add the Cases Report type:
- Now you have 2 Report Types, and the Left Pane changes to 3 sectios: common fields: shared between both Report Types, and can be used in Grouping (Group by is common across all blocks).
- Click Hide details:
Now the report looks like a scoreboard.
Summarizing Field Data:
- Tabular Report can summarize Grand Total only
- In Summary Report, you can summarize on the Grouping by that you picked (subtotal).
- Click on the column title that you want to summarize, and choose Summarize this field
- When you summarize, you can choose: Sum, Average, Max, Min
Custom Summary Formula:
- Another way to add calculation column, is the Add Formula that will act as an extra column
- It gives you the ability to perform calculations against summarized data only within a report.
- The source column as well as resulting format can be number, currency
- Max 5 formulas per report! Column will be last.
For instance, let’s say that I want to calculate the percent that each Account has contributed to the sales pipeline.
- 1 Group by Account name
- 2 Summarize by the sum
- 3 Double click Add formula
- 4 Use the function PARENTGROUPVAL(summary_field, grouping_level)
- The resulting formula would be:
- In this example: 235,000/TOTAL
Note: A custom summary formula is different than a formula field. In the above scenario, a formula field cannot calculate the sum of all won opportunity records, and thus could not be used to calculate a percentage of that sum.
- Can highlight the calculated value on the group sum or any operation using colors based on ranges that you choose.
- Should have at least 1 grouping by and summarized column to be able to choose this
For example, less than 50 = red, more than 200 = green, and yellow between.
- All filters are used to exclude records from report results. The report type determines which records are included in the report when all filters are removed (explored below).
- Allows you to exclude data from a report based on fields within the record. For example, I want to only report opportunities owned by “James Smith”
- Also note that you can use relative date values if the default date ranges do not exactly meet your needs
- When you are using more than one field filter, the default matching behavior is AND.
- If you want to change this behavior to OR, then you need to use filter logic. Click on Filer Logic to do so.
- For example, let’s say that I want to report all opportunities owned by “James Smith” OR opportunities created by “James Smith”. If I add both field filters, it will only report opportunities owned and created by “James Smith”. Using filter logic allows me to change this “AND” to an “OR” clause.
- Let’s you exclude records based on related objects and their fields. Ex:
- Accounts without a contact listed: contact object is child of account. Let’s do an account report, and then choose Cross Filter from Add Filter menu
You can cross filter against any object that is related to an object on your report
- Filter on using the with or the without conditions (with Related Object, or without)
- Report can have up to 4 Cross Filters
- Can also add sub filters on the Cross Filter itself (up to 5) – the sub filter will be related to the parent filter. Like Opp parent of Close date:
Row Limit: Can limit the number of displayed rows, like top 5. In Tabular Format report only.
- Bucketing is creating a new column, based on criteria that you choose
- Bucketing allows you to group like values (within a single field) into labels (buckets) within a report.
- 3 types of Buckets: Numbers, picklist, and text
- Grouping the Records by Value: you can create divisions, like large, medium and small, and define the value of each. Now you can group by these buckets.
- You can also filter by this Bucket.
- To create: click the dropdown on the column you want to bucket, example, Amount, choose Bucket.
- Call this Deal Size, add Ranges for the deal size: small is < 10K, medium 25-50K and large 50K+.
- Now select Group this field o the Amount dropdown
Picklist: good for combining related values into logical sets. Ex. You create a new segments based on the picklist values, like Technology will include all related technology types, Finance will includes all finance types. This way you can Group by this new field instead of all numerous types. Now you can group, filter and add this new field to a graph.
- To create it, select Add Bucket Field from the left pane
Text buckets: group many similar text under 1. Ex. Many close account names for the same account!
- To create it, select Add Bucket Field from the left pane
Run the Report:
Run report, you go to the Report Running Page where you have same filters and can change:
- Time filter: criteria and period
- Record filter: status, which Opty (my team, mine), Probability
Summarize by what? Here stage, but you can also change that.
- Time filter: criteria and period
In the Report pane at the bottom, you can:
- Change Sort By from dropdown or from the Column itself
At the bottom of the report, you can select a Grouping, then Drill Down: so in this case, we selected Stage=Identified, then Drilled down by Opty Owner: we will get a new Report where: filtered by stage=Identified only, and the Records that are Identified will be Grouped by Opty Owner. You can further Drill down.
- Run Report: run now, or schedule
- Hide details: hide records, and only keep the grouping summary information
- Customize: go back to the customize report page
- Save/Save As/Delete
- Printable View (good formatted Excel)
- Export Details: can choose Encoding and XLS or CSV – see:
- Subscribe (see below)
Subscribe to Reports (Notifications):
- Report notifications allow the user to be notified when a report meets specific criteria (for example more than 5 case escalations within a week).You can subscribe to reports, for example, when a report that counts escalated cases has more than 5, you will be notified
- You can define the conditions to meet before Salesforce sends a notification to you: ex. Record count > 5
- You can specify when the report should run and refresh itself to evaluate your condition and decides send or not
- Used: automate business processes, send reminders to case owners. Can subscribe to up to 5 reports. Choose how notified: Salesforce1 app, email, chatter, action
- enabled by default, but can be disabled by the administrator
Tips for Scheduling Reports:
- You can schedule reports to run daily, weekly or monthly, and the send to users
- To schedule a report, you need the “Schedule Reports” permission
3 things to consider when scheduling a report:
- Picking the running users: this will dictate the data that is seen in the report. You will see the data that this user can see as if you were him running this report.
- When inactive, scheduled reports won’t run.
- Choosing the schedule time: best during off-peak hours.
- Run in the time zone of the user who set-up the schedule.
Emailing users: reports can be emailed to users, public groups, portal users (provided they are allowed to receive such emails).
- You can send emails for users and groups with access to the reprot folder in question
Getting started with Reports and Dashboards – On-demand training course: (from the Getting started – Vid link)
Access and Navigate: to access, need the Report Builder upgrade enabled.
- Salesforce has Standard reports for standard Objects
- Navigate through the Report tab, or bottom of every standard Object page, dashboard home page
- When you are in Report page, the default view shows the most recent items, can filter it, and search
- Left side: view of all folders that you have access to. Can Quick search, create and manage folders
Run standard reports:
- What you see is what you u can Report on!
- Choose a report from folder (report tab folder visibility and access can be changed by Admin), click on a report in it to run a report.
- When you run, you move to another page. See the above Paragraph for details.
- If a Report has more than 2000 records, you need Excel to see them all, as max display is 2000. Excel up to 256 columns and 65,536 rows in 1 report
Customize standard / personal reports using report options: need Customize/create report permission.
- At the top, you can fine tune results using Report Options: can change summarize by (to change the Group by), change date using different date criteria and value, add filters
- Using Save-As, you can change standard reports into your reports.
Create new reports using report builder:
- Report builder should be enabled for profile – if not, then the Report Wizard will show only
- Drag and drop report building. See the example above.
Customize standard / personal reports using report builder:
- Open any standard or custom report, click on customize to go to the Report Builder page, and change accordingly. Save it or Save it as.
Making reports run faster:
A report can go slow if:
- Too many columns: remove those you don’t need
- You can hide details to speed up query (can show details in the repotr running page agani.
- Too many recrds: set the scope as narow: like start and end date, My team’s activity, user filters,
- inefficient filters: make sure to add the relevant filters with right operators
- if not working, make sure they run during off-peak time and email users, make dashboard run less often
Embedded Analytics – Add Report chart to an Object page:
- This can be done to offer more insights. To do so, go to the Report and make sure it has a chart
- Put the Report in a shared folder where the users can access
- Go to the Object page layout and edit it: add “Report Chart” type, and choose
- Up to 2 repots per record detail page
- You can filter to only show data about the records viewed
- Reports should be summary or matrix
- Chart should be present in the report
- If you want to falter the chart for each records, its reprot type must contain the ID field for the page layout object
- The users viewing the report embedded should have access to view the report (don’t put in personal folder)
- First run, the report will be cashed for 24 hours. Within this 24 hours, fetched from cache.
Keep in mind that there is an hourly limit on the total number of chart refreshes,
12.2- Describe the impact of the sharing model on reports
- Check how to Share reports in the DATA Management chapter 11 (last section)
- The main thing to know here is that the Sharing settings (OWD or Sharing Rules) affect the report in the sense that you will see oin the report ONLY the Records that you normally see in Salesforce.
- For example, if OWD for a Record is Private, and no Sharing Rules defined, then each user will only see his Records, as well as the Records of the Roles under him
- The OWD, Sharing Rules and Role hierarchy will define what you can see in the Report
- So if 2 different users see different results in the same report, this is due to the Sharing model (OWD, sharing rules, single record sharing and Role hierarchy).
12.3- Describe the capabilities of custom report types
Report Type is the first step you get when creating new Reports
- Report type allows: 1- which records to return (A without and with B, A with B only). 2- Which fields you can select from pane.
- Standard and custom Objects have Report Types (if “allow reporting” checkbox when creating custom Object)
- Standard report types are always using the With (A with B)
The type is mentioned at the top of the new Report page
- You cannot change the report type after the report is created (you would need to create a new report)
- For example, an “Opportunities” type will include all opty records
- An “Opportunities with Products” report type will return only opty that have 1 or more Products associated with it. With one line for each opty associated product. If an opportunity has 3 products, it will appear as 3 lines within this report. If 0, then not included in report.
- The report type determines which fields can be added to the report from the left pane
- Standard report types are automatically included with standard objects. They cannot be customized, and automatically include standard and custom fields for each object within the report type.
- Standard report types are automatically included with standard objects and custom objects where “Allow Reports” is checked. Standard report types cannot be customized, and automatically include standard and custom fields for each object within the report type.
Custom Report Type: Generally speaking, unless you need a custom report type, there is no reason to create one. Standard report types usually meet the majority of an organization’s needs. But sometimes needed:
- Standard report types do not return the required set of records: example, I want to run an “Opty with Products” report that includes all opty (with or without products). The standard report type will only include opportunities if and only if they have product. However, I could create a custom report type that used an optional object relationship to meet this need
- Another example if I want report on Accounts with Contacts AND with Cases.
- Standard report types do not include field(s) that you would like on the report: For example, I want to run an opportunity report that includes the account owner’s manager as one of the fields. The standard opportunity report type will not allow me to add this field. However, when I create a custom report type, I can reference this field.
You build custom report types off of the relationships (master-detail and lookup) between objects to:
- Choose which standard and custom Objects to display to users when crating reports
- Define the required relationship between these Objects
- Define the fields that user can select in report when building Report
To create a custom Report Type: Build | Create | Report Types
- Step1: Select Primary Object for example, if you are looking for Account that have both Cases and Contacts, then select Account
- Enter the RT label “Account with Cases and Contacts” and name (auto) and which RT folder (category)
- You can choose: In Development (only admin can see) or Deployed – start with Development.
Step 2: define Report Record set: link Objects, for example Accounts AND Cases AND Contacts (up to max 4 Object relations). It can be:
- Each “A” record must have at least one related “B” record.
- “A” records may or may not have related “B” records.
- Step 3: select available Fields, and edit page layout of Report building: Set how fields display on the Select Columns page in the report wizard via this report type by selecting fields from the right-hand box and dragging them to a section on the left. Arrange fields on sections as they should appear to users in the report wizard. Fields not dragged onto a section will be unavailable to users when they generate reports from this report type. Also specify the default fields added to the report automatically (check boxed)
12.4- Describe the options available when creating and modifying dashboards (e.g., dashboard components, data sources, chart types, scheduling, running user)
Create and view dashboard:
- Need the permission “Create and Customize Dashboards”
- Need “Manage Dashboards” permission to create, edit and delete dashboards
- New dashboard from the Report tab, then add components by dropping them onto any column
- Components that can be added: Charts, Metrics, Graphs, Gauges
- Choose the Data Source. This is the report (Tabular will not work)
- Once done, you can modify: re-order, re-size columns, edit source, edit header… etc.
- You can add filters to the dashboard to limit the records. In this case, you can use a single dashboard with one set of source reports to serve a wide audience.
- On the top right, the dashboard’s running user is clearly indicated on the page. Click the drop-down to change the running user or make it dynamic based on who’s logged in. (Needs permissions).
- Home page dashboard can be changed: click on customize page
- The folders in the Report tab can contain both Repos and Dashboard, you can filer by dashboard only
- Reports that are based on should be summary or grouping by for best results
- New dashboard has default 3 columns (max). Can add or delete.
- Dashboard Refresh: You can set it up to run daily, weekly or monthly
Dynamic vs. Static Dashboard
- Dashboards provide a representation of report data at a point in time, as viewed by a user.
- As admin, when configuring a dashboard, you can specify to run as: logged in user, or a specific user.
- Dynamic dashboards are used to display information for logged-in user: used to show each user user-specific data, such as their personal quotas and sales, or number of case closures, or leads converted
- Normal dashboard shows data only from a single user’s perspective: used when you want to show regional or organization-wide data to a set of users, such as a region’s sales, or a support team’s performance on case closures.
- A dashboard is only current as of its last refresh date, while reports are current whenever run.
The data collected from the source report and displayed within each dashboard component is determined by the dashboard running user. Therefore, it is possible for a user to drill into a report from dashboard and see different results than dashboard itself. Ex:
- A is a SM and can see his and his team’s opty (let’s say a total of 150 opty). A is running user in a dashboard displaying opty.
- B is an AM from A’s team. In the dashboard, B see all 150 opty, but when B clicks on the dashboard to dig into the report, he only sees his own opty. But when A clicks on the details, he can see all
|Chart||Use a chart when you want to show data graphically. You can choose from a variety of chart types:
|Gauge||Use a gauge when you have a single value that you want to show within a range of custom values. For example, to create a dashboard that measures where your current closed opportunity amounts fall within a range of values, set the Minimum Value, Breakpoint #1 Value,Breakpoint #2 Value, and Maximum Value for the gauge. The ranges that you set can indicate poor, acceptable, and good performance. Set appropriate colors for each of these ranges to visually indicate progress. To create a gauge with only two ranges, leave Breakpoint #2 Value blank.
Select Show Percentage or Show Total to display those values on the gauge. Values exceeding the maximum are shown as greater than 100%.
|Metric||Use a metric when you have one key value to display. For example, if you have a report showing the total amount for all opportunities in the Closed, Commit, and Base Case stages in the current month, you can name that value and use it as a revenue target for the month displayed on the dashboard.|
|Table||Use a table to show a set of report data in column form. For example, to see the top 20 opportunities by amount, set Maximum Values Displayed to 20, click Customize Table and select opportunity name, amount, and other columns to display, choose the sort order, and set conditional highlighting. Available columns include all chart groupings and report summary fields, as well as the second-level grouping defined in the report.|
|Visualforce Page||Use a Visualforce page when you want to create a custom component or show information not available in another component type. For example, a Visualforce page can display data from an external system or show Salesforce data in a custom way.|
|Custom S-Control||Custom s-controls can contain any type of content that you can display in a browser, for example a Java applet, an Active-X control, an Excel file, or a custom HTML Web form.|
Extra Notes – Historical Trending:
- Gives managers insights into how data changing over time to make decisions. Can compare data in different points in time.
- Enable if from: Customize | Reports & Dashboards | Historical Trending
In the new report, choose opty with Historical Trending, and there you can specify the days that you want to add up to 5 historical dates, from up to the last 3 month).
- You can also add 4 Historical filters to a report to only show for example opty that has shrined
This is the display:
You can make it a Matrix report, and also add a chart:
Limits: 100- fields and 5M rows of data / No report Wizard / Not support in IE6