Overblog Follow this blog
Administration Create my blog
Balavardhan Reddy Narani

Advanced Reporting with Matrix Control in SSRS

January 27 2010 , Written by Balavardhan Reddy Published on #SSRS

Here I will show you how to get these custom aggregates on a matrix report and will cover a few interesting reporting techniques along the way

Cells-by-Cells technique – This helps us achieve a bit more usability with the somewhat limited framework of the SSRS Matrix control by allowing us to add as many subcolumns within a column as we’d like

Custom aggregates – The heart of this article, and probably the very reason you are still reading. Going beyond the standard SUM, we’ll use the powerful inspection expression InScope() to provide nearly limitless calculations.

Custom coloring – We’ll add some data visualization to the report, but we’ll modify the chart coloring at runtime.

Here I had taken Sales of each Item on Year and Monthly basis

Here the Data structure is like

Item Name     Year     Month    Total Sales
Item1             2008    Jan          120
Item2             2008    Jan          150            
Item1             2009    Jan          230
Item2             2009    Jan          250

Here I designed the Cross tab by Year, Month basis for every Item. Add a matrix control to the RDL page and set the Dataset for the Matrix Control. set Column Group and Row group as per the my Previous post.

After settting the Row and Column groups, add another Column Group by right clicking on the first row of the Matrix, here set the Month name for the Column Group 2

-- Adding New Column Group


-- Set the Field for Column Group 2


-- The result Matrix will appear like below


Click on the Preview tab to see the Result, here you will find the Year and month wise report like below, and it will display the Total of the Sales for each Column and Row.


in the Above, the color of Total sales and each individual sales is Same, if we want provide the different color for Column totals, Row totals and Individuals Slaes, her wee need to use Inscope() method to found the each Column and Row then provide the Color as you like.

Select the Data Column, goto the Properties and select the Background Color and set the expression.


then the Output will display in below


Insetead of Sales total, here by I am Caluclating Avg for the Sales, to caluclate the avg, right click on the Data Column textbox, select the Expression and write your formula to find the Avg in the expression window, here also we should use Inscope() method to find the Total displaying Row, then instead of Total put the formula for Avg,

Formula for Avg :

Format(Sum(Fields!Sales.Value)*100 / Sum(Fields!Sales.Value, "DataSet1"),"#0.0#;(#0.0#);-\0-") + "%" ),


Then the report will appear like.



Read more

SSRS - Matrix Control

January 21 2010 , Written by Balavardhan Reddy Published on #SSRS

In SQL Reporting Services, the Matrix control provides a crosstab view of data, similar in behavior to a PivotTable. Rows and columns will have intersecting points of data which is often useful in time based reporting.

Matrix components are great for visualizing data that might normally be exported to Excel, or in a Pivot Table. Year over year analysis is a good example. But if an analyst wants to view monthly sales, year over year, then he'd need to add some custom sorting so the months line up as expected. Additionally, we don't want to add the values between years, we instead need to display a difference in the totals, and perhaps in percentage growth (or decline).

To add a matrix
      Open the client report definition (.rdlc) file in graphical design mode. In the Toolbox, click Matrix. Click the design surface of the report.


After the matrix is on the report, you can move it by pointing to the shaded border of the matrix and dragging it to a new location. When you first create a matrix, the matrix displays four cells. The upper-left cell is the corner cell. You can use the corner cell to display a label for the matrix, or you can leave it empty. The upper-right cell is a column header, which can contain a field or expression by which to group the data. The lower-left cell is a row header, which also can contain a field or expression by which to group the data. The lower-right cell contains an aggregate expression for the detail data.

After you add a matrix, you can add fields to the matrix. Each cell in the matrix contains a text box by default. You can type any expression into any cell, or you can change the item within the cell to another item (i.e. to Image ot Chart).

Each matrix on a report is associated with a dataset. If the report contains a single dataset, the matrix is automatically associated with that dataset when you place it on the report. If the report contains multiple datasets, you must associate the matrix with the correct dataset.

Here I had taken Dataset for the Slaes on each Item for every year. the result of the Dataset is like

Item      Year    Sales
Item1     2007    150
Item2     2007    200
Item1     2008    180
Item2     2008    160
Item3     2007    510
Item3     2008    320  


After Adding the Required Dataset set to the Matrix control, you need to set the Column Grouping and Row Grouping Fields for Cross tab

m3 m4

Add the required Fileds to Column Header, and Row Header in the Matrix Control and also the Aggregate Expression to display the Data. to set these fileds, Right clikc on the each textbox (i.e. Header Group, Row group and Data), select the properties and set Fileds Name.

Here I added Year for Column Header, Item for Header, Aggregate function like Sum of the Sales to the Data textbox


Here, we can set the different Colours to each section of the Matrix control. select the every textbox and set the Colour font nad etc.. from its properties explorer, and see the preview of the report.

m6 m7

Adding Total's to Cross tab
In the matrix control we can also add the Sub total to each Column on and Each Row.

It shows like Total Items Sales in the every Row, and it shows Total Sales for every Year.

Select the Column Header and right click on the Header, here you can get the Sub Total in the Right Click Context menu, do the same above for Row Header also.

