25 November 2007

Advanced Matrix Reporting Techniques

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

I remember the first time I used a Matrix component in SQL Server Reporting Services (SSRS). I right-clicked the detail row, selected Subtotal and was amazed that RS “knew” that I wanted to sum the values of the rows. Without any need to specify a formula, I had aggregates with a simple mouse click. That amazement soon wore off after I realized just how difficult it might be to generate aggregates based on values other than a standard SUM.

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).

This article will show you how to get these custom aggregates on a matrix report and will cover a few interesting reporting techniques along the way. All steps are included to build a fully functional report that illustrates key concepts for:

  1. Dynamic dataset creation – Rather than provide a simple select statement or reference to a stored procedure, you’ll be able to dynamically modify the dataset source code at runtime.
  2. Query-based dynamic grouping – My last article showed you how to dynamically change grouping levels on your report output (the layout tab). But those were based upon a static list of values in the Parameters collection. This time, we’ll use a query to illustrate that you can provide dynamic grouping based upon a dynamically changing dataset.
  3. Dynamic column names – Keeping in spirit with the whole dynamic nature of this article, we’ll change the actual column headings depending upon the type of data our user has selected.
  4. The cells-in-cells technique – Otherwise known as the “rectangle inside a textbox” 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
  5. Custom matrix 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.
  6. Custom chart coloring – We’ll add some data visualization to the report, but we’ll modify the chart coloring at runtime.

Using these techniques, you’ll be able to build more-robust Matrix style reports in SQL Server Reporting Services.

The Business Scenario

The head of sales for AdventureWorks Corp would like to analyze his business for any selected year versus the year prior. Additionally, he’d like to see an analysis of month over month variances. So, for example, for the first quarter of 2007 he’d like to see a report detailing the rise (or decline) in business for January, February and March, with percentage change. He then needs to be able to drill into the detail to see the actual aggregate of sales data.

As an added wrinkle, our sales executive wants to group those sales by territory, then salesperson. No wait, Products, then Customers…no wait…well, hopefully you get the point. Since this isn’t an unreasonable request we should provide flexibility in the attributes we allow for reporting.

Technique 1: Dynamic Dataset Creation

We’ll start with a new report based on AdventureWorks that will display sales data, based on some flexible user inputs.

The sample report can be downloaded by clicking on the Code Download link at the bottom of the article. You will need to open Business Intelligence Development Studio, create a new Report Designer project, and add the file to it. Then you will need to change the ‘AdventureWorks’ Data Source to point to your server and its AdventureWorks database

For the new dataset, named AW_Sales, I’ve joined several tables using the graphical query builder in SSRS. In this example I am going to allow the user to select a date filter based on any of the following fields: DueDate, OrderDate or ShipDate. I’ve called this input parameter TimeSlicer, and the RDL fragment is shown in Listing 1.

Listing 1

<ReportParameterName=”TimeSlicer”>

  <DataType>String</DataType>

  <DefaultValue>

    <Values>

      <Value>ShipDate</Value>

    </Values>

  </DefaultValue>

  <AllowBlank>true</AllowBlank>

  <Prompt>Slicer:</Prompt>

  <ValidValues>

    <ParameterValues>

      <ParameterValue>

        <Value>OrderDate</Value>

        <Label>Order Date</Label>

      </ParameterValue>

      <ParameterValue>

        <Value>DueDate</Value>

        <Label>Due Date</Label>

      </ParameterValue>

      <ParameterValue>

        <Value>ShipDate</Value>

        <Label>Ship Date</Label>

      </ParameterValue>

    </ParameterValues>

  </ValidValues>

</ReportParameter>

In addition to selecting the WHERE clause field for our filter, two additional parameters, for Month and Year, are available for user selection. Month has static values (1 for January, 2 for February, etc.) but will allow for multiple selection. Year is a list of the years 2002, 2003, and 2004.

