CODEWITHSUNDEEP
matrixreporting-servicesmaxreportbuilder
Steph
Steph

Reputation: 11

Get only the Max Value of [Count by Date]

I have a dataset that gathers all of the orders that were signed in a year. I need to get the count of Maximum # of orders that were signed in a single day. I set up a matrix that groups my rows based on date signed and my column in the matrix does the count of orders.

I need to only provide the single date that had the most orders signed. How do I pull only the max value from a tablix? Or is that not even the right way to start this? enter image description here

Upvotes: 0

Views: 371

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Rather than passing a lot of data to SSRS and then trying to apply logic using expressions, I think this would be much easier to do in your dataset query.

Something like

SELECT * FROM 
    (
        SELECT SignedDate, rCount, RANK() OVER(ORDER BY rCount Desc) as rnk
            FROM (
                        SELECT SignedDate, Count(*) as rCount FROM myTable GROUP BY SignedDate
                ) rc 
    ) a 
    WHERE a.rnk = 1

Starting from the inner query we get a count of records by SignedDate.

Then we get the RANK() of each returned row ordered by descending count.

Note: If two SignedDates both have the highest number of records, they will both be assigned a rank of 1.

The final outer query just returns anything with a rank of 1.

Your report then only gets the record or records (if more than one date has the same highest count) so you can use a simple table to display them.

Upvotes: 1

Related Questions