SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Blank rows in Excel export


Blank rows in Excel export

Author
Message
Andy Reilly
Andy Reilly
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3239 Visits: 418
Hi I have a report that calls a sub report multiple times - All works fine until I export to Excel and then I have two datasets that when included will add a blank line to the excel (but fine on screen) - There are multiple datasets that cause no issue. The datasets are very simple just a date and a float value.

So the main report is just a table that contains a subreport inside one of the cells and is then repeated for each value in the parameter. The sub-report is another table that displays the data series date and value pairs - V simple Sad

Any ideas please this is driving me mad Sad

Cheers
Andy
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75784 Visits: 6361
Andy Reilly - Wednesday, March 7, 2018 9:55 AM
Hi I have a report that calls a sub report multiple times - All works fine until I export to Excel and then I have two datasets that when included will add a blank line to the excel (but fine on screen) - There are multiple datasets that cause no issue. The datasets are very simple just a date and a float value.

So the main report is just a table that contains a subreport inside one of the cells and is then repeated for each value in the parameter. The sub-report is another table that displays the data series date and value pairs - V simple Sad

Any ideas please this is driving me mad Sad

Cheers
Andy

I'm not sure I can visualize what you're saying. If you can provide an image that would perhaps make this a lot clearer. However, that said, we'd probably also need an image of the design view on the report as well. Either way, we need a lot more detail.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Andy Reilly
Andy Reilly
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3239 Visits: 418
I've just figured the problem out - if I include blank cells in my table in the main report

------------------------------------------------
| | SUB - REPORT | |
------------------------------------------------
I get blank rows - If I take out the blank cells So the table only has one cell and put the blank cells in the sub-report - Happy days - Still no idea why, but it's fixed

Cheers
Andy
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75784 Visits: 6361
Andy Reilly - Thursday, March 8, 2018 8:57 AM
I've just figured the problem out - if I include blank cells in my table in the main report

------------------------------------------------
| | SUB - REPORT | |
------------------------------------------------
I get blank rows - If I take out the blank cells So the table only has one cell and put the blank cells in the sub-report - Happy days - Still no idea why, but it's fixed

Cheers
Andy

WYSIWYG. What you see is what you get. SSRS has been doing that for a rather long time. If you include a blank cell and don't suppress blank rows, that's what you'll get. Look into suppressing blank cells or rows.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Andy Reilly
Andy Reilly
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3239 Visits: 418
I'm getting blank rows not blank columns
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75784 Visits: 6361
Andy Reilly - Thursday, March 8, 2018 9:02 AM
I'm getting blank rows not blank columns

Then suppress blank rows..


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Andy Reilly
Andy Reilly
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3239 Visits: 418
There are no blanks rows on the report - They only appear when I export to excel Smile
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75784 Visits: 6361
Andy Reilly - Thursday, March 8, 2018 9:48 AM
There are no blanks rows on the report - They only appear when I export to excel Smile

Then be sure you have no NULL records in your source data, but beyond that, also be sure that you suppress a blank sub-report. and blank rows within said sub-report.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
sgmunson
sgmunson
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75784 Visits: 6361
And that the size of your rows in the sub-report matches the size of rows in the main report.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Andy Reilly
Andy Reilly
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3239 Visits: 418
Hi Steve,

Why would that make a difference only when exporting to excel ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search