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
How can I get this results?
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.