Filtering an Hour or Expense Report by Users Company ID

[JohnHo]

Hello all, I have a dashboard that my customers access. The dashboard has reports on it that show time and expenses associated on a project. The dashboard is accessed through a single project view click through. Because it is a single project view, Workfront automatically only shows the expenses and invoices associated with that project. However, if the customer clicks on the link to show them the entire report, it shows all data. Since these folks have an access level of Reviewer, I set the report to run with the access rights of me. I put a filter on the report to only show rows where the Project > Company ID = $$USER.companyID however, when I log in as that user it does not return any rows. Both the project and the user are tagged to the same company. I was wondering if anyone was able to get this to work. Is it b/c I have set them up to run as "me" (so they can read the hour and expense objects) or is there something else wrong with my filter. Any thoughts appreciated. John Hoebler Cross Country Consulting

[NRYNR]

Hi John, When you do the following " I put a filter on the report to only show rows where the Project > Company ID = $$USER.companyID " and run the report with the access rights of you, the $$USER.companyID returned will be your company ID, not the company ID of the logged in "reviewer". I'm pretty sure this is what's going on. You might have to create a dashboard for each customer and run with your access rights in order for them to see the hours data. Narayan Raum Senior System Admin, Solution Architect & Delivery Lead of Workfront. Service & Business Manager. COE Lead. AVP Enterprise Data Governance Execution (EDGE) SunTrust Bank

[JohnHo]

Hi Nayarn, Yep - I just tested that out and that is what it is. Good call. I changed my company ID to one of my customers and logged in as them and it worked. So my pickle is that I need to be able to run as me (so they can see the objects in general) but want to restrict them from seeing everyones data if they click on the full report. (vs. looking at the dashboard in the single project view). I already took away the REPORT navigation link from the Global navigation, so they won't be able to go to REPORTS and see their report list. They can still get to it though if they click on the hyperlink for the report name that appears on the dashboard. Do you have any ideas on ways to prevent them from running the report outside of the content of the dashboard (e.g. don't run reports unless they are on the dash or a way to disable the hyperlink for the report name that appear on the dash). For your suggestion - I assuming you are saying create versions of the report for each customer where the filer = the customers specific name - that is a possibility however not practical given I have over 100 customers. Am I understanding your question correctly? John Hoebler Cross Country Consulting

[JohnHo]

Your dashboard for every customer got me thinking though - if I put a prompt on the report - customer name, and I access the dashboard, when the dashboard comes up the prompt only shows me companies I have access to as my customer (even though I am running the report as me) so they cannot select any other project that they don't have access to - this seems to be a workaround although not a great one. THanks for the nudge, but if anyone else has ideas, that would be great. Thanks again. John Hoebler Cross Country Consulting

[NRYNR]

Yes, I thought about a prompt at first. However, when running the report as "you", each customer would be able to select another customer and see all their data, which I can't imagine you'd want. If you do go the route of multiple dashboards, make sure that your Views, Filters and Groupings are managed through the admin interface and then applied to the report using the "Apply an existing Filter, View or Grouping" option in the report builder. This way, when you make changes, you can open the reports and re-apply the settings instead of manually changing each report at the detailed-level.

[JohnHo]

Interesting enough, if I do the prompt and then say where project name =, it then lists only the projects associated with the user I am logged in as (which is good). Since I am on the single project dashboard, I can also run the report with the prompt - All (or any, can't remember the syntax) and it only shows me that dashboard. However, if I click on the report URL in the dash header, it takes me out of the single dashboard view and runs the report globally and I can run the report for all and see all of the data again (which is bad). I really need a way to not let the user run the report wide open (force them to select project name from the prompt) or not click on the report hyperlink to open the report outside of the single project view. John Hoebler Cross Country Consulting

[Doug_Den_Hoed__AtAppStore]

Hi John, Glad you got it going (and well deduced, Narayan). Now that you have the (desired) limitations imposed via the requestor's company clear, I invite you to take the final leap of faith: Remove the prompt. Although it is reassuring and helpful for you (noting that you can always keep it in a private copy of this report), it is unnecessary and (mildy) cumbersome for your clients. Assume that Workfront's Security will always automatically restrict the data to only what the logged in user is allowed to see. And to double check, which is the best way for you to establish the high confidence I enjoy, use the Setup > Login As feature to confirm what your customers will see and can do by impersonating them. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[Doug_Den_Hoed__AtAppStore]

Ah; and a post script, John: I suggest you also group the report by Project name. In normal mode, doing so neatly states the current Project at the top (which is also handy if exported to pdf or Excel, especially if you wisely omitted the Project name in your report view to save space), but also logically organizes the report data into its respective Project if a user does "inadvertently" run the whole report for all the data Workfront allows them to view. With these two workarounds, you can then stop struggling to lock down Reviewer Licensees navigational loopholes. Besides: that even if you could "https://writingexplained.org/idiom-dictionary/finger-in-the-dike-mean">finger-in-the-dike all of them, another could spring in a future release anyway. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

[JohnHo]

Hello again Doug, My current issue is that for the Reviewer to see the report, I need to have the report run as me (a higher level access) as it is my understanding that reviewers cannot access the expense object or the hours object via reporting. I can control what they can see on the dashboard b/c it is a single project dashboard, however, they can still click on the URL that is the report name that pops them out of the single project view and runs the entire report (and they see all of the data as they are running as me). I liked you suggestions but I am not sure either will solve this problem. John Hoebler Cross Country Consulting

[Doug_Den_Hoed__AtAppStore]

I stand corrected, John: right you are. My apologies. To recap, then, my understanding of the alternatives you face (leaving it with you to weigh the cost benefits) are: (Doug's) run as is, relying on each user's security to restrict to the correct data, grouping by Project in case they click the report title and run it for "all"....but each client would need a Worker license (Narayan's) Run As "you" with a prompt on Company which (fortunately) does restrict to the Requestor rights when run under a Project...but leaving all other client data exposed if they click the report title (Duplication) copy the reporr for every company, hard coding the filter on each, and sending that report out (possibly via a Share as Public url) to each client...noting that for 100+ clients, the manual effort to set them up and send them out is not insignificant, not to mention the maintenance effort involved in then changing the view (etc) later when business requirements change (Custom) consider our "http://store.atappstore.com/product/magic-reports/">Magic Report solution, which we could tailor to suit and ensure the data is protected (even using Requestor licenses)...but comes with some assembly required (Longshot) continue to look for some other solution that will meet your requirements within Workfront's existing reporting feature set (eg Run As "you", but adding Smart Formatting on every column in the report to only show the data if the logged in Requestor's Company matches the Project's Company, and if not, shows Null)...noting that I just thought of that option now, have never tried it, and that it might not be possible Pros and Cons abound. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads