Section Access - Summary data for all users

phcaptjim — Thu, 29 Aug 2013 11:45:54 GMT

I'm having an issue with summarizing data within my application that uses section access.

Right now I have implemented data restrictions based on the users Windows AD account. This is working well. What I would like to do is have each user be able to see our metrics summarized for the entire department compared to their own metric values. I've tried created a summary table that is an extension of the main fact table but that was producing incorrect results. Does anyone have experience with this sort of application?

My data model looks like this:

Facts	Security	Employees
MetricDate, MetricID, UserID, EmployeeAD, Department, Division, AppointmentStatus, NewRegistrationFlag, ManagerKey, DirectorKey, Numerator, Denominator	EmployeeAD, SECURITYID	UserID Name Address StartDate ManagerName

Facts

Security

Employees

MetricDate,

MetricID,

UserID,

EmployeeAD,

Department,

Division,

AppointmentStatus,

NewRegistrationFlag,

ManagerKey,

DirectorKey,

Numerator,

Denominator

EmployeeAD,

SECURITYID

UserID

Name

Address

StartDate

ManagerName

SECURITYID is what drives the security. That field is located in the Section Access table along with the ACCESS and NTNAME fields. Basically I need each user to see a summary of their department (manager level) and organization (director level). I have a key for each of those in the Fact table. I tried creating summary tables for manager level and director level data using the AUTONUMBER function but those results were not correct.

Here is the syntax I used:

AutoNumber(MetricDate&MetricID&ManagerKey&Department&Division&AppointmentStatus&NewRegistrationFlag) as ManagerSumKey

Once I created the key I did this:

ManagerSummary:

Load

ManagerSumKey,

Sum(Numerator) as ManagerNumerator,

Sum(Denominator) as ManagerDenominator

Resident Facts

Group By ManagerSumKey;

Again, the results of this were not accurate. Any suggestions here would be greatly appreciated!!!

topic Section Access - Summary data for all users in QlikView

Section Access - Summary data for all users