Logo

Tag Archive for dashboards

Can’t Clone Date or Range Widgets – Multiple Pages Workaround

Date or Range Widgets – The Problem

I wanted to create an interactive, multiple page dashboard like the one below:

The issue is that I wanted the same date widget to be accessible on every page as your user may want to modify the dates at any time.  Typically, you can create all of the widgets (containers, numbers, charts, lists/dropdowns) on a single page and then select them to add them to other pages, as below:

The problem is you cannot do this with Date widgets.  As of Winter ’19 (in a pre-release org) you get an error message that states: “Warning! Can’t clone Date or Range widgets.”  hmmmm….OK

Warning! Can't clone Date or Range widgets.

Warning! Can’t clone Date or Range widgets.

As the video demonstrates above, the “Add to Page” feature works fine for dropdown lists for use in faceting or bindings along with many other widget types but not for dates…

Workaround #1 – Clone Entire Page

If you are lucky enough to place the date widget onto the main page and set it up prior to creating your additional pages you can just clone the entire page and your date widget will be cloned (despite the error message) onto every page using not only the same step but the same widget (a linked widget just as you would expect).

Date Widget Is Linked!

Of course, this is only an option if you have not yet created your additional pages. In my opinion, it is the preferred workaround because it create a truly ‘linked’ widget and changes to the widget will not have to be manually replicated onto additional pages.  Additionally, it is a no-code workaround which is another plus for many.

Workaround #2 – Update Steps

This workaround consists of creating a second, third, … (as many as necessary) date widgets for each additional page required in your dashboard.  Then, you will update the JSON to point these new date widgets to the original step that the original date widget is using from page 1.

After creating the date widget onto the canvas, actually set up the underlying step by clicking on the “Date” link and selecting a dataset and date field.  You can choose any date field because we will be updating the underlying step manually in a minute.

This step of actually setting up a step is necessary because you cannot drag the date step from the list of steps to the date widget.

Can’t drag date step to date widget – I guess that’s why it’s italicized!

Now that we have the original date widget and step from page 1 AND all of the additional date widgets on subsequent pages we CMD+E or Ctrl+E into the JSON!

Locate the original date widget and step from page 1 (you can also click on the widget prior to accessing the JSON and view the details there).

In this case I can see that my widget from page 1 is using the step “Last_Activity_1”.  You will want to save this value because we want to update the date widgets from the other pages to also use this step.  Once you have located the other date widgets and the steps they currently leverage you can update them.

I like to use the Find & Replace tool that is hidden until you CMD+F or Ctrl+F twice (that’s right, use the same keyboard shortcut twice in a row!  Magic!).  Now it’s simply a matter of finding the incorrect values and replacing them with the correct value.

Once I have replaced all of them (only the ‘Step’ values), the widgets are linked to the same step and the user can now update the date widget on any page and their selection will persist while navigating to any other page.

Have you encountered this before?  I suppose a third workaround may be using code to actually add the same widget (linked) onto each page through JSON however this would be a lot more work for virtually the same result.

How long will we have to live without the ability to drag date steps to the widget or use the Add to Page functionality for date widgets? (Even though we can currently clone the entire page with the date widget included).  Let me know your thoughts and your best Einstein Analytics workarounds in the comments!

Salesforce Analytics SAQL Join Multiple Datasets

Want to combine more than one Salesforce Wave datasets into the same chart widget in SAQL?  Well, you’ve come to the right place.  This is something that practically no one is discussing and it’s easy to feel like you’re on your own!

Additionally, In my SAQL experience I’ve determined that it’s all about the bindings!  Salesforce has a good guide but it could contain additional information.  It’s also easy to get confused between the classic dashboard/bindings and the new & improved bindings.

Today we are going to cover two primary objectives: (1) Combine at least two datasets into the same stream/chart widget. (2) Create a static step to present a variety of fixed options that will be fed into the query from #1 as a parameter that will control the results and make the dashboard interactive!

