Copy/Pasting elements in SSRS…argh!

Report Builder was unable to paste successfully.

Hmmm….that one buggers me.
Turns out that it doesn’t like have custom code in elements which you would like to copy/paste.
It’s a known issue for years and has been reported, but they decided not to fix it…
https://connect.microsoft.com/SQLServer/feedback/details/767968/visual-studio-2008-unable-to-copy-and-paste-textboxes-in-ssrs-reports-with-custom-code-after-2008-r2-sp2-upgrade

So what’s the work-around you might ask? Well…you’ll need to comment out all the ‘Code.’-sections in the XML code of the report.

Workaround:

Step 1) Open up the XML view for the report, either in Visual Studio by right clicking the report and selecting View Code, or by editing in any text editor.
Comment out Code. blocks. Ctrl + F your way through the document looking for Code.. The goal is to preserve the code in some way, while temporarily commenting out the line. Depending on your actual code, this might be different, but I here’s what I like to do:

Step 2)
Find: =Code.
Replace With: =’Code.

Step 3) Go back to the Designer View and Copy and Paste the Tablix. You can do this by right clicking the top left corner of the tablix control: Copy Tablix

Step 4) Go back to the XML View and reverse the find and replace
Find: =’Code.
Replace With: =Code.

Visual Basic code
Another things it that is has a habit of putting Visual Basic code in front of your SSRS expression, like for example:

Microsoft.VisualBasic.Information.
Microsoft.VisualBasic.Interaction.
Microsoft.VisualBasic.Strings.
Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions.


When you copy/paste anything, afterwards view the XML code and just replace remove these strings with Find&Replace.

ReportItems!Textbox.Value
In case your using ReportItems!Textbox.Value, make sure to check a few that Visual Studio ‘changed’ the number accordingly. Don’t know why, but most of the time it does understand the logic and also adjusts the textbox-numbers accordingly. But sometimes it doesn’t 🙂

SSRS: Easily calculate the difference between two fields/textboxes with an expression

This might seem to be a simple approach, but for some it takes a while to figure out that such tricks are even possible in SSRS.
For example to calculate the difference/variance between Actual and Budget values in a report:

=ReportItems!Textbox50.Value - ReportItems!Textbox171.Value


Simply lookup the textbox’ name in the Properties-window:

This approach also works for ReportItems outside of the table/matrix (across the report even).
In some cases you’ll need to combine two KPI values from different matrixes/tables in the report and for example run another formula to come up with a definite rating/value.

Instead of ‘Fields!‘ use ‘ReportItems!‘ to refer to actual items on the report canvas (this can be on the header, footer or body of the report).
In my experience you may need to use a temporary hidden textbox to store the value temporary in order to use it in the header or footer. So in case you run into some sort of constraint, try this one out first.

Different between Drillthrough, Drilldown, Subreports, and Nested Data Regions

Drillthrough, Drilldown, Subreports, and Nested Data Regions

You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drilldown action. You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix. You can display the data in a subreport that is completely contained within a main report. Or, you can put the detail data in drillthrough reports, separate reports that are displayed when a user clicks a link.

A. Drillthrough report
B. Subreport
C. Nested data regions
D. Drilldown action

All of these have commonalities, but they serve different purposes and have different features. Two of them, drillthrough reports and subreports, are actually separate reports. Nesting is a means of putting one data region inside another data region. Drilldown is an action you can apply to any report item to hide and show other report items. They all are ways that you can organize and display data to help your users understand your report better.

Source: http://technet.microsoft.com/en-gb/library/dd207141.aspx

Finding colors for SSRS: Color Brewer 2.0!

Using SSRS? Well this might come in handy!
Color Brewer 2.0 is an easy tool for selecting color schemes.

– Total number of distinct colors? -> 5
– Colorblind safe? -> Check
– Print friendly? -> Check
– Hex-code? -> Check

SSRS: Formatting Date and Time

Something for your cookbook! (source)

The Format function is locale aware and can be used as follows [not the case senstivity]
Set Language=User!language in the Report properties and use the Format function with the following codes:
Standard Format Specifiers for Dates and Times:
The table below shows the standard date and time formatters.

Format Description
d – Short Date
D – Long date
f – long date & short time
F – long date & long time
g – short date & short time
G – short date & long time
M or m – month & day
Y or y – year & month
t – short time
T – long time
s – displays in ISO 8601 format using local time
u – displays in ISO 8601 format using universal time
U – date and time in unversal time
R or r – displays in RFC 1123 format

Custom formatting sequences:
There are also specific character sequences that can be used to achieve custom formatting of dates and times.

