@CWright The result from the subquery is no longer PartTran. It would have a relative reference of something like SubQuery2.PartTran_TranNum or SubQuery2.Calculated_MaxTranNum depending on if it is in the join or the calculation.
You need a total of 3 “subqueries”. I hate that the Top Level is called a subquery (typically SubQuery1). My reference to sub query 1 and 2 should have been as 2 and 3.
SubQuery1 - Top Level. The display columns of this query are what are visible to the report or dashboard. They’re what show in the Analyze tab.
SubQuery2 - InnerSubQuery. In my prior post I referred to this as SubQ1. Joins between PartTran_1 (it will have to have a different name as PartTran will first be used in Subquery3) and SubQuery3.
SubQuery3 - InnerSubQuery. In my prior post I referred to this as SubQ2. Contains just the PartTran, shoud have a table criteria of
TranDate <= @ReportinDate.
Make Subquery 3 first, and set it as the Top Level (this is just to test it, and you’ll need to set SubQuery1 to InnerSubQuery). Then when you run it it should return the Last Transaction <= the provided date. Once this work as expected, set the Query type back to InnerSubQuery.
You should get something like:
Make SubQuery2 by first adding the PartTran table (it will give it a new name), then SubQuery3. Set the query type of Subquery2 to TopLevel (again just for testing). Make the table relationships (Joins) as:
Have the display fields be:
(You don’t really need the MaxTran field)
Now test this IT WILL TAKE A LONG TIME TO RUN (just like the real SSR). Your output should be one record per part, with the cost used at on the last transaction <= your desired date.
Now make SubQuery2’s type be InnerSubQuery, and set SubQuery1 to TopLevel.
Build your Top level query, ignoring SubQuery 2 and SubQuery3 for the moment. Once it calculates the proper QOH for your desired date, you can add in SubQuery2, relating the company and PartNum fields.
Some final notes:
- This is just an example of how to use subqueries to get that cost on a desired date.
- SubQuery3 will need fine tuning to filter on the TranType, as not all PartTran records use the cost fields as you think. If use LAST costing there are PartTrans records whose cost fields are for the difference between the old and new cost