Thursday, November 12, 2009

Information Rights Management (IRM)

Here's an interesting article from Fahim Siddiqui, EVP of Intralinks, Inc. His article can help organizations in understanding the complexity of applying encryption and permissioning to corporate information. Read article here

Tuesday, November 10, 2009

Revenue and Expense Deferral

What is Revenue and Expense Deferral (RED)?

Revenue Deferral is considered a liability until it becomes relevant to the business at hand, such as a payment received for work that has not yet been performed. opposite of deferred charge. Expense Deferral refers to an item that will initially be recorded as an asset but is expected to become an expense over time and/or through the normal operation of the business.

Who can use this module?

Dynamics GP RED (Revenue and Expense Deferral) was designed for any company that needs to defer revenue or expense.

Let's say you're a media company with business in magazine publishing. You're offering your magazine subscription on a yearly, semi-annually or monthly (one-time) basis. When somebody subscribes to your magazine and pay a year subscription, you'll need to defer part of this subscription. At the end of the subscription period, you will have fulfilled your responsibility and the subscription will be fully realized as earned income.

How do I book a deferred entry from a Sales Invoice in Dynamics GP?

Once you have the sales invoice created, open the Distribution window select the account you want to defer then go to Extras>Additional>Deferral. Enter your Start and End dates, billing recognition account and choose your deferral method (Days Period, Equal Period and Miscellaneous).

Things to note when using this module:
- Deferred entries are always valued at functional currency
- You can only defer one invoice at a time
- Void Deferral Transactions can be utilize to void deferred entries only and have the original document like a Sales Invoice unvoided

There's room for improvement with this module including:
- Mass document deferral
- Audit-trail from a GL entry
- Multi-currency reporting based on originating document currency

To learn more about Revenue and Expense Deferral, you can visit

Saturday, October 10, 2009

Get Customers with more than one address

Here's a simple SQL statement that I've used to help me resolve some of our issues be it customer, vendor or item master files:

Select custnmbr, Count(ADRSCODE) As AddressCount
From RM00102
Group By custnmbr
Having Count(ADRSCODE) > 1

or if you want detail use this

Select *
From RM00102
Inner Join (
Select CUSTNMBR, Count(ADRSCODE) As AddressCount
From RM00102
Having Count(ADRSCODE) > 1
) As A

Tuesday, September 15, 2009

SQL script to show last invoice detail of every customer

Copy and paste this to SQL Server Enterprise Manager:

select distinct a.custnmbr,a.LSTTRXDT,a.lsttrxam,b.sopnumbe
from rm00103 a
join sop30200 b
on a.custnmbr = b.custnmbr and
a.LSTTRXDT = b.docdate
/* show in ascending order */
order by custnmbr asc

Friday, April 24, 2009

Planning SQL Upgrade

So you finally decided to upgrade to SQL Server 2008 but it's been awhile since you looked at options for your Raid configurations. Here's a summary of what your options are:

Raid 0 - data is spread in different disk (disk stripping) but lacks redundancy
PROS - performs well on read and write
CONS - Failure of disk means data is lost
Raid 1 - known as disk mirroring no disk stripping. Data is hosted on a single drive then copied to another drive for fault tolerance. Recommended for Transaction Logs.
PROS - Redundant so failure of a drive will not stop database access
CONS - Cost since you'll need to double the size of your disk.

Raid 5 - uses disk stripping with parity. Data is spread but also stores parity information so it can be used in case of disk failure. Best for read-based not many write operations. Also avoid for transaction log because it's primarily sequential write.
PROS - Fault Tolerance at a low price.
CONS - Write Performance. Additional time is required to compute parity info before it's written to disk.

Raid 10 - cross between Raid 0 and 1. Data is striped evenly accross all drives then mirrored.
PROS - Write performance is far superior than Raid 5 and read performance is similar to Raid 5. Offers Fault Tolerance.
CONS - Cost since you'll need twice the number of disk like Raid 1.

Monday, April 20, 2009

