Count of Multiple Columns via Pivot

[tommyclearyuk]

Hi All

Hope you're all well.

I need a bit of help.

I have a data set where I have a list of Servers (multiple values) in Row Labels, and a list of Violations Types (x4) in the Column Labels,in addition there is a username Column and a Platform Column.

I need a report preferably via a Pivot Table that will perform a count of all Violations for each host.

However, each host can have multiple Violations so this needs to reflected in the report.

The Column Names for the Violations are named as follows:

CyberArk Violation (Values within the Column are either 'CyberArk Violation' or 'N.'
Position Type Violation (Values within the Column are either 'Position Type Violation' or 'N.'
No User Info Violation (Values within the Column are either 'No User Info Violation' or 'N.'
Request Violation (Values within the Column are either 'Request Violation' or 'N.'

N = No Violation - I'm not concerned about these so do not require them to be present in the report.

So ideally I require similar to the following format including the count:

Col A, Col B, Col C, Col D, Col E, Col F
Server Name, CyberArk Violation, Position Type Violation, No User Info Violation, Request Violation, Totals

In addition, if possible, is there a way I could break this down further in a presentable way, there is another Column which confirms the Platform i.e. Windows, Unix, SQL, Oracle, can this be included in a separate report i.e. the same report but embed the Violations Types within the 4 different Platforms so the audience can see for instance, a count of CyberbArk Violations by Platform without using a slicer ideally?

Finally, there is another field named username, I require a distinct count to be applied so that I can confirm the number of unique users in the Pivot - I assume I just apply this via the field settings distinct option on the Username field in Values section of the Pivot Table dialogue?????

Many thanks in advance,
Tom

 

[Panda514]

Hello,

Instead of using words like 'CyberArk Violation' or 'N' I would use 1 or 0 that way you can show the sum in the pivot table. Then you can add a Grand Totals field, by adding all the types of violations together. This will give you the distinct count that you need per person. I am not aware of how to make sub columns for each violation in the pivot table. It would be great to find a solution for this! I usually make the table how I want it to look then use multiple pivot tables to get the date I need. Time consuming...