Concatenate list of values under a group

I have the following dataset:

State ZIP Homes Schools

WA 98007 2000 4

WA 98052 3000 5

WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as “[98007, 98052, 98079]” how can I accomplish this.

My report needs to show:

State Homes Schools

[98007, 98052, 98079] 7000 12

Any help will be greatly appreciated

SPJ11, Why dont you do this in SQL itselves? You can also do this in SSRS. At the group level use,

Sum(Fields!Homes.value), Sum(Fields!Schools.value) -- these will retun the counts correctly.

I am not sure as how to concatnate the zip codes. I thought this should work

Join(Fields!Zip.Value) -- but this does not seem to work, I will see if I can come with something else...

|||

Never mind. I could make it work. Thanks to everyone who spent time on this thread

sqlsql

Concatenate 2 date fields to show longdate format

How can I concatenate 2 date fields so that they show the date in long format i.e 18 March 2006 to 21 March 2006. I can get the expression to concatenate but I want to format the dates as above.

Thanks

I believe you can use this...

=CDate(Fields!date1.Value).ToString("dd MMMM yyyy")&" to "&CDate(Fields!date2.Value).ToString("dd MMMM yyyy")

where you can replace the fields where appropriate.

Craig

sqlsql

CONCAT + expression

how can i CONCAT 2 columns & () ?
SELECT CONCAT(A,B) AS C From myTAble
but I want to get A (B)
dog (red)
thank youare they both strings?

if so SELECT COALESCE(A,'') + '(' + COALESCE(B,'') + ')'|||yes string(100)

Complex expression in matrix subtotal

Hello.
How can I make an expression in matrix subtotal as (sum1) & "/" & (sum2)?
Thank's.=Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
If you want something other than this in the detail cells, you'll need to
use the InScope function. Something like this:
=iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
Value)/Sum(Fields.ItemCount.Value))
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Efim" <Efim@.discussions.microsoft.com> wrote in message
news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> Hello.
> How can I make an expression in matrix subtotal as (sum1) & "/" & (sum2)?
> Thank's.|||Thanks.
"Chris Hays [MSFT]" wrote:
> =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> If you want something other than this in the detail cells, you'll need to
> use the InScope function. Something like this:
> =iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> Value)/Sum(Fields.ItemCount.Value))
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."|||As indicated by Chris, you would use the InScope function. The subtotal
column is not in the scope of the detail columns of the group. E.g.
=iif(InScope("matrix1_Category"), Sum(Fields!Amount.Value),
Avg(Fields!Amount.Value))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shai" <Shai@.discussions.microsoft.com> wrote in message
news:E2641A91-9CAF-4C01-8693-A5BB142CCD29@.microsoft.com...
> Hi Chris !
> When you add subtotal to matrix it's add a column but you don't have
access to the expression of the actual calculation. I would like my "Total
column" to calculate Average of the columns on the left. How is this
possible?
> TIA
> Shai
> "Chris Hays [MSFT]" wrote:
> > =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> >
> > If you want something other than this in the detail cells, you'll need
to
> > use the InScope function. Something like this:
> >
> >
=iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> > Value)/Sum(Fields.ItemCount.Value))
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Efim" <Efim@.discussions.microsoft.com> wrote in message
> > news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> > > Hello.
> > > How can I make an expression in matrix subtotal as (sum1) & "/" &
(sum2)?
> > > Thank's.
> >
> >
> >|||Hi Robert,
I tried this option and i am getting #Error in the data column.Moreover i am
getting the below error on the preview of the report.
Aggregate functions other than First, Last, Previous, Count, and
CountDistinct can only aggregate data of a single data type.
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"Robert Bruckner [MSFT]" wrote:
> As indicated by Chris, you would use the InScope function. The subtotal
> column is not in the scope of the detail columns of the group. E.g.
> =iif(InScope("matrix1_Category"), Sum(Fields!Amount.Value),
> Avg(Fields!Amount.Value))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shai" <Shai@.discussions.microsoft.com> wrote in message
> news:E2641A91-9CAF-4C01-8693-A5BB142CCD29@.microsoft.com...
> > Hi Chris !
> >
> > When you add subtotal to matrix it's add a column but you don't have
> access to the expression of the actual calculation. I would like my "Total
> column" to calculate Average of the columns on the left. How is this
> possible?
> >
> > TIA
> > Shai
> >
> > "Chris Hays [MSFT]" wrote:
> >
> > > =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> > >
> > > If you want something other than this in the detail cells, you'll need
> to
> > > use the InScope function. Something like this:
> > >
> > >
> =iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> > > Value)/Sum(Fields.ItemCount.Value))
> > >
> > > --
> > > My employer's lawyers require me to say:
> > > "This posting is provided 'AS IS' with no warranties, and confers no
> > > rights."
> > >
> > > "Efim" <Efim@.discussions.microsoft.com> wrote in message
> > > news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> > > > Hello.
> > > > How can I make an expression in matrix subtotal as (sum1) & "/" &
> (sum2)?
> > > > Thank's.
> > >
> > >
> > >
>
>

comparision filter on a float field

I'm trying to do a comparision filter on a float field and I keep getting the
error
> Expression =Fields!float_cal.Value
> Operator <= > Value =0
I keep getting the error 'check data type returned'. I tried
> Expression =CInt(Fields!float_cal.Value )
but this did not work either.
Any ideasHi Marg,
if it is a float field you should convert it to double, not integer.
Try Expression = Cdbl(Fields!float_cal.Value ).
If that does not work you could even try cdbl(Expression) =Cdbl(Fields!float_cal.Value ).
Best regards,
Stefan
"marg" wrote:
> I'm trying to do a comparision filter on a float field and I keep getting the
> error
> > Expression =Fields!float_cal.Value
> > Operator <=> > Value =0
> I keep getting the error 'check data type returned'. I tried
> > Expression =CInt(Fields!float_cal.Value )
> but this did not work either.
> Any ideas
>