SQL Server: Difference Between LEN and DATALENGTH

So what exactly is the difference between the LEN and DATALENGTH functions in SQL Server? They sound similar but they're actually used for two different purposes.

• The LEN function tells you how many characters are in a string but will exclude any trailing blanks.
• The DATALENGTH function tells you how many bytes are used to represent an expression.

This means that two string expressions, one with some trailing blanks and one without, could have the same value of LEN but different values for DATALENGTH.

Read More

SSRS: Conditional Formatting for Negative Numbers

If you have an SSRS report and want to apply some conditional formatting to negative values, follow these steps:

1. Select the value in the Tablix to which you would like to apply conditional formatting.
2. In the Properties pane, go to the 'Font' group and go to 'Color'. Click the drop-down and choose the last option, 'Expression...'.
3. The Expression window will open. In the text box at the top enter the following: =Iif(Fields!NameOfYourField.Value<0,"Red","Black")
4. Click 'OK'.

Of course you can modify this to use any other logic and colours. This simple example will display negative values in red and everything else in black.

Read More

Android 10 on Pixel

I don't usually get excited about OS updates on my phone, mostly because I'm using an older piece of hardware at this point (my Pixel is about 3 years old). This latest Android 10 release is pretty awesome though so I've decided to write about my favourite new features. This is coming from a few days of use and running into some pretty awesome stuff.

Without further ado, here are the 3 things about Android 10 I'm loving the most on my Pixel:

• I received a couple of notifications telling me that certain apps had requested my location in the background. Each time one of these popped up I was given some options for removing or limiting this kind of access to my location data. I loved this because I didn't even realize some of these apps would be grabbing this in the background. I like preserving my battery so I was quick to change the options to only allow them to grab my location when I'm actually using the apps.
• There's an overall dark theme! I'm always switching whatever apps I can to be dark but now you can darken the entire OS.
• Previously connected devices are listed more readily when you go to your Bluetooth settings. I'm always connecting to different speakers and vehicles and love having this list more accessible because it used to be buried a bit.

I'm noticing some weird performance issues with my lock screen and while adding widgets to my home screen but otherwise it seems to be running just fine.

Read More

Excel: Connect to SSAS Cube

You can connect to an SSAS cube from within Microsoft Excel, which is pretty fantastic because it lets you analyze your data in a PivotTable.

Here's how to get connected to an SSAS cube in Excel:

1. Click on the 'Data' tab on the ribbon.
2. In the 'Get External Data' group, click on 'From Other Sources' and choose 'From Analysis Services'.
3. Enter the server name (e.g., 'Server\Tabular').
4. Choose either Windows Authentication or enter a different user name and password to connect. You'll need to make sure that this account has the appropriate permissions to read the cube.
5. Click the 'Next' button.
6. Choose the cube to which you would like to connect.
7. Enter a file name, description, friendly name, and/or keywords as desired. This is just to save the connection in a way that makes it easy for you to identify it later if you reuse it. You can leave the defaults here if you're not picky.
8. Click the 'Finish' button.
9. Choose if you'd like to create a PivotTable Report, PivotChart, or just simply create the connection.
10. Click the 'OK' button.

If you chose either the PivotTable Report or PivotChart option you will immediately be able to interact with the cube right in your worksheet. All of your cube's fields and measures will be visible in the pane on the right-hand side of the screen.

Personally I think this is one of the coolest things about SSAS, being able to expose a cube inside a tool like Excel that's so familiar to so many data analysts. A friendly cube design makes all the data accessible in a way that eliminates the need to know all the individual data sources and joins required.

Read More

SSMS: Select Multiple Tables/Views/Objects

In Microsoft SQL Server Management Studio (SSMS) I've often been annoyed by the apparent inability to use my Shift or Ctrl keys to select multiple objects at once in the Object Explorer window.

I'm happy to say that there is in fact a way you can multi-select tables, views, whatever objects you like!

Go to the 'View' menu and select 'Object Explorer Details'. This glorious new pane will open and allow you to select as many objects as your heart desires. You can also just hit the F7 key as a quick shortcut. How awesome is that?

