Logo

ideas

The Model View Controller (MVC) Method of Building EA Dashboard Interactivity With Bindings – Part 2

In Part 1 we looked at the MVC method of building Einstein Analytics dashboards conceptually.  Now we are going to review specific examples and code to put it into practice.  First, let me say that I’m not sure everything that is accomplished is fully supported by the platform.  So know that it could potentially break at any point in time.  However, it has worked for a few years and while some examples for how you could use this can be accomplished via other methods, especially now with the ‘advanced editor’ via the UI – there are use cases where I’ve found this to be the only method that works.

A Few Use Cases

I have used this MVC design pattern in other programming languages so when I ran into roadblocks in trying to design a dashboard with certain functionality I tried it as a workaround and I was very happy when it ended up working!  I have used this in cases where we have a single widget (or multiple that leverage the same step/query) but need a variety of control from the end user.  Sure, if they just want to switch the grouping and/or the measure we would be fine.  But what if at the same time they needed to toggle the specific function that is being calculated on the fly (for example a windowing function).  These functions need to already have the stream organized and ‘grouped’ properly prior to the calculation.  Moreover, you need to specify that grouping and measure within the function.  On top of all of this, I’ve had to build dashboards that toggle between not only various groupings but completely different timeframes (hour, year-week, year-month-day, month) and use different summary calculations (avg, running total, etc.) and these all have to be specified within the function and the stream already ‘prepped’ with the proper information.

A Relatively Simple Example

What follows is a relatively simple example where we are only changing the measure, toggling between ActivityDate and CreatedDate and also toggling whether we are viewing the ‘actual’ count of records or a Moving Average period of 10, 15, 30, 45 or 60 days.  Just know that you could easily expand on this and add additional toggles for some of the use cases I mentioned above.  Once you set up the dashboard in MVC you have laid the groundwork to add additional functionality relatively easily.

 

In order to build this we need our visual components which are the widgets and the static steps for the Toggles.  These are fairly simple and can be input via the UI:

 

Then, you need to create a Controller Step that will use toggles to ‘feed’ code into the main step that feeds the chart.

Below is the full code for the Controller Step.  Essentially it takes a single ‘row’ (limit 1) of the dataset that we are using so we ‘know’ we will have at least one result and then we use a series of case statements in order to evaluate the user selections and output code based on those:

"ControllerStep": {

"broadcastFacet": true,

"groups": [],

"label": "ControllerStep",

"numbers": [],

"query": "q = load \"activity\";\nq = foreach q generate case when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"Actual\" then \"sum(count)\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"10 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-10..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"15 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-15..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"30 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-30..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"45 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-45..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"60 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-60..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"Actual\" then \"sum(count)\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"10 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-10..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"15 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-15..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"30 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-30..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"45 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-45..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"60 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-60..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" else \"avg(sum(count)) over ([-60..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" end as 'WindowFunction', 'ActivityDate_Year' + \"~~~\" + 'ActivityDate_Month' + \"~~~\" + 'ActivityDate_Day' as 'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day', count() as 'count';\nq = limit q 1;",

"receiveFacetSource": {

"mode": "all",

"steps": []

},

"selectMode": "single",

"strings": [],

"type": "saql",

"useGlobal": true,

"visualizationParameters": {

"parameters": {

"borderColor": "#e0e5ee",

"borderWidth": 1,

"cell": {

"backgroundColor": "#ffffff",

"fontColor": "#16325c",

"fontSize": 12

},

"columnProperties": {},

"columns": [],

"customBulkActions": [],

"header": {

"backgroundColor": "#f4f6f9",

"fontColor": "#16325c",

"fontSize": 12

},

"innerMajorBorderColor": "#a8b7c7",

"innerMinorBorderColor": "#e0e5ee",

"maxColumnWidth": 300,

"minColumnWidth": 40,

"mode": "variable",

"numberOfLines": 1,

"showActionMenu": true,

"showRowIndexColumn": true,

"totals": true,

"verticalPadding": 8

},

"type": "table"

}

},

