Crosstab Microsoft Dynamics GP price tables

Rows to columns for price breaks

No doubt your sales team want to go on the road with a human friendly version of your prices for the customers to read. It is possible to do this with a SQL table function to extract the prices from GP with price breaks. The following example assumes you know how many price breaks you have in your price lists and will result in output something like the following screen shot. These results may be squirted into excel with more columns as described by your business requirements.
Results of join with IV00101 showing description pulled in

Two key SQL server functions that many people I find are not familiar with but are vital for this kind of data manipulation are; “ROW_NUMBER()” and “PARTITION BY” , one way to learn is to dive in with an example.

GP Price Table

Natively the prices are held in the table IV00108 of your company database.

ITEMNMBR CURNCYID PRCLEVEL UOFM TOQTY FROMQTY UOMPRICE
WIRE100 Z-US$ RETAIL Foot 100 0.01 0.35
WIRE100 Z-US$ RETAIL Foot 999999999999.99 100.01 0.29
WIRE100 Z-US$ RETAIL Spool 999999999999.99 0.01 190
WIRE100 Z-US$ RETAIL Yard 999999999999.99 0.01 0.65
WIRE100 Z-US$ EXTPRCLVL Foot 999999999999.99 0.01 0
WIRE100 Z-US$ EXTPRCLVL Yard 999999999999.99 0.01 0
WIRE100 Z-US$ EXTPRCLVL Spool 999999999999.99 0.01 0

There is a row per “price point”. Each row contains, the item sku, Currency of the price list, price list name, unit of measure, quantity break range and price.

This is unreadable to humans once you get, say 15,000 products, five currencies and ten or so price levels. From experience, one company this solution is used with has 1,623,586 rows in the price table IV00108.

Table Partitioning

Firstly the rows are grouped together by the common factor each output row should be sharing. Each row in this example should have the same Item, Currency, Price Level and unit of measure. A row number is added for each successive row within this grouping;

SELECT 
ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, TOQTY, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID, UOFM
ORDER BY toqty ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) WHERE itemnmbr='WIRE100'

The above TSQL partitions the returned rows from IV00108 by ITEMNMBR,PRCLEVEL, CURNCYID, UOFM, for each row in the group a row number is generated by ROW_NUMBER() see the following output example. For this example, there are two quanity break columns for the prices of the “foot” unit of measure.
These are breaks of; 0.01+ and 100+, resulting in row numbers one and two for this unit of measure.

ITEMNMBR CURNCYID PRCLEVEL UOMPRICE FROMQTY UOFM TOQTY RowNumber
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Foot

999999999999.99 1
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Spool

999999999999.99 1
WIRE100 Z-US$ EXTPRCLVL 0.00000 0.01000

Yard

999999999999.99 1
WIRE100 Z-US$ RETAIL 0.35000 0.01000

Foot

100 1
WIRE100 Z-US$ RETAIL 0.29000 100.01000

Foot

999999999999.99 2
WIRE100 Z-US$ RETAIL 190.00000 0.01000

Spool

999999999999.99 1
WIRE100 Z-US$ RETAIL 0.65000 0.01000

Yard

999999999999.99 1

 

Now that we have the RowNumber, this can act as the anchor field to crosstab the data with. It makes sense to wrap this query in a common table expression (CTE) to clean it up. The output from the below should be identical to that above.

WITH PriceTable 
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr='WIRE100'
)
SELECT * FROM PriceTable

Crosstabbing the Common Table Expression

Now building on the select statement from the CTE, it is crosstabbed by using CASE statements as shown below. All that has changed between these two scripts is the select out of the CTE. The select is also add “+” to the price from column results as well as some NULL handling to make the presentation cleaner for Excel should it end up there. This is optional, it might be more appropriate for other uses to keep the results as numeric values and do that kind of processing in the reporting tool.

WITH PriceTable
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr='WIRE100'
)

