January 23, 2017 at 4:05 am
Hello,
I have a report with a parameter than can take values from 1 to 12. End user of the report can choose one value between 1 and 12. What i would like to do is to display numers in a matrix from 1 to number choosed by end user. In attached .png file there are two red brackets pointing to a parameter value (7) and expression that should generate numbers from 1 to 7. Can anyone help with that? Sorry for my english.
Best regards
January 23, 2017 at 4:17 am
Not sure if you meant rows, or comma delimited, so here's both:USE DevTestDB;
GO
CREATE TABLE #Numbers (Num INT);
GO
INSERT INTO #Numbers
VALUES (1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12);
GO
DECLARE @N INT;
SET @N = 7;
SELECT N.Num
FROM #Numbers N
WHERE N.Num <= @N;
SELECT STUFF((SELECT ',' + CAST(N.Num AS VARCHAR(2))
FROM #Numbers N
WHERE N.Num <= @N
FOR XML PATH('')),1,1,'');
GO
DROP TABLE #Numbers;
EDIT: These alignment issues are really getting on my nerves now... I really shouldn't have to reformat my text after pasting...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 23, 2017 at 4:23 am
Thank you for replying. Unfortunatelly it's not quite what i wanted because it would require adding a new dataset to my report and join two datasets in one matrix.
January 23, 2017 at 4:55 am
lukaszpiech - Monday, January 23, 2017 4:23 AMThank you for replying. Unfortunatelly it's not quite what i wanted because it would require adding a new dataset to my report and join two datasets in one matrix.
Sorry, you posted in the SQL forums, so assumed you were looking for a SQL solution.
I think the only way you might achieve that cleanly would be with your own customer code (not something I've really used).
You could do it with a switch statement, but this relies on that your process will always have a number between 1 and 12. Anything else, or if you need to change the range later, it's not going to work.=SWITCH(parameters!Numbers.Value="12","1,2,3,4,5,6,7,8,9,10,11,12",parameters!Numbers.Value="11","1,2,3,4,5,6,7,8,9,10,11",parameters!Numbers.Value="10","1,2,3,4,5,6,7,8,9,10",parameters!Numbers.Value="9","1,2,3,4,5,6,7,8,9",parameters!Numbers.Value="8","1,2,3,4,5,6,7,8",parameters!Numbers.Value="7","1,2,3,4,5,6,7",parameters!Numbers.Value="6","1,2,3,4,5,6",parameters!Numbers.Value="5","1,2,3,4,5",parameters!Numbers.Value="4","1,2,3,4",parameters!Numbers.Value="3","1,2,3",parameters!Numbers.Value="2","1,2",parameters!Numbers.Value="1","1")
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 23, 2017 at 5:05 am
One more time thank you for replying! I've made a mistake posting in the SQL forums so I am sorry. Your help might work fine!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply