Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 data masking

Author  Topic 

bel30
Starting Member

4 Posts

Posted - 2012-11-21 : 08:20:26
I have two reports. i have created a view to fill one of the reports which has for example say: teacher name and student ID.

To fill the second one which has all the student details columns like the subjects he has taken, his marks, teachers column which has as many as 5 or more teachers names whose subjects he has taken. this report will be sent to all the five teachers. In the report sent to the teacher whose name is sharon. Sharon should see only her name in the teachers column. the rest of the names Should be masked. Also say for example if he has teacher John teaching the student two subjects, then he should see the dummy value for john twice in the teachers list. I dont' have a clue as to how I should go about this. need help ASAP. please help.

bel30
Starting Member

4 Posts

Posted - 2012-11-21 : 08:27:25
Just adding to the above question. The teacher name which is visible in each report should be the same teacher name in the previous page. I.e., same as the teacher name which is seen in the previous page along with the student name and ID.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 08:34:19
I assume you are using SSRS reports. Assuming so, one way would be to have two parameters - StudentId and TeacherId. Then, the query that generates the reports should use these two parameters to filter the data appropriately.

Take a look at some of the tutorials that you will find by googling - for example this: http://msdn.microsoft.com/en-us/library/aa337432(v=sql.105).aspx to get started.
Go to Top of Page

bel30
Starting Member

4 Posts

Posted - 2012-11-21 : 08:40:14
NO. I am not using SSRS yet. Eventually i will be but for now I am writing normal queries. So I am not able to understand how I should go about the data masking process. So I should be writing a script.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 08:50:04
Yes, you should write a T-SQL script using SQL Server Management Studio, run it to see if it is giving you the results you want. If it is, make it into a stored procedure and use that as the source to drive the reports.

Your query would be something like shown below - of course, I am making up the column names and table names. If you need more specific help, post the DDL for the tables etc. See this article for help in posting: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Example code:
DECLARE @student VARCHAR(32) = 'Jones';
DECLARE @teacher VARCHAR(32) = 'Smith';

-- all classes for a specific student.
SELECT
subjectname,
marks,
teachername
FROM
StudentRecordsTable
WHERE
studentname = @student;

-- for a specific teacher
SELECT
subjectname,
marks,
teachername
FROM
StudentRecordsTable
WHERE
studentname = @student
AND teachername = @teacher;
Go to Top of Page

bel30
Starting Member

4 Posts

Posted - 2012-11-21 : 09:13:55
Question: How Do I get the table which will have masked in place of teachers names except the name of the teacher of interest.

DDL for table

create table mytable1(marks_obtained int, subject_name nvarchar(30), number_of_attempts int, studentID nvarchar(10))

insert into mytable1 (50, science, 2, ID3), (70, geography, 1, ID3),(75, maths, 1, ID4 )

create table mytable2 (teachername navarchar(40), studentID nvarchar(10))

Did not attempt as I do not know how to mask data as shown in the result below.

Expected result:

marks_obtained subject_name number_of_attempts teachername

50 science 2 sharon

75 geography 1 Teacher 2

60 maths 1 teacher 3

65 social studies 1 teacher 2

61 english 1 teacher 4


P.S: please take a look at the results of the teachersname column. That is how I need the results. And dont know what concept to use. This report is sent to each teacher. The result set above is how it should look when the report is being sent to the teacher names sharon. If it is being sent to john then john's name should be seen in the teachersname column and sharon should be masked.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 09:42:57
What exactly is the assignment? Do you have to create tables that can be used in the report and/or just write the
t-SQL that produces the desired result? There needs to be a table that relates students to teachers, as you have started.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 09:53:20
DECLARE @Teacher VARCHAR(40) = 'Sharon'


SELECT t1.marks_obtained,subject_name,number_of_attempts
, CASE WHEN t2.teachername = @Teacher
THEN @Teacher
ELSE 'Teacher ' + CONVERT(VARCHAR(10),ROW_NUMBER() OVER(ORDER BY t2.teachername))
END AS Teacher
FROM #myTable1 t1
INNER JOIN #myTable2 t2 ON t1.StudentID = t2.StudentID

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -