1

I am working on my first report designed in Visual Studio (2015) SQL Server Data Tools. My sub-report is only returning one record for each parent event though the sub-report's dataset is returning multiple records.

My Report: On-the-clock-report in design view

The linking value for the sub-report is the field [EmployeeWorkSessionID]

The Sub-report Breaks Sub-report in design view

The sub-report's dataset also contains the field [EmployeeWorkSessionID]

Sub-report preview #1 Sub-report for WorkSession 293

Run by itself this sub-report returns 2 records for WorkSession 293, including a Lunch still in progress.

Sub-report preview #2 Sub-report for WorkSession 288

Run by itself this sub-report returns 2 records for WorkSession 288, including a Lunch that is completed.

The (not so) complete report The full report preview

The preview of the full report shows the first record for each WorkSession, but none of the others. The EmployeeWorkSessionID's are associated with the correct Employee. The report space is large enough for three lines. The correct records are return by the datasets, they are just not displayed. The open lunch break (containing NULLs) is not the only record being skipped.

Any ideas?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ross
  • 78
  • 7

2 Answers2

0

This does not constitute and answer but is too long to fit in a comment...

Basically the design, from what I can see, looks OK. (BTW: Thanks for posting plenty of screenshots of the design and outcome, so many people don't..)

There are a few points and thing to try, more to eliminate them than solve it but give them a go and see what happens.

Make a copy first !

  • Check if any items have CanGrow = false and set them to true (True is default)
  • Remove the rectangle around the report items
  • You have several text boxes overlapping, just ot be oin the safe side, move these so they don't overlap
  • Move the sub report so it's below the Breaks: text box

A few more comments just for reference.

  • The subreport placeholder in your main report can be smaller, it will expand to fit the content
  • You don;t need the blank space at the bottom of the subreport, the report will expand to fit the contents, any additional white-space, by default, will be appended under the subreport's tablix. In other words, shrink the subreport body as small as you can, this will also help to eliminate issues with pages spanning more than you want further down the line.

See how you go and post the results back here. Once these things are eliminated we can look further.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thanks for the reply. I don't see and false `CanGrows`. The rectangle is apparently integral to the Tablix List, anything moved outside it does not appear on the report. Without it nothing can be placed in the detail area. I've eliminated any overlaps. I have not reduced sizes yet; I want to wait until I start seeing multiple records before I experiment with that. The problem remains unchanged. What next, do you think? – Ross Jan 25 '19 at 21:31
  • Thank you Alan. Your suggestions were helpful, especially the ones regarding spacing. – Ross Jan 26 '19 at 00:35
0

This issue is caused because SSDT Report Designer caches the data collected the first time you preview the report and does not rerun the dataset queries on subsequent previews!

Obviously some actions must cause it to renew the datasets because my report did change over the course on the days; but it did not change often enough to make it obvious what was happening. The solution below looked like it was working because moving the sub-report caused an update. But after that run the report data remained static while the database changed.

Here is a tool to reset (delete) the cached data.

And the stackoverflow article that saved (what's left of) my sanity.

My report was working the whole time; Visual Studio was being lazy.

If anyone knows of a way to force live data access for all previews I would love to know about it too.

Ross
  • 78
  • 7