Cascading Parameters Assistance - SSRS - VisualStudio

Question

I have a cascading parameter setup for my report so that once I select a Client Number, it displays the Matter Numbers. I don't have a value for the client number set because we often search by the client number so it's easier to manually input that number in. The cascading parameter part works in that I type in a client number and received a drop down list of matters. However, when I click run report, it displays the details for ALL of the matters and not for just the one single matter I pick.

The main dataset has both parameters in the SQL as IN (@ClientNumber) AND IN (@MatterNumber). I've tried adding them as filters in the dataset properties, but that doesn't seem to do it either.

So the cascading parameters work in that I choose one and the next field ungreys and allows me to pick my next parameter, but then it just displays all data as if I hadn't picked a second parameter.

Any thoughts? I'm happy to provide whatever is needed.

--Including my query per request. I am using Microsoft SQL.

SELECT        
    HBM_MATTER.CLIENT_CODE, 
    HBM_MATTER.MATTER_CODE, 
    HBM_NAME.NAME, 
    HBM_MATTER.LONG_MATT_NAME, 
    HBM_PERSNL_ORG.EMPLOYEE_NAME AS Originator, 
    HBM_PERSNL_BILL.EMPLOYEE_NAME AS Billing, 
    HBM_PERSNL_RSP.EMPLOYEE_NAME AS Responsible, 
    HBM_CLIENT.OPEN_DATE AS [Client Open Date], 
    HBM_MATTER.MATTER_UNO, 
    HBM_CLIENT.CLIENT_UNO, 
    HBM_MATTER.MATT_TYPE_CODE AS [Area of Law Code], 
    HBL_MATT_TYPE.MATT_TYPE_DESC AS [Area of Law Description], 
    HBM_MATTER.OFFC AS [Office Code], 
    HBL_OFFICE.OFFC_DESC AS [Office Description], 
    HBM_MATTER.DEPT AS [Department Code], 
    HBL_DEPT.DEPT_NAME AS [Department Name], 
    HBM_MATTER.PROF AS [Practice Team Code], 
    HBL_PROF_CTR.PROF_CTR_DESC AS [Practice Team Description], 
    HBM_MATTER.STATUS_CODE AS [Status Code], 
    HBL_STATUS_MATT.STATUS_DESC AS [Status Description], 
    HBM_MATTER.OPEN_DATE AS [Matter Open Date], 
    HBM_MATTER.CLOSE_DATE AS [Matter Close Date], 
    TBM_MATTER.LAST_BILL_DATE AS [Matter Last Billed Date], 
    HBM_PERSNL_TIME.EMPLOYEE_NAME AS Timekeeper, 
    TBM_CLMAT_PART_BILL.EFF_DATE

