Aggregate on a Lookup in SSRS

The What

If you need to join multiple datasets inside SSRS, perhaps because of different sources, grains of detail etc, then you often need to aggregate over both datasets.

In SSRS, you can easily perform aggregations over another dataset but it can be tough to do this based on a grouping factor in your main dataset.

A key example of this might be Sales and Purchases – you want to show both of these by month but they come from two different data sources.

You could build two tables that appear to be just one table but this can be really clunky. Instead, you want just one table with the month, the total sales, and the total purchases in.

Although there’s no tidy way of doing this built in, you have the power to add your own functions to SSRS using the Code window of the report’s properties. Provided here is a block of VB script that can be added to your SSRS report to allow you to do those tricky aggregations as if they were just another built in function.

I call it AggLookup.

Continue reading Aggregate on a Lookup in SSRS

My Day/Night Cycle Demo

My Day/Night Cycle

Here’s my Day/Night cycle function demo, built in Construct 2. (Time moving at 4 minutes per second)

It took me a day and a half of hair pulling, but I’ve only been using Construct 2 for a week so I suppose it’s not too shabby. It uses only 6 events, 2 global variables and 9 objects.

Objectless Check Boxes using VBA

For my first ever blog post (be gentle with me!) I wanted to talk about an issue I have with Excel’s check box object, and my way of resolving it. It’s not perfect, and I’d love to hear of any other versions or ideas you may have. So here’s how I create check boxes in Excel without using Excel check boxes.


The Problem with Check Box Objects

They look good and they work well, there’s no denying they do what they’re supposed to, but they also annoy the heck out of me!

  • As far as I’ve been able to find they can’t be properly bound to a cell; this means if you want to get rid of them you need to select them and delete them, which can be a big job
  • If you want to refer to their state in simple terms you need to add a linked cell and refer to that, which to me is just plain messy
  • They’re awkward to format and style and if you want a big tick you need a big box and as such you need a big cell… again, messy


Continue reading Objectless Check Boxes using VBA