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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3226
  • Last Modified:

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

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
ezkhan
Asked:
ezkhan
1 Solution
 
mankowitzCommented:
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
 
ezkhanAuthor Commented:


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
 
ThomasianCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mimran18Commented:
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
 
ezkhanAuthor Commented:



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

Is this possible???????????
0
 
ThomasianCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now