Powershell – Script for Dates for file naming

This script produces the output:



For the date of 06/06/2018.

$DaysBack =-1
$Yr = Get-Date -format yyyy
$Mt = Get-Date -format MM
$Dy = Get-Date -format dd
$TodayString = $Yr+$Mt+$Dy
$YD = Get-Date
$YD = $YD.AddDays($DaysBack)
$Lyd = ($YD.Year).ToString()
$Lmd = '0'+ ($YD.Month).ToString()
$Lmd = $Lmd.Substring($Lmd.Length-2)
$Ldy = '0'+ ($YD.Day).ToString()
$Ldy = $Ldy.Substring($Ldy.Length-2)
$YesterdayString = $Lyd+$Lmd+$Ldy
ECHO $TodayString
ECHO $YesterdayString

Great Plains – Asset TSQL Case Statements

 WHEN 1  THEN 'New'
 WHEN 2  THEN 'Used'
 WHEN 3  THEN 'Leased'
 END as nvarchar(50)) as c_AssetType

 WHEN 1  THEN 'Active'
 WHEN 2  THEN 'Deleted'
 WHEN 3  THEN 'Partial Open'
 WHEN 4  THEN 'Retired'
 END as nvarchar(50)) as c_AssetStatus

 WHEN 1  THEN 'Personal'
 WHEN 2  THEN 'Personal, Listed'
 WHEN 3  THEN 'Real'
 WHEN 4  THEN 'Real, Listed'
 WHEN 5 THEN 'Real, Conservation'
 WHEN 6 THEN 'Real, Energy'
 WHEN 7 THEN 'Real, Farms'
 WHEN 8 THEN 'Real, Low Income Housing'
 WHEN 9 THEN 'Amortizable'
 END as nvarchar(50)) as c_PropertyType

 WHEN 1  THEN 'Reserve'
 WHEN 2  THEN 'Depreciation'
 WHEN 3  THEN 'Cost'
 WHEN 4  THEN 'Proceeds'
 WHEN 5 THEN 'Recognized Gain/Loss'
 WHEN 6 THEN 'Non Recognized Gain/Loss'
 WHEN 7 THEN 'Clearing'
 END as nvarchar(50)) as c_TransactionAccountType

 WHEN 1 THEN 'Straight-Line Orig Life'
 WHEN 2 THEN 'Straight-Line Rem Life'
 WHEN 3 THEN '125% DB'
 WHEN 4 THEN '150% DB'
 WHEN 5 THEN '175% DB'
 WHEN 6 THEN '200 DB'
 WHEN 7 THEN 'SOY Digits'
 WHEN 8 THEN 'Remaining Life'
 WHEN 9 THEN 'Amortization'
 WHEN 10 THEN 'ACRS Personal'
 WHEN 14 THEN 'ACRS Foreign Real'
 WHEN 15 THEN 'No Depreciation'
 WHEN 16 THEN 'Declining Balance'
 END as nvarchar(50)) as c_DepreciationMethod

 WHEN 1  THEN 'Half-Year'
 WHEN 2  THEN 'Modified Half-Year'
 WHEN 3  THEN 'Mid-Month (1st)'
 WHEN 4  THEN 'Mid-Month (15th)'
 WHEN 5 THEN 'Mid-Quarter'
 WHEN 6 THEN 'Next Month'
 WHEN 7 THEN 'Full Month'
 WHEN 8 THEN 'Next Year'
 WHEN 9 THEN 'Full Year'
 WHEN 10 THEN 'Full Year All Year'
 WHEN 11 THEN 'None'
 WHEN 12  THEN 'Next Period'
 WHEN 13  THEN 'Full period'
 End as c_AverageAgingConvention

 WHEN 1  THEN 'No switch'
 WHEN 2  THEN 'Straight-Line'
 END as c_SwitchOver


Thanks to Victoria Yudin. https://victoriayudin.com/gp-tables/fixed-assets-tables/

I’m just making these so we don’t have to re do this over and over again.

DOCTYPE Case Statement for Great Plains

cast(case a.DOCTYPE when 1 THEN 'Invoice'
 when 2 THEN 'Finance Charge'
 when 3 THEN 'Misc Charge'
 when 4 THEN 'Return'
 when 5 THEN 'Credit Memo'
 when 6 THEN 'Payment' end as nvarchar(50)) as DocType

Thanks to Victoria Yudin for the source data. https://victoriayudin.com/gp-tables/pm-tables/



DISTTYPE Case Statement for Great Plains

Victoria Yudin has some very useful information on the web but, I need a practical script for SQL. Now you can have it too.

cast(case DISTTYPE when 1  THEN 'Cash'  
WHEN 2  THEN 'Payable'  
WHEN 3  THEN 'Discount Available'
WHEN 4  THEN 'Discount Taken'  
WHEN 5  THEN 'Finance Charge' 
WHEN 6  THEN 'Purchase' 
WHEN 7  THEN 'Trade Disc.'
WHEN 8  THEN 'Misc. Charge' 
WHEN 9  THEN 'Freight' 
WHEN 10  THEN 'Taxes' 
WHEN 11  THEN 'Writeoffs'
WHEN 12  THEN 'Other' 
WHEN 13  THEN 'GST Disc' 
WHEN 14  THEN 'PPS Amount' 
WHEN 16  THEN 'Round'
WHEN 17  THEN 'Realized Gain' 
WHEN 18  THEN 'Relaized Loss' 
WHEN 19  THEN 'Due To'
WHEN 20  THEN 'Due From' end as nvarchar(50)) as DistributionType

Thanks to Victoria Yudin for the source data. https://victoriayudin.com/gp-tables/pm-tables/

SSIS Expression Convert From IntDate to String

