Implementing SmartList Builder: What is SmartList Builder?

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

Before launching into the installation of SmartList Builder, I thought it might be worthwhile to do a post on what it is. All users of Microsoft Dynamics GP will be familiar with SmartList; this is the flexible reporting tool which allows you to generate a report showing a list of master records or transactions. Each series has a set of SmartLists available by default, with predefined favourites where search criteria or columns are supplied already configured.

SmartList favourites can be customised and new ones created quite easily, but new SmartLists themselves cannot be created. To create a new SmareList, you need a tool such as SmartList Builder or, in more recent years, SmartList Designer. In this series, I am focussing on SmartList Builder as this is an additional product available from an ISV for Microsoft Dynamics GP.

Back in 2003 when I started working with Microsoft Dynamics GP, SmartList Builder was available direct from Microsoft. SmartList Builder was originally developed by eOne Solutions and then licensed by Microsoft; in the run up to the release of Microsoft Dynamics GP 2013 SP2, the licensing deal for SmartList Builder was ended and maintenance of the product reverted back to eOne Solutions.

SmartList Builder the product, includes four utilities:

  1. SmartList Builder – SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.
  2. Excel Report Builder – Excel Report Builder generates an Excel Spreadsheet with a live connection back to GP or any other data you include. This means you have live refreshable reports, anytime you need them. You can give all your data from GP to anyone in the organization via Excel – without buying new, full-user licenses of Dynamics GP.
  3. Drill Down Builder – Drill Down Builder completes the functionality of Excel Report Builder by letting you drill from your spreadsheet back into that same record within Dynamics GP. Where Microsoft hard codes a drill down – eOne makes it completely configurable to allow you to drill down to any screen or SmartList in Dynamics GP. This is an essential part of bringing the most common business tools together. Working in GP and Excel all day – it only makes sense to auto-switch between one and the other.
  4. Navigation List Builder – Navigation List Builder allows you to publish your SmartList into the Navigation List user interface.

The standard installation of the SmartList Builder product, gets you all four of the above utilities.

Implementing SmartList Builder: Series Index

eOne SolutionsI’ve posted series on a few different ISV addons for Microsoft Dynamics GP over the last few years, but, somewhat ironically I guess, have not posted all that much about the addin I’ve used the most over the years.

That addin is SmartList Builder from eOne Solutions.

SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.

This series is going to cover the implementation of SmartList Builder; I’ll take a look at report builder in a future series.

Implementing SmartList Builder
What is SmartList Builder?

Sample SQL Views for SmartLists (by Jen Kuntz)

Microsoft Dynamics GPI occasionally link to blog posts that other people have done and todays post is one of these.

Jen Kuntz is running a TIP Tuesday series of posts at the moment and her tip this week is looking at a set of SQL views from Microsoft (originally posted by Isaac Olson).

As Jen points out, while Microsoft bill these as for use with SmartList Designer, they will work with SmartList Builder or any other reporting tool (Excel Reports, Reporting Services Reports and so on).

To make use of these SQL views, you do need to have a CustomerSource login to download them and knowledge of creating views and granting permissions.

My addition to this would be a reminder that there are lots of other resources available from the wider community which can be freely amended and used in reporting, such as from Victoria Yudin.

You can read Jen’s full post here.

I try to keep up-to-date with blog posts about Dynamics GP, but the post from Isaac apparently went by without me seeing it, so it was good to get a second chance at reading about it from Jen.

** updated due to wrong name

SQL View to Return Budgets By Month

Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading “SQL View to Return Budgets By Month”

SQL Script Linking Purchase Orders to Purchase Requisitions

Microsoft Dynamics GPI have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn’t sure of the table.

It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

Continue reading “SQL Script Linking Purchase Orders to Purchase Requisitions”

A Diary of reIMAGINE 2016: Part 17 – Conference Day 3/Tips for Creating Integrations Session

reIMAGINE2016The third and final day of the conference was quite a short day with only three sessoins in the morning.

The first of the sessions I attended was Tips for Creating Integrations which was run by a former Microsoftie who now works at eOne. The session did have general tips on what you should be thinking about and asking when creating integrations, but was, as you would expect for a session run by a partner, focused on using their tools. In this case, the tool was SmartConnect.

As a partner of eOne already, SmartConnect was already on my list to take a look at (we currently mainly do SmartList Builder although we also have clients with Extender and SmartView), it was a very interesting session showing how data could be integrated into Dynamics GP from other sources such as Dynamics CRM or a web site and also how the Excel add-in can be used to extract data, and then update Dynamics GP once the user edits the data in Excel.

