I have a table called Goals. I create a record each month with two goals, LYGoal and GSGoal, for each location. I calculate the number of days left in the month and the month-to-date sales. Then the goal target for the day is calculated for both LY and GS.
Most of the fields in the Goals table display correctly. My problem is that three fields display perfectly for me (logged in with full access), but non-admin users see ?. That makes me think it's a security issue, but I'm also wondering if it's because MTDSales is an ExecuteSQL calculation. MTDSales is also used in the other two fields.
For non-admin users, the Goals layout is set to View Only. The fields are set to View Only. I've tried setting the fields to modifiable with no change. The field "SalesTotal" in the Performance table (used by month-to-date sales) is set to View Only.
The calculations look like this:
SELECT sum(SalesTotal) AS Sales
Loc = ?
AND SaleMonth = ?
AND SaleYear = ?
GROUP BY loc
If ( DaysLeft = 0 ; MTDSales - LYgoal ;
If ( LYgoal > MTDSales ;
Round ( (LYgoal - MTDSales)/DaysLeft; 2)
; "0" )
If ( DaysLeft = 0 ; MTDSales - GSgoal ;
If ( GSgoal > MTDSales ;
Round ( (GSgoal - MTDSales)/DaysLeft; 2)
; "0" )
[Purpose of the IF statements -- When looking at previous months, I want to display the difference between the Sales and the goal. If Sales have exceeded the goal, today's target is zero.]
Does ExecuteSQL have security ramification? Or have I just missed something in my security settings?