I've built a few applications that have displays of numbers in graphs. Pie charts, lines, bars, etc. I've used d3.js quite successfully in the past, but found it very heavy. I moved on to canvas.js and found it very fit for purpose, but decided to dig deeper. The end results of these, in modern browsers, is an svg element that draws the graph. Is it possible to cut straight to the chase, and avoid the library weight I don't need?

Reading up I found a site with sparklines that coded themselves into JavaScript embedded in SVG. You can see them deployed on my landing page, pulling in data from various sources. To see a really nifty implementation of sparklines and audio, see Jeremy Keith's site and click on any of the sparklines.

Javascript inside SVG works because the SVG is just more DOM and it can parse and rewrite itself as well as any other bit of html¹. For a dashboard at work I started building with dedicated SVG like this, where it's a very small and cachabe footprint and the parameters are passed as Query variables². Then came a new dashboard with caveats - users complete a survey. They then get access to a dashboard that shows their own data, and compares that to the minimum and maximum values for each survey question compiled from all entries.

Another caveat - it's in Sharepoint, saving to a List.

Option 1: Excel graph

When the survey saves, it's put into an excel spreadsheet as well as a Sharepoint list. Can I just build the graph in Excel? Well, I can, but then you can see everyone's data. Nuts to that.

Option 2: My funky SVGs

Sharepoint dashboards can embed images. That doesn't help me, as images don't parse as DOM, so can't self-draw.

Sharepoint dashboards can embed Markdown. Which don't pass parameters correctly, but again render the SVG in an image tag. Bleh.

Sharepoint dashboards have an <embed> option, which is very limited.

Sharepoint dashboards are very limited in what they can do, and I could not find a way to embed SVGs as <Embed> or <Object> tags that let me pass parameters to it.

Option 3: PowerBI

I haven't played with PowerBI before. It's very powerful, with a full on non-SQL query langauge between data and graph.

1. Create a function to convert strings to numbers (the survey had string values, but to graph them I need numbers, naturally)
2. Create a function to get the minimum values of all rows of a column, add that as a field
3. Create a function to get the maximum values of all rows of a column, add that as a field
4. Get the data
5. Convert each String-based result into their numerical representative (using function)
6. Use the Max and Min functions to add the max/ min for each survey answer and append it to each submission
7. UnPivot the table so we have a row per answer and submitter, rather than just a submitter
8. Get it all set for graphing.

I was very impressed with the ability to store functions in the logic and call them repeatedly for each table row. By adding the Max/ min as an column for each entry, I can easilly graph this as separate series of data; and I can use row-level security to lock those rows to the people who can see them, no exposing the data I don't want!

The license however. Unless you pay for the BIG end license, everyone needs a license to see the graph. Good for small teams, not so good for dashboards that may extend outside your organisation.

Option 4: PowerApps

Now PowerApps are an easy embed into a Sharpoint Dashboard, as well as creating an application you can use it its own right. I created a simple application of a Selector and Detail and then tried to mash it all into a single page. Reading a lot of posts it said it couldn't be done, but it can.

1. Get your landing page (with the list of rows) and hide it.
2. Put a trigger on Page Load to select only the row that the logged in user has access to (I did this with a selector based on Email; better if it's survey that links to a user).
3. Add the graph and get a stiff drink
4. In the Advanced tab, create a fake table based on the real table using Table(). In this table, manually set the columns you want as individual Records, pulling in the data from the main table and assigning it to a named field
5. Get another drink, as this is on the main table and not the PowerBI summary table, and Min/ Max don't work on multiple rows, just multiple fields in the same row.

So I was now left wondering how to get a Min/Max value. I'm in this deep, why not deeper?

Option 4 and a bit: PowerApps + Flows

Going all the way back to the start, the survey submits to a Sharepoint List. A Sharepoint List has triggers you can attach events to via Microsoft Flows, their IFTTT clone. This list is not a rapidly changing entity, there are repeat surveys done in 3+ month periods by a list of ~20 participants. So.

1. Create a flow attached to the table
2. On create/ update of a List item
   1. Create a variable to store the min/max as well as a swap variable, as Flow assignments can't update a variable based on its own value (unless a simple increment/ decrement, and not an object)
   2. Go through each row
      1. Calculate the new maximum for each field
      2. Store that in the Swap variable
      3. Move the Swap variable back to the Real variable (ergh)
3. Save that into a new MinMax list, set up to be column-ready for a graph series
4. Embed that new graph in PowerApps over the top of the other graph, aligning the X/Y axis and setting the min max to be the same as the other graph.
5. Set it to Lines so it's visually over the top of the Bars of the other graph.

It works, and was very interesting to experiment and delve into the inabilities of either the product or myself, but surely there's a better way. Any Sharepoint geniuses out there with a knowledge of how to do this simpler?