2

I have created a very simple report in Visual Studio (also tried it in Report Builder) that just returns 10 rows from a table in a MySQL (Not MSSQL) database. I changed the background and foreground color to make sure I could clearly see the rows in the report.

There are no parameters and it does not use a stored procedure. It's a straightforward SQL query:

select TASK_INSTANCE_ID, TASK_NAME, ASSIGNED_NAME, CREATED_DTTM
from task_instance where created_dttm > date_sub(now(), interval 1 day)
limit 10

Here's the result when previewing the report in Visual Studio. You can see that it is showing 10 rows of data. Preview showing 10 rows of data

I then publish that report to our SSRS server: Output of publishing the report

And when I view that report on the SSRS server I get 10 blank rows. Blank rows when viewing report in SSRS server

To confirm that I was actually seeing the rows and they were just blank, I changed the query to return just 5 rows. This shows that there are now only 5 blank rows, but with the defined background color. enter image description here

For reference, I also created a simple report from an Oracle DB and published it to the same folder on the same SSRS server. This report works just fine.

Oracle report that works just fine

So it appears to be something related to using a MySQL DB, but I have not been able to find anything that explains what I'm doing wrong. Any pointers/help will be greatly appreciated.

I then tried to add a couple new columns to the SQL query.

  1. I used the ROW_NUMBER function to get the rownum of each returned row
  2. I added a dummy column with a constant value for each row

Works fine in the Query Designer: Query Designer

Works fine in the Preview: Preview

Shows the new columns when published to SSRS, but still shows blank cells for the other columns: Published report

Dean Thomsen
  • 139
  • 2
  • 7
  • 3
    Did you check your data source? Usually when a Previewed report has different data than a deployed report, it's due to having a data source that points to a different environment (i.e. Report checked in TEST server from SSRS but deployed to report server with PROD server connection). – Hannover Fist Feb 25 '20 at 23:13
  • cloud or on premise? What version of MySQL you’re using? – gmwill934 Feb 26 '20 at 08:30
  • Thank you for the suggestion. Yes I have checked the data source and have tried using different data sources for different dev vs test vs prod databases. Same result for all. And I would expect the same result since it's simple query that pulls 5 rows from a table that has 100s of thousands of rows in every database. – Dean Thomsen Feb 27 '20 at 20:25
  • Both SSRS and MySql are on premise.MySQL 8+ – Dean Thomsen Mar 11 '20 at 12:38

1 Answers1

0

If your problem is/was the same as mine then I think you will find that if you export your report to excel from the report-portal that all of the data that you expect to see is in fact present.

After some digging into the report properties and trying page width settings etc. I found that I had different values for the CanGrow property of the cells in the row and once I set them all to be the same -- True in my case -- then the report rendered as expected.

I am thinking that the reason that Excel showed the data is because it effectively ignores the CanGrow setting so having a mixture of them didn't matter.

I hope that this helps -- you or others who encounter it later -- as it was a really bizarre thing to encounter...

Christopher King
  • 1,691
  • 23
  • 28