Trouble using matrix report with calculated values

Hello,

I have a dataset that returns the following values

DeviceId ( mac address for a device )
DeviceName ( user defined name for a device )
EventType ( has two values in and out ) many values for one device
EventValue ( ) many values for one device ( eventtype and event values come in pairs for a device)

SELECT CardEvent.DeviceId, GameDevice.Name, SUM(CardEventPoint.PointValue) AS EventTypeValue, CardEventType.Name AS EventType
FROM CardEvent INNER JOIN
CardEventPoint ON CardEvent.Id = CardEventPoint.CardEventId INNER JOIN
CardEventType ON CardEvent.EventType = CardEventType.id INNER JOIN
GameDevice ON CardEvent.DeviceId = GameDevice.MacAddress
GROUP BY CardEvent.DeviceId, GameDevice.Name, CardEventType.Name

I have a matrix report which looks like the following and works

Device ID | =Fields!EventType.Value
------------------------------------------------------------------------
=Fields!DeviceId.Value | =Fields!Name.Value | =Sum(Fields!EventTypeValue.Value)

Is it possible to do the following

Add a column header for =Fields!Name.Value

Add a column that shows the value of EventTypeValue.Value where eventType.Value =IN - eventType.Value = Out

If I should be using a table instead that would be fine two.

Help please

Keith

Answer this question

Trouble using matrix report with calculated values

Larry Beck
I was able to solve this using a table and the follwoing custom code!

    Public Function GetTransactionIn(ByVal eType1 As String,ByVal eType2 As String,ByVal v1 As Double, ByVal v2 As Double) As Double
        If (eType1 = "TransferIn") Then
            Return v1
        End If
        Return 0
    End Function

    Public Function GetTransactionOut(ByVal eType1 As String,ByVal eType2 As String,ByVal v1 As Double, ByVal v2 As Double) As Double
        If eType1 = "TransferIn" And eType2 = "TransferOut" Then
            Return v2
        ElseIf eType1 = "TransferOut" Then
            Return v1
        End If
        Return 0
    End Function

    Public Function GetHold(ByVal eType1 As String,ByVal eType2 As String,ByVal v1 As Double, ByVal v2 As Double) As Double
        If eType1 = "TransferIn" And eType2 = "TransferOut" Then
            Return v1 - v2
        ElseIf eType1 = "TransferIn" Then
            Return v1
        ElseIf eType1 = "TransferOut" Then
            Return v1 * -1
        End If
        Return 0
    End Function

    Public Function GetHoldPercentage(ByVal eType1 As String,ByVal eType2 As String,ByVal v1 As Double, ByVal v2 As Double) As Double
        If eType1 = "TransferIn" And eType2 = "TransferOut" Then
            Return (v1 - v2)/v1
        ElseIf eType1 = "TransferIn" Then
            Return 1
        ElseIf eType1 = "TransferOut" Then
            Return 0
        End If
        Return 0
    End Function
MikeGB621234
I have figured out how to show the subtraction, but not the column header.

Can anyone help
Trouble using matrix report with calculated values

Answer this question