0

I have a report that needs the user to enter a number that is passed to a parameter and that number determines the number of rows populated in a table. I then need the other parameter to pop up once per row. This is to populate a form that will be populated by a customer before the final report is run. My code that is going into SSRS at the moment is:

DECLARE @Results TABLE
(
    PositionNumber     NVARCHAR(25),
    JobTitle           NVARCHAR(50),
    JobStartDate       NVARCHAR(25),
    FullTimeEquivalent NVARCHAR(35),
    Ethnicity          NVARCHAR(25),
    Gender             NVARCHAR(25),
    AgeBand            NVARCHAR(25),
    Disability         NVARCHAR(25)
);

DECLARE @HLC INT, @COUNTER INT;
SET @COUNTER = 0;

SET @HLC = @NumberOfJobs;

WHILE @COUNTER < @HLC
BEGIN
    INSERT INTO @Results
    VALUES
    (
       'Position '+CAST(@COUNTER + 1 AS VARCHAR),
       '',
       @JobStartDate,
       @FullTimeEquivalent,
       @Ethnicity,
       @Gender,
       @AgeBand,
       @Disability
    );
    SET @COUNTER = @COUNTER + 1;

END;
SELECT 
    *
    FROM @Results;

Unfortunately I am getting all of the parameters at once. enter image description here

I have used multiple parameters before but never in order to populate a table as the report is running. I am assuming that I have my while loop in the wrong place but cant find anything to point me in the right direction. How do I get the first parameter to come up on its own and after that is populated by the user, the rest of the parameters to pop up so that the user can make their selections (and then just those parameters to pop up again in a group for each row that was entered in the first parameter). I.e. if the number 5 is entered in the first parameter the other 6 parameters should pop up 5 times.

I build my SQL query in SQL query pane and then copy it into the SSRS design query pane. I am using SSRS 2008 r2

Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39
  • 1
    If I understand you correctly, you can't do this. You can populate parameters and then have other parameters based on the selections from the first parameter but all parameters drop downs will show initially. You can't change parameter values once the report has run either. If you edit your question and mock up images to show step by step what you expect to see and in what order then maybe somebody will have some ideas. – Alan Schofield Jan 30 '18 at 16:35
  • OK thank you. Trying to run before I can walk. Will try a different approach – Caroline Allen Jan 31 '18 at 07:39

0 Answers0