none
Active Multi Value Selection display in SSRS 2008 RRS feed

  • Question

  • Hey everybody,

    Following are the tables that I am using for my report:

    Department(DeptID pk, deptName, deptDesc)

    UserGroup {UsrGrpID(pk), DeptID(fk)User, UsrGroupName}

    UserGroupMember {UsrGrpMemID(pk), UsrID(fk),....)

    User (UsrId pk, usrName, addr, phone, email...)

    Now I got two datasets(using aboave tables) that I used in the report

    1. dsDepartment: contains department id, and deparment Name

    2. dsUserByDepartment: contains deptName, UsrGroupName, UsrName, Phone, Email

    Now in my report I used first dataset to populate my Department parameter which is a multivalue selection. And I use second dataset display info on the selected department using stored procedure based on the selection from the Department parameter. Even if I choose SELECT ALL from the selection and click view report, I can not see some departments information, simply because there do not exist any user linked to that department. I want those departments not to be available for selection. i.e if there is no available user data for particular department, it shouldn't be appearing in the selection list.

    How can I achieve that? Using expression? Please let me know.. Your help is appreciated. thank you!! :)

     

    Saturday, July 31, 2010 9:55 AM

Answers

  • Try this query to fill in your departments dataset

    select * from Departments where DeptId in (select g.DeptId from UserGroupMember inner join UserGroup g on g.UsrGrpID = u.UsrGrpID)

    This will make sure that the departments filled belong to usergroups containing users.


    Ali Hamdar (alihamdar.com)
    • Marked as answer by de_gunner Thursday, August 5, 2010 11:44 AM
    Sunday, August 1, 2010 11:07 AM

All replies

  • When populating the departments dataset, you need to execute this query

    select * from Departments where DeptId in (select DeptId from UserGroup)

    So now the dataset Departments will only contain departments with related information and when filling the parameters of the report - clicking on a department will always lead to information.


    Ali Hamdar (alihamdar.com)
    Saturday, July 31, 2010 11:27 PM
  • Hi Ali

    The query somewhat did the job but wasn't good enough :( I could definitely see some of the departments only, but however if you look at my table, UserGroup may have users in them. Now the above query also showd me the departments that contains usergroup that is empty i.e. UserGroup do not have any users assigned. I don't want those departments to be shown aswel.

    One query I got it working was  select * from department where 

    select DeptId, DeptName FROM Department where active = 1

    This query got me wokring. Now Department databaset contains only active department i.e. they have usergroups and users assigned. However when I selected all the active departments and clicked VIEW REPORT. I could not see the results from one of the displayed departments. Any thoughts on that? Or any better query?

    Sunday, August 1, 2010 10:51 AM
  • Try this query to fill in your departments dataset

    select * from Departments where DeptId in (select g.DeptId from UserGroupMember inner join UserGroup g on g.UsrGrpID = u.UsrGrpID)

    This will make sure that the departments filled belong to usergroups containing users.


    Ali Hamdar (alihamdar.com)
    • Marked as answer by de_gunner Thursday, August 5, 2010 11:44 AM
    Sunday, August 1, 2010 11:07 AM
  • Hi Ali I came across one more issue, Please help me.

     

    Well I got Employee table that has employee information including StatusCode i.e. Fulltime, partime..etc. The Employee table is linked with EmployeeStatus table like this: MPLOYEE(Id (PK),  EmployeeStatusId(fk))

    Now when I run the following query, I see nothng but column headers. Any lights on tha? I can have NULL values for StatusId in Employee table BTW:

     

    SELECT     Employee.Id, Employee.Name, EmployeeStatus.Code

    FROM         Employee INNER JOIN

                                 EmployeeStatus ON Employee.EmployeeStatusId = EmployeeStatus.Id

     

    Please help. Thanks.

    Sunday, August 8, 2010 11:41 PM