title: SSAS report action to pass multi-value list values to a SSRS report parameter
body: <p>I have searched many hours attempting to find an end-to-end solution to this problem.</p>
  
  <p>I want to create a report action in an SSAS OLAP cube that generates a list of formatted values to pass into an SSRS report parameter that accepts a multi-valued parameter list.  This would be applied at the <em>cell</em> level in the SSAS action.  I have found a solution that gets me most of the way: <a href="http://www.bidn.com/blogs/DustinRyan/bidn-blog/6251/how-to-pass-multiple-values-from-an-ssas-report-drill-through-action-to-an-ssrs-multi-value-parameter" rel="nofollow">How to Pass Multiple Values from an SSAS Report Drill Through Action to an SSRS Multi-Value Parameter</a>, but not quite.  The action does appear in Excel and works <strong>if</strong> I run the action from a cell that is at or below the dimension attribute I am generating the list for, in this case, <em>Account Key</em>.</p>
  
  <p>Below is a link to a screen capture (unable to embed it due to lack of reputation) showing the action and dimension structure in Excel.  The action works as long as I run it at the <em>Account Key</em> level or below.  I want to be able to run it at higher levels, such as <em>Account Major</em> and still have it generate all then related <em>Account Key</em> values for the SSRS report parameter.  Running it at the higher <em>Account Major</em> level does not trigger the report to run.</p>
  
  <p>Excel Action Screen Shot: <a href="http://i.stack.imgur.com/QCGSp.png" rel="nofollow">http://i.stack.imgur.com/QCGSp.png</a></p>
  
  <p>Below is the MDX I am using to generate the value for the report parameter:</p>
  
  <pre><code>UrlEscapeFragment(
      GENERATE(
          DESCENDANTS(
              [Account].[Account Key].CurrentMember, 
              [Account].[Account Key].[Account Key]
          ),
          [Account].[Account Key].CURRENTMEMBER.Name,
          "&amp;rp:Account="
      )
  )
  </code></pre>
  
  <p>I am hoping that I can somehow modify the MDX above to make it return all the <em>Account Keys</em> for any attribute of the <em>Account</em> dimension when ran from any measure cell, not just when ran at self and children of <em>Account Key</em> in the pivot table.</p>
  
  <p>Also, if it helps, I can execute the following MDX query on the cube and get the results I am looking for.</p>
  
  <pre><code>WITH MEMBER [Measures].[Account Key List] as 
  GENERATE(
      DESCENDANTS([Account].[Account].CurrentMember, [Account].[Account].[Account]),
      [Account].[Account].CURRENTMEMBER.NAME,
      "&amp;rp:Account=")
  
  SELECT {[Measures].[Account Key List]} on 0,
  ([Account].[Account Company Number].[Account Company Number],[Account].[Account Major].[Account Major]
  )  on 1
  FROM  [Company 10 Action Demo]
  </code></pre>
  
  <p>Below are partial results:</p>
  
  <pre><code>10.116&amp;rp:Account=10.116.010
  10.117&amp;rp:Account=10.117.010&amp;rp:Account=10.117.020
  10.120&amp;rp:Account=10.120.005&amp;rp:Account=10.120.006&amp;rp:Account=10.120.010&amp;rp:Account=10.120.020&amp;rp:Account=10.120.030&amp;rp:Account=10.120.040&amp;rp:Account=10.120.050&amp;rp:Account=10.120.060&amp;rp:Account=10.120.380&amp;rp:Account=10.120.999
  10.123
  </code></pre>
  
  Questions
  
  <ul>
  <li>Any ideas what I might need to do to get <em>Account Key</em> to be returned for any attribute of the <em>Account</em> dimension?</li>
  <li>Would I possibly have to alter my <em>Account</em> dimension in the cube to get this to work?</li>
  </ul>
  
  <p>Thanks in advance.</p>
  
  <h1>Edit 1 - Adventure Works Cube Version</h1>
  
  <p>I was unable to get the suggested answer with the "Exists" function to work.  To better demonstrate this issue, I have recreated it using the Adventure Works Cube.</p>
  
  <p>I will focus on the <em>Customer</em> dimension, specifically the <em>Customer</em> and <em>Education</em> attributes.  I created a report action called <em>Test Report Action</em>.  Below is the XML created for it in the cube.</p>
  
  <pre><code>    &lt;Action xsi:type="ReportAction" dwd:design-time-name="f35ad5ee-5167-4fb8-a0e0-0a74cc6e81c6"&gt;
        &lt;ID&gt;Report Action 1&lt;/ID&gt;
        &lt;Name&gt;Test Report Action&lt;/Name&gt;
        &lt;TargetType&gt;Cells&lt;/TargetType&gt;
        &lt;Target&gt;&lt;/Target&gt;
        &lt;Type&gt;Report&lt;/Type&gt;
        &lt;ReportServer&gt;SQLSERVER&lt;/ReportServer&gt;
        &lt;Path&gt;ReportServer?/Test Report&lt;/Path&gt;
        &lt;ReportParameters&gt;
          &lt;ReportParameter&gt;
            &lt;Name&gt;Test Customer Existing&lt;/Name&gt;
            &lt;Value&gt;UrlEscapeFragment(
      GENERATE(
         EXISTING DESCENDANTS(
              [Customer].[Customer].CurrentMember, 
              [Customer].[Customer].[Customer]
          ),
          [Customer].[Customer].CURRENTMEMBER.Name,
          "&amp;amp;rp:Customer="
      )
  )&lt;/Value&gt;
          &lt;/ReportParameter&gt;
        &lt;/ReportParameters&gt;
        &lt;ReportFormatParameters&gt;
          &lt;ReportFormatParameter&gt;
            &lt;Name&gt;rs:Command&lt;/Name&gt;
            &lt;Value&gt;Render&lt;/Value&gt;
          &lt;/ReportFormatParameter&gt;
          &lt;ReportFormatParameter&gt;
            &lt;Name&gt;rs:Renderer&lt;/Name&gt;
            &lt;Value&gt;HTML5&lt;/Value&gt;
          &lt;/ReportFormatParameter&gt;
        &lt;/ReportFormatParameters&gt;
      &lt;/Action&gt;
  </code></pre>
  
  <p>Below are the steps to re-create the issue.</p>
  
  <ol>
  <li>Connect to the cube in Excel </li>
  <li>Add dimension <em>Customer -> More Fields ->
  Customer</em> </li>
  <li>Add measure <em>Internet Sales -> Internet Sales Amount</em></li>
  <li>Right-click Internet Sales Amount cell, select "Additional Actions"
  -> "Test Report Action" and see customer values created for URL  </li>
  </ol>
  
  <p>When the action is ran at this point with <em>Customer</em>, I see the values created in the URL shown message box (since there is no SSRS report server at location specified).</p>
  
  <p><strong>Now the part I'm unable to resolve</strong></p>
  
  <ol start="5">
  <li>Remove the <em>Customer</em> dimension and add <em>Customer -> Demographic -> Education</em></li>
  <li>Right-click Internet Sales Amount cell, select "Additional Actions" -> "Test Report Action" </li>
  </ol>
  
  <p>Nothing happens. If I ran the action on the cell next to "Bachelors", I would want it to build up all the list of all the "Customers"  that make up the "Bachelors" in the <em>Customer</em> dimension as part of the report parameter.  If no attributes where used in the cube from the <em>Customer</em> dimension for that cell, then I would like it to return "All Customers", or something similar to show that all customers are included in the aggregations.</p>
  
  <p>I am not too MDX savvy, thus far.  I think I need to somehow join the <em>Customers</em> to <em>Internet Sales Amount</em> in the Generate function portion.  I have tried several different combinations of the <em>Customer</em> dimension and <em>Internet Sales Amount</em>, along with various functions to see if I could get this to work with no success.  I am hoping that someone more knowledgeable the me will have a solution.   If you need more details, please ask and I will provide them.</p>