It's beyond me why this is only possible in this details pane, so if you happen to have some insight then I'd love to hear about it in a comment down below.

Read More

SSRS: Hide Toolbar and Navigation Using URL Parameters

If you have an SSRS report deployed to a Report Server you can use URL parameters to link to it in such a way that all of the toolbars and navigation items (like breadcrumbs) at the top of the screen are hidden.

First you need to get the URL pointing to your report from the report server. It will look something like this:

https://server/ReportServer/Pages/ReportViewer.aspx?%2fFolderName%2fReportName

Now just add on the following to the end of that URL:

&rs:Command=Render

So it will look something like this:

https://server/ReportServer/Pages/ReportViewer.aspx?%2fFolderName%2fReportName&rs:Command=Render

It's that easy!

Read More

SSAS: Error Deploying Metadata

This error threw me for a loop the first time I saw it and I figured I should post about it in case someone else has the same problem.

In Visual Studio I was making some changes to an SSAS tabular model I'd previously deployed. When I went to deploy it again with my updates I was presented with an error message for the work item "Deploy metadata", status "Error deploying metadata". Clicking on Error details gives a very unhelpful message: "Failed to save modifications to the server. Error returned: 'The column 'TableName[FieldName]' cannot be of type Empty. The Empty data type can only be set on a column if the column is either a calculated column or is part of a calculated table.'.".

As with many error messages, this is totally unhelpful and doesn't actually help in figuring out the problem. No field in my model was set to a data type of 'Empty' - and certainly not the one referenced in the error message!

What works for me when this happens is to go to the SSAS server directly in SQL Server Management Studio and to delete the model. Then when I try deploying from Visual Studio again it succeeds.

Why does this happen? I have no idea. If you have some insight I would love to hear about it in the comments. I have come to expect this behaviour any time I have deleted an object from my model and then created a new one with the same name. Redeployments seem to always fail after I've done that.

Read More

Google Chrome: Change Spell Checker Language

I get annoyed with the default American English spell checking that happens in my browser. It only recently occurred to me that this is probably something that can be changed in my settings, and then I thought I should really post about this in case someone else is looking for the same thing.

In Google Chrome you can change the language of your spell checker by following these steps:

1. Click on the ellipsis in the upper right-hand corner and click on 'Settings'.
2. In the 'Settings' tab which will have opened, scroll down to the bottom and click 'Advanced'.
3. Scroll down to the 'Languages' section and click the drop-down arrow next to 'Languages' to expand the menu.
4. Click 'Add languages'.
5. Select the language(s) you wish to add with checkmarks and then click 'Add'.
6. Click the ellipsis next to any of the languages listed and you can choose options like moving them to the top of the list, setting them as the display language, or removing them altogether.
7. Close the 'Settings' tab.

And suddenly I can write things like neighbour and colour without any red squiggly lines! Amazing.

Read More

Microsoft Surface Pen LED Not Working

I have one of the newer models of the Surface Pen, with no clip and just one long button on the side.

So the other day I went to mark up a document on my Surface Pro and I just couldn't get my Surface Pen to work. I tried to troubleshoot the Bluetooth pairing, replacing the AAAA battery, and watching a bunch of YouTube videos and just nothing was working. Apparently if you hold down on the top button for a few seconds you should at least see the little LED blinking. Not on mine! Completely dead.

Anyway, credit where credit is due: I came to write this post because I was pretty blown away by the Microsoft Store and the way they handled my problem when I reached out. I logged into the Microsoft Store and then initiated a chat session which was really quite painless. I explained my problem and the steps I'd taken to resolve it, and I fully expected the agent to run down a scripted list of troubleshooting steps that I'd already done. Instead she simply told me that they would gladly exchange it for me since I'd already determined it to be malfunctioning. Mine is also still under warranty.

It's weird to say that I'm shocked when a customer service agent actually listens to me but I really was. Within just a few minutes we'd sorted out the exchange details with my shipping address and everything and I was all done.

So if that LED stops working on your Surface Pen, just reach out to Microsoft and they'll deal with it quickly!

Read More

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