Selective Access on Drill Through Transaction Detail

OSUBlakester

Hi,

I have created a module that contains detailed actual transaction data.   The module is based on a transaction ID list and properties like month, account and cost center (and other details).

 

That module feeds into another module for reporting, using the SUM function.  

 

I'm able to Drill Through to Transaction Level detail from the reporting module.  It seems to work great.  However, if I drill through at a parent account or cost center level, the filters don't work correctly.  

 

Also, I have Selective Access set up for the Cost Center lists.  When working on the report module, users are only able to see their Cost Centers.  However, if they Drill Through at a Parent Cost Center, because the filter fails, it shows ALL Cost Center transaction detail.

 

How can I:

1) Fix the Drill Through to Transaction Detail to detail with Parent Accounts and\or Cost Centers

2) Limit the Transaction Detail to only the Cost Centers that have Selective Access

 

Thanks

nathan_rudman

Hi,

 

1) unfortunately drill through transaction only works at the lowest level

If really important, you can provide a "customer drill to transactions by:

- giving the users a customer filtering dashboard (easier but require the user to do the selection)

- rebuild the transactions as children of your reporting hierarchy so users can easily access them (harder and consumes space/performance)

 

2) The transactions having the cost center property, you can easily know if a user should read this transactions:

- You can use Dynamic Cell Access to hide the values and a user filter to hide the lines

- Or you can activate selective access on the transactions themselves, calculate the right access by user/transaction (using the system User list) in a module and use that module to import into the User Access see here

 

DavidSmith

@OSUBlakester 

I agree with @nathan_rudman 

This is actually the recommended approach.  Having loads of transaction data in your planning model can have a significant impact on model opening and calculation efficiency, so we recommend keeping this data outside of the main planning model (in the Hub, or a separate reporting model). 

Then, as Nathan suggested, provide the users with a set of filters.  It actually is much more powerful as this approach allows for multiple filter criteria rather than a single intersection from a drill down

David

OSUBlakester

Thanks.  Looks like #2 "a" or "b" would probably be my best options.   Going to give them a try. 

 

Still not exactly sure how to filter on the rows based on the Cost Center value in each row.  

nathan_rudman

I've done a lot of transactions filtering dashboards recently.

 

You'll need a module with just one dimension (called User Filter), the user list and a few line items formatted as by what you want to select.

Then in your transaction module, create line item with the user dimension + the transaction dimension and put a formula like:

    IF ISBLANK(User Filter.Cost Center) then TRUE else  User Filter.Cost Center = Transaction.Cost Center

You can can one line item like these for each condition (date, account, etc) and then gather everything into a Filter - Final that is:

   Filter 1 and Filter 2 and .... Filter x

and use that one to filter your module.

OSUBlakester

Thanks so much for following up an being patient.

 

I think I'm really close, but I must be missing something.  I'm just setting up the Cost Center filter.

 

I created a Module named Cost Center filter with the Users and Cost Centers and a boolean Item named PU Read Access.  I set the value to True for 1 Cost Center for my User account

 

I created another Module named TX Filter (probably a little overkill, but helps me visualize) with the Transaction IDs and Users.  It has a boolean Item named TX Read Access that refers to the PU Read Access item with a LOOKUP using the Cost Center.  This seems to work great.  The True values are assigned to the right TX items with the 1 PU.

 

Now my issue is applying this to the Transaction Properties Module that contains all of the properties like Cost Center, Account, Period, Amount, etc.  I tried creating an Item named Read Access that refers to the TX Read Access Item using the TX List and Users.  That seems to work ok, but I can only see the values if I use the Subsidiary view.  I can't see any other Items in that view.

 

How can I see all of the items?  

Also, once I get this working, how does this know who the current user is?

 

 

nathan_rudman

you have done the hard part, building the logic of the filter.

Now you need to APPLY it. 

When you are seeing the module (not blueprint), click on Data > Filter. This will open a window.  You should see one tab per dimension you have, so apply the filter by using the line item you created on the transaction dimension (click on the +)

OSUBlakester

I swear I tried that earlier and it didn't work, but it's working for me now.  I was originally thinking that this would be a "default" filter for drill-through, but looks like that's not the case.  However, this gives me another idea on how to show the transaction detail on in the dashboard.

 

Thanks again!

nathan_rudman

filters are very sensitive to what you have selected in the dialog box, sometimes we mess them up !