Hi all,
I have a rather weird situation happening in SSRS which I have never encountered before. I was hoping somebody can help me figure out what might be happening here. I will provide as much detail as I can about the situation, and can answer any follow up questions provided.
Overview of the problem:
I have an SSRS report in Microsoft Dynamics AX, using a report data provider class. The RDP class uses a simple query which selects from ProdTable, where I can also provide parameters to select from such as ProdId. In this report, I do a lot of data gathering, and populate the fields of my in memory (side note, I have attempted to try a temp table as well to see if it resolves this issue, it doesn't, so I reverted back to in memory) table, which is ultimately used in the data set of my report. I can provide more detail on the logic of my report if necessary, but I'm able to successfully gather my data, populate my in memory table fields, create a dataset in Visual Studio which looks at my RDP class / in memory table, and successfully display all my information like so:
Now for the issue. I've noticed that after a few hours have passed, (and every single time over night), that some data of this report will STOP showing up. When I say stop showing up, I mean that without me modifying anything, my report will now look like this:
As you can see, the Item Id and Wms location ID (barcode) fields are now displaying blank data. In this particular example that I managed to screenshot, it is these two fields that are missing data, but that does not mean that it is always those two fields. Sometimes those two fields stay intact and it is for example the name field that goes missing, or any other field.
Before I continue providing more information about the issue, I wanted to point out the following:
1. In this phase where the information is "missing", I have debugged the RDP class and can confirm that the values ARE being inserted into my in memory table, they just do not show up on the display.
2. What confirms step one even more, is that if you look at the first screenshot I've provided, I have set visibility conditions on the two bar code fields to be hidden if their respective field has no information. So for example, item CLH_56_WHB does not have a WMS Location Id, therefore it will not have a barcode. So the fact that the item barcodes are all showing up and not being hidden means that it's also not reading a null value.
3. If I rebuild /redeploy / restart SSRS / refresh the dataset/ restart AoT / recompile & re-deploy report on the AOT, the issue does not go away.
4. If I delete the report design entirely and remake the design, the issue will be resolved. However, after a few hours we will encounter the same problem again.
5. When I say I'm remaking the design, I mean absolutely from scratch where I right click on the auto design and create a precision design from it, then re-create the design and make it look pretty again. If I for example create a duplicate of my design as a backup, then delete/recreate my precision design and copy+paste my tablix/fields from my backup, it will not work. I need to use my new tablix, format it, etc. Only then it will work.
6. The auto design from the dataset will always successfully show show all the data. Obviously this is not the design we want to use, but this again verifies that the actual data is there.
Now, one more thing I've noticed. Prior to leaving for the weekend on Friday evening, I've left my visual studio with the project open on the screen (report working good). When I returned this Monday morning, I've tried to re-deploy my report, and was presented with the following:
This seems very huge information wise in the fact that there seems to be something else that's maybe somehow modifying the report.
My initial thinking is some kind of process in the background, but I've honestly never encountered something like this in the many SSRS reports I have done.
I'm not dismissing the fact that it's entirely not possible that another user has modified this report, but it is very unlikely. Add to the fact that after this 'break' happens, I cannot see any differences at all in the report. The structure is the same, the aforementioned visibility logic is the same, everything is the same as far as I can tell, just that these fields do not show up.
Does anybody have any suggestions at all to how I can debug/fix this? Has anyone ever encountered a situation like this or similar before? Any help would be greatly appreciated. I will answer any questions with as much detail as possible.
Thank you.