Books Online cautions on the use of dynamic queries in “Walkthrough – Using a Dynamic Query in a Report (http://technet.microsoft.com/en-us/library/aa237477(SQL.80).aspx)“, which you can read separately. However, the salient points are that you need to

  1. Create your query first without the dynamic components, using the query designer in SSRS
  2. Refresh your field list
  3. Switch to the Generic Query Designer and modify your SQL as needed.
  4. Remove any carriage returns or formatting created by the query designer so the SQL statement appears as one continuous string.

Finally, do not refresh your field list for this dataset! If you do, SSRS will not be able to parse your query and you will lose the field definitions created previously. If this happens to you, you’ll either have to manually create the field list again or revert back to your standard query to refresh those fields.

Figure 1 shows the SQL Query in all its unformatted glory.

Figure 1
453-Matrix1.jpg

The more human readable dynamic dataset used is found in Listing 2:

Listing 2
=”SELECT
      Sales.Customer.CustomerID,
      Sales.Customer.CustomerType,
      Sales.SalesOrderHeader.SalesOrderNumber,
      Production.Product.Name,
      Production.Product.ProductNumber,
      Production.Product.ProductLine,
      Production.Product.Class,
      Production.Product.Style,
      Sales.SalesOrderHeader.ShipDate,
      Sales.Store.Name AS Customer_Name,
      Sales.SalesOrderDetail.LineTotal,
      Sales.SalesTerritory.Name AS Territory_Name,
      Sales.SalesTerritory.[Group] AS Territory_Group,
      Person.StateProvince.Name AS State,
      Sales.SalesOrderHeader.DueDate,
      Sales.SalesOrderHeader.OrderDate,
      Sales.SalesOrderDetail.OrderQty,
      Production.Product.Size,
      Production.Product.Color,
      HumanResources.vEmployee.FirstName + ‘ ‘ + HumanResources.vEmployee.LastName
           AS SalesPerson_FullName,
      Production.vProductModelCatalogDescription.WarrantyPeriod,
      Production.vProductModelCatalogDescription.Material,
      Production.vProductModelCatalogDescription.RiderExperience
  FROM Sales.SalesOrderDetail
      INNER JOIN Sales.SalesOrderHeader
      ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
      INNER JOIN Sales.Customer
      ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
      INNER JOIN Production.Product
      ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
      INNER JOIN  Sales.Store
      ON Sales.Customer.CustomerID = Sales.Store.CustomerID
      INNER JOIN Sales.SalesTerritory
      ON Sales.SalesOrderHeader.TerritoryID = Sales.SalesTerritory.TerritoryID
    AND
      Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID
      INNER JOIN Person.Address
      ON Sales.SalesOrderHeader.BillToAddressID = Person.Address.AddressID
    AND
      Sales.SalesOrderHeader.ShipToAddressID = Person.Address.AddressID
      INNER JOIN Person.StateProvince
      ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID

INNER JOIN HumanResources.vEmployee
      ON Sales.Store.SalesPersonID = HumanResources.vEmployee.EmployeeID
      LEFT JOIN Production.vProductModelCatalogDescription
      ON Production.Product.ProductModelID =
             Production.vProductModelCatalogDescription.ProductModelID
  WHERE
      MONTH(&quot; &amp; Parameters!TimeSlicer.Value &a

The relevant portion is the WHERE clause:

You’ll notice the dynamic component of this query is in the WHERE clause. We never know what type of time value we’ll be inspecting (an Order Date or a Ship Date, for example) and we pass in a set of values for the months and years. Could this have been a really long conditional SELECT instead where we’d test 3 different types of dates depending upon what the user chose? Of course. But that statement would have been three times the size, and won’t scale if we want to test another time value easily in the future. Sometimes you’ll use dynamic datasets to access truly disparate pieces of information on the fly.

For the TimeSlicer selected by the user (ShipDate, for example), we investigate the Month and Year. Records will only be returned if they are in the multiselect parameter for Month selection and in the Year chosen, or in the year prior. This is a comparison report, remember?

One last point. You’ll notice that I wrapped the Month parameter input in a Join statement. Month, as we defined earlier, is a multi-valued parameter. Our user will be able to select anywhere from 1 to 12 months. Ideally, we’d like to pass that to an IN ( x,y,z) construct. The problem is we have a different data type passed to us from SSRS, similar to an array. The native Join() function will take each value and separate it by any delimiter we choose. In this case I have made certain it is a comma. When our user selects January and February from the multi-select dropdown we’ll see (1,2) passed to our SQL statement.

Technique 2: Query Based Dynamic Grouping

In this report, we’ll allow the users to select two levels of grouping so that the matrix behaves in a similar fashion to a Pivot Table in Excel. Users can group their analysis by Color then Size, or SalesPerson and State, and vice versa, or by any combination of fields we allow.

To provide this functionality we could simply create a list of field values in a new parameter, as I showed in “Reporting at the Top.” For this example, however, I’ll illustrate using a query. It’s partly because I am lazy and don’t want to enter the same value list twice and it’s partly to illustrate that the dynamic grouping can be served up from a query or stored procedure. OK, it’s mostly because I’m a geek. Like any developer, I’d rather code than copy and paste. Anyway, I’ve used a simple UNION query (Listing 3) to list the fields I want to provide, but you can build on this example by storing the values in a table for a truly scalable solution.

Listing 3SELECT 'Sales Person' AS Label , 'SalesPerson_FullName' AS Value
  UNION
SELECT 'Size' AS Label , 'Size' AS Value
  UNION
SELECT 'Color' AS Label , 'Color' AS Value
  UNION
SELECT 'State' AS Label , 'State' AS Value
  UNION
SELECT 'Territory' AS Label , 'Territory_Name' AS Value
  UNION
SELECT 'Customer' AS Label , 'Customer_Name' AS Value
  UNION
SELECT 'Warranty Period' AS Label , 'WarrantyPeriod' AS Value
  UNION
SELECT 'Rider Experience' AS Label , 'RiderExperience' AS Value
  UNION
SELECT 'Material' AS Label , 'Material' AS Value
  ORDER BY Label

Two parameters are then created, appropriately called Grouper 1 and Grouper2, with this dataset (named what else? …. Groupers) as their source for valid values.

On the Layout tab, add a new matrix component and tie it to the AW_Sales dataset. Access the Matrix Properties dialog and you’ll see that a Row Group and a Column Group have been created by default. Rename the default RowGroup to matrix1_Grouper1, and set the Group On Expression to:

Then, click the Sorting tab and use the same expression for sorting.

Create a new RowGroup, called matrix1_Grouper2 and set the Group On Expression and Sorting to:

Technique 3: Dynamic Column Names

Dynamic column names make our report truly scalable. We have our rows covered by the Grouping selected by our user, but sometimes you’ll need to change column heads to change with your data points. In this reporting example, all of our grouping of dates (Order Date, Ship Date, etc.), will be combined to Months and Years using the Month() and Year() functions. We will then dynamically display the appropriate Month or Year on our column heads directly above the data in our Matrix.

First, let’s set the grouping. Right click the Matrix control and select Properties. From the Groups tab, you’ll see matrix1_ColumnGroup1 in the Columns window. Click that and tap the Edit button. For the default ColumnGroup, set the Group On Expression and Sorting to:

Click OK, then back on the Groups tab of the Matrix Properties window select the Add button in the Columns section. Make this new ColumnGroup called matrix1_SLICER with the following values for Group On Expression and Sorting:

Back on the Report Layout tab, you’ll see a fairly simple matrix that should look like Figure 2.

Figure 2
453-Matrix2.jpg

In the cell I have highlighted, change the name to GROUPER1. Then enter the expression:

so our grouping value is shown. Then right click each cell other than “Data” and select Subtotal.

Right click the Data cell and select ‘Add Column’. Above the cell that displays the Year() calculation (shown in Figure 2) add the following expression:

This will dynamically display a user friendly name for Month, rather than just the number.

For the cells directly below the Year() calculation, we want to show the quantity sold as well as the amount (LineTotal). For the individual months totals will suffice, but on the summary columns we want to show the variance, or difference in sales between the two years. We’ll need to dynamically change the column head so users understand what to expect in the data displayed beneath. In the cell on the left use the expression:

.

…and the cell on the right gets…

The SSRS function, InScope(), will check for the relative positioning of our data as it is rendered. We’re interested in using standard column names like “Order Qty” or “Line Total” unless the summary groups are not in scope, meaning this is an aggregate column being rendered. In this case, we’ll display the verbiage “Variance.”

Technique 4: Cells in Cells

This technique will allow you to display more summary columns than detail columns. Specifically, we have one column each for quantity and dollar amount sold. But on the aggregate columns we want to render a column for the difference and the percentage respectively. To display two columns of data in the space provided for just one, we’ll add a textbox inside each of the two data level textboxes and conditionally display no data in them.

Select your first data cell, and drop a Rectangle control into it. You’ll notice the background change from solid white to the transparent grid pattern. Next, select a textbox control and drop this into the rectangle. You have to get it perfect, and sometimes it’s a bit annoying when you don’t land exactly on the control. However, if you do this correctly, you should have a display similar to Figure 3 at this point.

Figure 3
453-Matrix3.jpg

Next, drop in another textbox so that it rests directly beside your first one. Repeat this process for the second data cell and it will now appear as though you have 4 data cells with which to work. Be aware that when you want to set properties for these cells, your familiar point-click-edit routine will leave you within the context of the Rectangle surrounding your cells. You will need to click twice in order to access the textboxes within the Rectangle. You may need to play with their sizing a bit depending upon the width of data contained. I named the left textbox in the first rectangle QTY_COL and the left textbox in the second rectangle LINETOT_COL, so I can access their values later for percentage calculation.

Technique 5: Custom Matrix Aggregates

As mentioned in the beginning of the article, if we merely dropped a field definition into one of our matrix data cells (like QTY Sold), RS would automatically render the detail in the center and then sum the total on the far right (by default it renders totals on the right most column). But we want this report to behave a little differently. We want to display summary for each month detail. Our aggregate will be custom, and should display the difference (net) of the two years compared. From left to right, each of the cells should have the following expressions listed in order in Listing 4.

Listing 4=IIF (Inscope (“matrix1_SLICER”) AND InScope(“matrix1_ColumnGroup1”), Sum (Fields!OrderQty.Value), Sum (IIF (Year (Fields (Parameters!TimeSlicer.Value).Value) = Parameters!Year.Value, CDbl(Fields!OrderQty.Value) ,CDbl(Fields!OrderQty.Value) * -1 )))

=IIF(Inscope(“matrix1_SLICER”) AND InScope(“matrix1_ColumnGroup1″),””,IIF (SUM (IIF(Year(Fields(Parameters!TimeSlicer.Value).Value) = Parameters!Year.Value, cdbl(0), cdbl (Fields!OrderQty.Value) ) )=0,0,cdbl (ReportItems!QTY_COL.Value) / SUM (IIF (Year (Fields (Parameters!TimeSlicer.Value).Value) = Parameters!Year.Value, cdbl(0), cdbl(Fields!OrderQty.Value)))))

=IIF(Inscope(“matrix1_SLICER”) AND InScope(“matrix1_ColumnGroup1”), Sum (Fields!LineTotal.Value), Sum (IIF (Year(Fields (Parameters!TimeSlicer.Value).Value) = Parameters!Year.Value, Fields!LineTotal.Value, Fields!LineTotal.Value * -1))) =IIF(Inscope(“matrix1_SLICER”) AND InScope(“matrix1_ColumnGroup1″),””,IIF(SUM(IIF (Year (Fields (Parameters!TimeSlicer.Value).Value) = Parameters!Year.Value, cdbl(0), cdbl(Fields!LineTotal.Value)))=0,0,cdbl(ReportItems!LINETOT_COL.Value) / SUM(IIF (Year (Fields (Parameters!TimeSlicer.Value).Value) = Parameters!Year.Value, cdbl(0), cdbl(Fields!LineTotal.Value)))))

Using the InScope() function, we conditionally display either a simple sum or a custom calculation. In the first textbox you’ll notice the custom Sum includes a conditional IIF() statement.

Rather than display a blind total of all values in the row, we compare the Year. If it is the Year selected the value will be aggregated with a positive value. If however the value is from the set that belongs to the prior year (Year-1), we aggregate with a negative by multiplying by -1. Our end result is a summary that is the net effect of the two figures. Similar logic is used for the variance, supplied as a percentage difference, in the cells to the right of our field totals.

Since there will be no variance to offer for an individual month, we simply supply a double quote “” blank value when the column is not an aggregate.

To add some visual flair to the report I have added conditional Color formatting in the aggregates with:

Now, on a standard column the font will be black, but we’ll display positive movement in green and a decrease in sales with red. You can do the same with fonts, styles and shading. Special note: to set behavior (fonts, colors, etc.) on subtotal items you will need to first click the little green triangle in the upper right corner of your subtotal grouping cells to access their properties. It is my understanding that this annoyance will be going away in SQL Server 2008. After some additional formatting, my matrix looks like Figure 4.

Figure 4.
453-Matrix4.jpg

As a last touch, go to Matrix properties again and select the matrix1_Grouper2 row properties. On the Visibility tab, set Initial Visibility to hidden and allow the toggle based upon GROUPER1 (Figure 5).

Figure 5
453-Matrix5.jpg

We’ve just added a little interactivity to the display of the matrix which is important when real estate on the screen is at a premium. As an added benefit, the rendered output will behave much like an Excel PivotTable.

Technique 6: Custom Graph Colors

To add some additional splash, we’ll add a chart to display the variance of sales year over year. This will require the same grouping that we used on the matrix component. Add the chart above the matrix and set it to Simple Column, with the same dataset as our matrix (AW_Sales).

On the Data tab, create a new Value. Then, on the Values tab use the following expression:

This will display the total dollar amount of sales. Use the same expression on the Point Labels tab in the Data Label field. Be sure to check ‘Show Point Labels’ and format for currency (C0) so that we can see the actual totals on the chart. On the Appearance tab, click the Fill tab. Set the first Color to CornSilk (where do they get these color names?) and for the second color (since I am a gradient fiend) use the following conditional expression:

This will provide distinct colors on our report for each year (Gold for the current year, and Blue for the prior year) which will make the bars much easier to differentiate (see Figure 6):

Figure 6.
453-Matrix6.jpg

Next, create two Category Groups: one for the Month and the second for the Year. The Month will have the Group On and Sort Expression set to:

However, our label will wrap that expression in MonthName(), which is more user-friendly.

The Category Group for Year also uses the same expressions we used earlier in the matrix component grouping. The Group On, Sort and Label expression will be set to:

Final Output

When you Preview the report, you’ll seee that our parameters allow for a great deal of flexibility. Figure 7 shows the various groupings we can select, along with multiple months and a dynamic date criteria.

Figure 7.
453-Matrix7.jpg

Figure 8 shows a summarized view of variances only and

Figure 8.
453-Matrix8.jpg

Figure 9 illustrates what the detail looks like when exposed.

Figure 9.
453-Matrix9.jpg

The end result is a report that behaves very much like a Pivot Table in Excel. You can extend this with additional conditional formatting or provide additional value to reports that use Analysis Services as the source for more cube-like browsing experience in SSRS. Hopefully this example has armed you with some new skills so you can tackle those tougher matrix reporting challenges in the future.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 287716 times – thanks for reading.

Tags: , , ,

  • Rate
    [Total: 242    Average: 4.5/5]
  • Share

David Leibowitz (dleibowitz@cgsinc.com) is Product Manager for Computer Generated Solutions; helping customers realize value from portals and analytics. For over a decade he's implemented solutions for retail, wholesale and distribution companies specializing in fashion apparel, footwear and accessories ranging from startups to Fortune 500.

View all articles by David Leibowitz

  • Anonymous

    vs
    hello,this article very usefull for all.

  • Anonymous

    Very informative
    Good application of tips and tricks to do cool stuff with the matrix in RS. Still waiting for Microsoft to allow me to export a matrix with other objects (tables, rectangles) in cells to Excel, though. Hopefully in 2008 (fingers crossed!)

  • Aser Murias (amurias@bi-smart.com)

    Great Article
    David,
    Thanks a lot for your article, it has been very helpfull for us.
    Thank you again.
    Regards
    Aser

  • Anonymous

    Very informative
    Good application of tips and tricks to do cool stuff with the matrix in RS. Still waiting for Microsoft to allow me to export a matrix with other objects (tables, rectangles) in cells to Excel, though. Hopefully in 2008 (fingers crossed!)

  • Anonymous2

    Export to Excel not Works
    Not Usefull because export to excel is not working.

  • goracio

    Export to Excel not Works
    Not Usefull because export to excel is not working.

  • Anonymous

    Great Stuff
    This is great, but I was wondering how I would extrapolate this to compare multiple column groups. In other words, what if I wanted to create groups by quarter and then compare the numbers vs the previous quarter. Is this possible?

    Thanks again!
    Rich

  • Anonymous

    Nice
    This article is great i have learned a lot.Thanks

  • Anonymous

    Compare by month
    This was great! But, I am with the one who posted on on 1/10…I need to compare one month to the next for sales data and determine variance by month. I just can’t seem to find a solution and have been at this for 2 days now. Any chance we could get some direction on this?

  • Anonymous

    Excelent!
    Very good tips!

  • speters

    Good article!
    I have the same problem!!!
    ——————-
    This was great! But, I am with the one who posted on on 1/10…I need to compare one month to the next for sales data and determine variance by month. I just can’t seem to find a solution and have been at this for 2 days now. Any chance we could get some direction on this?

  • Abdul Karim Siddiqui

    Very good article!
    This was a great article on matrix.

  • travimca

    Good Article
    Very nice article, i am having one doubt is there any possiblity to highlight with background color for SubTotal and GrandTotal. Please help me….

  • Ricky

    q
    Very informative,

  • Ricky

    V.Good
    Very informative, can someone help on adding row headings in a matrix. There’s no real easy way, except using a rectangles and text boxes.

  • chandru

    Good article
    Hi your article on matrix customization has helped me a lot…Thanks you very much

  • frushko

    Great article
    Question: the query returns a considerable number of rows, 22k.
    Suppose we had to handle 500K rows(sales), would it be better (performance…) to compute the aggregations on the RS level(as demonstrated above) or having the grouping done on the sql query level? i.e. each change of Groupers will fire a new query with different “group by” clause( “group by color,size, group by size,color….)

  • frushko

    Great article
    Question: the query returns a considerable number of rows, 22k.
    Suppose we had to handle 500K rows(sales), would it be better (performance…) to compute the aggregations on the RS level(as demonstrated above) or having the grouping done on the sql query level? i.e. each change of Groupers will fire a new query with different “group by” clause( “group by color,size, group by size,color….)

  • ZehraNasif

    Thank You for the great article
    I have Googled a lot of articles and this was one of the best one which explains the Matrix very well. It was a great idea to link your Matrix Sales Comparison.rdl file, because I reversed engineer to help me speed up the learning curve.

  • kirank.gl

    how to download source code
    this execellent artical.please let me know,
    how to download source code for this artical.

  • Helal

    how to add column % to a matrix report
    This is a great article with step by step instruction. I have created a matrix report that shows only counts. I like to show column % next to count columns. How do I add column %?

    Thanks,
    Helal

  • mailtorajz

    how to download source code
    this execellent artical.please let me know,
    how to download source code for this artical.

  • nbussel

    code download
    For the code download you have to click on the Matrix Sales Comparison.rdl

  • meredigr

    Year column incorrect in matrix
    This is a brilliant article, most helpful indeed! One question, though – the report I downloaded shows the years correctly in the graph, bot not in the Matrix itself. In other words, 2003 and 2004 are both shown as 2004. Any idea why this would be the case? It seems the expression used in the Group for both the graph and the matrix table are the same.

  • gissah

    group by week
    This article is very knowledgeable, but is there anyway you can do it by week (7/1/10 thru 7/07/10)

  • JackLaff

    great (a must read)
    Wonderful, thanks for sharing a wealth of helpful information, code and knowledge.

  • joe2010

    Inserting Averages
    This is great! I wonder where can I find ways to calculate avegrages. I would like ot know how to use inscope.

  • schurers

    Can’t get matrix report to run- please any assistance!
    When I run the report I receive the following error: Query execution failed for dataset ‘AW_Sales’. Invalid object_name ‘Sales.SalesOrderDetail’

    I am rather new to using SSRS and unfortunately I am probably starting off with the most difficult at least for me. Can anyone tell me what I need to do to fix the error. I am using this as my shared data source: Data Source=TestSQLServer;Initial Catalog=AdventureWorks
    Thanks in advance.

  • Subbu$

    How to calculate Total – Matrix Cell Value (corresponding to a specific row)
    Good article….

    I have a matrix similar to the following,

    Jan Feb Mar

    SS 10 7 5

    BB 3 14 11

    DD 7 9 2

    Tot 20 30 18

    (Tot- 17 16 7
    BB)

    SS, BB, DD are prodcuts. I am able to get the total by adding the Total cell to the matrix. I also need to calculate the Total – Value of BB.

    For the above matrix,

    20-3 = 17
    30-14 = 16
    18-11 = 7

    How to get the value of the matrix cell for a specific condition..?? In this case, it is BB, which is one of the rows in the matrix result.

    Thanks in Advance

  • daisyd65

    Simplified Version
    I want to do EXACTLY what the author is doing in this article. However, the example report is so complicated with dyamic groupings and dynamic dataset creation, etc. that it makes it difficult to understand just the basic things that need to be done to subtract one value in a column group from another to get the variance, particularly if you’re not very familiar with using a matrix.

    My report has two parameters, @firstdate and @seconddate. This feeds into my sproc which looks at Field@CoverageDate.Value and which comprises the one column group that I have called coveragedate. For rows, there is just one static row group, and each row tallies something different. (I can separate these out in separate row groups if I need to, I think…) Anyway, the first row tallies the number of employers offering a medical plan on the coverage dates entered. The next row counts the number of participants in a medical plan on those two dates. the next row averages the premium the employees paid for the plan on those two dates, and so on. I just want to have a column that subtracts second year numbers from first year, and then another column that gives the percent variance. So, if coveragedate is my column group, and CountDistinct(Fields!ee.erid.Value) is my first row, how can I subtract the CountDistinct value in each year to get the variance. So, if there were 57 employers in the first year and 68 employers in the second year, how do I get 11 (68 – 57) in a column called #/$Variance? Anyone? Anyone? Bueller? Please?

  • virajdoshi

    Similar Requirement – Month as columns
    Hi,
    Thanks for the Article. Really neat !!!

    I have a similar requirement where in i need to build the report. i need to show the companies as detailed rows. in my sql i have 2 columns for months. Jan, JanAP – Feb,FebAP etc. These columns store respecitve values.

    My table structure is as follows :
    —–
    Company Id|Jan|JanAP|Feb|FebAP

    now,in the output i should get 12 months of data based on month selected. if i select august 2012, the report should start from july 2011. then it shoud have another columns as Jul and JulAP and then in details, company names and respective figures should come.

    Any idea how can i do that ? currently i have to write lots and lots of formulas in a table based report. i would like to do it easily using matrix.

    Thanks in advance.