asked on

Add a percentage column to table which displays [Freq]/Sum([Freq])

I need to display the number of counts of an item and the percentage of the total on the same row in an SSRS table. I'm guessing that this cell (see the %) needs an expression, but I don't know what it should be.

The column name of the value in the center column of each of these tables is [Freq]

slightwv (䄆 Netminder)

Don't know SSRS but in straight SQL you can return the total SUM as a column on each row. You can even compute the percentage or let SSRS compute it.

I made up a dummy table and columns based on the left side. You'll see that in the fiddle link below.

As a separate column:

select some_type, some_count,
	sum(some_count) over() total_count
from bob;

Open in new window

Computed in SQL:

select some_type, some_count,
	round(((some_count+0.0)/sum(some_count) over())*100,2) total_pct
from bob;

Open in new window

Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b2cbdbf1fb591cdac914e0232bf45e9e

Partha Mandayam

You have to use formula fields
See this article for more information on how to create formula fields
https://www.tutorialgateway.org/calculated-fields-in-ssrs/

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Partha Mandayam

@Dale That's exactly what I suggested
To use formula fields
=Fields![Freq].Value/ReportItem![txt_Total].Value is nothing but a formula field

Dale Fye

ASKER

Yes, but the piece you didn't provide was the piece I needed:
1. Name the textbox containing the sum([Freq])
2. Use the following as the denominator to get the percentage

ReportItem![txt_Total].Value

Open in new window

I already knew how to use expressions in SSRS, just didn't know how to refer to the value in that textbox in the expression, and that was not covered by your response, or the article you linked to.

Thanks for your suggestion.