Unique Count of Employees

[Gowtami]

Hi All,
I have a very important and very very urgent requiremnt at my work, I would be really  grateful if someone could give me a solution for this.

I have an issue with payroll project which we are counting the employees of each pay period in different locations.
As of now everything is fine with the report except employee counts.
I have a value filter with multi select option to select the pay periods.
Right now system is giving the 'sum of employees for the selected pey periods' as 'summing up all payperiods employee counts'.
But my requirement is when i select multiple pay periods it should give me the unique employee counts, means most of the employees (like permanent staff) will be repeated in the pay periods, i should count them based on their unique Employee number for all selected pay periods.
I'm using OLAP cube, Cognos 10.

Thanks in Advance
Gowtami

[CognosPaul]

Something like this depends on how the cube is set up. What type of cube is this, SSAS, Essbase, Powercube?

How exactly are you using the multiselect filter? Are you showing the individual periods in a crosstab? Are you using a detail filter?

How are you showing the total, with something like total(currentMeasure within set [Pay Periods])?

Try changing the total to aggregate.

[Gowtami]

Thanks for the reply Paul.

We are using powercube, and is a simple list report. I have a value prompt to select multiple pay periods wich is having cascade source to Regions, and using a detail filter. This is the main issue here. We are not using pay-period in time dimension, this data item is a level under Regions in Powercube because of the requirement of cascade source functionality.

This is not a crosstab report. I have a flat list report and it displays all selected pay periods Regions, employee names, locations and the measure values. I used Total to summarize the measures. (here i have tried aggregate, count distinct and few other trials, nothing worked).

Any ideas!!

[CognosPaul]

OLAP sources naturally create crosstabs. The same way that a relational source needs to do extra processing to create a crosstab, an OLAP source needs to do extra processing to create a list. Lists can also cause issues relating to context and comparitive calculations. I'd recommend switching to a crosstab, even if it looks the same as a list (with pay periods, regions, and so on in the rows and measures in the columns).

In transformer, create a new employee dimension, create a measure set to category count based on that dimension, hide the dimension. The category count will return the unique count of employees. Unfortunately, PowerCubes are notorious for their poor support of count distinct. If you have more than a few thousand employees, a different method for counting them will be needed. One of my clients tracks national road accidents using SSAS, the main measures are a distinct count on accident id and involved id.

Get rid of the detail filter. OLAP sources work with slicers and you can specificy specific sets.

The cascade requirement sounds fishy. If I understand, you select a pay period, and are different regions appear under based on the pay period? This is bad practice, go back to separate dimensions. Instead of using a cascade filter, filter the regions on a tuples measure.

So the prompt for regions would have a data source like:
filter([Regions Level],tuple(#prompt('Pay Period','mun')#,[Salary])>0)

OR (when selecting multiple pay periods
filter([Regions Level],total([Salary] within set set(#promptmany('Pay Period','mun')#))>0)


That will return all regions in which the salary was greater than 0 for the specified pay periods. Additionally, if you are using regions as children of the pay period, they will repeat when selecting multiple pay periods. (And I think I just realized why you're using a list.) You would then use the same prompt macro in the slicer to filter the query on the pay period.

The summary functions on the data items will generally not work in an OLAP environment. They are instructions to the relational engine. Cognos will use the aggregate functions when you use local processing, which is not recommended because local processing is evil and will make your friendly neighborhood Cognos admin turn red and spit fire.

[bdbits]

"...will make your friendly neighborhood Cognos admin turn red and spit fire."

Really? Cool! I gotta try this as soon as he gets back.  8)

(Not trying to hijack the thread, I just couldn't resist.)