Thursday, April 17, 2014
Text Size

SSRS Display question mark when date is blank

Applies to:
  • Microsoft SQL Server Reporting Services 2012
  • Microsoft Visual Studio 2012 Premium
  • Microsoft Windows 7 Enterprise

So I have a column in an SSRS report which displays a date. Being rather pernickety, I would like a question mark to display if there is no date to populate the field.

At the moment, the expression in there is something like this:

  1.  =Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss") 
  2.  -- yields 11/02/2014 11:21:32 
Sounds good, where's the problem?

SSRS No Delay for First Report of the Day

This article describes a solution our reporting server administrators found to cut the loading time of the first report of the day.

Take any SSRS report, if one of us was the first to run it on that day, it would take an additional 60 seconds to get with it and display the report. Any subsequent running of the report loaded it almost immediately.


SSRS Stop Scrolling Behind

Applies to:
  • Microsoft SQL Server Reporting Services (SSRS) 2008 R2
  • Microsoft Visual Studio 2012
  • Microsoft Business Intelligence Development Studio

I recently revamped our standard report with a new template comprising of a single Tablix which contained the images of the corners and sides of the report. It looked beautiful if simply displayed on a single page (the top half of the page) as long as the end-user doesn't scroll. If the page was scrolled, what should display went behind my report and instead the whole report acted as a header on top of the scrolling content and would only ever display, the top rows of the dataset.

A lot of articles out there on the net are offering solutions that do the exact opposite. Basically, I want the opposite result of "Fixed headers while scrolling".


SSRS Change Background Color of Image

Sounds easy but actually if you use an image which has transparent areas, ie. has one color which will be transparent, the transparent pixels will be colored in with the page background color.

I want an image to display per row as a status marker for 3 different types of results: Success, Failure, Unknown. My images are circles with the background being transparent. I want the first column to display an image based on the status result.

If I simply insert an image, the report would use the background color of the report.

How to Hide or Unhide a Drive per User in Windows 7

Applies to:
  • Microsoft Windows 7 Professional
Looking at ways of displaying different drives per user on a single computer. This one is by modifying the system registry, so if you aren't familiar with the system registry in MS Windows, you may need to find someone who is.

As a proof of concept, let's see how to hide a specific drive. Note that this section applies to the currently logged-in user. To restrict other users, see the example after this one:
  1. Open the Start Menu, then type regedit in the search box and press Enter.
  2. In regedit, expand to the following key HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer
  3. In the right pane of Explorer, right click on a empty area, click on New > DWORD (32-bit) Value > type NoDrives > Enter.
  4. Right click on the DWORD you just created and click on Modify.
  5. Type in the drive option hex or decimal number from the list below you want to set as restricted, and click on OK.

    1.  -- Restrict multiple drives by adding the values together 
    2.  -- For Example: To Restrict B & C, enter 6 decimal 
    3.  --              "      "    E & G, enter 80 decimal or 50 hexadecimal 
    5.  Drive Letter    Decimal         Hex 
    6.  A               1               1 
    7.  B               2               2 
    8.  C               4               4 
    9.  D               8               8 
    10.  E               16              10 
    11.  F               32              20 
    12.  G               64              40 
    13.  H               128             80 
    14.  I               256             100 
    15.  J               512             200 
    16.  K               1024            400 
    17.  L               2048            800 
    18.  M               4096            1000 
    19.  N               8192            2000 
    20.  O               16384           4000 
    21.  P               32768           8000 
    22.  Q               65536           10000 
    23.  R               131072          20000 
    24.  S               262144          40000 
    25.  T               524288          80000 
    26.  U               1048576         100000 
    27.  V               2097152         200000 
    28.  W               4194304         400000 
    29.  X               8388608         800000 
    30.  Y               16777216        1000000 
    31.  Z               33554432        2000000 
    32.  All Drives      67108863        3ffffff 
  6. Log off and log on, or restart the computer to apply changes.
Important! The above only applies to the user you are running Regedit as! (So if logged in as Admin, this hides it from the Admin, but not from the Guest account)

To only restrict Guest or a Specific User:
So undo the above if you didn't want this applied to the current logged-in user (delete the NoDrives DWORD). The concept of creating the NoDrives registry entry is what you need to remember. In the example below, we are applying the same principle to a specific user account (the Guest account):
  • Open the registry editor with administrative privileges
  • Select HKEY_USERS
  • Go to the menu File and select Load Hive
  • Navigate to that user's profile folder, usually C:\users\username in this case Users > Guest
  • Enter NTUSER.DAT in the File name box. (This file is a system-hidden file, so it won't show up in the file selection window. You have to type it in. Be sure not to select ntuser.dat.log by accident.)
  • Click ok, then enter a name for the key. We'll call it Foo.
  • Go to HKEY_USERS\Foo\Software\Microsoft\Windows\CurrentVersion\Policies
  • If it does not already exist, create a key called Explorer
  • Create a new 32-bit DWORD value inside the Explorer key/folder and name it NoDrives (or NoViewOnDrive).
  • The value you enter for NoDrives depends on the drive(s) you want to restrict (Refer to the list above)
  • CRITICAL STEP: Once you've saved this value, navigate back up to HKEY_USERS, select the Foo key you loaded, and then click File > Unload Hive > Yes (prompt).
  • Close the registry editor, then restart the computer.

  • Guest account will not see the drive in Windows Explorer but can still access it using the command prompt.
  • All users can access the drive using the command prompt.
  • To hide from specific users, you need to repeat the above for each user.
  • If your disk drive uses the NTFS file system: Modify the Security settings for the drive to restrict access via the Command Prompt.
  • If your disk drive uses the FAT32 file system, then you will have no security tab/setting.


Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
  • JComments 2.3.0 with ReCaptcha in Joomla 2.5.x

    • Fri 28-Mar-14
      You are a rockstar mate! thanks. Followed the steps listed and it worked! If only all tutorials were ...
  • K2 Items disappear

    • Thu 03-Apr-14
      The fix works great, but the problem is occurring a couple of times a day. Any idea how to fix ...
      Larry C.