SSRS - Joel Lipman .Com

SSRS Use T-SQL Like with a Parameter

: Joel Lipman; SQL Server Reporting Services; 04 April 2011; Hits: 11748

This must have been so obvious to everyone else that nobody bothered to write an article on it... till now.

Situation
I have a report that returns room bookings based on a user and given a date range. The problem is that there are a few thousand users and Microsoft's SQL Server Reporting Services interface isn't the most fun to scroll endlessly down. Advanced users can type the name really fast for it to auto-scroll down to the desired name. Our advanced users are exceptions to the rule.

Problem

: Joel Lipman; SQL Server Reporting Services; 01 April 2011; Hits: 8194

Open a Windows Explorer and make a duplicate of the report that you want to use as a template.
Rename the copy (suffix with template?)
Remove objects unique to the report and leave elements for all reports.
File > Save selected item as...
For SSRS 2008: Save in the folder: "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject"
Done!

SSRS Hide results table if empty

: Joel Lipman; SQL Server Reporting Services; 29 March 2011; Hits: 38682

Update August 2011
So I don't know how many people were just saying why don't you do the following:

Bring up the "Tablix Properties" of the dataset
Look for the section "No Rows"
Put in a value for "NoRowsMessage".

This solution hides the entire Tablix which kinda defeats the purpose of having anything there. I want my end-users to have faith in my report. The report just returning blank can also mean "I don't know". I want the report to either return the correct value or say "No data found". My end-users would otherwise ask is the data value reliable if it's blank?

The long way of doing this
I would have called this article "Iteration within SQL Server Reporting Services Business Intelligence Development Studio (BIDS) version 2008 through combining Transact-SQL and MDX expressions" but boy what a mouthful, and it's not really iterating anymore. So it's "SSRS Hide results table if empty" though I will add that if you wanted to put a message instead of hiding the table then following the below will also let you do this (requirements: common sense or the IQ of a duck).

It's what I've been searching for for the past hour and although you may think I'm just adding to the cyberspace pile of useless info, at least I'm not just copying and pasting from other sites to add content to my own. And I'm not just adding content, the way I'm doing the below is nothing similar to what I googled (probably a bad sign but time is ticking and no one has forever).

: Joel Lipman; SQL Server Reporting Services; 25 March 2011; Hits: 24818

Re: Business Intelligence Development Studio 2008

Often happens to me and not sure why. You open the project and the window panel for the Report Data containing the parameters and datasets isn't there. I spent a bit of time enabling/disabling toolbars before I found this.

Open a Report solution (or have one open already?)
The menu link is "report data" and it's right at the bottom of the "View" menu.

Report Data at the bottom of the View menu when a report is loaded in the editor

Border settings not saving in SSRS

: Joel Lipman; SQL Server Reporting Services; 24 March 2011; Hits: 23911

This may sound silly to some but I've written a note because I spent time googling and still didn't find an answer.

My situation is that I wanted to put an expression to format the border (empty cells have a border and non-empty don't). This was so that a grid would show for empty spaces.

I wasn't getting very far because I'd even ask the border to be grey and save the project but when I come back to the setting, it's still black.

: Joel Lipman; SQL Server Reporting Services; 23 March 2011; Hits: 11429

Well I tried and I spent a few hours searching the net for a solution which displays the hours in the top columns, and undefined number of rooms in the rows. No all I was looking for was a calendar showing 1 day with the hours on the top. Working hours only as well...

Ok I admit I only googled for about 1 hour before I gave up and just wrote my own. Saying that it's taken me 4 hours just to do the below... hopefully it will be quicker next time now that I noted it all down.

: Joel Lipman; SQL Server Reporting Services; 23 February 2011; Hits: 10418

I thought I'd put something here as there are lots of posts like this on forums (a lot of which seem to be copied & pasted from other sites) but they refer to SQL Server 2000 or SQL Server 2005.

This is how to do it in SQL Server 2008 and creating a report in Business Intelligence Development Studio (BIDS) or MS Report Builder.

Scenario
I have a report that specifies some room bookings for a particular week. When I open the report, each drilldown row is collapsed (as opposed to expanded). The user can specify the date as it's a report parameter.

What we want
The user will select a date. We want the report to open with the specified day expanded and the remaining days that week to be collapsed (hidden). The first time I did this, I couldn't expand the remaining days so don't forget to tick the option "display can be toggled by this report item" like I did. ahem...

: Joel Lipman; SQL Server Reporting Services; 11 February 2011; Hits: 18703

Thought I'd put a note on how to remove the toolbar using the URL as I didn't find this clear on the net. Tried it and it didn't work. That's because I was using the Reporting Manager URL rather than the Reporting Server URL.

: Joel Lipman; SQL Server Reporting Services; 04 February 2011; Hits: 12614

This is just a note for me as it took a while to find on the net and even then it was confusing as to why it works but it does. Not sure whether you call this an MDX Query or part of a Transact-SQL mashup. You need to go to "Fill" (of each text box in the row - unless there's a faster way) and instead of color, click on the expression button (fx) and use the following:

copy raw

=IIF(RowNumber(Nothing) Mod 2, "#ffffff", "#eeeeee")

or 

=IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "Garamond")

=IIF(RowNumber(Nothing) Mod 2, "#ffffff", "#eeeeee")
or
=IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "Garamond")

This is telling the report to be white (=#ffffff) in row 1 (odd row numbers) and a very light grey (=#eeeeee) in row 2 (even row numbers). The first row being of all rows in the dataset and not for a particular drill-down item. It will alternate between the two for the rest of the report. [The second example is what I use most frequently].

Category: SQL Server Reporting Services :: Article: 320

: Joel Lipman; SQL Server Reporting Services; 07 January 2011; Hits: 7712

Once again I've spent a whole morning going in the wrong direction with a convincing SQL script. This article is just a note on when using Business Intelligence Development Studio (BIDS) or Report Builder 2.0 and when previewing the report, the parameters are not accounted for (ie. the SQL query creating the dataset does not use the parameters in its query).

Problems:

Given a date, the date format was reverting to US format as opposed to European
Given a Campus as a text value, the SQL query was ignoring this completely
Hardcoding the scalar local variables worked

: Joel Lipman; SQL Server Reporting Services; 17 May 2010; Hits: 23594

Basically I thought that the regional settings of a report generated using Report Builder 2.0 on a MS SQL Server 2008 instance were dependent on either the server or the client machine. Realised that this was actually specified in the report. Here's a quick note on how to set Dates and Times used in parameter fields to UK format (dd/MM/yyyy).

: Joel Lipman; SQL Server Reporting Services; 06 May 2010; Hits: 9611

Dunno about you guys but I've searched the WWW for a solution and couldn't find it. The work around I've documented is a "solution" to my situation and environment.

Our setup is:

SQL Server 2008
Report Builder 2.0
BMC Service Desk Express 9.8

DataSet1:

Incident #
Group Name
Close Date & Time
Assigned to Full Name
Incident Type

The objective of this article is to explain how to hide "Series1" from appearing in your SQL Server Reporting Service (SSRS) report. I am guessing that Series1 is the data series from an outer join where the joining index value is null.