SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 data masking
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bel30
Starting Member

USA
4 Posts

Posted - 11/21/2012 :  08:20:26  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 11/21/2012 :  08:27:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  08:34:19  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 11/21/2012 :  08:40:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  08:50:04  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 11/21/2012 :  09:13:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/21/2012 :  09:42:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/21/2012 :  09:53:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000