RDL  Matricx Report Calculate Percentages

Posted on 2007-07-24
Last Modified: 2013-12-17
I am new to RDL reports.  I am building a matrix report in which I want to calculate and display percentages.   I have raw data that I group first by  State then by City  and then Count the number of "hits" for that city.  I now want to  calculate the percentage  of  hits for each city based on the total for the state.  How is this accomplished?
Question by:DylanJones1
    1 Comment
    LVL 21

    Accepted Solution

    If we are talking about your report, you don't need to group actually on anything - the matrix will do al lit's grouping, so have a query similar to this:

    SELECT ..., hits, city, state, ...

    Then create a matrix, drop city and state fields on headers and rows, your hits on the data area. By default it will set your hits to =First( ... ) - change it to the =Count( ... ) and it will show the number of hits in a city for a particular state. Now, If you want to get the percentage of this relation, you need to modify your data field. Right-click it, select expression and change Count( ... ) (the one you did in the previous step) to:

    =count( Fields!Hits.value ) / count( Fields!Hits.value, "matrix1_state" )

    "matrix1_state" - is the name of your matrix group, to see it, right click column or row header and select Edit Group - it will show you the name. Depending how you located you data (horizontally or vertically), you'll need to use row group or coluln group - which one is which will be clear as soon as you figure out how to get this percentage.

    You'll need to change the format of your datafield in the matrix to p2 to see it in the following form: 32.43%

    Good luck,


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how the fundamental information of how to create a table.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now