Month: September 2013

SSRS “Continued” Group Header on Subsequent Pages

Here’s an easy way to alter your group header on subsequent pages in SSRS, without using any custom code.

Step 1:  Add a row number by your grouping to the data set query
SELECT     
ROW_NUMBER() OVER(PARTITION BY Layout_Code ORDER BY Product_ID) AS Row
,[Layout_Code]
,[Product_ID]
,[Variant_Code]
  FROM[DIM_Product]
Step 2: Be sure your Row Group header is set to Repeat on New Page

Do this by selecting the small triangle at the top right of the grouping pane and turning on Advanced Mode.  Then select the Static member at the top of your group and set the RepeatOnNewPage property to True.

image

image

Step 3: Add the Row field in your report

Add the =First(Fields!Row.Value) field into the group header row of your report.  Call the textbox RowGroup.

Add the Row field into the detail row of your report. Call the text box RowDetail.

These fields will be hidden later.

image

Step 4: Add the Group Header expression

Set the Group Header expression like this:

=iif(ReportItems!RowGroup.Value=ReportItems!RowDetail.Value, Fields!Layout_Code.Value, Fields!Layout_Code.Value + ” Continued”)

Notice in the preview of the report that on the first page of the report the RowGroup textbox = 1 and the RowDetal textbox = 1.  The Iif statement dictates that the group header shows the Layout_Code value.

image

On page 2 of the report, since the Layout group continues and 1 <> 49, the work “Continued” is added to the group header.

image

This will carry on until a new group starts and the row number goes back to 1.  You can go ahead and resize and hide the column once you have the logic working.

Loop through dynamic SQL statements in SSIS

This is a situation where the data from a number of tables with the same structure needs to be imported into a single table.  Rather than hard coding multiple data flows from source to destination, you can loop through each SQL statement using a single data flow by building a dynamic SQL statement.  This simple package loops through each table name to accomplish just that.  It also has a secondary loop to pivot some hard coded week numbers in the field names.

image. 

The data source is a series of tables with the same structure. Each one holds a different set of planning data.  The fields have the fiscal month hard coded into the name, rather than have Week Number as an attribute.

image

The destination not only needs to map multiple tables to a single table, but it also needs to pivot the fiscal month weeks.

image

 

LOOP THROUGH TABLES

To accomplish this I hard coded the table names into the For Each loop, but an object variable could just as easily done this. 

image

 

image

 
LOOP THROUGH FIELD NAMES (WeekNo)

 

image

image

 
VB SCRIPT TASK TO WRITE THE DYNAMIC SQL STATEMENT

image

In the script portion, write a simple script which uses the variables and embeds them in the SQL statement you are writing.  This outputs to your SQLStatement variable to be used in the data flow.

    Public Sub Main()
        '

        Dim WeekNo As Integer
        Dim TableName As String
        Dim SQLStmt As Object

        WeekNo = Dts.Variables("WeekNo").Value
        TableName = Dts.Variables("TableName").Value
        SQLStmt = "SELECT [Version Code] as Plan_Version, [Fiscal Year] as FiscalYear,[Fiscal Month Sequence] FiscalMonth, " & WeekNo & " as FiscalWeek, convert(numeric(38,20),[SLS NET $ W" & WeekNo & "]) as Sales, FROM " & TableName
        Dts.Variables("SQLStatement").Value = SQLStmt

        Dts.TaskResult = ScriptResults.Success
    End Sub

To set up the data flow, you must first enter a valid SQL Statement in the SQLStatement variable.  Your variable will then be replaced with a new one during each loop. 

image

I hope you find this useful.

How to Enable Custom Logging for an SSIS Script Task

At times I like to capture certain errors and events that occur in a Script Task in an SSIS package, and include them in the package logging.  In order to make this happen simply include the appropriate statements in your Script Task and turn on some custom logging within the package logging configuration.

Add a Dts.Log statments to your vb Script Task.  For example:

            Dim dataBytes(0) As Byte
            Dts.Log("Did not find expected database", 0, dataBytes)

In order for this message to be included in the [sysssislog] table simply right click on the package Control Flow surface, and select Logging. Within the Containers window, drill down to your Script Task.

image

Check the box beside the Script Task until it has a black check mark, instead of a greyed out check mark. In the Providers and Logs tab select the log you want to write to.

image

On the Details tab select the Events you wish to log, and be sure to select the ScriptTaskLogEntry.

image

Click OK and you’re done.  Your custom messages will be included in the package logs.