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

Question

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!

Answer 1

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:

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.