Then, we use those outputs from the ‘ControllerStep’ in our primary step that feeds the visual chart on the dashboard:

"ActivityDate_Year_Ac_1": {

"broadcastFacet": false,

"groups": [],

"label": "ActivityDate_Year_Ac_1",

"numbers": [],

"query": "q = load \"activity\";\nq = filter q by date('ActivityDate_Year', 'ActivityDate_Month', 'ActivityDate_Day') in [dateRange([2018,4,28], [2018,8,12])];\nq = group q by ({{coalesce(cell(static_2.selection, 0, \"FirstGroup\"), \"'ActivityDate_Year', 'ActivityDate_Month', 'ActivityDate_Day'\").asString()}});\nq = foreach q generate {{coalesce(cell(static_2.selection, 0, \"Year\"), \"'ActivityDate_Year'\").asString()}} + \"~~~\" + {{coalesce(cell(static_2.selection, 0, \"Month\"), \"'ActivityDate_Month'\").asString()}} + \"~~~\" + {{coalesce(cell(static_2.selection, 0, \"Day\"), \"'ActivityDate_Day'\").asString()}} as {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}}, count(q) as 'count';\nresult = group q by {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}};\nresult = foreach result generate {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}}, {{coalesce(cell(ControllerStep.result, 0, \"WindowFunction\"), \"sum(count)\").asString()}} as 'MovingAvg';\nresult = order result by ({{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}} asc);\nresult = limit result 2000;",

"receiveFacetSource": {

"mode": "none",

"steps": []

},

"selectMode": "single",

"start": [],

"strings": [],

"type": "saql",

"useGlobal": false,

"visualizationParameters": {

"parameters": {

"autoFitMode": "keepLabels",

"showPoints": false,

"legend": {

"descOrder": false,

"showHeader": true,

"show": true,

"customSize": "auto",

"position": "right-top",

"inside": false

},

"axisMode": "multi",

"tooltip": {

"showBinLabel": true,

"measures": "",

"showNullValues": true,

"showPercentage": true,

"showDimensions": true,

"showMeasures": true,

"customizeTooltip": false,

"dimensions": ""

},

"visualizationType": "time",

"missingValue": "connect",

"dashLine": {

"measures": "",

"showDashLine": false

},

"timeAxis": {

"showTitle": true,

"showAxis": true,

"title": ""

},

"title": {

"fontSize": 14,

"subtitleFontSize": 11,

"label": "",

"align": "center",

"subtitleLabel": ""

},

"trellis": {

"flipLabels": false,

"showGridLines": true,

"size": [

100,

100

],

"enable": false,

"type": "x",

"chartsPerLine": 4

},

"fillArea": true,

"showActionMenu": true,

"showZero": true,

"measureAxis2": {

"sqrtScale": false,

"showTitle": true,

"showAxis": true,

"title": "",

"customDomain": {

"showDomain": false

}

},

"measureAxis1": {

"sqrtScale": false,

"showTitle": true,

"showAxis": true,

"title": "",

"customDomain": {

"showDomain": false

}

},

"valueType": "none",

"theme": "wave",

"applyConditionalFormatting": true,

"drawArea": {

"measure": "",

"showDrawArea": false,

"bounding1": "",

"bounding2": ""

}

},

"type": "chart"

}

},






I didn’t do it here but for a more polished look you can even update all display names on the chart axis with the proper string based on the user selection.

Please let me know if you have had success with this.  Please let me know if you have a better solution!  Honestly this is a bit of a hack that I’ve come up with but it has saved me countless times!  Every time I think I can do it via normal binding functionality I run into gaps and limitations due to issues with having to already have the stream prepped for the function or specifying the measure and grouping in the function which you cannot do with normal binding syntax.  This has been a lifesaver and I hope it is helpful to you.  Until next time, keep riding the Wave (or Einstein Analytics)!  Once Wave Always Wave!

