New dataitem with Lookup for each grouped value

[satish217_1990]

Can someone provide your inputs on how I could achieve the below requirement in Report Studio?

Existing:

EmployeeID Status
123              Active
123              Inactive
456              Active

Need it like this: (If an EmployeeID has both Active and Inactive then only one row should appear and value should be 'Both')

EmployeeID   StatusNew
123                 Both
456                 Active

[hespora]

Code Select Expand


if (
  count (
    distinct [empStatus]
    for [empID]
  )
  =2
)
then ('both')
else ([empStatus])


[satish217_1990]

Thanks hespora. It works. Just curious if any other possible method is available?

[Michael75]

Just curious if any other possible method is available?

Sure. Just ask your DBA / ETL guy or gal to include this logic when they populate your datamart. You'll have a simpler & better-performing report.

[AnalyticsWithJay]

Thanks hespora. It works. Just curious if any other possible method is available?

There are less efficient ways, but hespora's solution is the ideal and common approach for relational structures. You can define custom groupings when working with dimensional data.