Tag: Report Server

Choosing which version of Power BI Desktop opens a PBIX file

When you have both the “Report Server” and “Regular” version of Power BI Desktop installed, double-clicking on a PBIX file will open it in whichever one you updated last (most likely the non-RS version, since its updated every month). Unfortunately, I want to open PBIX files in my RS version, since that’s where most of the development for my job occurs.

I got tired of fixing the shortcut links every time I updated the Desktop, so I just added two new right-click options for PBIX files – “Start in RS version” and “Start in regular version. When it’s done, it looks like this (with the new options highlighted in red):

To add them, just take the code below and save it into a file with a .REG extension, and then double-click it to add it to your registry.

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\PowerBI.File]
@="Microsoft Power BI Desktop Document"

[HKEY_CLASSES_ROOT\PowerBI.File\DefaultIcon]
@="\"C:\\Program Files\\Microsoft Power BI Desktop RS\\bin\\PBIDocument.ico\""

[HKEY_CLASSES_ROOT\PowerBI.File\shell]

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in RS Version]
@="Open in RS Version"

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in RS Version\command]
@="\"C:\\Program Files\\Microsoft Power BI Desktop RS\\bin\\PBIDesktop.exe\" \"%1\""

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in regular version]
@="Open in regular version"

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open in regular version\command]
@="\"C:\\Program Files\\Microsoft Power BI Desktop\\bin\\PBIDesktop.exe\" \"%1\""

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open]

[HKEY_CLASSES_ROOT\PowerBI.File\shell\Open\Command]
; Uncomment this one if you want the public version to be the default
;@="\"C:\\Program Files\\Microsoft Power BI Desktop\\bin\\PBIDesktop.exe\" \"%1\""

; Uncomment this one if you want the server version to be the default
@="\"C:\\Program Files\\Microsoft Power BI Desktop RS\\bin\\PBIDesktop.exe\" \"%1\""

You can also download the .REG file (you may need to right-click and save it).

(Update – added the switcher at the end so you can choose which edition opens up the PBIX file when you double-click on it!)

Let me know if you have any issues!

Unexpected results from ISNULL in SQL Compact 3.5

In SQL Server, the ISNULL function takes two arguments – the first is a potentially NULL value, and the second is another potentially NULL value. If the first value is NULL, the function returns the second value instead. It’s always been this way – for example:

  SELECT V1, V2,
         ISNULL(V1, V2) as [Result],
         Descr
  FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
        SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
        SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
        SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t

In SQL Server, you get the following results:

V1      V2      Result  Descr
10      20      10      Returns first
10      NULL    10      Returns first
NULL    20      20      Returns second
NULL    NULL    NULL    Both NULL - return NULL

However, SQL Compact implements ISNULL differently, in a way that’s not compatible at all – instead, the ISNULL function returns a 0 or 1 based on whether the first parameter is NULL! It doesn’t even process the second parameter, but instead of failing with an “Invalid Syntax” error, it happily accepts it, executes, and returns unexpected results. In SQL Compact, you get the following result set:

V1      V2      Result  Descr
10      20      0       Returns first
10      NULL    0       Returns first
NULL    20      1       Returns second
NULL    NULL    1       Both NULL - return NULL

Notice the difference? The ISNULL column is returning the value based only on the first parameter (a “0” if it’s not NULL, and a “1” if it is), and if you’re not expecting it, it silently mis-calculates any formulas containing that function. If you’re relying on the function in any statements that add values together or make decisions based on the results, you’re in for some very expected behavior (in my case, I noticed totals that were wildly incorrect).

As an alternative, you should be using the COALESCE – it accepts multiple parameters and it walks down the list until it finds one that’s not null, returning a NULL value if all parameters are NULL. This function can be used in place of ISNULL in SQL Server code with exactly the same behavior (i.e. no changes), and as a bonus, it acts the same in SQL Compact, so your formulas will work properly there as well. The example above becomes:

  SELECT V1, V2,
         COALESCE(V1, V2) as [Result],
         Descr
  FROM (SELECT 10 as V1, 20 as V2, 'Returns first' as Descr UNION
        SELECT 10 as V1, NULL as V2, 'Returns first' as Descr UNION
        SELECT NULL as V1, 20 as V2, 'Returns second' as Descr UNION
        SELECT NULL as V1, NULL as V2, 'Both NULL - return NULL' as Descr) t

And on both SQL Server and SQL Compact, the result set is the same:

V1      V2      Result  Descr
10      20      10      Returns first
10      NULL    10      Returns first
NULL    20      20      Returns second
NULL    NULL    NULL    Both NULL - return NULL

+1 for consistency!

MORAL of the story:

When executing statements on different platforms, ensure the functions you use behave in the same manner on both – and even when considering multiple platforms from the same vendor, named almost identically, don’t assume things behave unless you’ve tested them 🙂