#GPLifeHacks 112: SQL View turned Excel Report for Fiscal Period Closing data

I have been using Mass Close for a few years now.  Not many Microsoft Dynamics GP users use it (in my experience).  I like to use it because it is origin specific closing, versus Series specific closing – the later is used by most everyone.

The ‘trouble’ with Series closing is that there are times when you need to enter transactions into certain areas for the previous month.  What folks typically do is open and close the series over and over again to achieve this.  Mass Close eliminates this need and adds extra internal controls to the closing process.

In this blog I’ll cover the mass close option, as well as share a SQL view that I turned into an Excel Report for easy managing of origin status!

After opening the fiscal period setup window – a user can check the box of the series and the corresponding period that they want to open/close.  This toggles the open/closed status for all transaction types within that series.

 

If we choose “Mass Close” in the bottom right corner, we are then taken to the mass close Fiscal Periods Window – this allows us to choose which origin (transaction type) we want to toggle. This becomes VERY cool when you realize you can close the entire Financial series for January 2017, but open the Bank Deposit entry Origin because you still have those to enter into GP, even though you’ve certainly taken them to the bank 🙂

Fiscal Period - Origin

That is just one example of the power of this feature – comment below to share your ideas on use cases!

One thing about this feature is it can seem a little confusing as to what is actually open and what is closed.

After a LONG time of ‘meaning’ to create a report to help manage this process, I decided today was the day and I am sharing it here.

Disclaimer:  I am NOT a SQL script expert so excuse my ignorance in the format.. I basically know enough to get myself into trouble but it gets the job done! 🙂  This view gets created on your company database.

— Start of Script

create view [Detailed_Period_Control]
as
select

Year1 Fiscal_Year,
Pername as Period,
Perioddt as Period_Start_Date,
Perdendt as Period_End_Date,
case sy40100.Series
when 2 then ‘Financial’
when 3 then ‘Sales’
when 4 then ‘Purchasing’
when 5 then ‘Inventory’
when 6 then ‘Payroll’
when 7 then ‘Project’
else ”
end Series,
ODESCTN as Origin_Name,
case sy40100.closed
when 0 then ‘Open’
When 1 then ‘Closed’
else ”
end Status,
DEX_ROW_ID

from sy40100
where Pername <> ‘Beginning Balance’ and series <> 1 and series <> 0

— Don’t Forget to add permission to DYNGRP

Go

Grant select on Detailed_Period_Control to DYNGRP

— End of Script

The next thing I did was easily create a new smartlist using this view.  Awesome!

Now, I love SmartLists but I REALLY love Excel, so with the click of a button, I published this SmartList to my Excel Refreshable Reports Catalog.  BOOM!  Now I’m ready to get creative and have a tool to quickly look live at my data and see what is open/closed.

Here are some screen shots of my excel file.

 

If you find any issues with the script, let me know – I did some elimination at the view level to keep my excel slicers cleaner.  Post your comments below!

Thanks for reading!

Shawn

 

2 thoughts on “#GPLifeHacks 112: SQL View turned Excel Report for Fiscal Period Closing data”

Leave a Reply