Format Description
d – day of month (1 or 2 digits as required)
dd – day of month (always 2 digits, with a leading 0 if needed)
ddd – day of week (3 letter abbreviation)
dddd – day of week (full name)
M – month number (1 or 2 digits as required)
MM – month number (always 2 digits, with a leading 0 if needed)
MMM – month name (3 letter abbreviation)
MMMM – month name (full name)
y – year ( last 1 or 2 digits, no leading 0)
yy – year (last 2 digits)
yyyy – year (4 digits)
H – hour in 24-hour format (1 or 2 digits as required)
HH – hour in 24-hour format (always 2 digits, with a leading 0 if needed)
h – hour in 12-hour format (1 or 2 digits as required)
hh – hour in 12 hour format
m – minutes (1 or 2 digits as required)
mm – minutes (always 2 digits, with a leading 0 if needed)
s – seconds (1 or 2 digits as required)
ss – seconds
t – first character in the am/pm designator
tt – am/pm designator
z – time zone offset, hour only (1 or 2 digits as required)
zz – time zone offset, hour only (always 2 digits, with a leading 0 if needed)
zzz – time zone offset, hour and minute
/ – default date separator
: – default time separator
\ – escape characters

Common SSRS URL Commands

Another page for your cookbook 🙂 (source)

&rc:Parameters=Collapsed
• This collapses the parameter bar but sends the parameters in the URL in the browser

&rc:Parameters=true
• This shows the parameter bar and is the default

&rc:Parameters=false
• This prevents the parameters from being passed in the browser and expanded

&rc:Toolbar=false
• This hides the toolbar

&rc:Format=PDF
• This will open up the report as a PDF, other examples are: HTML3.2, HTML4.0, HTMLOWC, MHTML, IMAGE, EXCEL, CSV, PDF, XML

&rs:Format=EXCEL&rc:OmitFormulas=true
• This will open the report in Excel

&rc:Zoom=Page Width
• This will open up the report in full width of the page

rs:ClearSession=true
• This clears the session state for the user. Used where the caching of the report prevents the designer from seeing their updated report and/or data. * This is a very important parameter to know as more often than not, the report data doesn’t update immediately unless parameters are changed, and your report may show cached data if this is not set.

&rc:StyleSheet=MyCustomStyle
• Note don’t add the .css extension. And this style sheet must be in the folder Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerStyles folder.
• Use the HTMLViewer.css as your base template.

More info on MSDN.

SSRS InScope() function: The key to format your matrix!

The InScope() function checks to see if the current item is in the specified scope.
For a basic matrix report with the use of ‘Totals’ on both axes:

The Green cells fall out-of-scope of the ColumnGroup, and In-Scope for the RowGroup
The Grey cells fall out-of-scope of the RowGroup, and In-Scope for the ColumnGroup
The Light blue cells fall In-Scope of both the ColumnGroup and the RowGroup.
The Dark blue cell falls out-of-scope of both the ColumnGroup and the RowGroup.

Expression example code:

=IIf(InScope(“ColumnGroup”),
IIf(InScope(“RowGroup”),”LightBlue”, ”Grey”),
IIf(InScope(“RowGroup”),”DarkGreen”, “DarkBlue”)
)

Thanks to Luke Hayler for his great example.

Multi value parameter within SSRS dataset (Command type: text)

Today I wanted to use my Multi-value parameter function within a SSRS dataset (Command type: text).
Guess what…it didn’t work!

After literally an hour, I found the answer….it’s not possible at all.
Instead SSRS basically ‘chops’ the multi value string into something like: ‘aa’,’ab’,’…’
My function requires a single string instead of many small ones, for example: ‘aa,ab,…’

Now you’re thinking, what about the Join-function? As in:
=JOIN(@MultiValue,”,”)
Which returns a single string like aa,ab (no quotes!)

Or if you wish:
=”‘”+JOIN(@MultiValue,”,”)+”‘”
Which returns a single string like ‘aa,ab’ (with quotes using dynamic string-manipulation)

Too bad….the JOIN-function isn’t available within the Dataset (nor is EXEC() btw)

The answer is actually quite simple, don’t use statements like
‘X IN (SELECT * FROM dbo.fn_MultiValue(@MultiValue,’,’))’
Instead use the dataset Filter (or List if you use cascading parameters in your report):

The ‘All’-entity as a value of a query parameter (SSRS 2005)

A smart trick I learned regarding an ‘All’-entity as a parameter value, is using:
‘OR (@Parameter IS NULL)‘

Parameter values:
Query:

Parameter value list:

Report Dataset: