SSRS rank with coditions

Question

Hi everyone,
I have a report that tracks how many hot cold leads i have. now i have a field that called strActionLevel and in the column it Hot Cold or waiting
I want to rank my results based on the strActionlevel .

my sql query:
SELECT
tblContact_Archive.strPropIDCalled AS strPropID,
CASE WHEN tblContact_Archive.intWebRef > 0 THEN tblContact_Archive.intWebRef ELSE NULL END AS intWebRef,
tblContact_Archive.lngContact,
tblContactHistory_Archive.dtMoveIn,
CAST(tblContactHistory_Archive.strRent as money) AS mnyRent,
CASE
WHEN tblContactHistory_Archive.dtMoveIn < @DatePrint THEN 1
ELSE 0
END AS fMovedIn,
tblContact_Archive.strActionLevel,
tblContact_Archive.strSourceID,
tblContact_Archive.dtIntro,
tblContact_Archive.dtSent,
ISNULL(tblContact_Archive.strLast, '') + ', ' + ISNULL(tblContact_Archive.strFirst, '') AS strName,
tblReservationPayments.mnyAmount AS mnyResPmtAmount,
tblProperties.strRVP,
tblProperties.strRTLID,
tblRVPUsers.strLast AS strRVPLast,
tblRTLUsers.strLast AS strRTLLast
FROM
tblContact_Archive WITH (NOLOCK)
INNER JOIN tblProperties WITH (NOLOCK) ON tblContact_Archive.strPropIDCalled = tblProperties.strPropID
LEFT JOIN tblContactHistory_Archive WITH (NOLOCK) ON tblContact_Archive.lngContact = tblContactHistory_Archive.lngContact --this is unique now
LEFT JOIN tblReservationPayments WITH (NOLOCK) ON tblContact_Archive.intWebRef = tblReservationPayments.intWebRef AND tblReservationPayments.dtVoid IS NULL
LEFT JOIN tblUsers tblRVPUsers WITH (NOLOCK) ON tblProperties.strRVP = tblRVPUsers.strUserID
LEFT JOIN tblUsers tblRTLUsers WITH (NOLOCK) ON tblProperties.strRTLID = tblRTLUsers.strUserID
WHERE
CASE WHEN tblContact_Archive.dtSent IS NOT NULL THEN tblContact_Archive.dtSent ELSE tblContact_Archive.dtSentWeb END BETWEEN @DateFrom AND DATEADD(d, 1, @DateTo) AND
tblContact_Archive.strActionLevel IN (@IntroLevel) AND
CAST(ISNULL(CASE WHEN tblContactHistory_Archive.dtMoveIn < @DatePrint THEN 1 ELSE 0 END, 0) as int) IN (@intMoveIn) AND
CASE WHEN tblContact_Archive.intWebRef IS NOT NULL THEN 1 ELSE 0 END IN (@intSource) AND
tblContact_Archive.strPropIDCalled IN (@Prop)

Answer 1

Hi @Jannette Jones
Your table is a bit complicated and I don't quite understand what you mean. You want to rank your results according to strActionlevel, which according to your table seems to show results as Hot, Cold or waiting.
What is the result of your desired ranking? Is the ranking based on strActionlevel only? Or is there another basis? For example, if in this group, the strActionlevel in the table shows the most Hot results, then the ranking results show Hot?
Best regards,
Aniya

Answer 2

Hi @Jannette Jones
From my searches, there are several ways to achieve ranking in SSRS. It seems that the simpler method is to use the RunningValue function, but the data needs to be sorted first.
I did a simple test. Here is the raw data.

Now I want to sort by the number of "Hot" in each group. Click Group Properties to add an expression in the sort window. For the convenience of observation, I also add a column "Hot Number" to the report.

Then we can add a column "Rank", using the RunningValue function.

Preview:

For more details, you can refer to this link: ranking-in-ssrs.
Best regards,
Aniya