Useful SSRS expression syntax

=IIF(Fields!a.value>100, True, False)

--------------------------------------------------------
=IIF(Fields!a.value >=10, "Green", IIF(Fields!a.value >=1, "Blue", "Red"))

values >=10 are green, between 1 & 9 are blue, less than 1 are red

--------------------------------------------------------

=SWITCH(
    Fields!a.value >=10, "green",
    Fields!a.value >=1, "blue",
    Fields!a.value = 1, "yellow",
    Fields!a.value <=0, "Red")

>=10 are green, between 1 & 9 blue, =1 yellow, <=0 red

--------------------------------------------------------

SWITCH function finds the first expression that's true. It will not catch errors further along.
IIF function evaluates all parts of the expression.

--------------------------------------------------------


=IIF(Rownumber("scope") Mod 2 = 0, "Khaki", "White")

set "scope" to reset row colours for every group

--------------------------------------------------------

Display parameter Label field - SSRS

I was familiar with displaying a parameter's value field by using the expression:
 =Parameters!parameterfield.Value

But in many cases this value will be a key field and pointless to display. The 'Label' field is required and is obtained by simply changing the expression to:
=Parameters!parameterfield.Label

Simple and obvious really!

Select All option with multi-valued parameters (Part 2) - SSRS

Following on from my previous post on the shortcomings of the 'Select All' option with multi-valued parameters, another workaround is to assess whether the user actually Selects All. If so, then deal with this in the main query code.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.

The EstabCount parameter is given the following properties:

The below code simply populates the mult-valued parameter list:

Once the two parameters are set up the next stage is to alter the main dataset query. Select the Parameters tab and create an IIF statement that returns a predetermined string , "Select All" if the count of the number of parameters selected equals or is greater than EstabCount parameter value, i.e. if the 'Select All' option is checked in the drop down box.

=iif(Parameters!EstablishmentParameter.Count>=Parameters!EstabCount.Value, "Select All", Parameters!EstablishmentParameter.Value)

Then we need to alter the WHERE clause in the main dataset query:

WHERE (('Select All' IN (@EstablishmentParameter)) OR (View_ActiveEstablishment.EstablishmentName IN (@EstablishmentParameter)))

So, if the user checks 'Select All' in the drop down the IIF statement returns the string 'Select All' and the WHERE clause evaluates to true and returns a full selection thus negating the need for a potentially massive IN clause being used in the query. Likewise, if the user selects multiple parameter values then these are treated as expected. Unfortunately this may still result in a cumbersome IN clause if the user decides to select all but a few of the parameter values!

A quick play with SQL Server Profiler shows the 'Select All' database query before and after the alteration:

Select All option with multi-valued parameters - SSRS

A major short coming with multi-valued parameters in SQL Server Reporting Services is that there is no way to disable the 'Select All' option. This may have massive performance issues if 'Select All' is checked and large numbers of records are pulled from the DB. A work-around is to create another parameter and by using the IIF function limit the number of parameters available for selection.
















The first stage is to create a new parameter, in this case, called LimitEstablishmentParameter.















I've decided to limit the number of parameters available for selection using the following code in Default Value for the LimitEstablishmentParameter.



=iif(Parameters!EstablishmentParameter.count<=20,Parameters!EstablishmentParameter.value,"xxx")












Where xxx is a value that will return zero records from the dataset.

Change your dataset to refer to your new 'limiting' multi-valued parameter (LimitEstablishmentParameter in this case).






It's also worth putting a textbox near the top of the report with an expression similar to the following, informing the user of the selection limit:

=iif(Parameters!LimitEstablishmentParameter.Value(0)="xxx","The selection is limited to 20 establishments.",(Join(Parameters!EstablishmentParameter.Value, ", ")))

Display a list of all multivalued parameter values - SSRS

To display a list of all the selected multivalued parameters in a report use the Join function:

=Join(Parameters!Field.Value, ", ")

Multivalued parameter drop down width - SSRS

