Search
Twitter
Tuesday
Aug172010

Repeating Column Headings & Other Advanced Tablix Member Properties in SSRS

After utilizing these properties to repeat column headings in SQL Server Reporting Services, I got curious as to what the rest of the properties do.  Here’s a brief reference.

The Advanced Tablix Member Properties are located in the Advanced Mode of the Grouping pane (the down arrow turns Advanced on and off).

Types of Tablix Members

Type

Description

Static

Items which repeat once for the row or column group, such as labels or subtotals. 

-->Settings to repeat column titles on each page are done with static members.

Dynamic

Items which repeat within the group, such as the detail rows.

 

Tablix Member Properties

Property Name

Default Value

Description of Use

Commonly used:

FixedData

False

Causes headers to behave like Excel’s “Freeze Panes” functionality when scrolling

Hidden

False

Hides the member

HideIfNoRows

False

Hides if there are now rows of data (useful to use instead of an iif expression)

KeepTogether

False

Keeps the entire tablix on one page if possible (this setting may cause white space before it)

KeepWithGroup

After

After keeps the header with the group of data after the header (Before keeps the footer with the group of data before it)

RepeatOnNewPage

False

Re-displays the label on every page

ToggleItem

Blank

Specifies if this member is hidden or shown based on another textbox – this specifies the other textbox that controls it

Less commonly used:

CustomProperties

Blank

For use with custom report renderers

DataElementName

Blank

For use with XML output: sets the name of the element

DataElementOutput

Auto

For use with XML output: controls whether element should be rendered

 

Repeating Column Headings

Settings for the first (Static) member in Row Groups to ensure column headings are repeated on each page & columns are frozen when scrolling in a web browser:

 

Additional documentation can be found at:

MSDN-Controlling Row and Column Headings

MSDN-How to Display Headers and Footers with a Group

Robert Bruckner's blog

 

Sunday
Aug152010

Using Table-Valued Functions to Support SSRS Reporting

I'm a big fan of Table-Valued Functions, as a way to centralize reusable logic in reports produced by SQL Server Reporting Services.  I’ll refer to Table-Valued Functions as TVFs for the remainder of this article. 

Full documentation for TVFs can be found here:  MSDN

To support SSRS reporting, the way I currently tend to use TVFs is as follows:

 

Reasons I find the above process valuable:

  • Centralize Logic.  Currently we utilize a TVF as a centralized way to find an initial set of data, based on a parameter passed.  Every reporting stored procedure joins to this centralized TVF so the dataset is retrieved the same way every time.  In my case, it’s a population of students made available to the report.  It goes without saying that standardizing this logic saves a lot of time if it has to change.
  • Replacement for a Stored Procedure.  For SSRS reports, we use stored procedures as the source for each dataset (in fact, the DBA doesn’t permit queries from within an SSRS dataset - this is enforced by allowing Execute permissions on the schema we need, but not Select).  Because we want to centralize a portion of the logic that isn’t report-specific, a developer’s first instinct could be to call this centralized logic from another stored procedure.  However, that won’t work because the SSRS report server can only process the first result set returned.  Therefore, we utilize a TVF lieu of another stored procedure so this process works for SSRS reporting.
  • Replacement for a View.  For SSRS reports, we pass a staff ID as a parameter in order to narrow down the set of data returned (for example:  the set of students a teacher is allowed to see).  Because a view cannot accept parameters, using a TVF for the centralized part of our logic instead of a view is very useful.
  • Allows Complex Logic.  We often use Common Table Expressions (CTEs) to arrive at a set of data in a multi-step process.  A TVF allows you to use the power of CTEs to arrive at the final Select statement.
  • Ease of Use.  Since you can join to a TVF as any other database object, its syntax is very easy to use.

Below is my simplified “cheat sheet” for the syntax.

Creation of the Table-Valued Function:

CREATE FUNCTION RPT.ufnMyFunctionName

   ( 

   @Variable1 varchar(10)

   )

RETURNS

   @TableVariableName TABLE

   (

    [Field1] nvarchar(20)

   ,[Field2] nvarchar(20)

   )

AS

BEGIN

INSERT INTO @TableVariableName

SELECT

    T2.Field1

   ,T2.Field2

FROM DW.Table1 AS T1

   INNER JOIN DW.Table2 AS T2

      ON T1.Field1 = T2.Field1

WHERE T2.Field3 = @Variable1

RETURN

END

 

Usage of the Table-Valued Function within a Stored Procedure:

CREATE PROCEDURE RPT.uspReportingProcName

AS

SELECT

    T3.Field1

   ,T3.Field2

   ,T3.Field3

   ,T4.Field1

   ,T4.Field2

   ,T5.Field1

FROM DW.Table3 AS T3

   INNER JOIN RPT.ufnMyFunctionName(@Variable1) AS T4

      ON T3.FieldA = T4.FieldA

   INNER JOIN DW.Table5 AS T5

      ON T3.FieldB = T5.FieldB

 

Sunday
Aug152010

Displaying Data in SSRS Tooltips

Tooltips are often used to help the viewer gain a better understanding of what's displayed.  This entry discusses the use of data within tooltips.

At a glance, the viewer sees the approximate percentage for each school based on the X axis:

 

In addition to the overall comparison of totals between schools, the exact percentage for each school is also useful information.  To keep this chart uncluttered, let's choose to provide the exact percentage using a tooltip.

Right-click the chart, select Series Properties, and open the tooltip expression (fx) window.  This expression uses the Fields Collection (i.e., the data) as well as text:

The above expression results in the viewer seeing the following:

 

Regarding the FormatNumber function:  My source data is stored as decimal(10,2) which translates perfectly for display on the X axis when the axis is formatted as a percentage.  However, that data type doesn't look very nice in a label.  To handle this, I first multipled by 100 (which took my .81 to 81.00), then I used the FormatNumber function with 0 decimal places (which took my 81.00 to 81), and finally concatenated the % symbol along with some descriptive text.

 

Page 1 ... 19 20 21 22 23