Much to think about.

Click to show/hide the A Diary of reIMAGINE 2016 Series Index

MDGP 2016 R1 Feature of the Day: Export/Import in SmartList Designer

Microsoft Dynamics GPMicrosoft Dynamics GP 2013 SP2 saw the introduction of SmartList Designer. It was reasonably useful, but nowhere near as useful as SmartList Builder. Little by little Microsoft are making SmartList Designer more useful. Microsoft Dynamics 2016 R1 actually makes it quote a bit more useful by adding the ability to export and import SmartLists:

Export/Import in SmartList Designer

Import in SmartList Designer

Export in SmartList Designer

One f the problems with SmartList Designer was that you had to create the SmartList on the server on which you wanted it; you could not develop it on a test system or development system and if, as a partner, you wanted to give it to another client you had to recreate it. This is why I often created SQL Views and plugged them in rather than creating the SmartList in the Designer.

I’ll need to do some testing to see how good the export/import functionality is, but this feature may allow us to put together SmartLists on our development system and then export them for clients.

Click to show/hide the MDGP 2016 R1 Feature of the Day Series Index

SQL View To Allow Customer/Item Link In SmartList Builder

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
	SELECT
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO

SQL View To Return Purchasing Transactions With Multicurrency

Microsoft Dynamics GPA client I was working with recently was using two SmartLists, one for Payables Transactions and the other from Purchase Order Transactions, to generate a listing of purchasing transactions. However, they had to do manual fiddling around in Excel to get some of the formatting correct and didn’t have all of the information they wanted. Always up for a challenge, I had a go at producing a view we could plug in using SmartList Designer which would give them the information they wanted in the format the needed.

