September 2, 2009 at 12:52 pm
What I'd like to do, but can't seem to find a way to get it done is this:
I want to prompt the user to select a number of participants. Based on the number the user enters, I need to automatically generate this number of details rows.
For example, if the user enters 10 (meaning they need 10 details rows), the report would generate the following:
Participant 1
Participant 2
Participant 3
Participant 4
Participant 5
Participant 6
Participant 7
Participant 8
Participant 9
Participant 10
Each on its own row.
TIA
September 3, 2009 at 7:24 am
Hi,
The tablix or matrix or similar object in the SSRS report can be bound to a dataset. Use a stored procedure with a parameter as the dataset. This will allow the user to specify the number of participants, and the no of rows displayed in the table for example will grow or shrink depending on the number of records returned by the stored procedure.
Regards,
Barkha.
September 3, 2009 at 7:39 am
It helps if you have a tally (or numbers) table, but it's easy enough to generate one on the fly. I've used the sys.columns table here, which should be large enough for your purposes here.
DECLARE @num AS tinyint
SET @num = 10
SELECT TOP (@num)
'Participant '
+ Cast( Row_Number() OVER ( ORDER BY object_id ) AS varchar(10) )
FROM sys.columns
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2009 at 11:40 am
Perfect. Thank you very much.
September 3, 2009 at 1:16 pm
TIA,
Another option is to generate a temporary table on the fly based on what you are looking to do. The below may help with that.
/***************************************************
Author: Brian K. McDonald, MCDBA, MCSD
Date: 9/3/2009
Purpose to generate the amount of detail rows
based on the value passed in
***************************************************/
DECLARE @Number_Of_Detail_Rows SMALLINT
, @Current_Row SMALLINT
SELECT @Number_Of_Detail_Rows = 10
, @Current_Row = 1
CREATE TABLE #My_Result_Set (Your_String VARCHAR(25))
WHILE @Current_Row <= @Number_Of_Detail_Rows
BEGIN
INSERT INTO #My_Result_Set VALUES('Participant ' + CONVERT(VARCHAR(5),@Current_Row))
SET @Current_Row = @Current_Row + 1
END
SELECT * FROM #My_Result_Set --this will be returned to the report
Best of luck,
Brian K. McDonald, MCDBA, MCSD
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant
Jacksonville, Florida
September 3, 2009 at 5:25 pm
Yes, you can use a WHILE loop, but I would not recommend it. According to the execution plan the WHILE loop is 24 times as expensive as the Tally table for 10 records. When you increase that to 100 records, the execution plan estimates that the Tally table takes up 0%.
The actual times aren't quite that large, but they are significant. With 100 records, the WHILE loop takes 2.5 seconds whereas the tally table takes 0.187 seconds.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2009 at 7:09 pm
I agree. As long as the user executing the report has permission to the underlying objects such as the sys.columns table. We often lock the report user down to objects and typically would not allow querying system tables. But you are absolutely correct in speed. A table variable could also be used.
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant
Jacksonville, Florida
September 4, 2009 at 7:04 am
It doesn't matter which table you use as long as it has enough rows to give you the highest number that you want to allow. I simply used the sys.columns table, because every database has one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply