SSRS Subreport not always Selecting Multi Values from Parent Report to Child Report [Solved]

LH 26 Reputation points
2022-03-11T00:31:41.49+00:00

This is in regards to an SSRS subreport not selecting parameter values from the parent report to the child report.
The report parameters of both reports are populated by the same shared datasets; which populate both the Shared Values and the Available Values.

In some cases, the subreport would error with: "one or more parameters required to run the report have not been specified". Upon selecting a different date range, the subreport would run without issue.

I checked that the shared dataset was not returning Null values as that is a common reason that the values are not selected in the parameter of the subreport.

I checked that I had the Parent report parameters for the multi-valued parameter selecting Parameters!Name.Value as opposed to Parameters!Name.Value(0).

By parsing through the different dates, I found that in the Parent report, the parameter for DepartmentHead was returning SCOTT WILKES but in the subreport when I ran it as a stand alone for the same dates, it returned Scott Wilkes. Apparently, the Case is what was causing the issue.

To fix, I had to change the returned values from the DepartmentHead shared dataset from

SELECT DISTINCT
dc.DepartmentHeadCode
,dc.DepartmentHeadName
FROM DimDPHead AS dc

to

SELECT DISTINCT
dc.DepartmentHeadCode COLLATE SQL_Latin1_General_CP1_CS_AS AS DepartmentCode
,dc.DepartmentHeadName COLLATE SQL_Latin1_General_CP1_CS_AS AS DepartmentName
FROM DimDPHead AS dc

Hope this helps someone else struggling with a similar issue. Thanks!

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
0 comments No comments
{count} vote

Accepted answer
  1. Joyzhao-MSFT 15,566 Reputation points
    2022-03-11T02:53:07.09+00:00

    Hi @LH ,
    Thanks for sharing.
    We know that SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.

    We can check whether certain columns in SQL Server are case-sensitive in advance with the following statement:

    SELECT COLUMN_NAME, COLLATION_NAME   
    FROM INFORMATION_SCHEMA.COLUMNS    
    WHERE TABLE_NAME = '<Name of the table that contains the column(s) you want to check>'  
    

    - If the output of the field contains ‘CI’, like in ‘Latin1_General_CI_AI’ then the column is case insensitive.

    • If the output of the field contains ‘CS’, like in ‘Latin1_General_CS_AS’ then the column is case sensitive.

    As shown below:

    182066-01.jpg

    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful