CODEWITHSUNDEEP
reporting-services
tsqln00b
tsqln00b

Reputation: 355

SSRS Tablix calculated field issue

I am using Microsoft Visual Studio 2010 to create a Tablix report. In this report I have two fields that I pull data for and another one that I want to be a calculated field. Here is what the Tablix looks like:

Location     Item Count     % Difference
ORD          100            0
MDT          90             10
RN4          80             20

The query to pull this data is simple:

SELECT
    IFLOC AS Location,
    COUNT(IFITEM) AS ItemCount
FROM
    dbo.S2K_IB
WHERE
    IFDEL <> 'I'
GROUP BY
    IFLOC
ORDER BY
    ItemCount DESC

My issue is the % Difference column. I want it to always find the difference in Location ORD as it relates to the other locations. Meaning, Location ORD will always have the highest Item Count and I want to know the difference between the other two locations as they relate to Location ORD. How can I achieve this?

Upvotes: 0

Views: 247

Answers (1)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

I see that the query has the Location grouped and ordered. You can use the following expression in this case.

=(first(Fields!ItemCount.Value, "DataSet1")-Fields!ItemCount.Value)/first(Fields!ItemCount.Value, "DataSet1")

Make sure to change the textbox format to percentage.

Upvotes: 2

Related Questions