D3.js & Crossfilter.js: Brushing Ordinal Scales for Bars & Choropleths

ScreenShotThere are surprisingly few online examples of solving this problem with D3. There’s Mike’s original example and this one.

This problem would seem to be simple until you try to use ordinal values that are actually numeric to produce something like a distribution chart.

The reference examples for Brushing D3 Charts typically involve a handy helper library called Dimensional Charting (dc.js) that abstracts you from most of the D3 and Crossfilter details. Useful if you just need something simple but unhelpful if you need to do things it doesn’t support. In which case, you’ll find yourself extending dc.js with D3 and thinking, “I should have just learned how to do this in D3 and saved myself the time spent picking through the dc.js API docs and SO questions”.

If you get to this point (like I did) you’ll find only a few examples of SVG brushing with Crossfilters done natively in D3. And all are pretty inaccessible for new comers. They involve iterating over arrays of reusable Chart objects (very sensible) peppered with ternary operators and terse variable and function names. This original and frankly overly sophisticated pattern seems to have become the basis for all subsequent work. It all has the feel of a secret society or private members club…

So, here it is. A very simple and hopefully well documented example of native D3 SVG Brushing of Ordinal Scales to filter a Crossfilter domain and update a Choropleth. And as a bonus I’ve thrown in some real 2013 Medicare Hospital Spend Per Beneficiary (MSPB) data.

Screen Shot barChart

In the end I have to thank Couchand for taking my +50 StackOverflow reputation for his insight in how to get this working.

An important obstacle in working with ordinal scales and SVG Brushing is the lack of an .invert() method to convert pixel coordinates to your ordinal data range. I have to thank AmeliaBR for this answer which involves using ordinal.range() and ordinal.rangeBand() to calculate brush extent values.

The X axis similarly involves some hackery to filter the ordinal scales to get a sensible looking axis scales and labels.

All in all, this turned out to be a more challenging visualization project than I expected. Full source code and data files on the Github repo with technical instructions in the README.md

[gist https://gist.github.com/nyquist212/1c83356daf0bf79653e5]
Advertisements

Filter SSRS Report Content or Visibility based on User ID

Challenge

Using one standard report template (RDL file), restrict and customize the report content for individual users. Users in Location A can see their own data but not that of Location B. Accommodate exceptions such as managers that may be authorized to see data from both Location A and Location B. We don’t want to use local groups to administer SSRS report access and security permissions.

I have hundreds of Locations so hundreds of RDL files with their own ACLs is not an acceptable maintenance proposition.

Solution

Use the Windows User ID of the report viewer to determine visibility properties of report elements. Use the SSRS User!UserID object (which contains the visiting users Windows User ID) as both a report Variable and a Parameter. Each can be used for slightly different things.

Create a database table (called Vistors) containing their Windows User IDs and their authorized Location ID. Users like managers may have multiple entries if they are authorized to view multiple Locations. The easiest way is to create an Excel .csv and use the SQL Server data import tool (or bulk copy) to get it into the table.

In Report Builder setup Data Sources so you can access this Visitor table then setup a Visitor Data Set with

SELECT UserID, LocationID FROM Visitors WHERE UserID = (@Visitor)

Create a hidden Visitor parameter with a default value using the following expression

=Right(User!UserID, Len(User!UserID) – InStr(User!UserID, “\” ))

When the report is loaded, the Users!UserID object gets populated and passed to the Data Set query. The query then returns any LocationIDs for the Visitor.

From here you can use a nested parameter to select data by matching LocationIDs. You can also assign User!UserID to a global report variable called Visitor and use this variable to set the report item visibility propert.

Why the two different methods? You can’t use report Variables in a Dataset Query (in the TSQL). You can’t use Parameter values in the Visibility property of a report item such as a Tablix. It is also not possible to use Parameters!Users.Value as an attributes in a Lookup() expression from within a Tablix. Pretty stupid, huh?

To make the Variable in Report Builder, right click on the report background and select the Variables tab of the report properties dialog box and use the same expression

=Right(User!UserID, Len(User!UserID) – InStr(User!UserID, “\” ))

In the Report Builder set the report element Visibility property (of say a Tablix) with the following expression

=IIF(Variables!Visitor.Value = Lookup(Fields!Facility.Value, Fields!Facility.Value, Fields!UserID.Value, “Users”), False, True)

Run and test the report.

The Results

If the report viewer’s user ID is in the Visitors table, the tablix should be visible. If not, the tablix is  hidden.

For example, the Parameter value can be used in the TSQL query to filter the data set on page load using syntax like,

SELECT * FROM table WHERE some_field IN (@Visitor)

The global report variable provides a means to set the Visibility property of the report object (Tablix).