Helpful Microsoft Forecaster Scripts

I know I have been quite absent from the blogosphere lately, but I hope that Steve has been keeping everyone entertained (or at least keeping you in deep thought over complex technical issues).  I wish I could say that I was on a boat in the middle of the ocean with not a care in the world, or sitting on a beach somewhere with a frosty beverage in hand.  But, alas, that is not the case.  Sadly, my father passed away last week after an 8-month battle with pancreatic cancer.  Cancer sucks.

But, on to something helpful regarding Forecaster.  When working with a large chart of accounts, it can be helpful to use SQL scripts to compare your line sets with your segment setup, or check to see if every department has a line set assigned as an override.  Here are a few scripts I have used to help with these sorts of reviews.

--Identify the assignable segment values (Setup>>Segments) that do not have an override line set defined (Setup-Budgets-Input Set-Overrides tab), does not include assignable segments defined as summary departments.  Note that depending on the position of your assignable segment in your segment definition, you may need to modify this to use a different M_SEG table (e.g., M_SEG2).


select M_SEG1.ID, M_SEG1.Label from M_SEG1 where M_SEG1.ID not in (Select Z_INP_SEG2.SEG1 from Z_INP_SEG2) and M_SEG1.LN=-1


--Identify accounts in line sets (Build-Lines) that are not set up in the master (Setup-Segments).  This is helpful when you have created line sets by copying and pasting, rather than using the wizard and/or inserting from segments. Note that depending on the position of your main/reversable segment in your segment definition, you may need to modify this to use a different M_SEG table.

select Z_LIN.LINE, Z_LIN.CODE from Z_LIN where Z_LIN.CODE not in (select M_SEG0.ID from M_SEG0)

--Here is the opposite of the script above, this one identifies accounts that are in the account master (Setup-Segments) but doesn't exist in a line set (Build-Lines) to ensure you caught everything.  Same note about the M_SEG table applies here as well.

select M_SEG0.ID, M_SEG0.LABEL from M_SEG0 where M_SEG0.ID not in (select Z_LIN.CODE from Z_LIN)

I am looking forward to getting back in to the swing of things here on the blog, and I hope everyone is enjoying their holiday season!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.