Percent of Totals in Reporting Services (Simple way without Data Set editing)

Suppose you have the following data set:


 Member Level InStore  Online  NumberofPurchases

------------ ---------- -------- -------------------

GOLD         1500.00    1200.00  10

SILVER       1000.00    600.00   8

BRONZE       500.00     300.00   4

and you wanted to display this in reporting services, but also have the % of totals (i.e. Gold = 50% of all In Store Purchases). Turns out there is a very easy way to do this, assuming you are only basing this off of a Grand Total or total row that is not dynamic (multiple versions of it will appear). Click on the total cell for In Store $, and rename it from textbox11 (or whatever it is) to TotalInStore.

Change the Name of this textbox.

Add a new column to the Table, give it the header % of In Store. On the Data Row, enter the following expression for the text field (note for the code, my column name is InStore:


=SUM(Fields!InStore.Value)/ReportItems!TotalInStore.Value

Format that as a percent, and this should auto calculate the total, and compare it to that individual group giving you the percentage of the total:

See the Percent in Store is accurately reflected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s