Solved

Calculate total and percentage columns from ssrs 2008 r2 dataset for reports.

Posted on 2011-09-05
7
2,941 Views
Last Modified: 2012-06-21
Hi,

I have a situation to calculate the total of a column in dataset in ssrs 2008 r2 to calculate percentage in another column.

e.g.,

ValueColumn       TotalColumn        PercentageColumn
20                            69                      20/69*100
33                            69                      33/69*100
46                            69                      46/69*100


I want to create TotalColumn and Percentage from SSRS 2008 R2 dataset (using calculated field or some other method not sure!!!!!!!!!!!)

Your help and  suggesionss are highly appreciated.

Thanks.

0
Comment
Question by:ezkhan
7 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 36485741
what does the raw sql query look like? What is the table structure? Is TotalColumn a calculated field, or is it something directly from the DB?

You might be able to do this:

SELECT ValueColumn, TotalColumn, ValueColumn/TotalColumn*100 AS PercentageColumn FROM DataTable
0
 

Author Comment

by:ezkhan
ID: 36485815


I can do it in db but these columns are not individual columns e.g.,

As per your select statment ValueColumn is derived from number of aggregagetes and divisions with 10 different columns in database dataset. If I calculate percentage again in database then i need to calculate ValueColumn twice and there more than one columns where i need to calculate percentage. It slows down the whole query and increases CPU usage.

Thats why I want to calculate percentage from already calculated columns in Reporting Services instead of DB.

Please suggest solution in reporting services. Your answers and suggestions are greatly appreciated.

Thanks
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36486349
TotalColumn:
=Sum(Fields!ValueColumn.Value, "DataSetName")

Open in new window

PercentageColumn:
=Fields!ValueColumn.Value/Sum(Fields!ValueColumn.Value, "DataSetName")*100

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 9

Expert Comment

by:mimran18
ID: 36486574
try this

 
=Sum(Fields!FieldName.Value,"DatasetName")

Open in new window

=Round((Fields!SrNo.Value/SUM(Fields!FieldName.Value,"Dataset")*100),2)

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36486780
0
 

Author Comment

by:ezkhan
ID: 36491180



Thanks a lot for your suggesions; I want to use this calculated percentage column in pie chart as series!!!!!!!!!

Is this possible???????????
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 36493062
If you want to use it on a pie chart, you don't need to calculate the percentage manually. Just drop the ValueColumn on the data field and it will automatically divide the chart as percentage. If you want to show the percentage as label, then you could select #PERCENT as the Label Data.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

22 Experts available now in Live!

Get 1:1 Help Now