I'm using SSRS for reporting and executing a stored procedure to generate the data for my reports
DECLARE @return_value int
EXEC @return_value = [dbo].[MYREPORT]
@ComparePeriod = 'Daily',
@OverrideCompareDate = NULL,
@PortfolioId = '5,6',
@OverrideStartDate = NULL,
@NewPositionsOnly = NULL,
@SourceID = 13
SELECT 'Return Value' = @return_value
GO
In the above when I passed @PortfolioId = '5,6'
it is giving me wrong inputs
I need all records for portfolio id 5 and 6 also
is this correct way to send the multiple values ?
When I execute my reports only giving @PortfolioId = '5'
it is giving me 120 records
and when I execute it by giving @PortfolioId = '6'
it is giving me 70 records
So when I will give @PortfolioId = '5,6'
it should have to give me only 190 records altogether, but it is giving me more no of records I don't understand where I exactly go wrong .
Could anyone help me? thanks
all code is too huge to paste , i'm pasting relevant code please suggest clue.
CREATE PROCEDURE [dbo].[GENERATE_REPORT]
(
@ComparePeriod VARCHAR(10),
@OverrideCompareDate DATETIME,
@PortfolioId VARCHAR(50) = '2', --this must be multiple
@OverrideStartDate DATETIME = NULL,
@NewPositionsOnly BIT = 0,
@SourceID INT = NULL
) AS
BEGIN
SELECT
Position.Date,
Position.SecurityId,
Position.Level1Industry,
Position.MoodyFacilityRating,
Position.SPFacilityRating,
Position.CompositeFacilityRating,
Position.SecurityType,
Position.FacilityType,
Position.Position
FROM
Fireball_Reporting.dbo.Reporting_DailyNAV_Pricing POSITION WITH (NOLOCK, READUNCOMMITTED)
LEFT JOIN Fireball.dbo.AdditionalSecurityPrice ClosingPrice WITH (NOLOCK, READUNCOMMITTED) ON
ClosingPrice.SecurityID = Position.PricingSecurityID AND
ClosingPrice.Date = Position.Date AND
ClosingPrice.SecurityPriceSourceID = @SourceID AND
ClosingPrice.PortfolioID IN (
SELECT
PARAM
FROM
Fireball_Reporting.dbo.ParseMultiValuedParameter(@PortfolioId, ',') )
There's no way to make an NVARCHAR parameter take "more than one value". What I've done before is - as you do already - make the parameter value like a list with comma-separated values. Then, split this string up into its parts in the stored procedure.
create procedure sp1 (p1 in varchar2) as begin select proc_id from proc_tbl where proc_id in (p1); end; The user expects to input multiple values separate by comma or space such as a1, b2, c3 in p1. All the PROC_ID stored in proc_tbl are in upper case.
Pack the values into one string with comma separated. Set the string as parameter and pass it into the SQL statement. Unpack the values and insert the values into a table, Where customerid in (select id from #temp)
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to convert the string parameter to xml variable and split it using comma delimiter and then you can query it. Given below is the script.
USE THIS
I have had this exact issue for almost 2 weeks, extremely frustrating but I FINALLY found this site and it was a clear walk-through of what to do.
http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/
I hope this helps people because it was exactly what I was looking for
This can not be done easily. There's no way to make an NVARCHAR
parameter take "more than one value". What I've done before is - as you do already - make the parameter value like a list with comma-separated values. Then, split this string up into its parts in the stored procedure.
Splitting up can be done using string functions. Add every part to a temporary table. Pseudo-code for this could be:
CREATE TABLE #TempTable (ID INT)
WHILE LEN(@PortfolioID) > 0
BEGIN
IF NOT <@PortfolioID contains Comma>
BEGIN
INSERT INTO #TempTable VALUES CAST(@PortfolioID as INT)
SET @PortfolioID = ''
END ELSE
BEGIN
INSERT INTO #Temptable VALUES CAST(<Part until next comma> AS INT)
SET @PortfolioID = <Everything after the next comma>
END
END
Then, change your condition to
WHERE PortfolioId IN (SELECT ID FROM #TempTable)
EDIT
You may be interested in the documentation for multi value parameters in SSRS, which states:
You can define a multivalue parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a data source by using the query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, Analysis Services, SAP BI NetWeaver, or Hyperion Essbase.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.
This I found here.
I spent time finding a proper way. This may be useful for others.
Create a UDF and refer in the query -
http://www.geekzilla.co.uk/view5C09B52C-4600-4B66-9DD7-DCE840D64CBD.htm
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With