1 Reply Latest reply on Nov 21, 2013 2:30 PM by Michael Evans

    Look through a grouping of data and calculate a field based on the entire groupin

    Shadi Hussein

      I have a list of employees and their projects.  The projects are broken down in column by their status (Assigned, Engaged, Completed, or Not Assigned, Engaged or Completed).  How can I create a filter/parameter to show only employees that are either on a project that's Assigned or Engaged OR employees that are not on any Assigned or Engaged project.  For whichever option the user chooses, I want the details to show (i.e. all the projects even completed)

       

      For example, in the attached example,  if the user selects Not Assigned or Engaged, then show employees like Jose Malsip, John Havesham, and Ismael Soliday.  If the user selects Assigned or Engaged, show employees like Zona Hildreth, McKinley Jang, and Inger Dimick.  Even though they have completed projects, they are assigned or engaged to other projects.  I also want all their projects to be displayed, including the completed projects.

       

       

      I've been trying to rack my brain to figure this out.  I was thinking of creating an indicator in the database where the data is being pulled from.  However, the user can change a data parameter which will change the statuses of the project.  Any ideas will be most appreciated.

        • 1. Re: Look through a grouping of data and calculate a field based on the entire groupin
          Michael Evans

          Hi Shadi,

           

          I read through your question several times.  Hopefully I understood this correctly.  There may be a more efficient solution, but I think this accomplishes what you want.  I've attached my solution.  Please let me know!

           

          1) Create calculated measures like this:

          CASE [Assigned]

          WHEN 'X' THEN 1

          ELSE 0

          END

           

          2) Create a parameter selection with the two options you'd like. [Selection]

           

          3) Create a calculated field to associate to your parameter selection.  [Label for Has Assigned or Engaged] = [Selection]

           

          4) Create a calculated field to label your resource.  [Conditional Resource Name]

          IF [Parameter Calculation] = true

          THEN [Resource Name]

          ELSE ' '

          END

           

          5) Drag [Project] and [Conditional Resource Name] to Rows, and your Measure Names to Columns.