title: Calculate Percentile in SSRS
body: <p>We need to calculate a percentile (95th and 99th) in an SSRS report based on a shared dataset which return aggregated data by 15 minute interval. The percentile needs to be for the day as a whole, but per contract.</p>
  
  <p>For arguments sake, lets say this is our data:</p>
  
  <pre><code>select
      'Test' as [ContractName],
      1 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      3 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      4 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      6 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      7 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      36 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      56 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      798 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      324 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      456 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      657 as [Value]
  
  union all
  
  select
      'Test' as [ContractName],
      658 as [Value]
  </code></pre>
  
  <p>This is almost sorted (it was when I created it) but the data itself wouldn't be in the real-world application.</p>
  
  <p>I need to be able to get the percentile from this dataset without altering the SQL as it's a shared dataset used for contractual reporting and cannot be changed. The shared dataset is used to ensure consistency across the entire suite of reports. The solution must therefore be in the tablix in the final report output.</p>
  
  <p>I've looked at <a href="https://stackoverflow.com/questions/25529498/ssrs-expression-query-from-dataset-with-group">SSRS expression - query from dataset, with group</a> and noticed that it has the same basic usage as this <a href="https://stackoverflow.com/questions/22415130/find-the-median-of-a-calculated-field-in-ssrs-2012">Find the median of a calculated field in SSRS 2012</a>, which comes from <a href="https://blogs.msdn.microsoft.com/robertbruckner/2008/07/21/using-group-variables-in-reporting-services-2008-for-custom-aggregation/" rel="nofollow noreferrer">https://blogs.msdn.microsoft.com/robertbruckner/2008/07/21/using-group-variables-in-reporting-services-2008-for-custom-aggregation/</a> and requires a number of hidden rows and group variables which are not easily maintained/edited at a later date.</p>