{"_id":"5770cfcd4607a30e001dec06","parentDoc":null,"project":"574a3bc0d1d1381900b73fab","user":"574bfb9290d8b32000864973","category":{"_id":"574d14140db0870e0075382d","__v":0,"project":"574a3bc0d1d1381900b73fab","version":"574a3bc0d1d1381900b73fae","sync":{"url":"","isSync":false},"reference":false,"createdAt":"2016-05-31T04:33:24.186Z","from_sync":false,"order":9999,"slug":"3-reporting-templates","title":"3. Reporting Templates"},"githubsync":"","version":{"_id":"574a3bc0d1d1381900b73fae","project":"574a3bc0d1d1381900b73fab","__v":7,"createdAt":"2016-05-29T00:45:52.939Z","releaseDate":"2016-05-29T00:45:52.939Z","categories":["574a3bc0d1d1381900b73faf","574be6d52aa7221700c51bed","574c0b6d281b010e00008f89","574c1afbcf99610e00ef7d2b","574d13b9a1f0be2000ae3803","574d14140db0870e0075382d","591eb3a72c1b1f0f000fa9f2"],"is_deprecated":false,"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"","version_clean":"3517.0.0","version":"3517"},"__v":22,"metadata":{"title":"","description":"","image":[]},"updates":[],"next":{"pages":[],"description":""},"createdAt":"2016-06-27T07:03:41.338Z","link_external":false,"link_url":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":1,"body":"The underlying database management system is Microsoft SQL Server.  End users are free to query the data structure and extract data using their own user-defined Transact-SQL queries.\n<br />\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Querying the Entire Optimization Solution\"\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"warning\",\n  \"title\": \"SQL SERVER 2008R2 & SQL SERVER 2005 ONLY\",\n  \"body\": \"The query syntax below uses the '**SET FMTONLY**' statement.  This has been deprecated in all SQL Server versions after 2008R2.  The query as shown below will only work on SQL Server versions 2008R2 and below.\"\n}\n[/block]\nIt is possible to [create a file that contains the complete optimization solution](doc:how-to-export), along with all the planning unit metadata that were defined in the forest description.\n\nThe resulting CSV file can be double clicked and opened using Microsoft Excel.  It can then form the basis of temporary pivot tables or any other comprehensive data analysis that may be of interest.\n\nIt is possible to query the same stored procedure that is used to export the optimization solution, with the additional functionality that users can add their own SQL logic to further refine the results returned by the stored procedure.\n\nTo do this, users can change the SQL code between the following two statements in the main block of Transact-SQL code presented below.\n\n1. `-- BEGIN USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE --` and,\n\n2. `-- END USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE --`\n\n[block:callout]\n{\n  \"type\": \"danger\",\n  \"body\": \"The Transact-SQL code out of the two statements above needs to remain static and should not be changed if the query is to return its results correctly.\",\n  \"title\": \"Only Change User Defined SQL Code\"\n}\n[/block]\nCopy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).  Then make changes to the `...SELECT * FROM ##tmp_consolidated...` statement as needed to further refine and enhance the data returned by the query.\n\n```\n      --BEGIN--\n      --TURN ON AD HOC QUERIES--\n      EXEC sp_configure 'show advanced options', 1\n      RECONFIGURE\n      EXEC sp_configure 'ad hoc distributed queries', 1\n      RECONFIGURE\n      EXEC sp_configure 'show advanced options', 0\n      RECONFIGURE\n      --END--\n\n\n      --BEGIN--\n      IF OBJECT_ID('tempdb..##tmp_consolidated') IS NOT NULL\n      BEGIN\n            IF OBJECT_ID('tempdb..IX01_tmp_consolidated') IS NOT NULL\n            BEGIN\n                  ALTER TABLE ##tmp_consolidated DROP CONSTRAINT IX01_tmp_consolidated\n            END\n            DROP TABLE ##tmp_consolidated\n      END\n      --END--\n\n\n      --BEGIN--\n      DECLARE     :::at:::SQL VARCHAR(MAX)\n      SELECT      @SQL = '' +\n            'SELECT * ' +\n            'INTO ##tmp_consolidated ' +\n            'FROM OPENROWSET(''SQLNCLI'', ''SERVER=' +\n            CONVERT(VARCHAR(100),SERVERPROPERTY('MachineName')) +\n            '\\' + \n             CONVERT(VARCHAR(100),SERVERPROPERTY ('InstanceName')) +\n            ';TRUSTED_CONNECTION=YES;'', ''SET FMTONLY OFF ' + \n            'EXEC \"' + DB_NAME() + '\".[dbo].[sp_RSConsolidated]'')'\n            \n      EXEC(@SQL)\n      --END--\n\n\n      --BEGIN--\n      --RECREATE PKEY FROM tbl_consolidated JUST BECAUSE--\n      ALTER TABLE ##tmp_consolidated ADD  CONSTRAINT [PK00_tmp_consolidated] PRIMARY KEY CLUSTERED \n      ([Period] ASC,\n      [PlunitID] ASC,\n      [OriginID] ASC,\n      [SpeciesID] ASC,\n      [Type] ASC,\n      [Status] ASC,\n      [Age] ASC,\n      [ProductID] ASC,\n      [DestinationID] ASC,\n      [Proportion] ASC) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]\n      --END--\n\n\n      -----------------------------------------------------------------------------------------\n      --BEGIN USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE--\n      -----------------------------------------------------------------------------------------\n\n      -- NOW DO WHATEVER YOU WANT WITH THE DYNAMIC TEMP TABLE ##tmp_consolidated\n      -- IT IS A MIRROR OF THE DATA RETURNED BY STORED PROC [sp_RSConsolidated]\n\n      SELECT * FROM ##tmp_consolidated\n      INNER JOIN mps_cf_volume\n                ON ##tmp_consolidated.[PlunitID] = mps_cf_volume.[PlunitID]\n\t\tAND ##tmp_consolidated.[Age] = mps_cf_volume.[Age]\n      WHERE ##tmp_consolidated.[Status] = 'Yield'\n\t \n      \n      \n      -----------------------------------------------------------------------------------------\n      --END USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE--\n      -----------------------------------------------------------------------------------------\n      \n      \n      --BEGIN--\n      IF OBJECT_ID('tempdb..##tmp_consolidated') IS NOT NULL\n      BEGIN\n            IF OBJECT_ID('tempdb..IX01_tmp_consolidated') IS NOT NULL\n            BEGIN\n                  ALTER TABLE ##tmp_consolidated DROP CONSTRAINT IX01_tmp_consolidated\n            END\n            DROP TABLE ##tmp_consolidated\n      END\n      --END--\n\n      \n      --BEGIN--\n      --TURN OFF AD HOC QUERIES FOR SAFETY--\n      EXEC sp_configure 'show advanced options', 1\n      RECONFIGURE\n      EXEC sp_configure 'ad hoc distributed queries', 0\n      RECONFIGURE\n      EXEC sp_configure 'show advanced options', 0\n      RECONFIGURE\n      --END--\n```\n<br />\n[block:api-header]\n{\n  \"title\": \"Create a Generic Allocation Model\"\n}\n[/block]\nIt is possible to list every combination of species log grade that exists in the model.  This simple list can then be used to quickly create a generic allocation model that can be used for initial model building and testing.\n\nThe SQL code below will produce data that can be copied and pasted to the worksheet 'Allocation'.  This can then be used to quickly create an initial model for testing and debugging purposes.\n\nThe 'Create a Generic Revenue Model' and 'Create a Generic Transport Model' code blocks below this current section can then be used to produce a compatible revenue model and a compatible transport model for this generic allocation data.\n\nCopy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).  \n\n```\nDECLARE @PeriodMax AS INT\nDECLARE @AgeclassMax AS INT\n\nSET @PeriodMax = (SELECT MAX([Period]) FROM tbl_period)\nSET @AgeclassMax = (SELECT MAX([Age]) FROM tbl_age)\n\nSELECT\n\t'1' AS 'Start',\n\t@PeriodMax AS 'End',\n\t'Period' AS 'Type',\n\t'All Plunits' AS 'Group',\n\t'All' AS 'Attribute',\n\t'0' AS 'Min',\n\t@AgeclassMax AS 'Max',\n\t'>' AS 'Equality',\n\t'0' AS 'Quantity',\n\t'm3' AS 'Unit',\n\t'Generic' AS 'Destination',\n\ttbl_species.[Species],\n\ttbl_product.[Product],\n\t'>' AS 'Sign',\n\t'0' AS 'Mix'\nFROM\n\ttbl_plunit\n\tINNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]\n\tINNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID]\n\tINNER JOIN tbl_yield ON tbl_croptype.[CroptypeID] = tbl_yield.[CroptypeID]\n\tINNER JOIN tbl_product ON tbl_yield.[ProductID] = tbl_product.[ProductID]\nWHERE tbl_product.[Status] = 'YIELD'\nGROUP BY\n\ttbl_species.[Species],\n\ttbl_product.[Product]\nUNION\nSELECT\n\t'1' AS 'Start',\n\t@PeriodMax AS 'End',\n\t'Period' AS 'Type',\n\t'All Plunits' AS 'Group',\n\t'All' AS 'Attribute',\n\t'0' AS 'Min',\n\t@AgeclassMax AS 'Max',\n\t'>' AS 'Equality',\n\t'0' AS 'Quantity',\n\t'm3' AS 'Unit',\n\t'Generic' AS 'Destination',\n\ttbl_species.[Species],\n\ttbl_product.[Product],\n\t'>' AS 'Sign',\n\t'0' AS 'Mix'\nFROM\n\ttbl_plunit\n\tINNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]\n\tINNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID]\n\tINNER JOIN tbl_silviculture ON tbl_regime.[RegimeID] = tbl_silviculture.[RegimeID]\n\tINNER JOIN tbl_product ON tbl_silviculture.[ProductID] = tbl_product.[ProductID]\nWHERE tbl_product.[Status] = 'YIELD'\nGROUP BY\n\ttbl_species.[Species],\n\ttbl_product.[Product]\nORDER BY\n\t[Species], [Product]\n```\n<br />\n[block:api-header]\n{\n  \"title\": \"Create a Generic Revenue Model\"\n}\n[/block]\nThe SQL code below will produce data that can be copied and pasted to the worksheet 'Price'.  This can be used to quickly create an initial revenue model for testing and debugging purposes and is compatible with the generic allocation data produced using the 'Create a Generic Allocation Model' code block above this current section.\n[block:callout]\n{\n  \"type\": \"danger\",\n  \"title\": \"If a species log grade combination does not exist\",\n  \"body\": \"If a species log grade combination does not exist then a NULL value is generated in the data.  Users should replace these NULL values with a blank cell after the data are pasted to the 'Price' worksheet in the forest description.\"\n}\n[/block]\nCopy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).  \n\n```\nDECLARE @Columns AS NVARCHAR(MAX)\nSET @Columns = STUFF((SELECT ',' + QUOTENAME(tbl_product.[Product]) \n\t\t\t\tFROM tbl_product WHERE tbl_product.[Status] = 'YIELD' GROUP BY tbl_product.[Product] ORDER BY tbl_product.[Product]\n\t\t\t\tFOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')\n\nDECLARE @SQL AS NVARCHAR(MAX)\nSET @SQL = '\n\tSELECT \n\t\t*\n\tFROM \n\t\t(\n\tSELECT\n\t\t' + CHAR(39) + 'Generic' + CHAR(39) + 'AS' + CHAR(39) + 'Destination' + CHAR(39) + ',\n\t\t' + CHAR(39) + 'All' + CHAR(39) + 'AS' + CHAR(39) + 'Origin' + CHAR(39) + ',\n\t\ttbl_species.[Species],\n\t\t' + CHAR(39) + 'None' + CHAR(39) + 'AS' + CHAR(39) + 'Index' + CHAR(39) + ',\n\t\ttbl_product.[ProductID],\n\t\ttbl_product.[Product]\n\tFROM\n\t\ttbl_plunit\n\t\tINNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]\n\t\tINNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID]\n\t\tINNER JOIN tbl_yield ON tbl_croptype.[CroptypeID] = tbl_yield.[CroptypeID]\n\t\tINNER JOIN tbl_product ON tbl_yield.[ProductID] = tbl_product.[ProductID]\n\tGROUP BY\n\t\ttbl_species.[Species],\n\t\ttbl_product.[ProductID],\n\t\ttbl_product.[Product]\n\tUNION\n\tSELECT\n\t\t' + CHAR(39) + 'Generic' + CHAR(39) + 'AS' + CHAR(39) + 'Destination' + CHAR(39) + ',\n\t\t' + CHAR(39) + 'All' + CHAR(39) + 'AS' + CHAR(39) + 'Origin' + CHAR(39) + ',\n\t\ttbl_species.[Species],\n\t\t' + CHAR(39) + 'None' + CHAR(39) + 'AS' + CHAR(39) + 'Index' + CHAR(39) + ',\n\t\ttbl_product.[ProductID],\n\t\ttbl_product.[Product]\n\tFROM\n\t\ttbl_plunit\n\t\tINNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID]\n\t\tINNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID]\n\t\tINNER JOIN tbl_silviculture ON tbl_regime.[RegimeID] = tbl_silviculture.[RegimeID]\n\t\tINNER JOIN tbl_product ON tbl_silviculture.[ProductID] = tbl_product.[ProductID]\n\tGROUP BY\n\t\ttbl_species.[Species],\n\t\ttbl_product.[ProductID],\n\t\ttbl_product.[Product]\n\t\t) AS qry_plunit \n\tPIVOT \n\t(    \n\t\tMAX(qry_plunit.[ProductID])\n\t\tFOR [Product] IN (' + @Columns + ') \n\t) AS pvt_plunit '\nEXECUTE (@SQL)\n```\n<br />\n[block:api-header]\n{\n  \"title\": \"Create a Generic Transport Model\"\n}\n[/block]\nThe SQL code below will produce data that can be copied and pasted to the worksheet 'Transport'.  This can be used to quickly create an initial transport model for testing and debugging purposes and is compatible with the generic allocation data produced using the 'Create a Generic Allocation Model' code block above this current section.\n\nCopy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar).  \n\n```\nSELECT tbl_origin.[Origin], '0' AS 'Generic' FROM tbl_origin WHERE tbl_origin.[OriginID] > 0\n```\n<br />","excerpt":"Transact-SQL that can be used with an optimized standard forest description database.","slug":"useful-sql","type":"basic","title":"Useful SQL"}

Useful SQL

Transact-SQL that can be used with an optimized standard forest description database.

The underlying database management system is Microsoft SQL Server. End users are free to query the data structure and extract data using their own user-defined Transact-SQL queries. <br /> [block:api-header] { "type": "basic", "title": "Querying the Entire Optimization Solution" } [/block] [block:callout] { "type": "warning", "title": "SQL SERVER 2008R2 & SQL SERVER 2005 ONLY", "body": "The query syntax below uses the '**SET FMTONLY**' statement. This has been deprecated in all SQL Server versions after 2008R2. The query as shown below will only work on SQL Server versions 2008R2 and below." } [/block] It is possible to [create a file that contains the complete optimization solution](doc:how-to-export), along with all the planning unit metadata that were defined in the forest description. The resulting CSV file can be double clicked and opened using Microsoft Excel. It can then form the basis of temporary pivot tables or any other comprehensive data analysis that may be of interest. It is possible to query the same stored procedure that is used to export the optimization solution, with the additional functionality that users can add their own SQL logic to further refine the results returned by the stored procedure. To do this, users can change the SQL code between the following two statements in the main block of Transact-SQL code presented below. 1. `-- BEGIN USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE --` and, 2. `-- END USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE --` [block:callout] { "type": "danger", "body": "The Transact-SQL code out of the two statements above needs to remain static and should not be changed if the query is to return its results correctly.", "title": "Only Change User Defined SQL Code" } [/block] Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar). Then make changes to the `...SELECT * FROM ##tmp_consolidated...` statement as needed to further refine and enhance the data returned by the query. ``` --BEGIN-- --TURN ON AD HOC QUERIES-- EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE EXEC sp_configure 'show advanced options', 0 RECONFIGURE --END-- --BEGIN-- IF OBJECT_ID('tempdb..##tmp_consolidated') IS NOT NULL BEGIN IF OBJECT_ID('tempdb..IX01_tmp_consolidated') IS NOT NULL BEGIN ALTER TABLE ##tmp_consolidated DROP CONSTRAINT IX01_tmp_consolidated END DROP TABLE ##tmp_consolidated END --END-- --BEGIN-- DECLARE @SQL VARCHAR(MAX) SELECT @SQL = '' + 'SELECT * ' + 'INTO ##tmp_consolidated ' + 'FROM OPENROWSET(''SQLNCLI'', ''SERVER=' + CONVERT(VARCHAR(100),SERVERPROPERTY('MachineName')) + '\' + CONVERT(VARCHAR(100),SERVERPROPERTY ('InstanceName')) + ';TRUSTED_CONNECTION=YES;'', ''SET FMTONLY OFF ' + 'EXEC "' + DB_NAME() + '".[dbo].[sp_RSConsolidated]'')' EXEC(@SQL) --END-- --BEGIN-- --RECREATE PKEY FROM tbl_consolidated JUST BECAUSE-- ALTER TABLE ##tmp_consolidated ADD CONSTRAINT [PK00_tmp_consolidated] PRIMARY KEY CLUSTERED ([Period] ASC, [PlunitID] ASC, [OriginID] ASC, [SpeciesID] ASC, [Type] ASC, [Status] ASC, [Age] ASC, [ProductID] ASC, [DestinationID] ASC, [Proportion] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] --END-- ----------------------------------------------------------------------------------------- --BEGIN USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE-- ----------------------------------------------------------------------------------------- -- NOW DO WHATEVER YOU WANT WITH THE DYNAMIC TEMP TABLE ##tmp_consolidated -- IT IS A MIRROR OF THE DATA RETURNED BY STORED PROC [sp_RSConsolidated] SELECT * FROM ##tmp_consolidated INNER JOIN mps_cf_volume ON ##tmp_consolidated.[PlunitID] = mps_cf_volume.[PlunitID] AND ##tmp_consolidated.[Age] = mps_cf_volume.[Age] WHERE ##tmp_consolidated.[Status] = 'Yield' ----------------------------------------------------------------------------------------- --END USER DEFINED QUERY TO RETURN DATA USING TEMP TABLE-- ----------------------------------------------------------------------------------------- --BEGIN-- IF OBJECT_ID('tempdb..##tmp_consolidated') IS NOT NULL BEGIN IF OBJECT_ID('tempdb..IX01_tmp_consolidated') IS NOT NULL BEGIN ALTER TABLE ##tmp_consolidated DROP CONSTRAINT IX01_tmp_consolidated END DROP TABLE ##tmp_consolidated END --END-- --BEGIN-- --TURN OFF AD HOC QUERIES FOR SAFETY-- EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'ad hoc distributed queries', 0 RECONFIGURE EXEC sp_configure 'show advanced options', 0 RECONFIGURE --END-- ``` <br /> [block:api-header] { "title": "Create a Generic Allocation Model" } [/block] It is possible to list every combination of species log grade that exists in the model. This simple list can then be used to quickly create a generic allocation model that can be used for initial model building and testing. The SQL code below will produce data that can be copied and pasted to the worksheet 'Allocation'. This can then be used to quickly create an initial model for testing and debugging purposes. The 'Create a Generic Revenue Model' and 'Create a Generic Transport Model' code blocks below this current section can then be used to produce a compatible revenue model and a compatible transport model for this generic allocation data. Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar). ``` DECLARE @PeriodMax AS INT DECLARE @AgeclassMax AS INT SET @PeriodMax = (SELECT MAX([Period]) FROM tbl_period) SET @AgeclassMax = (SELECT MAX([Age]) FROM tbl_age) SELECT '1' AS 'Start', @PeriodMax AS 'End', 'Period' AS 'Type', 'All Plunits' AS 'Group', 'All' AS 'Attribute', '0' AS 'Min', @AgeclassMax AS 'Max', '>' AS 'Equality', '0' AS 'Quantity', 'm3' AS 'Unit', 'Generic' AS 'Destination', tbl_species.[Species], tbl_product.[Product], '>' AS 'Sign', '0' AS 'Mix' FROM tbl_plunit INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID] INNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID] INNER JOIN tbl_yield ON tbl_croptype.[CroptypeID] = tbl_yield.[CroptypeID] INNER JOIN tbl_product ON tbl_yield.[ProductID] = tbl_product.[ProductID] WHERE tbl_product.[Status] = 'YIELD' GROUP BY tbl_species.[Species], tbl_product.[Product] UNION SELECT '1' AS 'Start', @PeriodMax AS 'End', 'Period' AS 'Type', 'All Plunits' AS 'Group', 'All' AS 'Attribute', '0' AS 'Min', @AgeclassMax AS 'Max', '>' AS 'Equality', '0' AS 'Quantity', 'm3' AS 'Unit', 'Generic' AS 'Destination', tbl_species.[Species], tbl_product.[Product], '>' AS 'Sign', '0' AS 'Mix' FROM tbl_plunit INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID] INNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID] INNER JOIN tbl_silviculture ON tbl_regime.[RegimeID] = tbl_silviculture.[RegimeID] INNER JOIN tbl_product ON tbl_silviculture.[ProductID] = tbl_product.[ProductID] WHERE tbl_product.[Status] = 'YIELD' GROUP BY tbl_species.[Species], tbl_product.[Product] ORDER BY [Species], [Product] ``` <br /> [block:api-header] { "title": "Create a Generic Revenue Model" } [/block] The SQL code below will produce data that can be copied and pasted to the worksheet 'Price'. This can be used to quickly create an initial revenue model for testing and debugging purposes and is compatible with the generic allocation data produced using the 'Create a Generic Allocation Model' code block above this current section. [block:callout] { "type": "danger", "title": "If a species log grade combination does not exist", "body": "If a species log grade combination does not exist then a NULL value is generated in the data. Users should replace these NULL values with a blank cell after the data are pasted to the 'Price' worksheet in the forest description." } [/block] Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar). ``` DECLARE @Columns AS NVARCHAR(MAX) SET @Columns = STUFF((SELECT ',' + QUOTENAME(tbl_product.[Product]) FROM tbl_product WHERE tbl_product.[Status] = 'YIELD' GROUP BY tbl_product.[Product] ORDER BY tbl_product.[Product] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') DECLARE @SQL AS NVARCHAR(MAX) SET @SQL = ' SELECT * FROM ( SELECT ' + CHAR(39) + 'Generic' + CHAR(39) + 'AS' + CHAR(39) + 'Destination' + CHAR(39) + ', ' + CHAR(39) + 'All' + CHAR(39) + 'AS' + CHAR(39) + 'Origin' + CHAR(39) + ', tbl_species.[Species], ' + CHAR(39) + 'None' + CHAR(39) + 'AS' + CHAR(39) + 'Index' + CHAR(39) + ', tbl_product.[ProductID], tbl_product.[Product] FROM tbl_plunit INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID] INNER JOIN tbl_croptype ON tbl_plunit.[CroptypeID] = tbl_croptype.[CroptypeID] INNER JOIN tbl_yield ON tbl_croptype.[CroptypeID] = tbl_yield.[CroptypeID] INNER JOIN tbl_product ON tbl_yield.[ProductID] = tbl_product.[ProductID] GROUP BY tbl_species.[Species], tbl_product.[ProductID], tbl_product.[Product] UNION SELECT ' + CHAR(39) + 'Generic' + CHAR(39) + 'AS' + CHAR(39) + 'Destination' + CHAR(39) + ', ' + CHAR(39) + 'All' + CHAR(39) + 'AS' + CHAR(39) + 'Origin' + CHAR(39) + ', tbl_species.[Species], ' + CHAR(39) + 'None' + CHAR(39) + 'AS' + CHAR(39) + 'Index' + CHAR(39) + ', tbl_product.[ProductID], tbl_product.[Product] FROM tbl_plunit INNER JOIN tbl_species ON tbl_plunit.[SpeciesID] = tbl_species.[SpeciesID] INNER JOIN tbl_regime ON tbl_plunit.[RegimeID] = tbl_regime.[RegimeID] INNER JOIN tbl_silviculture ON tbl_regime.[RegimeID] = tbl_silviculture.[RegimeID] INNER JOIN tbl_product ON tbl_silviculture.[ProductID] = tbl_product.[ProductID] GROUP BY tbl_species.[Species], tbl_product.[ProductID], tbl_product.[Product] ) AS qry_plunit PIVOT ( MAX(qry_plunit.[ProductID]) FOR [Product] IN (' + @Columns + ') ) AS pvt_plunit ' EXECUTE (@SQL) ``` <br /> [block:api-header] { "title": "Create a Generic Transport Model" } [/block] The SQL code below will produce data that can be copied and pasted to the worksheet 'Transport'. This can be used to quickly create an initial transport model for testing and debugging purposes and is compatible with the generic allocation data produced using the 'Create a Generic Allocation Model' code block above this current section. Copy the entire block of the following Transact-SQL code to the SQL Server Management Studio query window (or similar). ``` SELECT tbl_origin.[Origin], '0' AS 'Generic' FROM tbl_origin WHERE tbl_origin.[OriginID] > 0 ``` <br />