title: Filtering based on SSRS total/sum
body: <p>I am using Visual Studio 2010 and SQL Server 2012. </p>
  
  <p>I have searched the net and the stackoverflow archives and found a few others who have had this problem but I could not get their solutions to work for me. The problem involves filtering based on the aggregate value of a dataset using a user definable report parameter.</p>
  
  <p>I have the following report.</p>
  
  <p><img src="https://i.imgur.com/vj4rmwN.jpg" alt="SSRS1"></p>
  
  <p>The ultimate goal of this report is to filter portfolios that have a user definable % of cash as a percent of total assets. The default will be 30% or greater. So for example if a Portfolios total market value was $100,000 and their cash asset class was $40,000 this would be a cash perent of 40% and this portfolio would appear on the report.</p>
  
  <p>I have been able to select just the cash asset class using a filter on the dataset itself so that is not an issue. I easily added a cash percent parameter to the dataset but soon realized this is filtering on the row detail not the aggregated sum, and sometimes portfolios have multiple cash accounts. I need the sum of all cash accounts so I can truly know if cash is 30% or greater of total market value.</p>
  
  <p>At first I thought the report was working correctly.</p>
  
  <p><img src="https://i.imgur.com/XnHoKM4.jpg" alt="SSRS2"></p>
  
  <p>But after cross referencing this against another report I realized this portfolio only has 2.66 % <strong>total Cash</strong> because it has a large negative balance in a second cash account as well. It's the sum of all cash accounts I care about.</p>
  
  <p><img src="https://i.imgur.com/PgSfFWG.jpg" alt="SSRS3"></p>
  
  <p>I want to filter portfolios that have >= cash based on the total line not the detail lines. I suspect I may need to alter the dataset using the scalar function SUM() then building a parameter off that, but I have not had success writing a query to do that. I also would be very interested to know if somehow this can be done in the .rdl layer rather than at the sql dataset level. The SQL for this is a little complicated because the program that this is reporting on requries the use of SQL functions, stored procedures, and parameters.</p>
  
  <p>If the solution involves altering the query to include sum() in the dataset itself, I suspect it is line 20 that needs to be summed </p>
  
  <pre><code>a.PercentAssets,
  </code></pre>
  
  <p>Here is the data set for the report.</p>
  
  <p><a href="https://www.dropbox.com/s/bafdo2i6pfvdkk4/CashPercentDataSet.sql" rel="nofollow noreferrer">https://www.dropbox.com/s/bafdo2i6pfvdkk4/CashPercentDataSet.sql</a></p>
  
  <p>and here is the .rdl file.</p>
  
  <p><a href="https://www.dropbox.com/s/htg09ypyh7f1a98/cashpercent2.rdl" rel="nofollow noreferrer">https://www.dropbox.com/s/htg09ypyh7f1a98/cashpercent2.rdl</a></p>
  
  <p>Thank you</p>