CODEWITHSUNDEEP
reporting-servicesssrs-2017
AskMe
AskMe

Reputation: 2577

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?

Upvotes: 0

Views: 88

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21748

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

Upvotes: 1

Strawberryshrub
Strawberryshrub

Reputation: 3399

You need to write everything in one expression if you want to have it all in one textbox. Like this:

Lets say the follwing expression returning your 100, 200, 400.

=Sum(Fields!Result1.Value)  ' 100
=Sum(Fields!Result2.Value)  ' 200
=Sum(Fields!Result3.Value)  ' 400


="Here is the international school count: " & Sum(Fields!Result1.Value) & VbNewLine &
 "Here is the world school count: " & Sum(Fields!Result2.Value) & VbNeLine &
 "Here is the Universe school count: " & Sum(Fields!Result3.Value)

Upvotes: 0

Related Questions