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
if (
count (
distinct [empStatus]
for [empID]
)
=2
)
then ('both')
else ([empStatus])
Thanks hespora. It works. Just curious if any other possible method is available?
QuoteJust 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.
Quote from: satishfy on 15 Feb 2017 06:40:19 AM
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.