3 Replies Latest reply on Feb 22, 2012 7:16 AM by dana.komar

    Can a parameter be used to call a variable in a calculated field calculation?

      Hello all,


      Here is my overall question: Can a chosen parameter value indicate what variable is used in a calculated field calculation?


      Here is the situation around my question: I have a sales data aggregated by customer and date (month/year) and fields that have the number of transactions the customer had at each of 7 venues for that month/year. Each venue has its own column, so it is a cross tab of my original transaction data set pivoted on venue. I would like to set up a table that allows the user to select a venue, and then will return the a count of the number of customers that had transactions at that venue and the sum of transactions those customers had at ALL venues. My plan is to use a parameter field to allow the person to choose a venue, then make a calculated field to be used as a filter to keep records that have a non-null value in that venue's value field, then sum up the filtered transactions for all 7 venue value fields and do a distinct count on the customers. So the calculated field to keep records would be:


      If Isnull([venue1]) = False Then "Keep" Else "Drop" End


      But I want the parameter chosen to stand in for the venue. So if they choose the parameter to be Venue3, the calculated field would be


      If Isnull([venue3]) = False Then "Keep" Else "Drop" End


      Is this possible? Thank you in advance for your help!