The below is the SQL view which was produced. It includes the PO and Receipt Numbers as well as the Originating Currency (with symbol) and exchange rate; the Functional Currency was left without a symbol so it can be easily totalled in Excel. Where the transaction does not have an Originating Currency I am outputting the Functional Currency.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE VIEW uv_AZRCRV_PurchasingTrxList AS
	SELECT
		PM.VCHRNMBR AS 'Voucher Number'
		,CASE WHEN PM.DOCTYPE = 1 THEN
			PM401.PMTRXDSC_1
		WHEN PM.DOCTYPE = 2 THEN
			PM401.PMTRXDSC_2
		WHEN PM.DOCTYPE = 3 THEN
			PM401.PMTRXDSC_3
		WHEN PM.DOCTYPE = 4 THEN
			PM401.PMTRXDSC_4
		WHEN PM.DOCTYPE = 5 THEN
			PM401.PMTRXDSC_5
		WHEN PM.DOCTYPE = 6 THEN
			PM401.PMTRXDSC_6
		WHEN PM.DOCTYPE = 7 THEN
			PM401.PMTRXDSC_7
		WHEN PM.DOCTYPE = 8 THEN
			PM401.PMTRXDSC_8
		ELSE
			'Unknown'
		END AS 'Document Type'
		,PM004.DOCTYPE
		,CASE WHEN PM004.DCSTATUS = 1 THEN
			'Work'
		WHEN PM004.DCSTATUS = 2 THEN
			'Open'
		WHEN PM004.DCSTATUS = 3 THEN
			'History'
		ELSE
			'Unknown'
		END AS 'Document Status'
		,PM004.DCSTATUS
		,PM.PORDNMBR AS 'PO Number'
		,ISNULL(POP303.POPRCTNM,'') AS 'Receipt Number'
		,PM.VENDORID AS 'Creditor Name'
		,PM002.VENDNAME AS 'Creditor ID'
		,CONVERT(VARCHAR,PM.DOCDATE,103) AS 'Document Date'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.OPURAMT,PM.PRCHAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Purchasing Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORTAXAMT,PM.TAXAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Tax Amount'
		,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END 
			+ ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) 
			+ CAST(CAST(ISNULL(MC.ORDOCAMT,PM.DOCAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Document Amount'
		,CASE WHEN MC.XCHGRATE IS NULL THEN 0 ELSE MC.XCHGRATE END AS 'Exchange Rate'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.PRCHAMNT ELSE PM.PRCHAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Purchasing Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.TAXAMNT ELSE PM.TAXAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Tax Amount'
		,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.DOCAMNT ELSE PM.DOCAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Document Amount'
	FROM
		(
		-- Transaction History
		SELECT
			VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
		FROM
			PM30200 WITH (NOLOCK)
		UNION ALL
			-- Transaction Open
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM20000 WITH (NOLOCK)
		UNION ALL
			-- Transaction Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT
			FROM
				PM10000 WITH (NOLOCK)
		UNION ALL
			-- Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,CHEKTOTL
			FROM
				PM10300 WITH (NOLOCK)
		UNION ALL
			-- Manual Payment Work
			SELECT
				VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,DOCAMNT
			FROM
				PM10400 WITH (NOLOCK)
		) AS PM
	 INNER JOIN
		PM00400 PM004 WITH (NOLOCK)
			 ON PM004.CNTRLNUM = PM.VCHRNMBR AND PM004.DOCTYPE = PM.DOCTYPE
	 INNER JOIN
		PM00200 AS PM002 WITH (NOLOCK)
			 ON PM002.VENDORID = PM.VENDORID
	 LEFT JOIN
		MC020103 AS MC WITH (NOLOCK)
			 ON MC.VCHRNMBR = PM.VCHRNMBR AND MC.DOCTYPE = PM.DOCTYPE
	 LEFT JOIN
		POP30300 AS POP303 WITH (NOLOCK)
			 ON POP303.VCHRNMBR = PM.VCHRNMBR
	 LEFT JOIN
		DYNAMICS..MC40200 AS MC402 WITH (NOLOCK)
			 ON MC402.CURRNIDX = MC.CURRNIDX
	 INNER JOIN
		MC40000 AS MC400 WITH (NOLOCK)
			 ON MC400.FUNLCURR = MC400.FUNLCURR
	 INNER JOIN
		DYNAMICS..MC40200 AS MC402F WITH (NOLOCK)
			 ON MC402F.CURRNIDX = MC400.FUNCRIDX
	 INNER JOIN
		PM40100 AS PM401 WITH (NOLOCK)
			 ON PM401.UNIQKEY = PM401.UNIQKEY
	ORDER BY PM.DOCDATE
 GO 

GRANT SELECT ON uv_AZRCRV_PurchasingTrxList TO DYNGRP
GO

SQL View For Payables Transaction Distribution Accounts

Microsoft Dynamics GPOne of the odd limitations in SmartList is the inability to run a report and get the distribution accounts for payables transactions; there is a field available for the different distributions, but these fields are from the Vendor Card, not the transaction. This view was the result of a query from someone; it returns the account number and description for the payables distributions.

CREATE VIEW uv_AZRCRV_PayablesTransactionDistributionAccounts AS

SELECT
	['PM Distribution WORK OPEN HIST'].VCHRNMBR AS 'Voucher Number'
	,CASE WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 1 THEN
		'Cash'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 2 THEN
		'Payable'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 3 THEN
		'Discount Available'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 4 THEN
		'Discount Taken'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 5 THEN
		'Finance Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 6 THEN
		'Purchase'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 7 THEN
		'Trade Discount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 8 THEN
		'Miscellaneous Charge'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 9 THEN
		'Freight'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 10 THEN
		'Taxes'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 11 THEN
		'Writeoffs'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 12 THEN
		'Other'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 13 THEN
		'GST Disc'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 14 THEN
		'PPS Amount'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 16 THEN
		'Round'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 17 THEN
		'Realized Gain'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 18 THEN
		'Realized Loss'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 19 THEN
		'Due To'
	WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 20 THEN
		'Due From'
	END AS 'Distribution Type'
	,['PM Distribution WORK OPEN HIST'].DistRef AS 'Distribution Reference'
	,['Account Index Master'].ACTNUMST AS 'Account Number'
	,['Account Master'].ACTDESCR AS 'Account Description'
FROM
	(SELECT
		VCHRNMBR
		,DISTTYPE
		,DSTINDX
		,DistRef
	FROM
		PM10100 AS ['PM Distribution WORK OPEN HIST']
	UNION ALL
		SELECT
			VCHRNMBR
			,DISTTYPE
			,DSTINDX
			,DistRef
		FROM
			PM30600 AS ['PM Distribution History File']
	) AS ['PM Distribution WORK OPEN HIST']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON ['Account Index Master'].ACTINDX = ['PM Distribution WORK OPEN HIST'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master']
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
GO

GRANT SELECT ON uv_AZRCRV_PayablesTransactionDistributionAccounts TO DYNGRP
GO