0

My condition is that industry who has paying highest time amount and total receivable amount of that industry

1

My expected result is ABC industry is highest number of count and total receive amount for that is 3584

I am trying find SSRS expression for above query but I couldn't. please help me

Steven
  • 1,996
  • 3
  • 22
  • 33
Mitesh Patil
  • 1

1 Answers1

0

It will probably be better to do this in your dataset query, something like this.

DECLARE @t TABLE(IndustryType Varchar(3), Amount int)
INSERT INTO @t VALUES
    ('ABC', 500),('XYZ', 304),('KLM', 683),('ABC', 529),('ERJ', 703),
    ('XYZ', 902),('ABC', 852),('KLM', 950),('ABC', 703),('ABC', 1000)

SELECT TOP 1 * 
    FROM(
        SELECT IndustryType, COUNT(*) as cnt, SUM(Amount) as TotalAmount 
            FROM @t
            GROUP BY IndustryType
    ) x 
    ORDER BY x.cnt DESC

This gives the following results

enter image description here

If you cannot do this for whatever reason, edit your question and show what the final output should look like.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Alan thanks for this solution but I want expression for stored procedure. Could you please provide me expression ? – Mitesh Patil Feb 02 '21 at 07:20
  • As I said in the answer, if this does not help, EDIT your question and show what you expect the final output to look like in the report. I think this might be quite difficult to achieve as an expression, that's why I offered an alternative – Alan Schofield Feb 02 '21 at 11:11