Answered by:
Erratic behavior - One instance of a group sometimes does not show up while executing the same selection

Question
-
I have a report with multiple parameters which runs on a stored procedure.
Within the tablix I use 4 filters. And on the dataset I also use a filter.
When selecting data and while maintaining the same selection - in other words I do not change the parameter entries - a field which has to be shown, 'sometimes' disappears, without any logical reason. The only logical reason would be if we would change the parameter entries (when we select other data) but we do not change the entries.
So for instance the data returned looks like this
English
English period 1
English period 2
Dutch
Dutch period 1
Dutch period 2
etc.
English and Dutch are in the same groups, so it is not the case the group runs defect as a whole, nope just 1 value is incidentally missing. In this case in some runs "English" disappears.
I'm using SSRS 7+ years (version 2005 to 2012) but never noticed something like this because there is no logic behind it of disappearing as the parameter values are the same between the different report runs
Interesting info:
I displayed the same dataset within an additional tablix and the instance english is properly shown even when it is not visible within the other tablix. as far as I know the dataset is only queried once which leads to my conclusion that the query result is ok (I also used profiler and the query result contains "English")
- Edited by giovi2012 Tuesday, June 6, 2017 6:46 PM
Tuesday, June 6, 2017 12:21 PM
Answers
-
I found the issue. It is NOT a Microsoft software problem.
The cause was the following:
- A query within the stored procedure did not have an order by resulting in different orders when selecting
- The grouping in the report relied on the order, if certain conditions were not met the group would be invisible but because of the recordset being unsorted and sometimes being in the wrong order caused this mishap
Thursday, June 15, 2017 1:01 PM
All replies
-
Hi giovi2012,
I was unable to reproduce this odd behavior in my local environment, also, I never encountered this problem before.
In your scenario, if clicking the refresh button, will the missing records display again? If publishing this report to report server, and run it on report manager, will issue persist? If possible, could you recreate this report or recreate the tablix which has this problem to check whether it works or not?
Regards,
Yuliana GuMSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Wednesday, June 7, 2017 5:59 AM -
Hi Yuliana, yes when I refresh it shows again, sometimes after 2 refreshes and sometimes at start.
Same like you respond I was already planning for a remake of the report (copy paste where possible) and deploy it to another server version (i have 2016 locally). I will do this thank you for replying and sketching some alternatives
Thursday, June 8, 2017 12:32 PM -
I deployed it to SQL Server 2016 Reporting Services which I run locally. Until now it works well on the local machine even on selections which show the erratic behavior in production or test.Thursday, June 8, 2017 3:45 PM
-
i would like to know @Mssupport what the name is of this bug and the conditions, so I can do an impact analysis on our existing reportsFriday, June 9, 2017 10:53 AM
-
I found the issue. It is NOT a Microsoft software problem.
The cause was the following:
- A query within the stored procedure did not have an order by resulting in different orders when selecting
- The grouping in the report relied on the order, if certain conditions were not met the group would be invisible but because of the recordset being unsorted and sometimes being in the wrong order caused this mishap
Thursday, June 15, 2017 1:01 PM