February 20, 2003 at 6:22 pm
Hello,
I am attempting to set up an MS Excel Pivot Chart in which the data source is a SQL Server 2000 view. I want the .xls file to behave in such a way that the data displayed in the chart is equal to the SYSTEM_USER logged in on the machine. I have done this with MS Access ADP files using connections to SQL Server 2000.
The data displayed in the chart will depend on whether or not the logged in SYSTEM_USER is a school or district administrator. For this, I have a table called 'tblMMAdmins' which contains a field called 'SchoolNum' and one called 'SystemUserName'. 'SystemUserName' holds the equivalent system-supplied value for the system username used by SYSTEM_USER. A record in this table with a NULL 'SchoolNum' means that the 'SystemUserName' for that record is a district administrator. Otherwise, the 'SystemUserName' is a school administrator with the appropriate three digit school code for 'SchoolNum'.
The Business Rule I'm trying to establish, is that district administrators can view any records of any SchooNum, and school administrators can only view records pertaining to their specific SchoolNum.
The first section of code below shows the querry designed for the district administrators so that data from all schools appear in the results:
*************************************************************
SELECT TOP 100 PERCENT
SN.SchoolName, SD.Grade, TD.LastName as TLastName, TD.FirstName as TFirstName,
TT.TestDesc,
ROUND(AVG(CAST(TS.TestScore AS Decimal(8,2))),0)AS Average_Score
FROM tblMMStudentTestScores TS
INNER JOIN Student_Data_Main SD On TS.Permnum=SD.Permnum
INNER JOIN Teacher_Data_Main TD On SD.TeacherID=TD.TeacherID
INNER JOIN tblTests TT on TS.TestShortName = TT.TestShortName
INNER JOIN tblSchoolNames SN on SN.SchoolNum=TD.SchoolNum
INNER JOIN tblMMAdmins TA on TA.SchoolNum=SN.SchoolNum
WHERE TS.TestScore IS NOT NULL
AND TS.TestScore <> 0
AND(TS.TestShortName = 'HMKRL2'
or TS.TestShortName = 'HMKRL3'
or TS.TestShortName = 'HMKRL4'
or TS.TestShortName = 'HMKRL5'
or TS.TestShortName = 'HML1'
or TS.TestShortName = 'HMLM'
or TS.TestShortName = 'HMR1'
or TS.TestShortName = 'HMRM'
or TS.TestShortName = 'RRC1'
or TS.TestShortName = 'RRC2'
or TS.TestShortName = 'SFM1'
or TS.TestShortName = 'SFM2')
GROUP BY SN.SchoolName, SD.Grade, TD.LastName, TD.FirstName, TT.TestDesc
ORDER BY SN.SchoolName, SD.Grade, TD.LastName, TT.TestDesc
*************************************************************
The second section of code below shows the querry designed for the school administrators so that only those school admin SYSTEM_USERS will see their school specific data:
*************************************************************
SELECT TOP 100 PERCENT
SN.SchoolName, SD.Grade, TD.LastName as TLastName, TD.FirstName as TFirstName,
TT.TestDesc,
ROUND(AVG(CAST(TS.TestScore AS Decimal(8,2))),0)AS Average_Score
from tblMMStudentTestScores TS
INNER JOIN Student_Data_Main SD On TS.Permnum=SD.Permnum
INNER JOIN Teacher_Data_Main TD On SD.TeacherID=TD.TeacherID
INNER JOIN tblTests TT on TS.TestShortName = TT.TestShortName
INNER JOIN tblSchoolNames SN on SN.SchoolNum=TD.SchoolNum
INNER JOIN tblMMAdmins TA on TA.SchoolNum=SN.SchoolNum
WHERE TA.SystemUserName = (SYSTEM_USER)
AND TS.TestScore IS NOT NULL
AND TS.TestScore <> 0
AND(TS.TestShortName = 'HMKRL2'
or TS.TestShortName = 'HMKRL3'
or TS.TestShortName = 'HMKRL4'
or TS.TestShortName = 'HMKRL5'
or TS.TestShortName = 'HML1'
or TS.TestShortName = 'HMLM'
or TS.TestShortName = 'HMR1'
or TS.TestShortName = 'HMRM'
or TS.TestShortName = 'RRC1'
or TS.TestShortName = 'RRC2'
or TS.TestShortName = 'SFM1'
or TS.TestShortName = 'SFM2')
GROUP BY SN.SchoolName, SD.Grade, TD.LastName, TD.FirstName, TT.TestDesc
ORDER BY SN.SchoolName, SD.Grade, TD.LastName, TT.TestDesc
************************************************************
The only difference between these two queries is in the 'WHERE' statement;
The condition in the second query expresses the additional 'TA.SystemUserName = (SYSTEM_USER)' where the first code section does not.
I have tried to put togther a query (to set up as a View if possible, otherwise an SP) in which I first set TA.SystemUserName = to SYSTEM_USER. Then, CASE 'TA.SchoolNum' is NULL, THEN run the first set of code, ELSE run the second set of code. The following query attempt to accomplish this generates a syntax error at the 'END' statement:
**********************************************************************
SELECT tblMMAdmins.SystemUserName AS MainSystemUserName,
tblMMAdmins.SchoolNum AS MainSchoolNum
FROM tblMMAdmins
WHERE tblMMAdmins.SystemUserName = (SYSTEM_USER)
AND
CASE
WHEN tblMMAdmins.SchoolNum is Null THEN
(SELECT TOP 100 PERCENT
SN.SchoolName, SD.Grade, TD.LastName as TLastName, TD.FirstName as TFirstName,
TT.TestDesc,
ROUND(AVG(CAST(TS.TestScore AS Decimal(8,2))),0)AS Average_Score
FROM tblMMStudentTestScores TS
INNER JOIN Student_Data_Main SD On TS.Permnum=SD.Permnum
INNER JOIN Teacher_Data_Main TD On SD.TeacherID=TD.TeacherID
INNER JOIN tblTests TT on TS.TestShortName = TT.TestShortName
INNER JOIN tblSchoolNames SN on SN.SchoolNum=TD.SchoolNum
INNER JOIN tblMMAdmins TA on TA.SchoolNum=SN.SchoolNum
WHERE TS.TestScore IS NOT NULL
AND TS.TestScore <> 0
AND(TS.TestShortName = 'HMKRL2'
or TS.TestShortName = 'HMKRL3'
or TS.TestShortName = 'HMKRL4'
or TS.TestShortName = 'HMKRL5'
or TS.TestShortName = 'HML1'
or TS.TestShortName = 'HMLM'
or TS.TestShortName = 'HMR1'
or TS.TestShortName = 'HMRM'
or TS.TestShortName = 'RRC1'
or TS.TestShortName = 'RRC2'
or TS.TestShortName = 'SFM1'
or TS.TestShortName = 'SFM2')
GROUP BY SN.SchoolName, SD.Grade, TD.LastName, TD.FirstName, TT.TestDesc
ORDER BY SN.SchoolName, SD.Grade, TD.LastName, TT.TestDesc)
ELSE
(SELECT TOP 100 PERCENT
SN.SchoolName, SD.Grade, TD.LastName as TLastName, TD.FirstName as TFirstName,
TT.TestDesc,
ROUND(AVG(CAST(TS.TestScore AS Decimal(8,2))),0)AS Average_Score
FROM tblMMStudentTestScores TS
INNER JOIN Student_Data_Main SD On TS.Permnum=SD.Permnum
INNER JOIN Teacher_Data_Main TD On SD.TeacherID=TD.TeacherID
INNER JOIN tblTests TT on TS.TestShortName = TT.TestShortName
INNER JOIN tblSchoolNames SN on SN.SchoolNum=TD.SchoolNum
INNER JOIN tblMMAdmins TA on TA.SchoolNum=SN.SchoolNum
WHERE TA.SystemUserName = (SYSTEM_USER) and TS.TestScore IS NOT NULL
AND TS.TestScore <> 0
AND(TS.TestShortName = 'HMKRL2'
or TS.TestShortName = 'HMKRL3'
or TS.TestShortName = 'HMKRL4'
or TS.TestShortName = 'HMKRL5'
or TS.TestShortName = 'HML1'
or TS.TestShortName = 'HMLM'
or TS.TestShortName = 'HMR1'
or TS.TestShortName = 'HMRM'
or TS.TestShortName = 'RRC1'
or TS.TestShortName = 'RRC2'
or TS.TestShortName = 'SFM1'
or TS.TestShortName = 'SFM2')
GROUP BY SN.SchoolName, SD.Grade, TD.LastName, TD.FirstName, TT.TestDesc
ORDER BY SN.SchoolName, SD.Grade, TD.LastName, TT.TestDesc)
END
**************************************************************
I'm trying to avoid a Stored Procedure for this, because I don't want the user to possibly have to deal with entering a Parameter to have the Pivot Chart data show up.
I hope I have given enough detail to spell out what it is I am trying to do. Thanks for your help!
CSDunn
February 21, 2003 at 2:47 am
Very quick answer off top of my head and not tried or tested, but I would use your first query and add the following
INNER JOIN tblMMAdmins XX ON XX.SystemUserName = (SYSTEM_USER) AND (XX.SchoolNum IS NULL OR (XX.SchoolNum IS NOT NULL AND XX.SchoolNum = SN.SchoolNum))
Another thought, probably could replace
INNER JOIN tblMMAdmins TA on TA.SchoolNum=SN.SchoolNum
with
INNER JOIN tblMMAdmins TA ON TA.SystemUserName = (SYSTEM_USER) AND (TA.SchoolNum IS NULL OR (TA.SchoolNum IS NOT NULL AND TA.SchoolNum = SN.SchoolNum))
Edited by - davidburrows on 02/21/2003 02:53:31 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply