Restricted Views
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21499
Printed Date: 05 May 2024 at 8:17am
Topic: Restricted Views
Posted By: ssiah91
Subject: Restricted Views
Date Posted: 21 May 2015 at 7:32am
So I have this report which looks something like this
gh1 regionname | 506
gh2 marketname | 193
gh3 branchname | 12
d 1
d 1
d 0
and so on...
each region, market, branch is assigned to a manager login, so in the select expert it is something like this:
{RegionManager} = CurrentCEUserName or
MarketManager} = CurrentCEUserName or
{BranchManager} = CurrentCEUserName or
CurrentCEUserName = 'administrator'
administrator can see all the data, but each manager can only view the detail rows that are affiliate to them.
The problem now is when I ran it using a market manager login, the detail section did only show the rows that are related to the market manager, but the groups are not giving me the sum of rows that are related to the market manager.
Anyone has an idea on how to solve this?
p.s runningtotal does not work in this case because my sums must be placed on the report header.
|
Replies:
Posted By: hilfy
Date Posted: 21 May 2015 at 8:52am
Do you have this in the formula of the Select Expert or in the Suppress Formula on the section where the data is displayed?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ssiah91
Date Posted: 21 May 2015 at 8:54am
Hey Dell,
I am using the formula in the select expert.
|
Posted By: hilfy
Date Posted: 22 May 2015 at 3:24am
Is that all that's in the select formula or do you have other criteria as well? If you have other criteria, you should either add parentheses around it like this:
(
{RegionManager} = CurrentCEUserName or
{MarketManager} = CurrentCEUserName or
{BranchManager} = CurrentCEUserName or
CurrentCEUserName = 'administrator'
)
Or change it to something like this:
CurrentCEUserName in ({RegionManager}, {MarketManager},{BranchManager}, 'administrator')
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ssiah91
Date Posted: 22 May 2015 at 4:07am
Thank you for the reply.
Yea that is all it is in the select formula, basically what it does is to filter (not suppress) the details so that it will only show the rows that are related to the manager login.
The formula I have is doing the job at the detail section, but the group levels are still summing all values of all manager, instead of that specific manager.
It is weird because i applied the formula to all 3 select experts (records, group, saved data). Somehow CR still summing up all the values, regardless if it is related to the manager or not.
|
Posted By: hilfy
Date Posted: 22 May 2015 at 4:52am
Are you using summaries for your sums or do you have them in formulas? If formulas, please post the formula.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ssiah91
Date Posted: 22 May 2015 at 4:58am
I actually tried both ways.
1. I just right click the detail value and insert a summary.
2. I used COUNT ({detail value}, {branchname}) , i also dd it for other group levels too.
But both still sum up all the values even when I ran it where CurrentCEuserName as one of the managers.
|
Posted By: hilfy
Date Posted: 22 May 2015 at 5:03am
Ok, let's break this down a bit. For the part where your summary is a count, try using DistinctCount instead of Count. If that gives you the correct number, then the issue is not that it is counting other managers, it's that there are multiple rows for each item for the current manager.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ssiah91
Date Posted: 22 May 2015 at 5:12am
I am sorry, maybe I did confuse you there a bit. So the detail part can only be 1 or 0 .
So for example this is the full table (full access to view):
Manager Total
branch group 3
detail John 1
detail Mike 1
detail Jason 1
So if I run it with John's login, I am supposed to only get sum total for branch level to be 1:
Manager Total
branch group 1
detail John 1
but instead I got this sum is 3:
Manager Total
branch group 3 <-- sum is still 3
detail John 1
I am sorry if I caused any misunderstanding.
|
Posted By: hilfy
Date Posted: 22 May 2015 at 6:25am
This still has to be an issue with the data - if you have the filter in the Select Expert, Crystal won't even pull from the database the data that doesn't match the filter.
If you go to my website (www.dellstinnett.com) you can get my email address. If you send me the .rpt file after saving it with data, I'll take a look at it and see if there's anything obviously wrong.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: ssiah91
Date Posted: 22 May 2015 at 6:33am
|
I sent you the .rpt file. Thank you so much!
|
|