I am trying to add the ResaleID field to a modified packslip rrd. I believe that this is in the customershipto temp table embedded in the RDD and I have checked and their is no exclusion. I have amended the embedded query definition in SSRS and added the field and included in the report but it always come back blank.
In case it wasn’t actually in the CustomerShipTo table I linked to the ship to table linking on company, shiptocustnum and shiptonum and modified the embedded query in SSRS to link to this table. Again the modified pack slip runs but the resaleid field is blank.
I have added other tables to this modified pack slip without issue - is there an issue with the ResaleID field being Null.
Has anyone added the resaleid field to their packing slip and if so can they advise how they did it?
I see what you mean. The built-in RDD does not exclude ResaleID. And the original PackSlip SSRS does not include ResaleID in the query expression.
Are you sure you got the name of the field added to the expression correctly?
The SSRS references two datasets (ShipHeadShipDtl, and Company), which don’t exactly line up with the tables/views of the RDD - and the tales/views of the RDD don’t extactly match the db tables.
The from has been updated to include the left joins below
LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T4
ON T2.Company = T4.Company AND T2.PartNum = T4.PartNum LEFT OUTER JOIN ICommCode_" + Parameters!TableGuid.Value + " T5
ON T4.Company = T5.Company AND T4.CommodityCode = T5.CommodityCode LEFT OUTER JOIN ShipTo_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.ShipToCustNum=T6.CustNum AND T1.ShipToNum=T6.ShipToNum
What I am trying to do is include the commodity code from the part table and the associated commodity code description (t4 and t5) which works without issue. When I try and include ResaleID (the tax id for the customer) it comes up blank For T6 I have tried the embedded CustomerShipTo as well as it seems to have the ResaleID field but same issue, everything works and teh reports generates but that field is blank.
From further investigation the ResaleID does appear in the sales order acknowledgement report coming from the orderrel table - I’ll try and link to that.
What is the CustomerShipTo “table” referenced in the pack slip RDD - it doesn’t appear in the data dictionary - is it a temporary table created purely when this RDD runs?
Yes. If you look at the original query in the ssrs report, you’ll see reference to “table” ShipHeadShipDtl. I assume thats a temporary view made from the RDD.
Since ResaleID is in the original RDD, try seeing if you can reference it from the T1 table in the the report. The field names in these views is usually just the field name from its tables. If the name is common among tables, it gets the table name prefixed.
For example, if an RDD had only one table with the field OrderNum, you’d just use OrderNum. If there were two or more (like OrderHed and ShipDtl), you’d use OrderHedOrderNum (no dot between).
One thing you coukd do as a last resort… copy the Report Style and SSRS. Point the new style to the new SSRS, then Sync Data. This will restructure the datasets in the SSRS, into individual tables from the RDD
I almost always advise against Sync Data because it can really mess up the SSRS. Thats why you use a copy.
It looks like I have got this working. I changed the relationship between shiphead and ship to in the RDD from definition only to output and the field is now passed through.
When i defined the relationship between shipdtl and part (to get the commodity code for the part) and then part and Icommcode I left the relation type as definition only, however the fields from those tables display without issue.
For my own future benefit any reason why this would make a difference (matching records exist in both tables)
Not sure why they use the terms “definition” and “output”, but they effectively mean “inner join” and “outer join” (respectively).
Using “definition” when it should be “output” usually ends up “clipping” the data. Such that records are excluded because not all related fields match between the tables.