select itemnmbr,
isnull(max(case when PriceTable.[RowNumber] = 1 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then
uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then
uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then
uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then
uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

 
 
The above TSQL generates the following table, where the rows have been transformed into columns by TSQL, just as required.
itemnmbr Break1   Price1 Break2 Price2 Break3 Price3 Break4 Price4 Break5 Price5 Break6 Price6
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.35000 100+ 0.29000   NULL   NULL   NULL   NULL
WIRE100 0+ 190.00000   NULL   NULL   NULL   NULL   NULL
WIRE100 0+ 0.65000   NULL   NULL   NULL   NULL   NULL

Table valued function


Great there we have it, price table partitioned and crosstabbed. Lets not stop there as this is much more useful as a table valued function. This is achieved by wrapping the above SQL as shown below. Here we have decided that the calling script should provide the currency, pricelist, item pattern and unit of measure to export. Your application may differ and not require the expensive type conversions.

CREATE function [dbo].[Extract_PricesCrosstabTable] (
@CURNCYID varchar(15),
@PRCLEVEL varchar(11),
@ITEMPATTERN nvarchar(31) = '%',
@UOFM varchar(9) = '%'
)
RETURNS @retTable TABLE
(
[ITEMNMBR] [varchar](31) primary key NOT NULL,
[BREAK1] [varchar](255) NOT NULL,
[PRICE1] [numeric](19, 5) NULL,
[BREAK2] [varchar](255) NOT NULL,
[PRICE2] [numeric](19, 5) NULL,
[BREAK3] [varchar](255) NOT NULL,
[PRICE3] [numeric](19, 5) NULL,
[BREAK4] [varchar](255) NOT NULL,
[PRICE4] [numeric](19, 5) NULL,
[BREAK5] [varchar](255) NOT NULL,
[PRICE5] [numeric](19, 5) NULL,
[BREAK6] [varchar](255) NOT NULL,
[PRICE6] [numeric](19, 5) NULL
)
AS
BEGIN

WITH PriceTable
(ITEMNMBR, CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY,UOFM, TOQTY,[RowNumber]) AS
(SELECT ITEMNMBR,CURNCYID, PRCLEVEL, UOMPRICE, FROMQTY, UOFM, toqty, ROW_NUMBER()
OVER(PARTITION BY
ITEMNMBR,PRCLEVEL, CURNCYID,UOFM
ORDER BY TOQTY ASC) AS 'RowNumber'
FROM iv00108 (NOLOCK) where itemnmbr like @ITEMPATTERN and PRCLEVEL= @PRCLEVEL
AND CURNCYID=@CURNCYID AND UOFM LIKE @UOFM
)
INSERT @retTable
select itemnmbr,
isnull(max(case when PriceTable.[RowNumber] = 1 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break1
, max(case when PriceTable.[RowNumber] = 1 then
uomprice end) as Price1
, isnull(max(case when PriceTable.[RowNumber] = 2 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break2
, max(case when PriceTable.[RowNumber] = 2 then
uomprice end) as Price2
,isnull( max(case when PriceTable.[RowNumber] = 3 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break3
, max(case when PriceTable.[RowNumber] = 3 then
uomprice end) as Price3
,isnull( max(case when PriceTable.[RowNumber] = 4 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break4
, max(case when PriceTable.[RowNumber] = 4 then
uomprice end) as Price4
, isnull(max(case when PriceTable.[RowNumber] = 5 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break5
, max(case when PriceTable.[RowNumber] = 5 then
uomprice end) as Price5
, isnull(max(case when PriceTable.[RowNumber] = 6 then
LTRIM( STR(FROMQTY,6,0)) + '+' end),'') as Break6
, max(case when PriceTable.[RowNumber] = 6 then
uomprice end) as Price6
from PriceTable
group by itemnmbr, curncyid, prclevel, UOFM;

RETURN

END;
 

Putting it to work

Now it is a table valued function, this allows a crosstabbed price table to be used as if it were a table. For example to add in the item description from the item master table IV00101, the following is used;

 
SELECT 
IV00101.ITEMDESC,
PricesCrossTab.*
From Extract_PricesCrosstabTable('Z-US$','RETAIL','WIRE%','Foot') PricesCrossTab
JOIN IV00101
ON PricesCrossTab.ITEMNMBR= IV00101.ITEMNMBR

Results of join with IV00101 showing description pulled in

The unit of measure has been used as a parameter here for selection, however by changing the schema of the table valued function returned table type to include unit of measure as part of the primary key, all units of measure can be returned. This is the foundations of some scripts that can be amended to produce the results that you require for your particular circumstances.

Add comment

Loading