My Wave…Ahem, Einstein Analytics Wish List PLUS A Bug!

SFDCr - Salesforce Ideas

I’ve been thinking about Einstein Analytics, its current feature set and the gaps impacting my day to day.  I thought I would list some of those here, in no particular order.  I’m sure I am forgetting some, so remind me in the comments and I can add as I think of more!

Alright…I know everyone doesn’t get a pony!  But we can try?

Edit Steps w/ Bindings In UI Outside of JSON

Currently, we still edit these steps with static bindings…but we do a lot of “\n” \” etc…

Edit SAQL & Still Use UI

Being able to edit the groupings, measures, filters and other features in the UI after making an edit to the SAQL would be a huge win.  You can always go back to before you made that change but currently it makes you think twice about the order of operations.

Allow “Locked” Containers

Imagine a container that is ‘frozen’ (just like “freeze panes” or locking the top row in Excel) and anything that you placed in that container – Titles, Pillbox, Filters would remain glued to the area of the dashboard that you selected.  It would be ‘immune’ from scrolling.  This would allow users the ability to scroll down to view other dashboard widgets but always have the filters/pillboxes to toggle from within easy reach.

Multi-Line Commenting

If I have to ‘dash-dash’ another consecutive line…

Advanced Filter Logic (And/Or) Outside of SAQL

Sure, AND (((we AND can) OR (do AND the)) OR advanced) AND in AND SAQL AND (but OR why OR not OR make OR it OR easier?)

This includes being able to select (in the UI) the same field to filter for two or more conditions!

Allow The Use of Bindings to Toggle Images

This would allow the limit of 20 pages to go a lot farther.

Bug – Incrementing After Mouse No Longer Clicking

I have to list this bug as I realized this is not yet resolved even as of Winter ’19 in my pre-release org.  As you can see below, you can click the mouse once on the arrows and it will continue to increment even after ‘mousing up’ during hover-over…while the mouse is no longer clicking!

How to Close or Complete Event Activities + Salesforce Bug!!

Salesforce Events – Closed or Open?

Do you know what determines whether a Salesforce Event Activity displays in the “Open Activities” related list or the “Activity History” related list?  Let’s start with an event activity with a start/end date in the past:

Event Details

This record displays on the Account detail page within the “Activity History” related list, not the “Open Activities” section.

account activity history related list

What about when you are selecting to view Open or Completed Activities within a Salesforce Report?

report show open or completed activities

 

Events Are Automatically Completed/Closed

Salesforce documentation indicates “All events will automatically be moved to the Activity History the day following the scheduled event date.”  In other words, unlike tasks where they are manually marked “complete” – events are automatically completed after the scheduled date has past.

I found this very interesting because I’ve seen events appear “completed” on the same day as the scheduled start/end date.

A Warning About Utilizing Report Data

Within Salesforce Reports, there is a field labeled “Closed.”  This field indicates whether the task or event record has been completed.  This isn’t an actual field on the record and cannot be found within the database utilizing a SOQL query.

event soql db details no closed field

The issue with Events is that the same exact “Closed” field can display different results depending on whether it is a standard or custom report type.

The same record above that displays in the “Completed Activities” related list displays as “Closed” = True in the standard activity report type:

Standard Report Type Closed Event

However, when using a custom report type the same record displays as “Closed” = False.

Custom Report Type Open Event

Come on Salesforce!  This can’t be by design, can it?!  I can only assume that because it’s really a ‘faux field’ and the report is determining whether the event is closed or not that this logic was only applied to the standard report types.

This can be dangerous particularly if you were using this field in a CRT as filter criteria or using this field to aggregate – sum/avg/etc across all activities.

What are your thoughts or issues with Events?  Have you created a custom field to let users manually indicate whether they have completed the activity?  There are numerous ideas on the Success Community regarding the way Events are marked complete.  Here’s one.  Please let me know your thoughts in the comments or on twitter @SFDC_r – I would love to hear from you!

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!