it fixes the issue Sigh. The column being selected in the SP has a char data type, but the SP had a varchar2 input parameter for it. In Oracle, varchar2 variables actually lose their trailing spaces (unlike in SQL Server). On top of that, I was doing RTrim() on the SSRS parameter as queried from the database.
wish of those help I created a report to get a bunch of data. The report calls a stored procedure called sp_get52week, which gets me some totalled data about our customers. The report has 3 parameters, @Year, @Week, @SalespersonNumber. @SalespersonNumber is a parameter that can accept multiple values. The report works when only one SalesPersonNumber is selected, but not multiple. When I select multiple, I get the error: , I'll show this as an answer instead
CREATE FUNCTION [dbo].[sp_Get52Week] (
RETURNS table AS
,SUM(a.[tot_sls_amt]) AS CYSales
,(b.[tot_sls_amt]) AS PYSales
FROM .[MW].[MW_Submitted_Weeks_By_RBC_ROY] a
LEFT JOIN (SELECT sum([tot_sls_amt]) tot_sls_amt
WHERE ((Year = @Year-1 AND Week <= @Week) OR
(Year = @Year - 2 AND Week > @Week))
AND cmp_status = 'A'
GROUP BY [Group]
ON a.[Group] = b.[Group]
AND a.[Owner] = b.[Owner]
AND a.[SalesPersonNumber] = b.[SalesPersonNumber]
WHERE ((Year = @Year AND Week <= @Week)
OR (Year = @Year - 1 AND Week > @Week))
AND cmp_status = 'A'
GROUP BY a.[Group]
FROM [dbo].[sp_Get52Week](@Year, @Week)
WHERE [SalesPersonNumber] IN (@SalesPersonNumber)
How can I specify a Stored Procedure to be used in the SSRS Report Wizard?
To fix the issue you can do Based on my comment, give this a try, should get you moving in the right direction on how you can parse the xml and zero in on the specific command. You might have to update the name spaces in the script below and also add your report name.
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition') --You may have to change this based on you SSRS version
report_xml.value( '(/Report/DataSources/DataSource/@Name)', 'VARCHAR(50)' ) AS DataSource,
report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())', 'VARCHAR(MAX)' ) AS CommandText,
report_xml.value( '(/Report/DataSets/DataSet/Query/CommandType/text())', 'VARCHAR(100)' ) AS CommandType,
CAST( CAST( content AS VARBINARY(MAX) ) AS XML ) report_xml
WHERE Content IS NOT NULL
AND [Type] = 2
--use below in where clause if searching for the CommandText. Depending on how the report was developed I would just use the proc name and no brackets or schema.
--Example: if you report was developed as having [dbo].[procName] just use LIKE '%procName%' below. Because other reports could just have dbo.procName.
report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())', 'VARCHAR(MAX)' ) LIKE '%Your Proc Name here%'
--comment out the above and uncomment below if know your report name and want to search for that specific report.
--[x].[Name] = 'The Name Of Your Report'
Hiding Sub report in SSRS based on Parameter (and not executing the Stored Procedure)