FROM            
    HBM_MATTER INNER JOIN TBM_CLMAT_PART AS TBM_CLMAT_PART_ORG ON HBM_MATTER.MATTER_UNO = TBM_CLMAT_PART_ORG.MATTER_UNO INNER JOIN
    TBM_CLMAT_PART AS TBM_CLMAT_PART_BILL ON HBM_MATTER.MATTER_UNO = TBM_CLMAT_PART_BILL.MATTER_UNO INNER JOIN
    HBM_CLIENT ON HBM_MATTER.CLIENT_UNO = HBM_CLIENT.CLIENT_UNO INNER JOIN
    HBM_PERSNL AS HBM_PERSNL_ORG ON TBM_CLMAT_PART_ORG.EMPL_UNO = HBM_PERSNL_ORG.EMPL_UNO INNER JOIN
    HBM_PERSNL AS HBM_PERSNL_BILL ON TBM_CLMAT_PART_BILL.EMPL_UNO = HBM_PERSNL_BILL.EMPL_UNO INNER JOIN
    HBM_PERSNL AS HBM_PERSNL_RSP ON HBM_MATTER.RESP_EMPL_UNO = HBM_PERSNL_RSP.EMPL_UNO INNER JOIN
    HBM_NAME ON HBM_CLIENT.NAME_UNO = HBM_NAME.NAME_UNO LEFT OUTER JOIN
    HBL_MATT_TYPE ON HBM_MATTER.MATT_TYPE_CODE = HBL_MATT_TYPE.MATT_TYPE_CODE INNER JOIN HBL_OFFICE ON HBM_MATTER.OFFC = HBL_OFFICE.OFFC_CODE INNER JOIN
    HBL_DEPT ON HBM_MATTER.DEPT = HBL_DEPT.DEPT_CODE INNER JOIN
    HBL_PROF_CTR ON HBM_MATTER.PROF = HBL_PROF_CTR.PROF_CTR_CODE LEFT OUTER JOIN
    HBL_STATUS_MATT ON HBM_MATTER.STATUS_CODE = HBL_STATUS_MATT.STATUS_CODE INNER JOIN TAT_TIME ON HBM_MATTER.MATTER_UNO = TAT_TIME.MATTER_UNO INNER JOIN
    HBM_PERSNL AS HBM_PERSNL_TIME ON TAT_TIME.TK_EMPL_UNO = HBM_PERSNL_TIME.EMPL_UNO INNER JOIN
    TBM_MATTER ON HBM_MATTER.MATTER_UNO = TBM_MATTER.MATTER_UNO

GROUP BY 
    HBM_MATTER.CLIENT_CODE, HBM_MATTER.MATTER_CODE, HBM_NAME.NAME, HBM_MATTER.LONG_MATT_NAME, HBM_PERSNL_ORG.EMPLOYEE_NAME, HBM_PERSNL_BILL.EMPLOYEE_NAME, HBM_PERSNL_RSP.EMPLOYEE_NAME, HBM_CLIENT.OPEN_DATE, HBM_MATTER.MATTER_UNO, HBM_CLIENT.CLIENT_UNO, HBM_MATTER.MATT_TYPE_CODE, HBL_MATT_TYPE.MATT_TYPE_DESC, HBM_MATTER.OFFC, HBL_OFFICE.OFFC_DESC, HBM_MATTER.DEPT, HBL_DEPT.DEPT_NAME, HBM_MATTER.PROF, HBL_PROF_CTR.PROF_CTR_DESC, HBM_MATTER.STATUS_CODE, HBL_STATUS_MATT.STATUS_DESC, HBM_MATTER.OPEN_DATE, HBM_MATTER.CLOSE_DATE, TBM_MATTER.LAST_BILL_DATE, HBM_PERSNL_TIME.EMPLOYEE_NAME, TBM_CLMAT_PART_ORG.EFF_DATE, TBM_CLMAT_PART_ORG.PERCENTAGE, TBM_CLMAT_PART_BILL.EFF_DATE

HAVING        
(HBM_MATTER.CLIENT_CODE IN (@ClientNumber)) AND (HBM_MATTER.MATTER_CODE IN (@MatterNumber))

Please, edit your question to include the all of the SQL you are using. We cannot tell what the problem may with the little that you have included here. — R. Richards
Are you quite sure you are passing the matter number from the matter drop down and not the client number? — Paul Bambury
Why not display the values of both parameters on the report just to make sure that what @PaulBambury said isn't happening? — sams
@Sams, using your idea, I did add the params to the header to see what it was pulling, and it was pulling my client code and matter_uno, so when I changed it to matter_code it works now! So it was a rookie incorrect value mistake. Thanks everyone! — T. Meyer

iamdave iamdave · Accepted Answer · 2016-12-21T11:11:10

This is a known bug in SSRS.

The only way I have found as a workaround is to create a hidden multi-select parameter that is based on the value selected in the first parameter, and on which the second parameter takes it's values from.

This solution is messy and confusing to any future maintainers if your documentation is not detailed and available, but at times inescapable.

I would recommend trying to find a way to have your selection criteria without the use of these cascading parameters.

Cascading Parameters Assistance - SSRS - VisualStudio

1 Answers