title: Dynamically Reporting or Adding fields on Crystal Reports
body: <p>I have a very poorly designed back end system which I cannot change.  To keep it simple for the purpose of this question, the data looks like this:</p>
  
  <blockquote>
    <p>VendorNumber,
    ItemNumber,
    PONumber,
    DocumentNumber,
    PiecesToDistribute1,
    PiecesToDistribute2,
    PiecesToDistribute3,
    PiecesToDistribute4,
    .....
    PiecesToDistribute300</p>
  </blockquote>
  
  <p>The number at the end of PiecesToDistribute## refers to store numbers.</p>
  
  <p>I need to show in the report pieces to distribute to each location where the field is not 0 but at 300 potential columns wide I'm struggling for ideas to make this work.  I attempted to do a sub report with a 300 line union as a command table (ok, I tried it only with 5 lines first) putting paramaters in the SQL but the report wizard "link" screen does not seem to give me parameters from SQL as options to link to.</p>
  
  <p>I do have a table with my store numbers (which may change over time).  </p>
  
  <p>Is there any way to dynamically select the field (even by iteration) based off a link back to the table with store numbers?  e.g. dynamically build the name of the field  (similar to Excel's indirect() function).</p>
  
  <p>I basically want my output to be:</p>
  
  <pre><code>ItemNum     Description Item                         Cost      Retail
  ABCD1234    Some Widget My Company Has Here          25.00      35.00
             Loc 1:   4             Loc 7:  3
             Loc 2:   3             Loc 9:  8
             Loc 3:   2             Loc 16:  5
             Loc 4:   2             Loc 98:  8
  </code></pre>
  
  <p>If there was a separate table with the distribution lines this would be a non issue, but as it is now, I'm a little stumped.   Immediate solution I can hard code this for only active stores, but that is not maintainable long term.</p>
  
  <p>What am I missing or any good ideas?
  (modifying the back end system is not an option;  I doubt that the vendor of the purchasing system will change their schema any time soon, it has probably been this way for 20 years)</p>