Hide and Group Columns in SSRS Using a Parameter

Ever had users come to you and request another version of a report just to add another field and group data differently? Today, was such the day for me. I really don’t like have multiple versions of the same report out there. So, I got a little fancy with the current version of the report and added a parameter then used expressions to group the data differently and hide columns. For those new to SSRS I’ve embedded some links to MSDN to help you along the way.

Current Report

The report gives summarized counts by invoice date.  It currently has a ROW group using date_invoiced and the detail row is hidden from user.

current-report

row-group-2

group-exp3

New Version

To complete the user request to have Item Codes and Descriptions added to the report I need to find a way to group the data by Item and show Item columns without disturbing the current report that is currently used by many consumers.

To Do:

  • Add Parameter
  • Set Available Values
  • Set Default Values
  • Add New Columns
  • Change Visibility
  • Change Grouping to group data using parameter

Step 1: Add Parameter

add-para-4

 Step 2: Set Available Values

add-values-5

Step 3: Set Default Values – I want to make sure my current users get their version of the report simply, so I set it to No (N).

add-default-6

Step 4: Next Add Columns.  I was lucky that the fields (Item Code, Item Desc) the user requested to be add was already part of the dataset used, so no additional coding was needed on the stored procedure.

add-fields-7

Step 5: Next change the Visibility attributes. You want to HIDE the column when the IncludeItemDetails parameter is NOT YES (Y). I did this for both item columns.

visibility-8

visibility-9

Step 6: Next I needed to change the grouping. The report is currently group by date_invoiced only. To make the data now total by Item I need to group it by Item only when the IncludeItemDetails parameter is Yes (Y). I did this using an IIF expression setting it to IF IncludeItemDetails=Y then group using field value else don’t (0). Again I did this for both fields.

grouping-10

expression-11

espression-12

You will see it’s relatively simple to do, and prevents a whole new report version from being created. For you beginners out there, it’s a very easy way to start to minimize the number of reports you have to maintain. Try it.

 

 

SSRS Report Won’t Render in VS Preview

I love getting get a laptop, but getting all the software reinstalled and making sure everything works can be trying. Last week, I was lucky enough to get a new one and spent two days getting it setup just right. At least so I thought…. once I started working on it of course, BAM I hit my first road block. Visual Studio using SQL Server Data Tool will not render any reports in the Preview tab.

Let the trouble shooting commence!

  1. Error Message? No help… gives me nothing useful

Capture

  1. Can I deploy report to SharePoint and View? (We use SSRS Integrated Mode)- Success!! This leads me to believe the issue probably lies on my local machine. 
  1. Test Datasets? Can I return data from my query or store procedure connection in Query Designer – Yes. Did I test all my datasetsYes

Capture2

  1. Should I try to uninstall and reinstall? So I did just that. After 2 hours finally was able to test and guess what NO GO! 
  1. Did I install all service packs to VS? Missed one – so installed and tested, still no luck     

vas

—TIME TO TURN TO MY VIRTUAL CO-WORKERS ON TWITTER #SQLFAMILY—-

  1. Try Running Visual Studio as Admin (suggested by fellow Twitter tweep, Martin Schoombee @sqlmartin) – tried… yep no difference
    Capture5Capture4
  1. Finally was given a suggestion to delete my shared data sources and re add them. (suggested by fellow Twitter tweep, John Morehouse @SQLRUS)

I deleted the shared data source for my report I was testing and re-added it. Hit the PREVIEW and BINGO IT WORKS!

So now to see WHY???

Looking over all my data sources I noticed that any of them that use “SQL Authentication” had the user blanked out. Any reports that had used Windows credentials worked, of course, first 5 in my project were all SQL Authentication, just my luck. So instead of actually deleting and re-adding all 30 shared data sources in my project, I was able to go through and just re-input the SQL user names and passwords.

Double Click on Share Data Source

Go to Credentials

If SQL Authentication re-input user name and password

Click OK

Capture3

Questions still remained as to why my data sets tested ok and returned data. My guess is that it was using my network credential to connect to the data source upon execution. That’s my only explanation and reason why it didn’t dawn on me to check the shared data source connections. The second question is to why the user names were wiped out. My assumption is that they are locally stored and were not carried over to new laptop.

Since this was an interesting mystery I figured I would do a simple blog, so anyone else that may have this issue can have a reference. Hope it helps.