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
 Need help in sql query for SSRS

Author  Topic 

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-09 : 05:17:34
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

52326 Posts

Posted - 2013-10-09 : 05:41:04
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 - 2013-10-09 : 05:51:03
Yes you are correct visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 05:57:44
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 - 2013-10-09 : 06:31:44
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 - 2013-10-09 : 06:37:42
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 07:16:17
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 - 2013-10-09 : 07:45:33
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

52326 Posts

Posted - 2013-10-09 : 07:55:14
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 - 2013-10-09 : 08:01:22
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

52326 Posts

Posted - 2013-10-09 : 08:07:53
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 - 2013-10-09 : 08:16:19
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 08:48:09
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 - 2013-10-09 : 08:56:43
Yes Visakh, exactly you are correct.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 09:07:04
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 - 2013-10-09 : 09:23:17
Yes. We need to relate like you told.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 12:59:51
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 - 2013-10-10 : 01:41:41
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

52326 Posts

Posted - 2013-10-10 : 01:44:53
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 - 2013-10-10 : 02:17:31
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 03:56:36
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
  Previous Page&nsp;  Next Page

- Advertisement -