I was working on a SSRS project where I was using multivalued parameters. This creates a drop down list whereby the user can select the relevant values. Unfortunately in SSRS 2005 (SP3) the width of the dropdown is fixed, which seems to me as a bit of an oversight.

I believe that this may be corrected by altering the HtmlViewer.CSS file or by programmatically fixing the width of the available parameter values.

Neither is ideal.

















I will look into whether this has been rectified in SSRS 2008.

Display dataset query in the report - SSRS

To display a dataset query in the report, simply place the following code in a textbox expression:

=datasets!datasetname.commandtext

SOS! - Please help! - Report Manager times out - ***CULPRIT - AVG antivirus LinkScanner***

Any SQL Server installation/admin experts out there?


I have a development environment on my home PC running:

Windows 7 Ultimate 64-bit

(Windows Ultimate was installed as a 'clean copy' via an upgrade. The previous OS, Vista had SQL Server and all components including Reporting Services running successfully)

IIS spec

Prior to installing SQL Server I ensured the following IIS windows features were enabled:

-Internet Information Services
- -Web Management Tools
- - -IIS 6 Management Compatibility
- - - -IIS 6 Scripting Tools
- - - -IIS 6 WMI Compatibility
- - - -IIS Metabase and IIS 6 configuration compatibility
- - -IIS Management Console
- -World Wide Web Services
- - -Application Development Features
- - - -.NET Extensibility
- - - -ASP.NET
- - - -ISAPI Extensions
- - - -ISAPI Filters
- - -Common Http Features
- - - -Default Document
- - - -Directory Browsing
- - - -HTTP Errors
- - - -HTTP Redirection
- - - -Static Content
- - -Health and Diagnostics
- - - -HTTP Logging
- - - -Request Monitor
- - -Security
- - - -Request Filtering
- - - -Windows Authentication




















Navigating to http://mycomputername successfully displays the IIS 7.0 welcome screen

SQL Server spec
All components of SQL Server 2005 Developer Edition 64-bit were then installed successfully, followed by SP3.
I also installed SP1 for Visual Studio 2005 followed by SP1 update.

A default configuration of SSRS was also implemented.

Each service is running under the following:
SQL Server - LocalSystem
SQL Server Agent - LocalSystem
SQL Server Analysis Services - LocalSystem
SQL Server Browser - LocalSystem
SQL Server FullText Search - LocalSystem
SQL Server Integration Services - NT Authority\NetworkService
SQL Server Reporting Services - NT Authority\NetworkService

Shared Memory & TCP/IP enabled







Reporting Services Configuration

All relevant parts are correctly configured including the following:

Windows Service Identity:
Sevice Account - NT Authority\NetworkService
Built-in Account - Network Service

Web Service Identity:
ASP .NET Service Account - NT Authority\NetworkService
Report Service & Report Manager run under ReportServer application Pool

IIS Manager

ReportServer Application Pool runs under the Classic Managed Pipeline mode and NetworkService Identity.

Internet Explorer settings
Running IE8

Local intranet settings:
Include all local (intranet) sites....
Include all sites that bypass....
Include all network paths....

Set my login which has admin rights Content Manager in Report Manager

PROBLEM

I can view and render reports via reportserver
I can view reports but CANNOT render reports in report manager. I get the 'Report is being generated' message and it eventually times out with the message 'The underlying connection was closed: An unexpected error occurred on a receive.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host'

I've tried with various reports, some that pull data from my local db, some that have parameters and some that simply display text. NONE render in report manager, although the report paramaters are successfully retrieved from the local db.
I tested this using Mozilla firefox and although the layout is poor the reports render successfully.

I'm at a complete loss!
I've tried reinstalling the OS and SQL Server.
Tried setting up a new application pool in IIS using my login account and Classic managed pipeline mode.
In Reporting Services config I've tried using my login account for Windows Service Identity and ASP .NET Service Account.
All to no avail.


