none
User input for SQL Server 2008 R2 Reporting

    Question

  • I was trying below report which was OK. What I wanted collection value e.g. '5000000C'  is to be as user input.

    ************

    SELECT arp.DisplayName0, sys.User_Name0, sys.Netbios_Name0, arp.Publisher0, arp.Version0
    FROM v_R_System sys
    JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
    JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

    WHERE
     fcm.CollectionID = '5000000C'
    AND
    ( arp.DisplayName0 IS NULL OR (
    arp.DisplayName0  NOT LIKE  '%iSeries%'
    AND  arp.DisplayName0  NOT LIKE  '%Adobe%'
    ))
    AND 
    ( arp.Publisher0 IS NULL OR (
    arp.Publisher0   NOT LIKE  '%Adobe%'
    AND  arp.Publisher0   NOT LIKE  '%Microsoft%'
    ))
    ORDER BY DisplayName0, Version0
    *******************************

    I have got separate query to get the valu of Collection field:

    ********************************

    begin
     if (@filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM fn_rbac_Collection(@UserSIDs)  Where CollectionType = 2 ORDER BY Name
     else
      SELECT DISTINCT CollectionID, Name FROM fn_rbac_Collection(@UserSIDs) 
      WHERE CollectionID like @filterwildcard and CollectionType = 2
      ORDER BY Name
    end

    ********************************

    If I click values pop up list should come from 2nd query. based on the value 1st query will display.

    How can I pass values from the 2nd query to the 1st query. Please help.

    Sunday, August 18, 2013 12:56 PM

Answers

  • Hi raofu09,

    According to your description, do you want to pass values from one query to another, right? In Reporting Services, we can pass the values from one query to another query by using a parameter. In your scenario, create a parameter named CollectionID which get value from the second query (DataSet: DataSet1, Value field: CollectionID, Label field: CollectionID).

    Then you can use the following where clause in the first query.
    Pass multiple values: WHERE CollectionID IN (@CollectionID)
    Pass single value: WHERE CollectionID=@CollectionID

    Reference: Adding Parameters to Create a List of Available Values (SSRS)
    http://technet.microsoft.com/en-us/library/aa337400(v=sql.105).aspx

    If you have any questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    Thursday, August 22, 2013 3:24 PM

All replies

  • I hope this link will be helpful:

    MDX query based on a subquery


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Sunday, August 18, 2013 1:48 PM
  • Hi raofu09,

    According to your description, do you want to pass values from one query to another, right? In Reporting Services, we can pass the values from one query to another query by using a parameter. In your scenario, create a parameter named CollectionID which get value from the second query (DataSet: DataSet1, Value field: CollectionID, Label field: CollectionID).

    Then you can use the following where clause in the first query.
    Pass multiple values: WHERE CollectionID IN (@CollectionID)
    Pass single value: WHERE CollectionID=@CollectionID

    Reference: Adding Parameters to Create a List of Available Values (SSRS)
    http://technet.microsoft.com/en-us/library/aa337400(v=sql.105).aspx

    If you have any questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    Thursday, August 22, 2013 3:24 PM