The Data

We are going to use data downloaded from the US Government – thrilling!

Dataset 1: US Population by State from 2010 Census

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

Dataset 2: Top 10 Causes of Death by State (Years 1999-2013)

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

With Dataset #2 we can do some really cool things like a donut to show the # of deaths by cause for a single year:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

We can also use a timeline chart to see the # of deaths by cause over time:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

The reason we want Dataset #1 is that some states have more people living in them than others (shocking, I know).  We want to take the # of deaths by state in 2010 and divide by the population of each state in 2010 to calculate deaths per capita.

Combining Multiple Datasets Into Single Widget

The primary rule with joining more than one dataset into a single SAQL query/widget/chart is that you have to use a dashboard.  You cannot do this within a single Lens.  A Lens is explicitly tied to a single dataset.

We also have to add a place-holder step/chart to the dashboard for both of the datasets we want to access before we can combine both datasets into a single step.

For example, if I just create a new dashboard, drag on a chart widget, click on it and then create a new step and navigate to the SAQL and create the query I want to use, which is this by the way:

q = load "X2010_Census_Population";
q = group q by 'State';
q = foreach q generate 'State' as 'State', sum('X2010_Census_Population') as 'sum_X2010_Census_Population';
q = order q by 'State' asc;
q = limit q 2000;
a = load "Deaths_for_top_10_causes_by_State";
a = filter a by date('YEAR_Year', 'YEAR_Month', 'YEAR_Day') in [dateRange([2010, 1, 1], [2010, 1, 1])];
a = filter a by 'CAUSE_NAME' != "All Causes";
a = group a by 'STATE';
a = foreach a generate 'STATE' as 'State', sum('DEATHS') as 'sum_DEATHS';
a = order a by 'State' asc;
a = limit a 2000;
x = union q, a;
x = group x by 'State';
x = foreach x generate 'State' as 'State', sum('sum_DEATHS') / sum('sum_X2010_Census_Population') as 'Deaths per Capita';
x = filter x by 'State' != "United States";
x = order x by 'Deaths per Capita' desc;
x = limit x 2000;

I get the following error:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

