logo
down
shadow

Hiding Sub report in SSRS (and not executing the Stored Procedure)


Hiding Sub report in SSRS (and not executing the Stored Procedure)

Content Index :

Hiding Sub report in SSRS (and not executing the Stored Procedure)
Tag : reporting-services , By : scott.sizemore
Date : December 01 2020, 04:55 PM


Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

SSRS Report from Oracle DB - Use stored procedure


Tag : sql , By : user107506
Date : March 29 2020, 07:55 AM
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.

SSRS Report fed by Stored Procedure \ IN operator


Tag : sql-server , By : user185144
Date : March 29 2020, 07:55 AM
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] (
    @Year INT,
    @Week INT
)
RETURNS table AS
RETURN (
    SELECT a.[Group]
          ,a.[Owner]
          ,a.[OwnerLastName]
          ,a.[SalesPersonNumber]
          ,SUM(a.[tot_sls_amt]) AS CYSales
          ,(b.[tot_sls_amt]) AS PYSales
    FROM [001].[MW].[MW_Submitted_Weeks_By_RBC_ROY] a
         LEFT JOIN (SELECT sum([tot_sls_amt]) tot_sls_amt
                          ,[Group]
                          ,[Owner] 
                          ,[SalesPersonNumber]
                    FROM [001].[MW].[MW_Submitted_Weeks_By_RBC_ROY]
                    WHERE ((Year = @Year-1 AND Week <= @Week) OR 
                           (Year = @Year - 2 AND Week > @Week)) 
                          AND cmp_status = 'A' 
                    GROUP BY [Group]
                            ,[Owner] 
                            ,[SalesPersonNumber]) b 
             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] 
            ,a.[Owner]
            ,a.[OwnerLastName]
            ,a.[SalesPersonNumber]
            ,(b.[tot_sls_amt])
)
SELECT * 
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?


Tag : sql-server , By : user181445
Date : March 29 2020, 07:55 AM
wish help you to fix your issue The Report Wizard will let you only use Query strings so you can't select a stored procedure, but You can use the EXEC statement to specify a stored procedure to run.
exec my_stored_procedure_name

Using T-SQL find the Stored Procedure associated to an SSRS Report


Tag : sql-server , By : Jonathan
Date : March 29 2020, 07:55 AM
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
SELECT
    [Path],
    Name,
    report_xml.value( '(/Report/DataSources/DataSource/@Name)[1]', 'VARCHAR(50)' ) AS DataSource,
    report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())[1]', 'VARCHAR(MAX)' ) AS CommandText, 
    report_xml.value( '(/Report/DataSets/DataSet/Query/CommandType/text())[1]', 'VARCHAR(100)' ) AS CommandType, 
    report_xml
FROM
    (
    SELECT 
        [Path], 
        Name, 
        [Type],
        CAST( CAST( content AS VARBINARY(MAX) ) AS XML ) report_xml 
    FROM dbo.[Catalog]
    WHERE Content IS NOT NULL
    AND [Type] = 2
    ) x
WHERE 
--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())[1]', '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)


Tag : development , By : oiyto
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
shadow
Privacy Policy - Terms - Contact Us © scrbit.com