Bad Debt Model Quarterly

 One of my users requested this report to be able to get the specific list of accounts in one report instead of going to Summary Inquiry one at a time.

Solution:

1. Create an SQL View in your SQL Server by running the SQL script below

USE [CO100 - @yourcompanyID]

GO

/****** Object:  View [dbo].[view_Open_Yr_GL_TB_Month_End]    Script Date: 10/11/2023 2:15:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create view [dbo].[view_Open_Yr_GL_TB_Month_End]

as

 -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

-- Created July 9, 2014 by Victoria Yudin

-- Flexible Solutions, Inc.

-- For other code, please visit http://victoriayudin.com

-- Only returns the first open year in GP

-- Month names in columns use calendar fiscal year

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

 select

g.YEAR1 [Year],

a.ACTNUMST Account,

g.ACTDESCR [Description],

c.ACCATDSC Category,

sum(case g.PERIODID when 0

    then g.PERDBLNC else 0 end) Beginning_Balance,

sum(case when g.PERIODID <= 1

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,

sum(case when g.PERIODID <= 2

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,

sum(case when g.PERIODID <= 3

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,

sum(case when g.PERIODID <= 4

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,

sum(case when g.PERIODID <= 5

    then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,

sum(case when g.PERIODID <= 6

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,

sum(case when g.PERIODID <= 7

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,

sum(case when g.PERIODID <= 8

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,

sum(case when g.PERIODID <= 9

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,

sum(case when g.PERIODID <= 10

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,

sum(case when g.PERIODID <= 11

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,

sum(case when g.PERIODID <= 12

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

 from GL11110 g --GL summary data

 inner join GL00102 c --categories

on g.ACCATNUM = c.ACCATNUM

 inner join GL00105 a --for account number

on g.ACTINDX = a.ACTINDX

 where g.ACCTTYPE = 1

and g.YEAR1 = (select min(YEAR1) from GL11110)

 group by g.YEAR1, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

 GO

2. Create your Smartlist Builder 

Add your SQL Table (Views)

Add your SQL Script

Here is the SQL Script for the accounts filter

select * from CO100..GL00105 where ACTNUMST IN 

('100-000-14800', 

'100-000-13000', 

'200-000-12502',

'200-000-12501',

'100-000-11999',

'100-645-41000',

'100-000-12500',

'100-000-12504',

'100-645-41300')

union all

select * from CO200..GL00105 where ACTNUMST IN 

('100-000-14800', 

'100-000-13000', 

'200-000-12502',

'200-000-12501',

'100-000-11999',

'100-645-41000',

'100-645-41300')

Edit your Display Name and mark defaults

3. Grant Users access to SmartList Object