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.
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*
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.