Retrieve Row Count on SSRS / MySQL

Question

I'm using SSRS linked to MySQL by ODBC. My query sums the payment amount by customer, and sorts by amount desc. I want to use this to create a LeaderBoard in SSRS, showing the Rank, and only including the top 10 customers.

Option 1: Do an additional query on my group query in SQL, adding the Row Number.

Option 2: Add a calculated field in SSRS.

Option 1 seemed bulky, so started with Option 2; I added a calculated field to the dataset called "Rank", defined as =RowNumber("DataSet1")

I added a calculated field to the dataset called "Rank", defined as: =RowNumber("DataSet1")

But I got the following error:

The expression used for the calculated field 'Rank' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

So, I then added it to the actual tablix, and I was able to get the Rank to show correctly. (When I did this, it automatically added an extra column to my dataset.

I then wanted to filter the top 10 customers.

I first tried by "bottom 10" on this new field, but it didn't work. (Seems this field is all zeros in the actual dataset.)

I then tried by "top 10" on the payment amount, but received an error that the filter only supports Integers.

So I tried to convert the payment amount to Integer in MySQL, using CAST and Convert, but they don't support conversion to Integer, and SSRS didn't like 'SIGNED' or any of the other options.

I then started trying Option 1, which was building the query into MySQL. I added:

SET @rank=0; 
SELECT @rank:=@rank+1 AS Rank, ...

This works in MySQL, but I get an error when I paste that query into the report definition on SSRS.

Any ideas?

Unknown Unknown · Accepted Answer · 2013-07-02T15:21:45

Rather than setting @rank=0 in a separate statement, try setting it in a subquery cross-joined to the main query - like so:

SELECT @rank:=@rank+1 AS Rank, ...
FROM (SELECT @rank:= 0) r
CROSS JOIN ...

Retrieve Row Count on SSRS / MySQL

1 Answers