I am trying to customize the material tag to do a few things. First, if it is for a job pull some information from the project phase data. Second, if it is for other (non-job related) I want to see the person who put in the requisition or the buyer for the PO. I have added the POHeader, PORel, ProjPhase, and ReqHead tables and then tried to create relationships for them. I then created a copy of the Material Tags to be my custom SSRS report and initially did the sync dataset from the Report Style. I have attached a snapshot of my RDD below.
I can get the data from the project phase sometimes. I have associated the release job information and sometimes I see my desired data and sometimes I don’t. No matter how I try to link it up I never see any of the requisition data. I have also tried setting up the queries on the tables in the rdl file but nothing seems to work.
Does anyone know the magic of how to make the relationships work the way I need them to?
Sometimes you can’t get the exact relations ships you want in the RDD, and have to let it create more data than you need. Then in the RDL, edit the dataset queries to further refine it.
I have tried that and I don’t get the data. I have created the sql in the rdl for example:
=“Select T1.[PONum], T1.[BuyerID] from POHeader_” + Parameters!TableGuid.Value+ " T1 LEFT OUTER JOIN MtlQueue_" + Parameters!TableGuid.Value+ " T2 ON T1.[PONum] = T2.[PONum]"
But I don’t get anything for the buyer. Perhaps I am not connecting the proper material tag table. I have also tried MtlTags instead of MtlQueue but neither give ne the buyer ID. I am at a loss as to what to do.
Here are 2 snapshots of part of this. This is the Tag PO to the PO Release and PO Release to the phase table. I may be falsely thinking that if I connect the tag data to the release then the release to the project phase it will properly connect. What is weird is that sometimes it works and sometimes it doesn’t. I have not been able to figure out what the difference is on the 2 POs I am working with.
I would run your custom report, ignoring the rendered output from SSRS. (set the archive period to 1 day).
Then use SSMS (SQL Server Mngmnt Studio) to look at the temp tables the RDD created. Particularly the ones you added.
Do they have the expected data in them? If so the RDD is good and the issue is in one of the RDL’s query expressions.
If they don’t have the expected data, Look at the temp tables created for the built-in sources (PORel, MtlQue, & MtlTags) and see if the fields you’re using to relat to are containg what you expect.
