April 27, 2012 at 6:37 am
I have a table with one row group and one column group. I would like to be able concatenate all of the values in the grouped cell. If I use Count(Fields!AttrValue.Value) I am returned with the correct number of values for each of the cells in my grouping. I was hoping to take that to the next step and get the all of the values that were counted, something like ConCat(Fields!AttrValue.Value,”|”)
I have been told that you cannot aggregate values across both a row group and a column group in a single expression. OK how do I do it in more than one expression 🙂
I have tried to use the RunningValue function along with some custom code but this function doesn’t work right when using both the row and column groups.
Is there a way to concatenate these values?
April 27, 2012 at 8:47 am
Just to update you a bit I have tried to use the following code but it looks like the Last function in RunningValue is stopping when the row group value has changed but not when the column group has changed so I am getting erroneous information in my rows. I'm wondering if I could nest the RunningValue Function to do this.
Report Code Section:
Private CurrRowGroup As String
Private CurrColGroup As String
Private ConcatVal As String
Public Function ConCat(ElementVal As String, Seperator As String, RowGroup As String, ColGroup As String) As String
Dim ConcatVals As String()
Dim ValueFound As Boolean = False
Dim i As Integer
If (CurrRowGroup = RowGroup And CurrColGroup = ColGroup) Then
If Len(ElementVal) > 0 Then
' If ElementVal already exist in ConcatVal then there is no need to add the same value twice
If Len(ConcatVal) > 0 Then
ConcatVals = Split(ConcatVal, Seperator)
For i = 0 To ConcatVals.GetUpperBound(0)
If ConcatVals(i) = ElementVal Then
ValueFound = True
End If
Next i
If ValueFound = False Then
ConcatVal = ConcatVal & Seperator & ElementVal
End If
Else
ConcatVal = ElementVal
End If
End If
Else
CurrRowGroup = RowGroup
CurrColGroup = ColGroup
ConcatVal = ElementVal
End If
Return ConcatVal
End Function
Expression Code Section:=RunningValue
(
Code.ConCat(Fields!AttrValue.Value, "|", Fields!TagName.Value, Fields!AttrName.Value),
Last,
"TagName"
)
April 27, 2012 at 8:51 am
slpgma (4/27/2012)
I'm wondering if I could nest the RunningValue Function to do this.
Nope, "The Value expression for the textrun ‘AttrValue.Paragraphs[0].TextRuns[0]’ uses a RunningValue function in an outer aggregate. RunningValue functions cannot be specified as nested aggregates."
April 27, 2012 at 1:02 pm
OK I am slowly getting closer to an answer, I think :crazy:. I can use the query below to concat the information I need into a dataset but I some how need to pass the query the current row value that I'm on so that in concats the right data (I used SAMPLE1 as a test). Since datasets have to be define when the report is ran I don't think this will work. I also thought about using a stored procedure but that can't be used for a textbox value expression.
Any suggestions? I can't be the first person to give this a go.
Thanks
SELECT AttrName,
SUBSTRING
(
(
SELECT '; ' + CAST(AttrValue AS varchar(MAX))
FROM REP_TagAttributes
WHERE (AttrName = Results.AttrName AND TagName = 'SAMPLE1') FOR XML PATH ('')
)
,3,9999
) AS name_values
FROM REP_TagAttributes Results
WHERE TagName = 'SAMPLE1'
GROUP BY AttrName
May 21, 2012 at 7:28 am
OK I was able to resolve this issue by creating a Scalar function to concatenate the values I was looking for based on the fields I was using in my report's groupings. I then used this scalar function as part of my select statement for the report's dataset.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply