Data Reduction/Section Access - data access/presentation issue

[#1]

I implemented Section Access so that the salesperson can only see his/her sales.  We use SALESNO (Salesperson ID)as the field to reduce access.

We have sales that are split sometimes up to 4 ways.  Data for a sale might look like this

OrderNo	SalesNo	Percent	Sales $	Sales Credit
1	001	100	1000	1000
2	001	50	1000	500
2	002	50	1000	500
3	002	25	2000	500
3	004	50	2000	1000
3	007	25	2000	500

Problem:  With Section Access, in the split sales the Salesperson would only see his/her portion of the sale.  This is fine if you are analyzing Sales by Salesperson.  

What if you are analyzing Customer Sales or Product Sales? The salesperson will only see their percentage contribution to that sale.   In this example, we would want sum up the complete sale regardless of salesperson while still restricting access

[adamdavi3s]

Hi Cam,

I had a similar requirement and it is not easy to deal with.

In the end I created a dummy field which is a copy of SalesNo but NOT linked to it so say just a field called 'Access Code'

Then don't use the data reduction option, but use set analysis like:

SalesNo={concat(Access Code,',')}

(Sorry I don't recall the exact syntax off the top of my head)

Its a proper faf, but at least it then allows you to do the kind of comparitors that you want.

[avinashelite]

one way to handle this is , create a separate which is disconnected from the model and use this table for the customer sales analysis ...

[#4 · Accepted solution]

I reworked my data model and made a new table with KEY and SalesNo as SECLINK - this separated from the Percent calculation appears to have done the trick.   Not sure why i was overthinking it?