locked
Need Help with a Filter on a Query RRS feed

  • Question

  • Hi guys, I need help with a filter on a query.  Just to keep the example simple, I'm working off AdventureWorks2016.  The table that I'm working on is Person.Person.

    I created a stored procedure for dataset1  The code is below.

    CREATE Procedure [dbo].[usp_PersonType] @PersonType varchar(50) AS Select BusinessEntityID, PersonType, Title, FirstName, LastName from person.Person

    Where person.PersonType
    IN (select _id from AdventureWorks2016.dbo.fnSplitString(@PersonType, ','));

    GO

    I have one parameter for the field PersonType using the following dataset.

    Select distinct (case when personType = 'VC' THEN 'SC' Else persontype END) as PersonType 
    from person.Person

    So the point of all these is to filter the report on PersonType.  However this is where my issue comes in.  I want to be able to return  persontype values SC and VC whenever they select SC as a parameter from the drop down.

    So VC is not an available selection option on the parameter list, but if they select SC and other options then VC should be added as well, but VC is dependent on the SC selection only.


    Sunday, April 29, 2018 6:14 PM

Answers

  • Ok, I have solved it.  The main thing was to have a way to check for the SC value.  What I ended up doing was to pass the values from the SP into a temp table, then create an IF statement to check for the SC value, if it exists, then add the VC value to the table and use the table to filter the values afterwards.

    CREATE Procedure [dbo].[usp_PersonType] @PersonType varchar(50)
    AS
    --Create Table #Table (A varchar(50))
    Select * INTO #Table from (select _id as A from AdventureWorks2016.dbo.fnSplitString(@PersonType, ','))x
    
    IF Exists (Select 1 from #Table Where A = 'SC')
    BEGIN
    	Insert INTO #Table (A) Values ('VC')
    	Select businessentityid, lastname, firstname, persontype from person.person
    	Where PersonType IN (Select A from #table)
    End
    ELSE
    BEGIN
    	--Runs the values that have been passed regularly.
    	Select businessentityid, lastname, firstname, persontype from person.person
    	Where PersonType IN (Select A from #table)
    END
    
    GO

    • Marked as answer by Diango Monday, April 30, 2018 12:27 AM
    Monday, April 30, 2018 12:19 AM

All replies

  • Hi, 

    In SSRS, Please perform below steps:

    1. Create one more Dataset in Report data. Select option "Use a dataset embedded in my report" option and enter below query:

    Select distinct (case when personType = 'VC' THEN 'SC' Else persontype END) as PersonType 
    from person.Person

    2. Create Parameter @PersonType in ReportData and map this parameter from step 1. Use option "Get values from a query".

    Example:

    3. Create Dataset using store proc:

    CREATE Procedure [dbo].[usp_PersonType] @PersonType varchar(50) AS Select BusinessEntityID, PersonType, Title, FirstName, LastName from person.Person

    Where person.PersonType = @PersonType

    Then Go to Parameters tab and map the Parameter from above Proc to parameter created in Step 2.

    Hope this helps.

    Thanks,

    Alambir

    Please vote this as helpful or mark this as answered if this helps.



    Sunday, April 29, 2018 7:17 PM
  • Hi Alambir,

    I already had that much.  My problem is not creating the report and creating parameters.  

    My issue is this, when a user selects the SC option in the parameters, it should also bring back the VC values as well.

    Sunday, April 29, 2018 8:14 PM
  • Hi,

    I think you can add case statement in the where filter to use VC values as filter in SC. So your store proc would be like as below:

    CREATE Procedure [dbo].[usp_PersonType] @PersonType varchar(50) AS Select BusinessEntityID, PersonType, Title, FirstName, LastName from person.Person

    Where person.PersonType = (CASE WHEN person.PersonType='VC' THEN 'SC' ELSE @PersonType END)

    So above logic will bring all VC records too.

    Hope this helps.

    Thanks,

    Alambir

    Please vote this as helpful or mark this as answered if this helps.

    Sunday, April 29, 2018 8:29 PM
  • Ok, I have solved it.  The main thing was to have a way to check for the SC value.  What I ended up doing was to pass the values from the SP into a temp table, then create an IF statement to check for the SC value, if it exists, then add the VC value to the table and use the table to filter the values afterwards.

    CREATE Procedure [dbo].[usp_PersonType] @PersonType varchar(50)
    AS
    --Create Table #Table (A varchar(50))
    Select * INTO #Table from (select _id as A from AdventureWorks2016.dbo.fnSplitString(@PersonType, ','))x
    
    IF Exists (Select 1 from #Table Where A = 'SC')
    BEGIN
    	Insert INTO #Table (A) Values ('VC')
    	Select businessentityid, lastname, firstname, persontype from person.person
    	Where PersonType IN (Select A from #table)
    End
    ELSE
    BEGIN
    	--Runs the values that have been passed regularly.
    	Select businessentityid, lastname, firstname, persontype from person.person
    	Where PersonType IN (Select A from #table)
    END
    
    GO

    • Marked as answer by Diango Monday, April 30, 2018 12:27 AM
    Monday, April 30, 2018 12:19 AM
  • actually you dont even need IF..else

    you can just do this

    CREATE Procedure [dbo].[usp_PersonType] @PersonType varchar(50)
    AS
    --Create Table #Table (A varchar(50))
    Select * INTO #Table from (select _id as A from AdventureWorks2016.dbo.fnSplitString(@PersonType, ','))x
    
    
    	Select p.businessentityid, 
    p.lastname, 
    p.firstname, 
    p.persontype 
    from person.person p
    join (
    select *,
    sum(case when A= 'SC' then 1 else 0 end) over () AS SCcnt,
    sum(case when A= 'VC' then 1 else 0 end) over () AS VCCnt
    from #Table
    )t
    ON t.A = p.PersonType 
    AND (t.SCCnt > 0 AND t.VCCnt = 0 AND p.PersonType = 'VC')
    GO


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 30, 2018 5:41 AM
  • Hi Visakh,

    I tested out your solution and it didn't return any values.  Seems like it would be right though.

    Tuesday, May 1, 2018 1:23 AM