Dynamics GP - SQL Trigger - Create a trigger to update RM00101 with last payment date from RM00103

USE [AW]
GO

/****** Object:  Trigger [LastPmtDate]    Script Date: 11/14/2011 14:59:33 ******/
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_LastPmtDate]'))
DROP TRIGGER [dbo].[trg_LastPmtDate]
GO

USE [AW]
GO

/****** Object:  Trigger [dbo].[LastPmtDate]    Script Date: 11/14/2011 14:59:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trg_LastPmtDate]
   ON  [dbo].[RM00103]
   AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;

update RM00101 set RM00101.CCRDXPDT = inserted.LASTPYDT
from INSERTED
where RM00101.CUSTNMBR = inserted.custnmbr


END

GO

price group header not found for item class group

The company has not been correctly converted from Standard pricing to Extended pricing.

1) Go to Tools>Setup>Sales>Extended Pricing
2) Enable Standard pricing
3) Logout, Login
4) Enable Extended Pricing
5) Logout, Login

Dynamics GP Restore a copy of live data into a test company

1) Backup live
2) Create test company
3) Restore over test
4) Run this

/******************************************************************************/
/* Description: */
/* Updates any table that contains a company ID or database name value */
/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */
/* */
/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
  create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables

declare @cStatement varchar(255)

declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
  then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
  'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
  and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
   insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
   Exec (@cStatement)
  FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

select [tableName] as 'Tables that were Updated' from ##updatedTables

5) Then run this

sp_changedbowner 'DYNSA'

--------------------------------------------------------------------------------

View
KB871973
for more information

SSAS How to calculate Variance, YTD, Prior Year in MDX

To use any of this code, go into your cube, click on Calculations Tab, then Script View, and just paste it in there. Or you can build it the long way through the form
 view.


SIMPLE VARIANCE
Example: Difference of Actual sales (A Value) from Budgeted Sales (B Value)
You run into problem with zero values on the Budget side if you're dividing by the budget to get your variance percentage.
--------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[B Var Value PC]
 AS case

when [Measures].[B Value] = 0
and [Measures].[A Value] > 0
then 1

when [Measures].[B Value] = 0
and [Measures].[A Value] < 0
then -1

when [Measures].[B Value] = 0
and [Measures].[A Value] = 0
then 0

else [Measures].[B Var Value]/[Measures].[B Value] end,
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [A Value] },
VISIBLE = 1;
--------------------------------------------------------------------------------

YTD CALCULATIONS
YTD Calculations require that your time periods are set up properly first.
Ensure that your Time dimension has a hierarchy for at least
Year>Month
You must also define your attribute relationships
Key>Month>Year

Go to Time dimension>Attribute Relationships>Right click Year Attribute>Properties
Ensure Type is set to Years
This defines the year level for this time hierarchy.

Now your YTD function will work without any errors
The YTD will represent all periods within the yr-month hierarchy up to the currentmember
The SUM will keep adding across each period to get a running total.

-----------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A YTD Case]
 AS sum(ytd([View B Time].[yr-month].currentmember),[Measures].[A Value]),
VISIBLE = 1;
-----------------------------------------------------------------------------

PRIOR YEAR YTD
To calculate a prior Year YTD uses almost the same formula.
Instead of using the currentmember, we have to use the PARALLELPERIOD of the currentmember one year ago.
PARALLELPERIOD(Year Set Containing your year numbers, 1 year ago, Month set containing the months.the currentmember year)

-------------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[Measures].[A PYYTD Case]
 AS sum(YTD(
PARALLELPERIOD(
[View B Time].[Yr-Month].[Year Num],
1,
[View B Time].[Yr-Month].currentmember
)
),
[Measures].[A Value]
),
VISIBLE = 1  ;
---------------------------------------------------------------------------------

MDX - Basics

Basic Syntax of an MDX Query


select
{
([View Time].[Year Num].&[2010]),
( [View Time].[Year Num].&[2011])
}
on columns,
([View B Items].[Item Brand].members)
on rows
from budget
where [Measures].[Sales]

Shows columns: 2010, 2011
Rows: All members of Item Brand
Values: Total Sales for each brand for each year

Dynamics GP Links and Resources

Developer Info
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/07/15/developer-articles.aspx

GP 2013 Service Packs, Hotxies, Patches, Updates
https://mbs.microsoft.com/partnersource/downloads/servicepack/mdgp2013_patchreleases.htm?printpage=false&sid=2th1yrqua13zbfg4q2jwc2v2&stext=gp 2013 hotfix

Dynamics GP Report Writer - Barcodes

Under report definition, format options, untick text report

Create a formula field, use constant * cat strip(your field value) cat  *.
The * is required to identify the barcode beginning and end.
Strip is needed to remove the trailing blank spaces after your field value
Change the font type to your barcode font Tools>Drawing Options (Font name is 3 of 9 or Code 128)

Regular Code128 font will never work, it requires encoding through an encoder