Search code examples
functionreporting-servicescrystal-reportsssrs-2008ssrs-2008-r2

PercentOfSum(fld, condfld) SSRS Equivalent


Crystal Reports has a built-in function PercentOfSum(fld, condfld) (documentation here).
How can I achieve the same functionality in SSRS?


Solution

  • It's a tricky question because grouping in SSRS is handled outside of functions, so the equivalent to condfld is explained here. The short answer is the cell will generally obey the grouping that you have applied to the row.

    So onto the percent, you'll need an expression (right click on the cell, click create placeholder, click the f(x) button for value). At it's most basic the the expression will be:

    =field1/sum(field1)
    

    However that assumes the data type can be a decimal, otherwise you'll need to convert it via cdec(field1) like so

    =cdec(field1)/sum(cdec(field1))
    

    Once we have that, you just need to format the cell to display percentages, right click, go to cell properties, click on number, select percent, and specify the number of decimal places you want. Hope that helps :)