SQL Wildcards

We implemented Multicurrency module and part of this implementation is changing the naming convention of our customers in Great Plains. We want to easily identify what kind of customer are they. Currently, our customer id ends in 001 so we decided to change it to whatever the contract currency is. So let's say ABC has a EURO contract with us then their customer id will then be ABCEUR instead of ABC001.

Our database is shared to other systems like our BI team to create relational DBs. A question came to my lap on the easiest way to segregate the customers based on their contract currency. We don't want to give them the Currency ID field as it's not consistent with other system. So I provided them with this Select statement:

Select * from RM00101
where custnmbr like '%[EUR]'

Thursday, March 26, 2009

Proud Uncle

I'm so proud of my niece, Guia del Prado, that I wanted to congratulate her in my blog for being featured in her schools website - University of Nevada, Reno : .

She's currently the news editor for the campus magazine - Insight.

Wednesday, March 25, 2009

Convergence 2009 update

Just came back from Convergence 2009 - New Orleans and found this year quite a disappointment as I didn't see a lot of value to the seminars, GP ver 11 and the future of products around Dynamics GP.

Here are my updates this year:

1) GP ver 11 - The presenter in the kiosk didn't show a lot of functionality as she said they're still finalizing the list but I like the option of printing a form to Microsoft Word which many of us had been requesting.
2) FRx - No goods new here as Microsoft announced last year Management Reporter will replace it. I found that Management Reporter lacks some functionality including XBRL (I hope they've resolve this already), etc. FRx support end on December 2010.
3) Forecaster - The big question - PerformancePoint or Dynamics?
4) CRM - There were a few seminars showing the new integration between this product and Dynamics GP. One presenter said it can now compete with vendors like Scribe.

I'll post more later....

Wednesday, February 11, 2009

FRx Error - Period not found

It's a new year and you just closed 2008 in Great Plains. You run FRx for the first period of the year and use /BB and @PERDESC header. The report will run without error but you're getting this - Period not found in Company Calendar, as the column header.

So what do you do? Download Microsoft FRx 6.7 Service Pack 10. Here are the links to download the file:

For GP Partners download here.

For GP Customers download here.

Tuesday, January 27, 2009

PPS Planning Discontinued by April 1,2009

A couple of emails I've received and feedback I've seen from different forums are not consistent with the announcement Microsoft gave last Friday and I want to clarify this.

The PerformancePoint Server 2007 is rebranded as PerformancePoint Services and will still be offered in the market as part of MOSS Enterprise. There are 3 components of the PPS server: Planning, Analytics and Monitoring (PAM). Of this 3 only Planning is discontinued while Analytics and Monitoring will be called PerformancePoint Services like Excel Services, Forms Services and Visio Services in the Sharepoint platform.

Thursday, January 22, 2009

My favorite SQL Server sites

Data Mining

Best Practice

Reporting (William Pearson)


Microsoft BI

SQL Protocols

BI according to Microsoft

The Microsoft BI team presented the new BI stack to the New York Business Intelligence New York Meetup group yesterday. The first part of the presentation was the product demonstration then an open discussion on the product functionalities which got too detailed. A comparison of Excel 2007 and Proclarity was also in the table but was cut short because it was out of content. It was refreshing to see ProClarity working in harmony with PerformancePoint Server and MOSS.

To get more info about Microsoft BI click here. To join the New York Business Intelligence Meetup click here.

Friday, January 16, 2009

Microsoft Convergence 2009 New Orleans Announcements

If you're planning on attending Convergence this year please read this:

  • Early Registration discount date has been extended to January 21, 2009. Save $300 by registering early, secure your favorite hotels while they are still available and take advantage of better airline rates.
  • The session catalog (containing 225 sessions) is now live on the Convergence marketing site.
  • The Convergence Customer Excellence Awards recognize, honor and celebrate customers who have achieved outstanding success with their Microsoft solutions as well as Microsoft partners' solutions. Customer Excellence Award nominations are now being accepted through January 20, 2009.