SSRS: Listing Multivalue Parameter Selections on Report

If your SSRS report has a multivalue parameter and you would like to display the values that were selected on the report itself, use the JOIN functions to list them delimited by commas (or whatever other character you'd like). Just insert a text box on your report and insert the following expression:

=JOIN(Parameters!YourParameterName.Value, ", ")

So if for example your parameter allows you to choose the name of a country, the output using the above expression after selecting a few countries would be something like this:

Canada, Brazil, Dominican Republic, Cuba, USA, Italy
Read More

SQL Server Agent: Automatic Refresh of SSAS Cube

Unless you're dealing with completely static data, you're going to want to set up an automatic refresh of your SSAS cube on some sort of schedule. Here's how you can get a full refresh going on a regular basis:
  1. Make sure that permissions to your SSAS server are set up properly to allow the agent to trigger the refresh. Your service account should be added as a server administrator.
  2. Create a new SQL Server Agent job.
  3. Add one or more schedules on the Schedules page.
  4. Add a step of type 'SQL Server Analysis Services Command'.
  5. Choose your service account under 'Run as'.
  6. Enter the URL for your SSAS server under 'Server'.
  7. Enter the following as the command, making sure to edit the "database" value to match the name of your SSAS cube:
{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "NameOfYourSSASCube"
      }
    ]
  }
}
Read More

Power BI: Force Correct Locations on Map Visualization

If your locations aren't correct on your Power BI map visualization, you can make an adjustment to your dataset to force them into the right places. This is possible without having specific latitude and longitude coordinates. For example, if I try to map the city of Sydney then Bing Maps tends to place that in Australia. Unfortunately what I'm actually looking for is the city of Sydney in Canada.

The easiest way I've found to quickly correct this issue is to add a new field to your dataset that joins together as much of the location information as possible. For my purposes I would have City, Province/State, Country. So instead of using 'Sydney' as a location I am now using 'Sydney, Nova Scotia, Canada'.
Read More

My Site Has Been Taking a Long Nap

I know: I've been neglecting my site. The fact is that I've been really busy with so many projects that I just haven't thought about spending the time to write something here. For a while I thought about disabling this site altogether but then I keep seeing comments and getting e-mails here and there from people that are still finding some of the content useful today. So perhaps I'll start adding to it again, but in the meantime I'll just say that this site is just taking a rest but isn't totally abandoned. Have a nice day!
Read More

Stop Using Windows XP!

With the exception of non-networked computers running Windows XP for the sake of continued use of legacy applications... stop using Windows XP!

After supporting the Windows XP operating system for just over a decade, Microsoft eventually decided to stop rolling out updates and providing technical support. Though you might have found those updates annoying and never used their support anyway, you should really heed the advice of experts and install a more current operating system. Otherwise you're potentially leaving your computer open to new threats from which you won't be automatically protected.

What are your options?
  1. If you want to continue using Windows, upgrade to a newer version.
  2. Consider trying out macOS if Apple is more your speed.
  3. Check out an open source OS like Linux. They're budget-friendly and can provide you with a lot of control over your system. There are a lot of different ones to choose from but I personally enjoy using Fedora.
It's a decent new year's resolution. Keep your computer up-to-date.
Read More

Excel: Filter XML Using XPATH

Excel plays really nicely with XML (as it ought to, because XML is super cool). You might be pleased to learn that you can use XPATH expressions with a handy function called FILTERXML. It works fine for XML dumped straight into a cell, or you can apply it to a WEBSERVICE call. The function has only two arguments: the first is your XML and the second is your XPATH expression.

Try this very contrived example I’ve concocted to see it in action. Here’s a bit of XML you can paste into a fresh worksheet in cell A1:
Hello<item>apple
banana</item>peach</example>

Now enter this formula in any other cell, where I’m using XPATH to pull out the second item:
=FILTERXML(A1,"//item[2]")

That’ll give you banana!
Read More