NIC & Wireless settings
This may/may not have any bearing on matters, I'm not sure!
When in the process of upgrading from Vista to Windows 7, it mentioned that the DELL Wireless WLAN Card Utility was not compatible with Windows 7 and I removed it.

Also, I was directed to this post (many thanks Jerry Nee) http://blogs.msdn.com/sql_protocols/archive/2008/04/08/understanding-connection-forcibly-closed-by-remote-host-errors-caused-by-toe-chimney.aspx regarding network adapters. Not sure if this is relevant seeing as I'm running a development system all on one PC. But, Im not sure! Please advise!


I hope someone can help me rectify this issue as I've been at it now for a good ten days!

I'm willing to flatten the whole lot and start again if recommended, if someone can state the best setup required for a 'stand-alone' development system such as mine.

Links to my forum posts are at:
http://www.sqlservercentral.com/Forums/Topic917776-162-1.aspx
http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/e976a8c9-a8a0-4223-8aec-36a3899a8e77
http://forums.iis.net/t/1167401.aspx



Many thanks,
Dom Horton

==============================
UPDATE
==============================


I've managed to solve this problem after many frustrating hours!
I'm running the free edition of AVG 9.0 and by disabling the 'LinkScanner' component I've got things to work. Funny thing was that I was sure I'd tried disabling the anti-virus software as part of my process of elimination!


==============================
Further info
==============================


http://forums.avg.com/gb-en/avg-forums?sec=thread&act=show&id=151271

Nth largest value - SSRS

I recently had a query regarding replicating Crystal Reports nth largest functionality in Reporting Services (hope this helps, Andy!).

For example, In Crystal Reports, its simply a case of using Insert>>Summary

I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.

Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)

SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson

The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.

SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc


By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.

Select All option - SSRS

It's often necessary to have a 'select all' option when using parameters in Reporting Services.
Here's a way to do it:

For my report I've created three datasets.

1) The first dataset, named 'Package' creates the parameter values, using UNION command to adjoin a '** SELECT ALL **' value to those obtained from a database.

SELECT '** SELECT ALL **' as PACKAGE

UNION

SELECT PACKAGE.PACKAGE as PACKAGE

FROM PACKAGE

order by PACKAGE.PACKAGE

2) The second dataset, named 'SelectAll' is used to populate the Default Value for the parameter.

SELECT '** SELECT ALL **' as PACKAGE

These two datasets are then used to populate the Report Parameters window as below:

3) The third parameter, in this case named 'ChangedRequest', provides the select logic.

SELECT .....

FROM .....

WHERE PACKAGE.PACKAGE = @Package or '** SELECT ALL **' in (@Package)

order by PACKAGE.PACKAGE

Which results in something like this:

Green bar matrix report - SSRS

I was recently asked by an anonymous blog poster (if reading please identify yourself!) how to go about creating a 'green-bar' style cross-tab/matrix report in both Crystal and SSRS.

Here's how to do it in SSRS (Cystal to follow at a later date):


1) Create an inner row grouping, in this case named 'Count' and given a grouping expression of =1















2) for the cell 'Count', set the property Value =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")

3) for the cell 'Count', set the property BackgroundColor =value















4) for the matrix data cell, named 'STATUS', set the property BackgroundColor =ReportItems!count.Value














5) for the outer row grouping, named 'TITLE, set the property BackgroundColor =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")















6) To hide the inner row grouping cell, 'Count', set
the following properties:
-set width to a minimum
-set BorderStyle Left =None
-set Color =value
-set CanGrow =False
set FontSize =1pt

for the outer row grouping cell:
-set BorderStyle Right =None

Useful Character code chart

Here's a link to a useful character code chart:


http://tlt.its.psu.edu/suggestions/international/bylanguage/mathchart.html


and to get the Windows Character Map Utility:

Start>>Programs>>Accessories>>System Tools

and simply copy and paste the relevant character or obtain the unicode number from the keystoke value, where applicable, or convert the hex to decimal.

Hexadecimal to decimal converter:

http://easycalculation.com/hex-converter.php