In a SSIS Expression how do you get from 20160101 to 01/01/2016?

Use this command in Derived Column Editor and replace the field that you’re intending to convert.

SUBSTRING((DT_WSTR,8)[TimePeriod],5,2) + “/” + SUBSTRING((DT_WSTR,8)[TimePeriod],7,2) + “/” + SUBSTRING((DT_WSTR,8)[TimePeriod],1,4)

I found several incomplete examples that didn’t include the (DT_WSTR,8) conversions of the int field. This is just to help people who also need int to Date in SSIS.



TSQL – Replace URL Escape Characters

This T-SQL code is to help parse out fields that need to be concatenated into a url string.
Create FUNCTION dbo.ReplaceURLEscapeCharacters
Created to parse Tokens that are being concatenated for URL generation.
Myles Yamada 
 SELECT @Token = REPLACE( @Token, char(37), '%25')--% must be first because output contains this character
 SELECT @Token = REPLACE( @Token, char(32), '%20')--space(1)
 SELECT @Token = REPLACE( @Token, char(33), '%21')--!
 SELECT @Token = REPLACE( @Token, char(34), '%22')--"
 SELECT @Token = REPLACE( @Token, char(35), '%23')--#
 SELECT @Token = REPLACE( @Token, char(36), '%24')--$
 SELECT @Token = REPLACE( @Token, char(38), '%26')--^
 SELECT @Token = REPLACE( @Token, char(39), '%27')--'
 SELECT @Token = REPLACE( @Token, char(40), '%28')--(
 SELECT @Token = REPLACE( @Token, char(41), '%29')--)
 SELECT @Token = REPLACE( @Token, char(42), '%2A')--*
 SELECT @Token = REPLACE( @Token, char(43), '%2B')--+
 SELECT @Token = REPLACE( @Token, char(44), '%2C')--,
 SELECT @Token = REPLACE( @Token, char(45), '%2D')-- -
 SELECT @Token = REPLACE( @Token, char(46), '%2E')--.
 SELECT @Token = REPLACE( @Token, char(47), '%2F')--/
 SELECT @Token = REPLACE( @Token, char(58), '%3A')--:
 SELECT @Token = REPLACE( @Token, char(59), '%3B')--;
 SELECT @Token = REPLACE( @Token, char(60), '%3C')--<
 SELECT @Token = REPLACE( @Token, char(61), '%3D')--=
 SELECT @Token = REPLACE( @Token, char(62), '%3E')-->
 SELECT @Token = REPLACE( @Token, char(63), '%3F')--?
 SELECT @Token = REPLACE( @Token, char(64), '%40')--@
 SELECT @Token = REPLACE( @Token, char(91), '%5B')--[
 SELECT @Token = REPLACE( @Token, char(92), '%5C')--\
 SELECT @Token = REPLACE( @Token, char(93), '%5D')--]
 SELECT @Token = REPLACE( @Token, char(94), '%5E')--^
 SELECT @Token = REPLACE( @Token, char(95), '%5F')--_
 SELECT @Token = REPLACE( @Token, char(96), '%60')--`
 SELECT @Token = REPLACE( @Token, char(123), '%7B')--{
 SELECT @Token = REPLACE( @Token, char(124), '%7C')--|
 SELECT @Token = REPLACE( @Token, char(125), '%7D')--}
 SELECT @Token = REPLACE( @Token, char(126), '%7E')--~
RETURN @Token;
select dbo.ReplaceURLEscapeCharacters('#$^$@#%&(@#$%#%(*@')

Thanks to http://www.asciitable.com/ for the chart to build this script from.

This original work of art or code is provided free of charge for the betterment of humanity as a public service. No warranties or guarantees should be assumed or implied by the posting of this code. anyone using this code is doing so at their own risk. Be a good dba/developer and test anything found on the web in a test or dev box. 🙂

Editing AX 2012 SSRS Report – PurchPurchaseOrder

When modifying this report in Visual Studio 2010 the classes will throw all types of errors.

The first error that I got was explained here earlier today Next there was an error when refreshing the PurchPurchaseOrder Data source on the AX 2012 Reporting services Report. It stated that, “Element’;PurchPurchaseOrder.Parameters.IsPurchConfirmationRequestJournal’ has already been defined. To correct this, rename on or more of the model elements so that they have a unique name. Another developer already solved this issue so here is his fix: The second error was explained here The last error that I received was this:

Error 1 An exception occurred in the query metadata execution. 
The exception is Function PurchTableAllVersions.initFromProformaPurchTableVersion has been incorrectly called.
(S)\Classes\Info\add - line 94
(S)\Classes\Global\error - line 3
(S)\Data Dictionary\Views\PurchTableAllVersions\Methods\initFromProformaPurchTableVersion - line 15
(S)\Classes\PurchPurchaseOrderDP\initProformaPurchTableAllVersions - line 9
(S)\Classes\PurchPurchaseOrderDP\retrieveJournalAndVersion - line 23
(S)\Classes\PurchPurchaseOrderDP\createData - line 30
(S)\Classes\PurchPurchaseOrderDP\processReport - line 52
(S)\Classes\SrsReportRunRdpPreProcessStrategy\execute - line 27
(S)\Classes\SrsReportRunRdpPreProcessService\executeWithContract - line 82
(S)\Classes\SrsReportProviderQueryBuilder\runPreviewModePreProcessedRdp - line 29
(S)\Classes\SrsReportProviderQueryBuilder\initialize - line 58
(S)\Jobs\. PurchPurchaseOrder.Report [Preview] 0 0 

These errors are related to the fact that we can’t run some reports in Visual Studio to see results especially if there are args that come from the AX UI. You can ignore these errors. When the report is run from the form it works perfectly.