CODEWITHSUNDEEP
sqlsql-serverreporting-services
Frenchi In LA
Frenchi In LA

Reputation: 3169

ssrs dataset shows just 1 row

I need to add a parameter in order to select a range from Monday to Sunday. I made a dataset with the following code

DECLARE @W int
SET @W = 0
WHILE @W < 10
BEGIN

  SELECT
    CONVERT(VARCHAR(25),DATEADD(wk, DATEDIFF(wk, 6, GETDATE() -7*@W), 0),
    101)                                                      +' - ' + CONVERT(
    VARCHAR(25),DATEADD(wk, DATEDIFF(wk, 6, GETDATE()         -7*@W), 6),
    101) Dte,
    CONVERT(DATE,DATEADD(WEEK,-1*@W,DATEADD(DAY , 1-DATEPART(WEEKDAY,
    GETDATE()),GETDATE()))) "Sunday"
   SET @W = @W +1

END

And I added a parameter to get available values from this dataset, but It does show just the first row 05/29/2017 - 06/04/2017. What I'm doing wrong and How can I get the 10 rows from the query?

Upvotes: 0

Views: 53

Answers (1)

GarethD
GarethD

Reputation: 69769

Your query returns 10 result sets, and SSRS will only ever use the first. I am going to skip ahead how to correct your query, and go straight to the right way of doing it, you don't need a loop at all. It is fairly straight forward to generate a list of 10 numbers (0-9) in SQL Server using a table value constructor:

SELECT Number
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t (Number);

With this table of numbers, you can generate your full data set:

 SELECT
        CONVERT(VARCHAR(25),DATEADD(wk, DATEDIFF(wk, 6, GETDATE())-7*t.Number, 0), 101) +' - ' + 
        CONVERT(VARCHAR(25),DATEADD(wk, DATEDIFF(wk, 6, GETDATE())-7*t.Number, 6),101) Dte,
        CONVERT(DATE,DATEADD(WEEK,-1*t.Number,DATEADD(DAY , 1-DATEPART(WEEKDAY,GETDATE()),GETDATE()))) "Sunday"
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS t (Number);

If you need more than 10 rows, then there are still much better ways than a loop. See the following series for further reading:

Upvotes: 3

Related Questions