condition on the dimension field in a table

[#1]

Hi All,

I have a table as follows:

ID	Emp ID	Emp Name	Sales
1	1000	John	100
2	1001	Peter
3	1002	Mark
4	1000	John
5	1001	Peter	1000
6	1002	Mark	1000
7	1000	John	500

I want to create a table which will display only the 'Emp Name' which has a value in the field 'Sales'. Basically i want the output as follows:

Emp Name	Sales
John	100
Peter	1000
Mark	1000
John	500

Your help is much appreciated. Thanks.

[#2]

I may not be able to post my script. But my Department and Employee_Sales tables are as follows:

Department ID	Department Name	Region
1	Sales	NA
2	Engineering	SA
3	Marketing	NA
4	Admin	SA
5	HR	NA
6	Operations	SA
7	IT	NA

ID	Employee ID	Emp Name	Department ID	Sales
1	10000	John	1	100
2	10001	Peter	1
3	10002	Mark	1	100
4	10003	Jack	2	100
5	10004	Mary	3	300
6	10005	Irina	4	200
7	10006	Amy	4	200
8	10007	Rich	5
9	10008	James	6	500
10	10009	Pierre	7	600
11	10010	Shaun	7	100
12	10011	Susan	7

I want the straight table to display data of only those employees who belong to the department of region 'NA' and have data in the 'Sales' field. My output should be as follows:

Department Name	Employee Name	Sales
Sales	John	100
Sales	Mark	100
Marketing	Mary	300
IT	Pierre	600
IT	Susan	100

This is the exact problem statement. Your help is much appreciated, Thanks.

[ankitaag]

Hi Ramya,

Please write your calculated dimension as

=if(Region='NA',[Department Name])

and check the suppress null value box.

The result will be

For more reference pfa the application.

Thanks and Regards,

Ankita

[jonathandienst]

Create the straight table like this:

Dimensions: Department Name, Employee Name

Expression: Sum({<Region = {'NA'}>} Sales)

Make sure suppress zeroes is checked

HTH

Jonathan