m8 m9

After adding the Subtotal to the Matrix, see the preview of the report, it looks like below.


-- Advanced Matrix Control next...

Read more

Beginning SQL Server 2005 - First Sample SSRS

January 20 2010 , Written by Balavardhan Reddy Published on #SSRS

The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:


To create a simple report using the Report Project, follow these steps:

  1. Launch Business Intelligence Development Studio.
  2. Select File > New >Project.
  3. Select the Business Intelligence Projects project type.
  4. Select the Report Server Project template.
  5. Name the new report ProductReport1 and pick a convenient location to save it in.
  6. Click OK.
    1. 1
    2. 2
  7. Add the Data Source to the Project,
    1. Right click on the Shared DataSources in the Solution Explorer and select the add shared DataSources.
    2. Provide Data Source name, Data Sour Type and Connection string for the Data Source in the immidiate window.
    3. 3
  8. Add New Report
    1. Right Clikc on the Report Folder from the Solution Explorer, select add new Item.
    2. 4
    3. 5
  9. Create the Data Set for the Report
    1. Select New Data set from the Dataset Drop Down in the Data Tab of the Report page
    2. Select required Data Source Name for the Dataset
    3. Provided Name and Query for the Dataset and Click OK, then the Data tab wil looks like below.
    4. 6
    5. It will shows the Data set Fields, Query and result in the Data tab
  10. Display Data in the Table
    1. Select the Layout tab of the report.
    2. This tab will have the Toolbox, to add the different controls to report UI
    3. 8
    4. Add the table control to Report UI page
    5. 9
    6. Set the Dataset to table, by selecting the table Properties from the right click on the table control
    7. Here we also set the required Properties like, Displaying the Header and Footer rows values
    8. 10
    9. Set the Data Fields to Detail Row of the table
      1. Right click on the Single cell of the Detail Row and Select the Properties to set the required Field to that Cell
      2. 11
    10. Add Extra Columns to table
    11. Set the Color, Font and allign properties for each section (i.e. Header, Data, Footer)
    12. Select The Preview tab of the Report to View the Report
    13. 15
  11. Deplyment
    1. Select the Properties of the Solution file
    2. set the Start Item, Target URL, Target Folder Name.. etc
    3. After setting the above properties, right click on the Solution file and Click on the Deply, It will autometicallly Deploy the Project into your Target Folder.
    4. 16
Read more

Beginning of Reporting Services 2005

January 20 2010 , Written by Balavardhan Reddy Published on #SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can be used to prepare and deliver a variety of interactive and printed reports. It is administered via a web interface. Reporting services features a web services interface to support the development of custom reporting applications.

SSRS competes with Crystal Reports and other business intelligence tools, and is included in Express, Workgroup, Standard, and Enterprise editions of Microsoft SQL Server as an install option. Reporting Services was first released in 2004 as an add-on to SQL Server 2000. The second version was released as a part of SQL Server 2005 in November 2005. The latest version was released as part of SQL Server 2008 in August 2008.

In SSRS, reports are defined in Report Definition Language (RDL), an XML markup language. Reports can be designed using recent versions of Microsoft Visual Studio with the included Business Intelligence Projects plug-in installed or with the included Report Builder, a simplified tool that does not offer all the functionality of Visual Studio. Reports defined by RDL can be generated in a variety of formats[2] including Excel, PDF, CSV, XML, TIFF (and other image formats[3]), and HTML Web Archive. SQL Server 2008 SSRS can also prepare reports in Microsoft Word (DOC) format.

Third-party report generators offer additional output formats.

Users can interact with the Report Server web service directly, or instead use Report Manager, a web-based application that interfaces with the Report Server web service. With Report Manager, users can view, subscribe to, and manage reports as well as manage and maintain data sources and security settings. Reports can be delivered via e-mail or placed on a file system. Security is role-based and can be assigned on an individual item, such as a report or data source, a folder of items, or site wide. Security roles and rights are inherited and can be overloaded.

In addition to using the standalone Report Server that comes with SQL Server, RDL reports can also be viewed using the ASP.NET ReportViewer web control or the ReportViewer Windows Forms control. This allows reports to be embedded directly into web pages or .NET Windows applications. The ReportViewer control processes reports in one of two ways: (a) server processing, where the report is rendered by and obtained from the Report Server; and (b) local processing, where the control renders the RDL file itself.

SQL 2005 reporting services also support ad hoc reports: the designer develops a report schema and deploys it on the reporting server, where the user can choose relevant fields/data and generate reports. Users can then download the reports locally.


image ssrs arc

The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including SOAP and WMI interfaces.

    *  Report Server is the core engine that drives Reporting Services.
    * Report Manager is a Web-based administrative interface for Reporting Services.
    * Report Designer is a developer tool for building complex reports.
    * Report Builder is a simplified end-user tool for building reports.
    * The Report Server database stores report definitions. Reports themselves can make use of data from many different data sources.

The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:

    * The data source to use
    * The query to use to retrieve data
    * Whether to use a tabular or matrix layout for the report
    * How to group the retrieved data
    * What visual style to use
    * Where to deploy the finished report
Read more