General Discussion Topics

[Ralph]

I've made a copy of vdvSalesHist so that I can edit it.  I would like to add the warehouse's shipTo state to the report.  The view already contains timWarehouse and tciAddress, and tciAddress.StateID is included as a field in the report natively, but it represents the customer's shipTo state, not the warehouse's shipTo state.  I need to maintain the customer's shipTo state, but ADD the warehouse's shipTo state.  So in other words, tciAddress.StateID would be being used twice for each record on the report.  I don't see how that could work.  Is that possible?  Is there a trick to accomplishing something like this?

Thanks.

Ralph

v7.30.5 & SQL 2008R2

[JohnHanrahan]

Ralph,

Just do an alias.

Example:  SELECT tciAddress.StateID AS 'ShipToStateID' FROM tciAddress

John

[Ralph]

Thanks, John.  But how would MAS/SQL know that I want the warehouse's shipTo state instead of the customer's shipTo state in the alias?  As it stands now, tciAddress.StateID is included natively and it provides the customer's shipTo state.

[jnoll · Accepted solution]

Try this...

SELECT dbo.timItem.CompanyID, dbo.tsoSalesAnalysis.COSAmt, dbo.tarCustClass.CustClassID, dbo.tarCustClass.CustClassName, dbo.tarCustomer.CustID, dbo.tarCustomer.CustName, dbo.tarCustomer.UserFld1 AS CustUserFld1, dbo.tarCustomer.UserFld2 AS CustUserFld2, 
dbo.tarCustomer.UserFld3 AS CustUserFld3, dbo.tarCustomer.UserFld4 AS CustUserFld4, dbo.timItemClass.ItemClassID, dbo.timItemClass.ItemClassName, dbo.timItem.ItemID, dbo.timItem.ItemType, dbo.vListValidationString.LocalText AS ItemTypeAsText, 
dbo.timItem.UserFld1 AS ItemUserFld1, dbo.timItem.UserFld2 AS ItemUserFld2, dbo.timItem.UserFld3 AS ItemUserFld3, dbo.timItem.UserFld4 AS ItemUserFld4, dbo.timItem.UserFld5 AS ItemUserFld5, dbo.timItem.UserFld6 AS ItemUserFld6, dbo.tarNationalAcct.NationalAcctID, 
dbo.tarNationalAcct.Description AS NationalAcctDesc, dbo.tsoSalesAnalysis.SalesAmt - dbo.tsoSalesAnalysis.ReturnAmt AS NetSalesAmt, dbo.tsoSalesAnalysis.SalesQty - dbo.tsoSalesAnalysis.ReturnsQty AS NetSalesQty, dbo.tsoSalesAnalysis.PostDate, 
dbo.tsoSalesAnalysis.SalesAmt - dbo.tsoSalesAnalysis.ReturnAmt - dbo.tsoSalesAnalysis.TradeDiscAmt - dbo.tsoSalesAnalysis.COSAmt AS Profit, dbo.tsoSalesAnalysis.SalesAmt - dbo.tsoSalesAnalysis.ReturnAmt - dbo.tsoSalesAnalysis.COSAmt AS ProfitBeforeTradeDisc, 
dbo.tsoSalesAnalysis.ReturnAmt, dbo.tsoSalesAnalysis.ReturnsQty, dbo.tsoSalesAnalysis.SalesAmt, dbo.timSalesProdLine.Description AS SalesProdLineDesc, dbo.timSalesProdLine.SalesProdLineID, dbo.tsoSalesAnalysis.SalesQty, dbo.tarSalesTerritory.SalesTerritoryID, 
dbo.tciAddress.City AS ShipToCity, dbo.tciAddress.CountryID AS ShipToCountry, dbo.tarCustAddr.CustAddrID AS ShipToCustAddrID, dbo.tciAddress.PostalCode AS ShipToPostalCode, dbo.tciAddress.StateID AS ShipToState, dbo.tarSalesperson.SperID, 
dbo.tarSalesperson.SperName, dbo.tarCustomer.StdIndusCodeID, dbo.timItem.StdPrice, dbo.timItem.TargetMargin, dbo.tsoSalesAnalysis.TradeDiscAmt, dbo.tsoSalesAnalysis.TranDate, dbo.timWarehouse.WhseID, dbo.timWarehouse.Description AS WhseDesc, dbo.timWarehouse.WhseKey, 
dbo.tarSalesperson.SperKey, dbo.tarCustAddr.AddrKey, dbo.tarSalesTerritory.SalesTerritoryKey, dbo.timItemClass.ItemClassKey, dbo.timSalesProdLine.SalesProdLineKey, dbo.timItem.ItemKey, dbo.tarNationalAcct.NationalAcctKey, dbo.tarCustomer.CustKey, 
dbo.tarCustClass.CustClassKey, dbo.tarNationalAcctLevel.NationalAcctLevelKey, dbo.tarCustClass.UserFld1 AS CustClassUserFld1, dbo.tarCustClass.UserFld2 AS CustClassUserFld2, dbo.tarCustClass.UserFld3 AS CustClassUserFld3, dbo.tarCustClass.UserFld4 AS CustClassUserFld4, 
dbo.tarSalesperson.UserFld1 AS SalespersonUserFld1, dbo.tarSalesperson.UserFld2 AS SalespersonUserFld2, dbo.tciAddress.AddrName,
tWHS.StateID AS WHSShipToState 
  FROM dbo.tsoSalesAnalysis 
	LEFT OUTER JOIN dbo.timWarehouse ON dbo.tsoSalesAnalysis.WhseKey = dbo.timWarehouse.WhseKey 
	LEFT OUTER JOIN dbo.tarSalesperson ON dbo.tsoSalesAnalysis.SperKey = dbo.tarSalesperson.SperKey 
	RIGHT OUTER JOIN dbo.tarCustAddr ON dbo.tsoSalesAnalysis.ShipToCustAddrKey = dbo.tarCustAddr.AddrKey 
	LEFT OUTER JOIN dbo.tciAddress ON dbo.tciAddress.AddrKey = COALESCE(dbo.tsoSalesAnalysis.ShipToAddrKey,dbo.tarCustAddr.Addrkey)
	LEFT OUTER JOIN dbo.tciAddress tWHS ON tWHS.AddrKey = dbo.timWarehouse.ShipAddrKey
	LEFT OUTER JOIN dbo.tarSalesTerritory ON dbo.tsoSalesAnalysis.SalesTerritoryKey = dbo.tarSalesTerritory.SalesTerritoryKey 
	LEFT OUTER JOIN dbo.timSalesProdLine ON dbo.tsoSalesAnalysis.SalesProdLineKey = dbo.timSalesProdLine.SalesProdLineKey 
	LEFT OUTER JOIN dbo.timItemClass 
	RIGHT OUTER JOIN dbo.timItem 
	INNER JOIN dbo.vListValidationString ON dbo.timItem.ItemType = dbo.vListValidationString.DBValue 
						ON dbo.timItemClass.ItemClassKey = dbo.timItem.ItemClassKey 
						ON dbo.tsoSalesAnalysis.ItemKey = dbo.timItem.ItemKey 
	LEFT OUTER JOIN dbo.tarNationalAcctLevel 
	LEFT OUTER JOIN dbo.tarNationalAcct ON dbo.tarNationalAcctLevel.NationalAcctKey = dbo.tarNationalAcct.NationalAcctKey 
	RIGHT OUTER JOIN dbo.tarCustomer ON dbo.tarNationalAcctLevel.NationalAcctLevelKey = dbo.tarCustomer.NationalAcctLevelKey 
			ON dbo.tsoSalesAnalysis.CustKey = dbo.tarCustomer.CustKey 
	LEFT OUTER JOIN dbo.tarCustClass ON dbo.tsoSalesAnalysis.CustClassKey = dbo.tarCustClass.CustClassKey 
 WHERE (dbo.vListValidationString.TableName = 'timItem') 
