Help with supressing employee name within a bar chart (stacked)

[#1]

Hello all,

I have a bar chart that I created that looks at two things: employee name and time it takes to complete a stage of the project. There are four stages to the project that I have combined to form a stacked bar chart.

My question today relates to help on supressing certain employee names within my chart. There are several employees that I would wish to exclude from showing the results within this chart.

Can anyone help with this?

My Script within the chart expression that combines my stages is:

=num(if(IsNull(avg({$*<ProjectType={'StageB}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={' StageC '}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +
if(IsNull(avg({$*<ProjectType={' StageD '}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={' StageD'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +
if(IsNull(avg({$*<ProjectType={' StageA '}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={'StageA'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)) +
if(IsNull(avg({$*<ProjectType={'StageC'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), 0, avg({$*<ProjectType={'StageC'}, [Milestone.Flag]={'0'}, [Project Status]={'Actual'}>} ProjectTime)), '#,##0')

Regards,

Myles

[agomes1971]

Hi,

maybe using section access

section access and application access

HTH

André Gomes

[giakoum]

I would create a new field in my reload script and use this as a dimension in the chart instead of the employee. The employees you need to eliminate could have a null value in that new field :

Employee     EmployeeNew

John     John

George null

Sam     Sam

Maria     null

[#4]

Thanks for the response Ioannis.

Is there a direction you could point me in to help with this?

[giakoum]

An IF statement in your reload :

load

...

[Employee name],

if(condition, [employee name], null()) as [New Employee Name]

.....

resident [YourTable]

If you can post the script and a condition to determine who should be shown and who not, I could be of more help

[#6]

Thanks for the quick response.

Before I get started wouldn't this exclude these individuals from all of my reporting? Or is there a way to only apply this to my one chart? I ask, because I wouldn't want to exclude these people because I have several other objects that they should be reported on.

Regards,

Myles

[giakoum]

There are 2 fields to use if you implement this solution. Use any one in your charts according to the requirements, Employee as dimension when you need all employees and New Employee as a dimension if you only need the reduced ones.

[#8]

Okay, so that works well for what I would like to do.

Now the only thing is I don't exactly have a condition. It's essentially I do not want to include x, y and z staff members.

Must there be an if statement? Or rather, could I simply request to only show/exclude the following individals a, b and c.

[giakoum]

an if statement with condition : if(employee='x' or 'y' or 'z', null(), employee) etc.

[#10]

Okay Ioannis I think I better understand the approach. You mentioned above I could make this much easier for you to help if I had a script to show you. And I assume in reference to how I currently get the Employee Field.

Let vPath='$(RootFolder)Live Data\Project Details';

[Project Details]:
LOAD [Project Number],
     replace(mid([Project Number], 1, 3), '-', '') as [Project Type],
     [Project Description],
     [Rec Number],
     if(   replace(mid([Project Number], 9, 4), '-', '')='PPJ'
     , 'New Initiative [OOSys]', [Business Division]) as [Business Division],
     Category,
     [Stakeholder Rep],
     [Reck Forwarded to Dept],
     [Employee Assigned To],
     [Employee Assigned To Date],
     [Project Created Date],
     [Project Coordinator],
     [Estimated Value of Proj],
     [Success Value],
     [Priority],
     [Project Status],
     Date(Date#(replace(filebasename(), 'Project Details - ', ''), 'MMMM DD, YYYY'), 'M/D/YYYY') as [Project Details.Load Date],
     Date(Date#(replace(filebasename(), 'Project Details - ', ''), 'MMMM DD, YYYY'), 'M/D/YYYY') as [Load Date%]    
FROM $(vPath)\*.xlsx
(ooxml, embedded labels, table is Sheet1);

My Condition would be for the only field that I'm focusing on in the above being Employee Assigned To. The condition itself would be as you mentioned above, but to lament it in my a real world term:

=if(Employee Assigned to='David O' or 'Ricky B' or 'Bobby V', null(), employee)

Regards,

Myles