First, some notes on the code above.  We are loading “q” which is our 2010 Population by State Dataset (dataset #1 above) and simply grouping by state and providing the total population by state.  Then we are loading “a” which is our dataset that contains the # of deaths by state by year for the top 10 leading causes.  We are making sure we filter down to just 2010 and filtering out a cause of “All Causes” as these are already included in each granular cause.  Then we are just getting the total # of deaths by state.  We UNION these together (not really much different than a UNION in SQL) as “x” and run the division to get our ‘Deaths per Capita.’  We have to filter out a state that is listed in both datasets as “United States” as again, just like the “All Causes” scenario, these totals are already included within each individual state.

Here’s how you can use the same code (as above) but not get this error: “You can’t use the following datasets in this query because they aren’t included in the dashboard:”

Step 1: Drag a chart widget onto the dashboard, then click into the middle of the widget where it says “Chart.”

Step 2: Select one of the datasets you want to access in your query.

Step 3: Wave ahem, Einstein Analytics will have a default chart for Count of Rows.  You don’t need to change anything here – just click “Done.”

Step 4: Now click on the “Create Step” button in the right column of the Dashboard builder.  If it isn’t displaying, ensure you are in “Edit” mode (a Pencil Icon will be displaying in the buttons at the top right if you are not in Edit mode, click this button).  If you are in edit mode and you do not see “Create Step” then click somewhere on the background of the dashboard.

Step 5: Now in this step select the other dataset that you need, the one you didn’t select in Step 2.

Step 6: Here is where you can switch to the SAQL mode and write your query using data from both datasets and then join them together either using UNION or COGROUP.
SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

Click “Run Query” to make sure it’s working how you need it to and then click “Done” to finish creating the step.

Now you can use this step for your chart widgets (Drag and drop the step you just created) and delete the first chart widget and step you had to create in steps 1-3.  The fact that we have to go through steps 1-3 only to just get rid of them is unexplainable just for fun!

I went ahead and added a US Map widget in addition to the column widget.  The state with the highest deaths per capita (from the top 10 causes of death) in 2010 was….drum roll please….West Virginia!

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

Now we have a step that is accessing data from both datasets and providing the visualization needed.  Now, how do we add some interactivity?

Bindings for Interactivity

We are going to create a static step to present options to our dashboard user and a pillbox/toggle for the user to click on to input their selection.  Finally we are going to take the result of that input and update our main query step (previously created above) to change the widget based on this input.

We can make a static binding for anything we want.  Red/Blue, Avg/Total, East/West.  We are simply defining the text that will present to our users and the parameter value we are going to include in our query.

Let’s start with East/West.

The easiest way to create a static step is to CMD+E / CTRL+E into the JSON and locate where all the Steps are defined.  This image displays where the steps begin and the first step:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

Place your cursor before the first step and create some line breaks.  Then we will use the following code to create our static step.

 

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 "toggle_states": {
   "datasets": [],
   "dimensions": [],
   "groups": [],
   "isFacet": false,
   "isGlobal": false,
   "numbers": [],
   "selectMode": "single",
   "strings": [],
   "type": "staticflex",
   "useGlobal": false,
   "values": [
             {
         "display": "West",
         "value": [
         "West"
             ],
         "Region": "West"
             },
             {
         "display": "East",
         "value": [
             "East"
             ],
         "Region": "East"
              }
         ]
 },

The name of the static step is defined first, you could set this as anything you want.  Then we are just adding two values “West” and “East” and labeling them “Region” – this again could be anything you want to label it!  We also have ‘value’ and ‘display’ defined to show how you can add multiple properties.  ‘Value’ is the text that will display in our toggle pillbox to the end user.

Note the ‘selectMode’ is “Single.”  This is important as it defines whether we can select more than one at a time, whether one has to always be selected, etc.  For now we are going to use “Single” which means that one of these options is not required to be selected, but we can’t select more than one at a time.

After this code is entered you can click the “Done” button.  Now drag this step “toggle_states” onto the dashboard.  You will instantly see your two options available to you.  You can even run the dashboard (click the eye icon) and toggle between the two options.  Yes, they don’t do anything to your data yet…but they exist!

Now we are going to edit our original query from before to include input from this toggle step.

Use CTRL+E or CMD+E to get back into the JSON and locate your original step with the query for the deaths per capita.  Here are the groups, numbers, query, and strings for mine after I’ve updated it.  Essentially I’ve used a case statement to group the states into West & East.  It can even detect when the toggle button is not used and return ‘All.’  I’ve then added a filter that uses the input from the toggle step to filter down to only West or East states.

 "groups": [
 "State"
 ],
 "isFacet": true,
 "numbers": [
 "Deaths per Capita"
 ],
 "query": "q = load \"X2010_Census_Population\";\r\nq = group q by 'State';\r\nq = foreach q generate 'State' as 'State', sum('X2010_Census_Population') as 'sum_X2010_Census_Population';\r\nq = order q by 'State' asc;\r\nq = limit q 2000;\r\n\r\na = load \"Deaths_for_top_10_causes_by_State\";\r\na = filter a by date('YEAR_Year', 'YEAR_Month', 'YEAR_Day') in [dateRange([2010, 1, 1], [2010, 1, 1])];\r\na = filter a by 'CAUSE_NAME' != \"All Causes\";\r\na = group a by 'STATE';\r\na = foreach a generate 'STATE' as 'State', sum('DEATHS') as 'sum_DEATHS';\r\na = order a by 'State' asc;\r\na = limit a 2000;\r\n\r\nx = union q, a;\r\nx = group x by 'State';\r\nx = foreach x generate 'State' as 'State', case when \"{{column(toggle_states.selection,[\"Region\"]).asObject()}}\" == \"\" then \"All\" when 'State' in [\"Colorado\",\"California\",\"Utah\",\"Oregon\",\"Washington\",\"Idaho\",\"Nevada\",\"New Mexico\",\"Utah\",\"Wyoming\",\"Montana\",\"Texas\",\"Oklahoma\",\"Alaska\",\"Hawaii\",\"Kansas\",\"Nebraska\",\"South Dakota\",\"North Dakota\",\"Arizona\"] then \"West\" else \"East\" end as 'Type', sum('sum_DEATHS') / sum('sum_X2010_Census_Population') as 'Deaths per Capita';\r\nx = filter x by 'Type' == \"{{column(toggle_states.selection,[\"Region\"]).asObject()}}\" || 'Type' == \"All\";\nx = filter x by 'State' != \"United States\";\nx = order x by 'Deaths per Capita' desc;\nx = limit x 2000;",
 "selectMode": "single",
 "strings": [
 "State"
 ],
 "type": "saql",

Now I can toggle between all:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

West:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

 

or East:

SAQL Join Multiple Datasets Salesforce Analytics Einstein Wave

There are a lot of cool things you can do with selection bindings to give the user interactivity and control.  In this case we are using the parameter as a filter, but you can use it to change the actual metric.  For example, we could toggle between the calculation of ‘Deaths per Capita’ and ‘Sum of Deaths’.  I’ve even used 3 or 4 separate toggle steps with at least 2 to 3 options each for a single chart widget.  You can combine them to build a full featured interactive dashboard that allows for quick and valuable business insights!

There is so much more you can do with bindings, but because this post was primarily about how to combine multiple datasets in SAQL this was just a small taste.  Stay tuned for more!  Have you had a need to combine multiple datasets?  Has this post been helpful?  What other questions do you have?  I would love to hear in the comments or on twitter @SFDC_r

Analytics Up! A Primer on Riding the Salesforce Wave

Riding The Salesforce Wave - SFDCr

Salesforce Wave Analytics – An Introduction

So you want to ride the Wave, ahem build some really cool stuff in Salesforce’s Analytics product?  Great!  I’m so excited about the future of Wave.  It’s a joy to work in and it’s smooth visualizations and speedy updates allow for fast insights.

In this post, I am not going to spend a lot of time covering the already well covered basics of how to build Datasets, Lenses, & Dashboards within Wave.  Take this Module on Trailhead: Wave Analytics Basics or this one: Wave Dashboard Designer Basics

Or just sign up for a dev org here and get started: developer.salesforce.com/promotions/orgs/wave-de

Coming from a SQL background, what I have been very interested in and have attempted to follow very closely is the ability to edit the SAQL programmatically and do things outside the norms of what is allowed in the UI.  This has taken a few different paths over the years but I’m happy to report that we are now closer than ever before to make it as easy as possible for those who want to get into the code.

Why would you want to get into the code?  Because it allows you to customize the visualizations for your end user in any way that is supported by the product.  A quick example may be that your end user wants to see lost opportunities as a negative amount instead of a positive amount within a bar chart.  Just quickly edit the underlying code and use a Case statement to accomplish this:

That’s Great, Now How Do I Get Into The Code?

CTRL + E or CMD + E

It’s really that simple and it just depends whether you are using WIN/MAC.

Once you are in the code on a particular lens or dashboard, you will be viewing the JSON.

Think of the JSON as a blueprint for the entire dashboard; the queries are just one small part of it.  It defines the datasets, queries, widgets, bindings, colors, all the properties of the dashboard.

Why Is This Awesome?

I love self-contained code like this!  It means that all you need to re-build that dashboard or copy it to another org w/ changes is to copy and paste all of the JSON to a brand new dashboard.  I’m going to go ahead and try it below.  Watch me start with a brand new dashboard and quickly update with all my JSON from a previously created dashboard:

Isn’t that awesome?!  All I had to do was take my custom JSON that I painstakingly worked so hard to edit and paste it into a brand new dashboard.  Now I have rebuilt it and can continue working on a duplicate or test out new customizations.  I especially love this as I like keeping a text backup of different versions in case I need to go back to an earlier revision or reference an old query in a new project.

Query Salesforce Real-time With SOQL

As of the Salesforce Spring ’17 Release, it is now possible to directly query Salesforce objects in a SOQL step:

It’s relatively simple how it works.  Just create a dashboard, create a step for a single chart.  Then edit the JSON to go find that step.  Replace everything within that step with the code below:

"soql": {

  "type": "soql",

  "query": "SELECT Name from ACCOUNT",

  "strings": ["Name"],

  "numbers": [],

  "groups": [],

  "selectMode": "single"

}

Go ahead and type standard SOQL within the Query section.  Use workbench to validate it’s proper SOQL as it’s hard to troubleshoot in Wave.  I was happy to find that it supports column aliasing.  This allows you to rename the labels that will be used within the charts/tables to whatever your end users will best understand.  After you write your SOQL query, make sure to place the alias/column names into the proper ‘property’ as outlined in the table below.  For example, make sure to place your values in the numbers section.

Property Description
type Step type. Set to soql.
label Step label.
query SOQL query. For more information about SOQL queries, see Force.com SOQL and SOSL Reference.
strings Flags the specified fields as non-grouping dimensions. For example, you can flag a field as a dimension for a values table in which no groupings are allowed.
numbers Flags the specified fields as measures.
groups Flags the specified fields as groupings. For example, you can flag a field as a grouping for a pivot table or chart.

Using a table with the SOQL step while writing the query is helpful as it will display data even at times when a chart will not display any results.  The table will help you understand what is broken and what needs to be updated with the query:

Salesforce Wave - Dashboard Table for SOQL

There is practically no documentation on this feature (that I could find) or information on the limits of what you are allowed to do or not.  At first I thought it would not support grouping and aggregation, but I did determine that I was able to as long as I followed standard SOQL grouping rules (cannot group by formula field, cannot group by number field, etc.)  Once I overcame this limitation (workbench was a big help), I was able to write the query below which uses aliases, aggregation, and grouping.  Unfortunately due to the mentioned limitation of not being able to group on a number or formula field, I had to create a string field to store the value as well (two fields to store the same value?!) to group on.  In a production org we would need a trigger to keep them in sync.

 

            "soql": {

                "groups": [

                    "CustRating"

                ],

                "numbers": [

                    "CustomerRating",

                    "NumAccounts"

                ],

                "query": "SELECT CusRatingText2__c CustRating, COUNT(Id) NumAccounts,Min(CusRating__c) CustomerRating from ACCOUNT WHERE CusRating__c > 0 GROUP BY CusRatingText2__c ORDER BY Min(CusRating__c) ASC",

                "selectMode": "single",

                "strings": [

                    "CustRating"

                ],

                "type": "soql"

            },

This is so great, it allows me to query Salesforce directly and not wait for the scheduled Dataflow job to run to pull in fresh data into Wave!

See this example below for how it will pull in live data – look right above the 70 for Customer Rating.  At first it’s missing, then it appears:

All I did was switch the dashboard to edit mode, update an Account in Salesforce, and then switch back to the dashboard and close out of edit mode and it instantly updated the chart to reflect the org change.  This is pretty powerful stuff.

I would like to see the standard ‘refresh’ button within the dashboards actually re-query these SOQL steps.  The reason I went into edit mode and back out in the example above is that the refresh button will not force a refresh and pull in this new point like going into and out of edit mode.  I also would like to see a timed update – imagine being able to set it to re-query in 15-30 second intervals for placing one of these dashboards on a screen within the office that’s always up to date!

Simply hover over the selected point in the chart to see the additional pop-up details:

Salesforce Wave - SOQL Chart 70 Hover Over

Thanks for coming along on this first outing into the surf.  We have only scratched the surface of what is possible by getting under the hood with Wave.  I plan to spend a lot more time in Wave Analytics in the near future and will share my experiences.  What are you interested in or have already discovered with Wave?  I would love to hear about it!  Comment below or tweet them @SFDC_r!

 

SFDC Idea – Set Dashboard Running User To Role

SFDCr - Salesforce Ideas

Set Dashboard Running User To Role

Have you ever wanted to run a dashboard as a role instead of setting it to a specific individual user?  I know, I know, you are probably saying that you could simply use a Dynamic Dashboard so that it will always run as the logged-in user.  Besides the sad fact that this would use up one of your 10 dynamic dashboards (yes, a limit of 10 – even on Unlimited Edition.  C’mon Salesforce!) there are other reasons why you don’t always want it to run as the logged-in user.

For example, you may have a “Team Dashboard” with the running user set to the team’s manager.  The dashboard would then display rankings for every member on the team and how they stack up against each other.  When an individual rep clicks into an individual component, they only see the details of their own records, but on the dashboard everyone sees the same info.  When the manager clicks on a component, they would see the detailed report with data for the entire team.

This all works great, and only is really possible for many objects by setting the dashboard to run as an individual user (not dynamic).  Where the problem lies is that you have to change the running user upon any personnel changes concerning sales management.  If you have 100 sales teams within your org, you may end up becoming the ‘running user’ running far far away!  So, you’re wondering what would solve this dashboard dilemma?

Check out the following idea on the Salesforce IdeaExchange: Running User by Role.

Run The Dashboard As A Role – Not A User

This would be awesome!  You could set each dashboard to run as the manager role for each team and never have to adjust the dashboard or underlying reports again, no matter what changes take place with personnel!

What do you think?  Would this feature benefit you in your organization?  If so, vote!  Do you have other workarounds for the same situation?  Go ahead and tweet or post your thoughts in the comments!

Avoid Long Load Time To Edit Dashboard Component Report

Salesforce Quick Tips Edit Dashboard Component Report

Edit Dashboard Component Report

Have you ever wanted to edit the underlying report for a dashboard component and found it cumbersome?  A dashboard executes with a ‘running user’ and this user feeds into all of the underlying reports.  This is great, because it means that you can create a single set of reports and not have to edit the reports for the dashboard to provide useful data to an entire set of your users.

The Problem – Edit Dashboard Component Report

Because your user is likely placed very high up in the role hierarchy (so that you have access to all or most of the Org’s records) when you click on a dashboard component to view the underlying report, it can take many minutes (or even timeout).  Your users don’t have this issue, they click the dashboard component and the user running the report would only see records at or below them in the role hierarchy – likely running quickly.  You as the admin, however, can only finally click “Customize” to edit the report after waiting a long time (and that’s if it doesn’t timeout).

The other option to avoid the above dilemma (and wait) is to navigate to “Reports” and locate the report you want to edit.  Instead of running the report, click the arrow and “Customize.”  The issue here is that you have to know which report you want to edit, which probably requires “Editing” the Dashboard to view the exact name of the report for the specific component.  This can be time consuming and cumbersome as well.

A Better Solution – Salesforce Quick Tip!

I present to you a better solution.  Simply right click the Dashboard Component you would like to edit the underlying report for and copy the URL.  Paste the URL into your browser (probably a new tab) it should look something like this: https://[YOURSERVERURL].salesforce.com/00O61000003p9Nj if it happens to have anything after the Report ID – just remove it so it looks like the above.  Now add “/e?” to the end of the URL.

Final URL: https://[YOURSERVERURL].salesforce.com/00O61000003p9Nj/e?

Problem Solved

Now just hit Enter and you will be taken directly to editing the underlying report – and it will load quick.  Never get stuck waiting for a report to load just to edit it!

What quick tips do you have related to Dashboards or Reports?  Feel free to post in the comments or on Twitter!