AND (dbo.vListValidationString.ColumnName = 'ItemType')

[Ralph]

So basically you just added "tWHS.StateID AS WHSShipToState" to the end of the SELECT part of the query.  And then added "LEFT OUTER JOIN dbo.tciAddress tWHS ON tWHS.AddrKey = dbo.timWarehouse.ShipAddrKey" to the FROM part of the query.

That works, Joe! Thanks a lot!

[jnoll]

Yes that was it. Sorry, I was headed out the door but wanted to share it with you. So it contains both of th StateID's.

[Ralph]

One thing I don't get though, Joe, is how the system came up with the column heading that I'm seeing in the new BIA report.  The heading is "W H State Ship To state."   I don't see that text anywhere in the script that I used to create the view.

[Ralph]

Warehouse state is appearing on the report just fine, but I assume it is also the case that there are some situations in which it is simply not possible to include certain fields in certain views.  For example, I'm trying to add the actual sales UOM, SO#, customer PO#, invoice#, and invoice date.  As far as I can tell, the database is just not structured in a way that would allow those fields to be added to this view.

[jnoll]

This is a BIA not a BIE. BIE is at the transaction level. BIA is more of an aggregate or summary. The BIA is de-normalized data like a lightweight data warehouse.

The column headings come from the MAS 500 metadata dictionary. This is something you need to add into database. I think you can use the Context Menu tool which is now on the menu to modify this data. (I might have the tool named incorrectly. I think it is in System Manager.

[Ralph]

Oh yeah, the Maintain BI Views and Context Menus task works fine for changing the heading. I was just curious as to how the system automatically came up with the column name that it did.  The "metadata dictionary" explanation works for me.  Thanks, Joe!

It's an interesting point that you make about BIA reports being intended to be summary reports, and BIE reports being intended to provide more transaction detail.  I'd never really seen that stated before, but it does seem to be the case now that you mention it.  I was going with vdvSalesHist as the basis for my report because it had the most fields that I needed already included on it.  Now I am thinking it would be better to just build the needed view from scratch.  Although at this point I'm skeptical as to if it will be possible to include all the fields the user wants to see, again, because I think there are some combinations of fields that are just not possible to include on a single report due to the way the database is structured.  And if that's the case, I'm not saying it's a fault in the database design, it's just the way it is.  I think the situation would probably be the same with just about any ERP RDBMS.