locked
MULTI VALUE PARAMETER WITH THREE DATASETS RRS feed

  • Question

  • User-149030355 posted

    HELLO, this is the scenario I have tried everything... I  guess.

    I have one dataset I use to filter the current professor for the term, the second dataset have professor information with student and class ID and balance owe for the term. I have the last dataset with the student information and balance and class ID, I do have Student Id as PK, 

    When I pick a professor from my filter (dataset 1) the tablix show all student who are taking classes with this professor and the balance if they have one, I am doing a lookup to the student Dataset to bring it in.

    Now when I want to set multiple values to get in my tablix all the students just for 3 or 4.... and ALL is not working.... I did almost where I found over te net....and is not working

    I am guessing that my problem is because I am doing a lookup to bring the amount for each student, then probably I should do something in my lookup to bring all them....

    PLEASE HELP

    Tuesday, February 24, 2015 4:28 PM

Answers

  • User1711366110 posted

    hi Ysa206,
      As per your case, I have been shared some ideas below :
    1.In the report data--> goto dataset properties-->filter -->choose your expression,operation & value

    2.then DataSetName property of the tablix should be set to the relevant dataset

    3.When you click inside a tablix an outer rectangle will appear with a square  in the upper left-hand corner. Right-click the square and choose Tablix Properties

    For more information, refer the following links :
    1. Click here to know more about Dataset and tablix filtering in SQL Server Reporting Services

    2. Click here to know more about the step by step process of filter data without changing dataset in ssrs

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 25, 2015 4:43 AM

