I have made 3 parameter name Invno1 , Date1 , Amount1 & Pass this in table as below mentioned :-

I have made 3 parameter name Invno1 , Date1 , Amount1 & Pass this in table as below mentioned :-

=Parameters!InvNo1.Value(0) , =Parameters!Date1.Value(0) , =Parameters!Amount1.Value(0)

In invno1 parameter i pass multiple values like = 1 , 2
In Date1 parameter i pass multiple values like = 3 , 4
In Amount1 parameter i pass multiple values like - 5 , 6

but only 1st data field in table like below mentioned image SSRS ScreenShot , i want this data as

• reporting-services
• ssrs-2008
• ssrs-2012
• ssrs-tablix
• sqlreportingservice

• A few questions: Is this supposed to retrieve data from a database or do you just want to show the parameter values in a table? How do you know that Inv 1, Date 3 and Amount 5 should be on the same row? – Alan Schofield Commented Nov 20, 2024 at 8:38
• I want to show the parameter values in a table . – Nemil Commented Nov 20, 2024 at 8:47

1 Answer 1

I've made a few assumptions here..

1. You can connect the report to SQL Server, as most of the work will be done there

2. As it looks like (based on the tags), an old version of SQL Server, you cannot use the string_split function that comes with newer version so I've included the code to create this. If you already have a function then use that.

The basic idea is to pass the parameter values to a stored proc on SQL Server, this will call a Table Valued Function get each parameter into a table. We'll then join the tables (one for each parameter) to return the required results.

Step 1: Create the TVF. If you already have a function that does this, or you are using a new version of SQL Server then you can use the string_split function.

CREATE FUNCTION dbo.SplitString (@CSV VARCHAR(8000), @Delim varchar(10))
RETURNS TABLE
AS
RETURN
(
    WITH Numbers AS -- builds a table of integers from 1 to 2048 to use as row numbers
    (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Position
        FROM master.dbo.spt_values
        WHERE type = 'P' 
        AND number < ISNULL(DATALENGTH(@CSV),0)
    ),
    Splits AS -- split the string by delim and join to the numbers CTE 
    (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY Position) AS Position,
            LTRIM(RTRIM(SUBSTRING(@CSV, Position, 
                CHARINDEX(@Delim, @CSV + @Delim, Position) - Position))) AS CSValue
        FROM Numbers n
        WHERE SUBSTRING(@Delim + @CSV, Position, 1) = @Delim
    )
    -- finally, select the results of the Splits CTE trimming CSValue
    SELECT 
        Position,
        LTRIM(RTRIM(CSValue)) as CSValue
    FROM Splits
    WHERE CSValue <> ''
)

If we run the following statement

SELECT * FROM dbo.SplitString('A1 , B2,Z3,Y4', ',')

We get these results

Step 2: Create a proc to return the results

CREATE PROC dbo.ParamsToTable(@InvNo varchar(8000), @Dates varchar(8000), @Amounts varchar(8000)) AS
DECLARE @Delim varchar(10) = ','

SELECT 
    i.CSValue as InvNo,
    d.CSValue as [Date],
    a.CSValue as Amount
    FROM dbo.SplitString(@InvNo, @Delim) i 
        JOIN dbo.SplitString(@Dates, @Delim) d on i.[Position] = d.[Position]
        JOIN dbo.SplitString(@Amounts, @Delim) a on i.[Position] = a.[Position]
    ORDER BY a.[Position]

This proc takes 3 params as simple CSV strings, passes each to the TVF we created earlier and joins the results.

So if we do this

EXECUTE dbo.ParamsToTable '1,2', '3,4', '5,6'

We get these results.

Now all we have to do is pass the parameters to the SP from SSRS.

Step 3 : Call the SP from SSRS

In your report, add a dataset that calls the SP above. For each of the 3 parameters, we need to join the individual values into a single string

To do this set each parameter value to

=JOIN(Parameters!InvNo.Value, ",")

This assumes your invoice number parameters is called InvNo (case sensitive) in your report design.

Repeat this for the other two parameters.

Now you can simply add a table to your report pointing to this new dataset.

That should be it.