Fit multiple results returned from one dataset in single textbox

Question

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.

            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

Hope I have explained this correctly. How can I get this results? Thanks.

score 1 · Accepted Answer · answered May 22 '19 at 22:24

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.

Will try this and mark as Answer. Thank you so much. – AskMe May 23 '19 at 04:36 — AskMe, May 23 '19 at 04:36

score 0 · Answer 2 · answered May 22 '19 at 04:34

0

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)

answered May 22 '19 at 04:34

Strawberryshrub

3,301
2
11
20

Is it that, I need to put all these expression in one text box? Also, you have provided hardcoded values (which was provided in question just an example). The values are dynamic. Any other clue please? – AskMe May 22 '19 at 06:21
Then just replace the name with the appropiate field `="Here is the " & Fields!Name.Value " & " count: " & Sum(Fields!Count.Value)`etc... – Strawberryshrub May 23 '19 at 04:18

Fit multiple results returned from one dataset in single textbox

2 Answers2