All replies

  • User37182867 posted

    Please post your code. 

    Tuesday, February 24, 2015 4:33 PM
  • User-149030355 posted

    DATASET FOR FACULTY FILTER ( ONLY WAIT TO GET CURRENT DATA)

    SELECT DISTINCT SECTIONPER.PERSON_CODE_ID, PEOPLE.FIRST_NAME+' '+PEOPLE.LAST_NAME AS PROFESSOR
    FROM CODE_POSITION INNER JOIN
    PERCONTRACT ON CODE_POSITION.CODE_VALUE_KEY = PERCONTRACT.POSITION RIGHT OUTER JOIN
    SECTIONS INNER JOIN
    SECTIONPER ON SECTIONS.SECTION = SECTIONPER.SECTION AND SECTIONS.EVENT_SUB_TYPE = SECTIONPER.EVENT_SUB_TYPE AND
    SECTIONS.ACADEMIC_SESSION = SECTIONPER.ACADEMIC_SESSION AND SECTIONS.ACADEMIC_TERM = SECTIONPER.ACADEMIC_TERM AND
    SECTIONS.ACADEMIC_YEAR = SECTIONPER.ACADEMIC_YEAR AND SECTIONS.EVENT_ID = SECTIONPER.EVENT_ID ON
    PERCONTRACT.PEOPLE_CODE_ID = SECTIONPER.PERSON_CODE_ID LEFT OUTER JOIN
    PEOPLE ON SECTIONPER.PERSON_CODE_ID = PEOPLE.PEOPLE_CODE_ID
    WHERE (SECTIONS.ACADEMIC_YEAR = @YEAR) AND (SECTIONS.ACADEMIC_TERM = @TERM) AND (SECTIONS.ADDS > 0)

    order by PROFESSOR ASC

    DATASET2  (TABLIX)

    SELECT DISTINCT [SECTIONPER].[PERSON_CODE_ID],
    [TRANSCRIPTDETAIL].[PEOPLE_CODE_ID],
    [PEOPLE].[LAST_NAME],
    [PEOPLE].[FIRST_NAME],
    [PEOPLE].[MIDDLE_NAME],
    [SECTIONS].[EVENT_ID],
    [SECTIONS].[EVENT_LONG_NAME],
    [PEOPLEMETADATA].[Last_Name][LAST_NAME_2],
    [PEOPLEMETADATA].[First_Name][FIRST_NAME_2],
    [PEOPLEMETADATA].[Middle_Initial][MIDDLE_INITIAL]
    FROM dbo.[PEOPLE] CROSS JOIN dbo.[PEOPLEMETADATA] CROSS JOIN dbo.[SECTIONPER] CROSS JOIN dbo.[SECTIONS] CROSS JOIN dbo.[TRANSCRIPTDETAIL]
    WHERE ( [SECTIONS].[ACADEMIC_YEAR] = [TRANSCRIPTDETAIL].[ACADEMIC_YEAR] AND
    [SECTIONS].[ACADEMIC_TERM] = [TRANSCRIPTDETAIL].[ACADEMIC_TERM] AND
    [SECTIONS].[ACADEMIC_SESSION] = [TRANSCRIPTDETAIL].[ACADEMIC_SESSION] AND
    [SECTIONS].[EVENT_ID] = [TRANSCRIPTDETAIL].[EVENT_ID] AND
    [SECTIONS].[EVENT_SUB_TYPE] = [TRANSCRIPTDETAIL].[EVENT_SUB_TYPE] AND
    [SECTIONS].[SECTION] = [TRANSCRIPTDETAIL].[SECTION] AND
    [TRANSCRIPTDETAIL].[PEOPLE_CODE_ID] = [PEOPLE].[PEOPLE_CODE_ID] AND
    [SECTIONS].[ACADEMIC_YEAR] = [SECTIONPER].[ACADEMIC_YEAR] AND
    [SECTIONS].[ACADEMIC_TERM] = [SECTIONPER].[ACADEMIC_TERM] AND
    [SECTIONS].[ACADEMIC_SESSION] = [SECTIONPER].[ACADEMIC_SESSION] AND
    [SECTIONS].[EVENT_ID] = [SECTIONPER].[EVENT_ID] AND
    [SECTIONS].[EVENT_SUB_TYPE] = [SECTIONPER].[EVENT_SUB_TYPE] AND
    [SECTIONS].[SECTION] = [SECTIONPER].[SECTION] AND
    [SECTIONPER].[PERSON_CODE_ID] = [PEOPLEMETADATA].[PEOPLE_CODE_ID] ) AND
    ( [SECTIONS].[ACADEMIC_YEAR] = @YEAR And
    [SECTIONS].[ACADEMIC_TERM] = @TERM And
    [SECTIONPER].[PERSON_CODE_ID] = @PEOPLE )

    DATASET 3

    SELECT DISTINCT [PEOPLE].[PEOPLE_CODE_ID],
    [PEOPLE].[LAST_NAME],
    [PEOPLE].[FIRST_NAME],
    [PEOPLE].[MIDDLE_NAME],
    [ACADEMIC].[CLASS_LEVEL],
    [PEOPLEORGBALANCE].[BALANCE_AMOUNT]
    FROM dbo.[ACADEMIC] CROSS JOIN dbo.[PEOPLE] CROSS JOIN dbo.[PEOPLEORGBALANCE]
    WHERE ( [PEOPLEORGBALANCE].[PEOPLE_ORG_CODE_ID] = [PEOPLE].[PEOPLE_CODE_ID] AND
    [PEOPLEORGBALANCE].[PEOPLE_ORG_CODE_ID] = [ACADEMIC].[PEOPLE_CODE_ID] ) AND
    ([PEOPLEORGBALANCE].[SUMMARY_TYPE] = N'CUM' And
    [PEOPLEORGBALANCE].[BALANCE_TYPE] = N'ALLTERMCUM' And
    [PEOPLEORGBALANCE].[ACADEMIC_YEAR] = @YEAR And
    [PEOPLEORGBALANCE].[ACADEMIC_TERM] = @TERM And
    [ACADEMIC].[CURRENT_ACTIVITY] = N'Y' and
    [ACADEMIC].[ENROLL_SEPARATION] = N'ENRO' and
    [PEOPLEORGBALANCE].[BALANCE_AMOUNT] > 0.00 )

    Tuesday, February 24, 2015 4:55 PM
  • User37182867 posted

    check your data to what you think you have to what you actually have. I think you might find that the data in the set that you are trying to update does not have all of the records available.

    Tuesday, February 24, 2015 5:06 PM
  • User1711366110 posted

    hi Ysa206,
      As per your case, I have been shared some ideas below :
    1.In the report data--> goto dataset properties-->filter -->choose your expression,operation & value

    2.then DataSetName property of the tablix should be set to the relevant dataset

    3.When you click inside a tablix an outer rectangle will appear with a square  in the upper left-hand corner. Right-click the square and choose Tablix Properties

    For more information, refer the following links :
    1. Click here to know more about Dataset and tablix filtering in SQL Server Reporting Services

    2. Click here to know more about the step by step process of filter data without changing dataset in ssrs

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 25, 2015 4:43 AM