Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

RDL  Matricx Report Calculate Percentages

Posted on 2007-07-24
1
Medium Priority
?
993 Views
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?
0
Comment
Question by:DylanJones1
1 Comment
 
LVL 21

Accepted Solution

by:
Yurich earned 2000 total points
ID: 19562411
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,
Yurich

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Suggested Courses

581 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