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
 Need help in sql query for SSRS
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  05:17:34  Show Profile  Reply with Quote
Hi,

I have a group called Leaders group. Users belonging to this Leaders group should be able to see only people information belonging to Leaders group. The following people belong to Leaders group[a,,c,d,e,f]. I need to display this for a SSRS report. Can anyone help me with the query.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  05:41:04  Show Profile  Reply with Quote
you need to use a filetr based on User!UserID. This will pass in userid corresponding to logged in user, check and see if they belong to leader group in query behind and show only the data for leaders group if they belong to it.
I assume you've table storing Group Member details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  05:51:03  Show Profile  Reply with Quote
Yes you are correct visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  05:57:44  Show Profile  Reply with Quote
then just do like

DECLARE @IsLeader bit

SET @IsLeader= CASE WHEN EXISTS(SELECT 1 FROM GroupMember WHERE GroupName='Leader' AND  UserID=@UserID) THEN 1 ELSE 0 END
SELECT
FROM Table
WHERE UserName IN ('a,'c','d','e','f')
OR @IsLeader=0

Set UserID parameter to take value from User!UserID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  06:31:44  Show Profile  Reply with Quote
Initially the user logs in and where we query a table called "userprofile". This table has a column called user_Id which checks the username who logs in.

Then we query a table called "GroupLookup" which has a column named People_Group which has leaders group in it.
This table also has a column called "People_CD" and we compare this column to another table called Impacted_People which has same column. So we fetch the datas from these.
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  06:37:42  Show Profile  Reply with Quote
Here is my requirement

Initially the user logs in and where we query a table called "userprofile". This table has a column called user_Id which checks the username who logs in.

Then we query a table called "GroupLookup" which has a column named People_Group which has leaders group in it.This table also has a column called "People_CD" and we compare this column to another table called Impacted_People which has same column. So we fetch the datas from these.

Ontop of this some people group present in People_Group column in GroupLookup table should belong to Leaders group.


Edited by - vignesht50 on 10/09/2013 06:38:31
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  07:16:17  Show Profile  Reply with Quote
Apply same logic to your tables like


DECLARE @IsLeader bit

SET @IsLeader= CASE WHEN EXISTS(SELECT 1 FROM GroupLookup gl INNER JOIN Impacted People ip ON ip.People_CD = g.People_CD
INNER JOIN userprofile up ON up.User_ID = ip.User_ID
WHERE People_Group ='Leader' AND  up.UserName=@User) THEN 1 ELSE 0 END



SELECT *
FROM YourQueryTable
WHERE UserName IN ('a,'c','d','e','f')
OR @IsLeader=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  07:45:33  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Apply same logic to your tables like


DECLARE @IsLeader bit

SET @IsLeader= CASE WHEN EXISTS(SELECT 1 FROM GroupLookup gl INNER JOIN Impacted People ip ON ip.People_CD = g.People_CD
INNER JOIN userprofile up ON up.User_ID = ip.User_ID
WHERE People_Group ='Leader' AND  up.UserName=@User) THEN 1 ELSE 0 END



SELECT *
FROM YourQueryTable
WHERE UserName IN ('a,'c','d','e','f')
OR @IsLeader=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi Visakh

I ran the query and get the error message

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Group'.
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@User".
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '‘'.

This is my query I made changes:
DECLARE @IsCCSG bit

SET @IsCCSG Group= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB _CD
INNER JOIN USER_PRFL up ON up.User_Id = il.User_ID
WHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 END


SELECT *
FROM BSP_LOB_GRP
WHERE UserName IN (‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘Auto and Home’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MetLife Group Services’, ‘Group Life Products’, ‘WebTech’, ‘Long Term Care’, ‘Total Control Account’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘Marketing Services’)
OR @IsCCSG Group=0




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  07:55:14  Show Profile  Reply with Quote
Group is a reserved word in SQL

Try enclosing it within []

DECLARE @IsCCSG bit

SET @IsCCSG Group= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB _CD
INNER JOIN USER_PRFL up ON up.User_Id = il.User_ID
WHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 END


SELECT *
FROM BSP_LOB_GRP
WHERE UserName IN (‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘Auto and Home’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MetLife Group Services’, ‘Group Life Products’, ‘WebTech’, ‘Long Term Care’, ‘Total Control Account’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘Marketing Services’)
OR @IsCCSG Group=0


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  08:01:22  Show Profile  Reply with Quote
You Mean like this

DECLARE @IsCCSG bit

SET @IsCCSG[]= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB_CD
INNER JOIN USER_PRFL up ON up.User_Id = il.User_ID
WHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 END


SELECT *FROM BSP_LOB_GRPWHERE UserName IN
(‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘A&H’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MGS’, ‘GLP’, ‘WebTech’, ‘Long Term Care’, ‘TCA’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘MKS’)
OR @IsCCSG[]=0


The thing is I have a group called CCSG Group in the BSP_LOB_GRP column
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  08:07:53  Show Profile  Reply with Quote
quote:
Originally posted by vignesht50

You Mean like this

DECLARE @IsCCSG bit

SET @IsCCSG[]= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB_CD
INNER JOIN USER_PRFL up ON up.User_Id = il.User_ID
WHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 END


SELECT *FROM BSP_LOB_GRPWHERE UserName IN
(‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘A&H’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MGS’, ‘GLP’, ‘WebTech’, ‘Long Term Care’, ‘TCA’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘MKS’)
OR @IsCCSG[]=0


The thing is I have a group called CCSG Group in the BSP_LOB_GRP column


nope in this case dont put [] as its a variable name
I was thinking you've a column by name Group
your earlier code's issue was space character in name of variable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  08:16:19  Show Profile  Reply with Quote
Here is the one
TABLES:

User_Profile:
User_Id User_FullName Email User_Group

Group_Lookup:
LOB_GRP LOB_CD LOB_TYPE_CD

Impacted_LOB:
Event_ID LOB_CD

First user log-ins which is queried in User_Profile table. Then I have a group called CCSG Group in LOB_GRP column in Group_Lookup table. So now this CCSG Group has its respective LOB_CD in same table. Now I need to compare LOB_CD column of Group_Lookup and Impacted_LOB and display the result for that particular group.

Edited by - vignesht50 on 10/09/2013 08:37:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  08:48:09  Show Profile  Reply with Quote
so how do relate between user_profile and other tables? is it based on
User_Group = LOB_GRP ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  08:56:43  Show Profile  Reply with Quote
Yes Visakh, exactly you are correct.

Edited by - vignesht50 on 10/09/2013 08:57:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  09:07:04  Show Profile  Reply with Quote
then shouldnt your join be this?


SET @IsCCSG[]= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB_CD
INNER JOIN USER_PRFL up ON up.User_Group = gl.LOB_GRP
WHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/09/2013 :  09:23:17  Show Profile  Reply with Quote
Yes. We need to relate like you told.

Edited by - vignesht50 on 10/09/2013 09:31:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  12:59:51  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/10/2013 :  01:41:41  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

welcome
Hi Visakh,

Here is my full thing
Here is the one
TABLES:

User_Profile:
User_Id User_FullName Email User_Group

Group_Lookup:
LOB_GRP LOB_CD LOB_TYPE_CD

Impacted_LOB:
Event_ID LOB_CD

First user log-ins which is queried in User_Profile table. Then I have a group called CCSG Group in LOB_GRP column in Group_Lookup table. So now this CCSG Group has its respective LOB_CD in same table. Now I need to compare LOB_CD column of Group_Lookup and Impacted_LOB and display the result for that particular group. On top of this some groups like x,y,z present in LOB_GRP in Group_Lookup table should belong to CCGS Group.

Can you pls help me.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  01:44:53  Show Profile  Reply with Quote
did the previous suggestion gave you your desired output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/10/2013 :  02:17:31  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

did the previous suggestion gave you your desired output?
No Visakh

Here is my original datas.

Here is the one
USER_PRFL
User_Id User_FullName Email User_Group

BSP_LOB_Grp_Lookup
BSP_LOB_GRP BSP_LOB_CD BSP_LOB_GRP_TYPE_CD

IMPACTED_LOB
Event_ID BSP_LOB_CD

Scenario1:
First user log-ins which is queried in USER_PRFL table. Then I try to relate User_Group and BSP_LOB_GRP.
Now I have a value called CCSG Group in BSP_LOB_GRP column in BSP_LOB_Grp_Lookup table. So now this CCSG Group has its respective BSP_LOB_CD in same table. Now I need to compare BSP LOB_CD column of BSP_LOB_Grp_Lookup and IMPACTED_LOB and display the result for that particular group.
On top of this the following values (Life, MLCT, GenAm, NEF, Annuity, Auto and Home, Annuity and Investments) present in BSP_LOB_GRP should belong to CCSG Group of the sane column.
Scenario2:
Assume we have two rows values like Service Leaders & Service Area in BSP_LOB_GRP. Now Service Leaders should belong to Service Area. Let’s have X&Y as the users they need to see
X should be able to see LIFE, MLCT, NEF present in BSP_LOB_GRP
Y should be able to see Long Term Care, Total Control Account BSP_LOB_GRP

The thing is BSP_LOB_CD column in BSP_LOB_Grp_Lookup table is null.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Edited by - vignesht50 on 10/10/2013 02:20:01
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  03:56:36  Show Profile  Reply with Quote
Scenario 1
the below should give you details on CCSG Group

SELECT *
FROM USER_PRFL up
INNER JOIN BSP_LOB_Grp_Lookup bl
ON bl.BSP_LOB_GRP = up.User_Group 
INNER JOIN IMPACTED_LOB il
ON il.BSP_LOB_CD = bl.BSP_LOB_CD 
WHERE BSP_LOB_GRP = 'CCSG Group'

but if BSP_LOB_CD column in BSP_LOB_Grp_Lookup table is null you wont get anything from above query so you need to fix that issue first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 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.23 seconds. Powered By: Snitz Forums 2000