reporting-servicesssrs-2017

Fit multiple results returned from one dataset in single textbox


I have a data set which returns results like below:

   SELECT 
    [Name] 
     ,[Count]
   FROM [dbo].[TestTable1]
   
   ID           Name                    Count
   ------------------------------------------
    1           International school    100
    2           World school            200
    3           Universe school         400
    

I have one text box in which I would like to show the count. enter image description here

            Here is the international school count: «Expr»
            Here is the world school count:    «Expr»
            Here is the Universe school count: «Expr»
            

I'm seeking an expression in which the result should return like below:

            Here is the international school count: 100
            Here is the world school count:    200
            Here is the Universe school count: 400

Here is my example expression :

            =IIF(First(Fields!Name.Value, "CountinOneBox")="International school",(Fields!Count.Value, "CountinOneBox"),"")
            

Note: sum(Fields!Count.Value, "CountinOneBox") provides 700

How can I get this results?


Solution

  • I would do this in SQL. I've replicated your sample data here and then just dropped the resulting field in a simple report

    DECLARE @t table(ID int, [Name] varchar(100), [Count] int)
    
    INSERT INTO @t VALUES
        (1, 'International school', 100),
        (2, 'World school', 200),
        (3, 'Universe school', 400)
    
    DECLARE @s nvarchar(max) = ''
    DECLARe @crlf char(2) = char(13) + char(10)
    
    SELECT @s = 
            @s + 'Here is the ' 
               + [Name] 
               + ' count: ' 
               + CAST([COUNT] as varchar(10)) 
               + @crlf
        FROM @t
    
    SELECT @s as Result
    

    Results looks like this. (I've set a border on the text box so you can see it's not wrapping, it's using the CR/LF we added.

    enter image description here