Common Patterns For Formula-Driven Charts

Bar Charts

Basic

LET $data := DB_REPORT(ARRAY("[Username]", "[ActionType]", "COUNT(1)"), "sys.Audit", "[DateCreated] > (CURRENT_DATE - 900)");

// put source data into a structure that we can easily search- (Using HASH_GET later)
LET $lookup := ARRAY_TO_HASH(
	$data,
	// first 2 items are our grouping columns
	$(i, item) ARRAY_TAKE($item, 2),
	// value column
	$(i, item) ARRAY_LAST($item)
);

LET $xlabels := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 1)));
LET $seriess := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 2)));

RETURN HASH(
   "series", ARRAY_SELECT(
		$seriess, 
		$(l, label) HASH("label", $label)
	),
   "panes", ARRAY(
	   HASH(
		   "labels", $xlabels,
		   "layers", ARRAY_SELECT(
				$seriess,
				$(s, series) HASH(
						"type", "bar", "data",
						ARRAY_SELECT(
							$xlabels,
							$(i, label)
								COALESCE(HASH_GET($lookup, ARRAY($label, $series)), 0)
						)
					)
			)
		)
	)
)

Results in this output:

Date X-Axis

This is a sample bar chart with dates across the X Axis. You will need 2 date fields, Date From and Date To. Only data from between these dates will display:

LET $dateFrom := COALESCE([Date From], DATE(YEAR(NOW()), 1, 1));
LET $dateTo   := COALESCE([Date To],   DATE(YEAR(NOW()), 1, 1));

// filter strings, leave null to exclude from DB_REPORT
LET $filters := ARRAY(
	"[DateCreated] >= " & DB_ENCODECONSTANT($dateFrom),
	"[DateCreated] < "  & DB_ENCODECONSTANT($dateTo)
);
LET $filtersWithoutNulls := ARRAY_WHERE($filters, $(i, item) $item != NULL());
LET $joinedFilter := ARRAY_JOIN($filtersWithoutNulls, " AND ");

LET $data := DB_REPORT(ARRAY("TO_MONTH([DateCreated])", "[ActionType]", "COUNT(1)"), "sys.Audit", "[DateCreated] > (CURRENT_DATE - 900)");

// calculate how many months from dateFrom to dateTo
LET $diff := DATEDIFF_PARTS($dateFrom, $dateTo);
LET $numMonths := ARRAY_INDEX($diff, 2);

LET $dates := ARRAY_SELECT(
	RANGE(0, $numMonths),
	$(i, item) (
		DATEADD("month", $item, $dateFrom)
	)
);

// put source data into a structure that we can easily search- (Using HASH_GET later)
LET $lookup := ARRAY_TO_HASH(
	$data,
	// first 2 items are our grouping columns
	$(i, item) ARRAY_TAKE($item, 2),
	// value column
	$(i, item) ARRAY_LAST($item)
);

LET $seriess := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 2)));

RETURN HASH(
   "series", ARRAY_SELECT(
		$seriess, 
		$(l, label) HASH("label", $label)
	),
   "panes", ARRAY(
	   HASH(
		   "labels", ARRAY_SELECT($dates, $(i, item) DATEFORMAT($item, "MMM yy")),
		   "layers", ARRAY_SELECT(
				$seriess,
				$(s, series) HASH(
						"type", "bar", "data",
						ARRAY_SELECT(
							$dates,
							$(i, date)
								COALESCE(HASH_GET($lookup, ARRAY($date, $series)), 0)
						)
					)
			)
		)
	)
)

Produces this output:

Pivot Tables

A Basic Example

This is a basic non-filtered pivot chart of Users against the audit log.

LET $data := DB_REPORT(ARRAY("[Username]", "[ActionType]", "COUNT(1)"), "sys.Audit", "[DateCreated] > (CURRENT_DATE - 900)");

// put source data into a structure that we can easily search- (Using HASH_GET later)
LET $lookup := ARRAY_TO_HASH(
	$data,
	// first 2 items are our grouping columns
	$(i, item) ARRAY_TAKE($item, 2),
	// value column
	$(i, item) ARRAY_LAST($item)
);

// assumes that index 1 is the y-label (down the side), and 2 is the x-label (headings)
LET $ylabels := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 1)));
LET $xlabels := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 2)));

// this structure is required for a reporting table, a different structure is needed for charts
RETURN HASH(
	"columns", ARRAY_CONCAT(
		// fixed columns (just a heading for the y axis
		ARRAY(
			HASH("heading", "", "width", 100)
		),
		ARRAY_SELECT(
			$xlabels,
			$(i, xlabel) HASH("heading", $xlabel, "width", 100)
		)
	),
	"rows", ARRAY_SELECT(
		$ylabels,
		$(i, ylabel) ARRAY_CONCAT(
			ARRAY($ylabel),
			ARRAY_SELECT(
				$xlabels,
				$(i, xlabel) (
					HASH_GET($lookup, ARRAY($ylabel, $xlabel))
				)
			)
		)
	)
)

You will see across the left hand side a list of usernames, and on the top a list of types of auditable actions.

This is some sample output:

With Filters

This is a small extension on top of the previous report. You will need 2 date fields, Date From and Date To. Only data from between these dates will display:

// filter strings, leave null to exclude from DB_REPORT
LET $filters := ARRAY(
	IF([Date From]==NULL(), NULL(), "[DateCreated] >= " & DB_ENCODECONSTANT([Date From])),
	IF([Date To]==NULL(),   NULL(), "[DateCreated] < " & DB_ENCODECONSTANT([Date To]))
);
LET $filtersWithoutNulls := ARRAY_WHERE($filters, $(i, item) $item != NULL());
LET $joinedFilter := ARRAY_JOIN($filtersWithoutNulls, " AND ");

LET $data := DB_REPORT(ARRAY("[Username]", "[ActionType]", "COUNT(1)"), "sys.Audit", $joinedFilter);

// put source data into a structure that we can easily search- (Using HASH_GET later)
LET $lookup := ARRAY_TO_HASH(
	$data,
	// first 2 items are our grouping columns
	$(i, item) ARRAY_TAKE($item, 2),
	// value column
	$(i, item) ARRAY_LAST($item)
);

// assumes that index 1 is the y-label (down the side), and 2 is the x-label (headings)
LET $ylabels := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 1)));
LET $xlabels := ARRAY_DISTINCT(ARRAY_SELECT($data, $(i, item) ARRAY_INDEX($item, 2)));

// this structure is required for a reporting table, a different structure is needed for charts
RETURN HASH(
	"columns", ARRAY_CONCAT(
		// fixed columns (just a heading for the y axis
		ARRAY(
			HASH("heading", "", "width", 100)
		),
		ARRAY_SELECT(
			$xlabels,
			$(i, xlabel) HASH("heading", $xlabel, "width", 100)
		)
	),
	"rows", ARRAY_SELECT(
		$ylabels,
		$(i, ylabel) ARRAY_CONCAT(
			ARRAY($ylabel),
			ARRAY_SELECT(
				$xlabels,
				$(i, xlabel) (
					HASH_GET($lookup, ARRAY($ylabel, $xlabel))
				)
			)
		)
	)
)

 

 

Related Articles