I have been customizing our priority dispatch report and have run into an issue where the results are not showing expected work. I believe this problem began when I added in the syscube table. I have removed the region code reference from my where statement and have the syscube listed as a left outer join but I have not yet been able to get expected work to show up again. Has anyone else had a similar issue in the past, or know of another avenue that I can try to get this working?
What type of join did you use to link the SysCube table?
If you have a field in the query that has no value ânullâ it will eliminate any records from your results unless you add exception criteria.
Edit: Left Join fixes joined table from being filtered out is what I meant.
The relation between the syscube and joboper table in the rdd looks like this:
The join within the report looks like this:
The where statement simply points it to the syscube I need the data from.
I did not know that! What kind of handler do I need to put in place and where do I do it?
Left Join would take care of it
The Join for that table is a left outer join. Is there something else I need to do to handle these?
If you remove the SysCube logic from the report, does it work properly? It would seem that more was changed besides this.
Jason Woods
http://LinkedIn.com/in/jasoncwoods
1 Like
I figured it out! My link between the syscube and joboper table was built wrong. Joboper is t5 instead of t1. Another part of this issue was that I was linking operseq, an int, to dimension2, an varchar. Once I added the operation as an integer then the results flooded in. Thank you all for the help! Thumbs up all around!
2 Likes
Correction it is not actually working. It was producing some expected items but those were flukes because the Syscube data had doubled and not deleted correctly. Now I am back to square one. I did a quick test to be sure that expected work does show up when the syscube is removed and it does.
1 Like
Bummer. I would check to ensure that the report itself is not being filtered (like the Tablix or the DataSet?).
You can run the query in the report in SSMS by getting the report GUID and replacing the datasetâs parameter text with the GUID. This will tell you if the records are correct. Then you will know where to look.
1 Like
I dont believe I can do that because we are hosted on Gov Cloud
Ah. Then I would check to ensure there are no additional filters in the report first.
But you could could make another report that just contains a data set of the one table, displayed on a tablix.
edit
whoops⌠misread the post you replied to. Thought is was using SSMS just to veiw the data created by the RDD. Didnât realize you were trying to test the actual query expression
@ckrusen and @Jason_Woods,
I believe I have found the issue within my query expression:
The CubeID needs to only look at âprevopdateâ. But, if I remove that from the where statement then the report works as needed. How do I go about forcing t11 to only look at âprevopdateâ while still giving me all results from the other tables?
Any criteria in the WHERE clause applies after all the joins are done.
I believe the ON criteria doesnât have to be table to table. It can contain a constant.
try adding the following to the end of the ON criteria of T11
AND T11.CubeID = 'PrevOpDate'
(and removing it from the WHERE clause)
Relevant discussion I had a few years ago. More focused on non-equi joins, but devolved into a performance discussion about FROM VS WHERE. Check out some of the replies
@ckrusen,
That did the trick. I have a similar issue with our job traveler report where Im trying to add in mtlpart revision from the partrevision table. I want to pull the max approved revision which I have done using max(revnum) in the rawmaterialcomponents section of the report. Then using an âAND PartRevision.[Approved] = âTrueââ statement I figured I would get the max approved part revision. Instead I get the max revision, and if it is not approved it gives me a blank return. Any ideas on how to handle this?