SSRS subreports inserting pagebreaks.

It can be frustrating when a subreport (or any report item) inserts pagebreaks a and you have to go through and get the KeepTogether and KeepWithGroup properties correct AND correct at the right level.

KeepWithGroup in the parent report is the key property and the key level is at the static row group the sub report is in for the Parent Report.

If you are having pagebreaks inserted a rule of thumb is

• Make sure KeepTogether is set to False everywhere it is not explicitly required
• KeepWithGroup = After for the report item that is creating the pagebreaks (in this case the sub-report)

SSRS - Add a label listing items selected from a multi select parameter

The following code in ds_Vendor_Type_SelectedList returns a string that can be used in the report heading to describe the values selected in a multi select parameter.

A simple alternative might be to use the VB code
=Join(Parameters!ParameterName.Label, ",")
But in large parameter selections this does not indicate to the user if ALL the parameters have been selected.

If all parameters are selected then the following structure returns 'ALL' or alternatively, if a subset is selected then it lists the actual items. This is a more intuitive and user friendly approach.

The second data set called ds_Vendor_Type_SelectedList is populated based on the user selection of the parameter (@VendorTypes parameter variable)



Parameter Name = @VendorTypes

List populated by following Dataset
ds_VendorType_List

SELECT     VENDTYP_CODE,VENDTYP_DESC
FROM APL_VENDTYP
WHERE INACTIVE = 'N'
ORDER BY VENDTYP_DESC


ds_Vendor_Type_SelectedList

DECLARE @CountSelected AS INT
DECLARE @CountAvailable AS INT
DECLARE @ReturnString AS VARCHAR(MAX)

SET @CountAvailable =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE INACTIVE = 'N')
SET @CountSelected =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE VENDTYP_CODE  IN (@VendorTypes))

IF @CountAvailable = @CountSelected
    BEGIN
        SET @ReturnString = 'All Vendors Selected'
    END
ELSE
    BEGIN
        SET @ReturnString = 'Vendors Selected : ' +
        (STUFF(( SELECT ', ' + VENDTYP_DESC
        FROM APL_VENDTYP WHERE VENDTYP_CODE IN (@VendorTypes)
        FOR XML PATH('')), 1, 2, ''))
    END
   
SELECT  @ReturnString AS VendorsSelected