Month: February 2016

SQL Server Reporting Services (SSRS) – Add a Group Row Header to an Existing Group Row

If you want to add a group row header to an existing group row in order for that group row header to repeat on each group break, do the following.

  1. In the Row Groups Panel below the report designer in the Design tab, right mouse click the group where you want a group row header to appear above it and select, ‘Add Total’ then choose ‘Before’.
  2. Add an expression to the column(s) of the group row header that you just created that you want displayed to the client based on the group break.

For example, let’s say you have two groupings, the first being TruckModel, the second being V8Engine with a value of Y or N; Y being that it is a V8. If you created a row group that breaks on V8Engine, the group row header will appear only when the V8Engine changes for the specific  TruckModel. In the expression, you can check for the value of Y, and if true, display V8 else V6 like so:

=Fields!TruckModel.Value & " " & iif(Fields!V8Engine.Value = "Y", "V8", "V6")

Microsoft Visual Studio – ASP.Net This Collection Already Contains an Address with Scheme HTTP

This collection already contains an address with scheme http.
There can be at most one address per scheme in this collection.
If your service is being hosted in IIS you can fix the problem by setting ‘system.serviceModel/serviceHostingEnvironment/multipleSiteBindingsEnabled’ to true or specifying ‘system.serviceModel/serviceHostingEnvironment/baseAddressPrefixFilters’. Parameter name: item

In .Net 4 and above, you can add this to the web.config to resolve the issue:

<system.serviceModel> 
 <serviceHostingEnvironment multipleSiteBindingsEnabled="true" /> 
</system.serviceModel>

SQL Server – Multiple data rows into one generated column, XML Path char(13), and removing that last carriage control in SSRS Expression

Let’s say you have a table called ‘car_make’ and another table called ‘car_model’. There is a one to many relationship between make and model.

You want to bring back a result set of all makes, 1 row per make, but you want only 1 generated column for model that has all the models concatenate into that 1 column.

You also want to add carriage controls so each model will appear on a separate line in your row on your report.

Let’s say you have 4 models for your 1 make of Chevy. Here is the output of what you are striving for:

Make     Model
--------------
Chevy    Astro
         Beretta
         Blazer
         Caprice

The following SQL will give you this result:

SELECT make,
left(ModelsConcat.Models,LEN(ModelsConcat.Models)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',REVERSE(ModelsConcat.Models))+1) As CarModels
OUTER APPLY (SELECT DISTINCT SUBSTRING((SELECT DISTINCT (m1.model + ', ' + + char(13))
 FROM car_make AS m1
 WHERE a.make = m1.make
 ORDER BY (m1.model + ', ' + char(13)) For Xml Path(''), type).value('.', 'nvarchar(max)') ,1,2000) AS Models 
 FROM car_model) AS ModelsConcat 
 FROM car_make a

In your expression, you simply reference the field like this:

=Fields!CarModels.Value