Zero-value Budget Column in FRx SP11

Hello all,

We have just resolved an interesting problem that one of our customers
was encountering after upgrading from FRx SP9 to SP11, that I'd like
to share with you all, along with our resolution.  Bear in mind, I am
not recommending this resolution to you, so much as providing it to
assist you with your own enquiries.


The symptoms of the problem are as follows:

A report contains a two columns, a budget column and an actuals
column.  Running the report on FRx SP9 produces the correct figures.
Running the report on FRx SP11 produces correct actuals, but the
budget figures are predominently zero, or where they are not, they are
incorrect.

The budgets display correctly in Dynamics GP both in Account Rollup
Enquiry, and when the Budget is exported to Excel.


It turned out the problem was caused by the contents of the GL00201
table, which stores budget information by account, by period.  In this
table, the YEAR1 column had a value of 0 for most rows.

It seems that FRx SP11 relies on this field having a value in it,
reflecting which financial year the budget line is for.  SP9, it
appears, did not require this.  The fact that some rows in GL00201 did
have a YEAR1 explains why some budget figures were returned on the
report, but not all.

Our fix was to correct the contents of the YEAR1 field by rebuilding
it from the financial calendar, as follows:

UPDATE BD
SET BD.YEAR1 = PM.YEAR1
FROM
	GL00201 BD
	LEFT OUTER JOIN SY40101 PM
		ON BD.PERIODDT BETWEEN PM.FSTFSCDY AND PM.LSTFSCDY
WHERE
	BD.YEAR1 <> PM.YEAR1

Once the YEAR1 values were correct, running the report on SP11
produced the same results as running on SP9.


What is less clear is why the YEAR1 value was blank in the first
place.  My guess is that this field was added on an upgrade, but no
rebuild was done on pre-existing data.  When new budgets were created
by copying an old budget, the erroneous YEAR1 value was copied with
it.  Of course that could be rubbish - if I find out the actual
answer, I'll let you all know.


I hope this proves useful to someone!

Andrew Cooper
0
Andrew
4/7/2010 11:09:49 AM
greatplains 29623 articles. 6 followers. Follow

1 Replies
1827 Views

Similar Articles

[PageSpeed] 25

Hi Andrew
Thanks for sharing that - you might just be on to something there.   At 
least you've brought it to our attention.
Thanks
Sheila


"Andrew Cooper" wrote:

> Hello all,
> 
> We have just resolved an interesting problem that one of our customers
> was encountering after upgrading from FRx SP9 to SP11, that I'd like
> to share with you all, along with our resolution.  Bear in mind, I am
> not recommending this resolution to you, so much as providing it to
> assist you with your own enquiries.
> 
> 
> The symptoms of the problem are as follows:
> 
> A report contains a two columns, a budget column and an actuals
> column.  Running the report on FRx SP9 produces the correct figures.
> Running the report on FRx SP11 produces correct actuals, but the
> budget figures are predominently zero, or where they are not, they are
> incorrect.
> 
> The budgets display correctly in Dynamics GP both in Account Rollup
> Enquiry, and when the Budget is exported to Excel.
> 
> 
> It turned out the problem was caused by the contents of the GL00201
> table, which stores budget information by account, by period.  In this
> table, the YEAR1 column had a value of 0 for most rows.
> 
> It seems that FRx SP11 relies on this field having a value in it,
> reflecting which financial year the budget line is for.  SP9, it
> appears, did not require this.  The fact that some rows in GL00201 did
> have a YEAR1 explains why some budget figures were returned on the
> report, but not all.
> 
> Our fix was to correct the contents of the YEAR1 field by rebuilding
> it from the financial calendar, as follows:
> 
> UPDATE BD
> SET BD.YEAR1 = PM.YEAR1
> FROM
> 	GL00201 BD
> 	LEFT OUTER JOIN SY40101 PM
> 		ON BD.PERIODDT BETWEEN PM.FSTFSCDY AND PM.LSTFSCDY
> WHERE
> 	BD.YEAR1 <> PM.YEAR1
> 
> Once the YEAR1 values were correct, running the report on SP11
> produced the same results as running on SP9.
> 
> 
> What is less clear is why the YEAR1 value was blank in the first
> place.  My guess is that this field was added on an upgrade, but no
> rebuild was done on pre-existing data.  When new budgets were created
> by copying an old budget, the erroneous YEAR1 value was copied with
> it.  Of course that could be rubbish - if I find out the actual
> answer, I'll let you all know.
> 
> 
> I hope this proves useful to someone!
> 
> Andrew Cooper
> .
> 
0
Utf
4/9/2010 5:00:01 PM
Reply:

Similar Artilces:

take out comma ID, 10000 to ID 100000 in column
I have a long column and I need to take out the commar between the State and the zip code - anyone know how to do this without going into each cell?? Select your data Edit>Replace, Find what is a comma and Replace with nothing Press Replace all -- Kind regards, Niek Otten "Annette" <Annette@discussions.microsoft.com> wrote in message news:BC270C85-9F5F-4A94-BFB9-34327AF45697@microsoft.com... >I have a long column and I need to take out the commar between the State >and > the zip code - anyone know how to do this without going into each cell?? Annette, ...

Help with copying dynamic column selected based on remote cell value and dynamic formula fill
Before posting my nightmare I would like to direct my thanks to all of you who posted answers before. Browsing trough the response I complete maybe 80% of my task in a very neat way. The remaining 20% of the task it seems that will require more than 80% of the time! There are 3 worksheets named S1, S2, and S3. I use a macro to first fill the no value NA() a range sayA1:C700 in S3! And second to open a text file and format data in it (get rid of empty rows via sorting since data in first column is ascending, etc). At the end of this macro there is another worksheet named P1.txt which has 12 co...

Columns Entry
How do I use VBA code to lookup the last entry in a column. Select row 65536 in the column of interest and then control-up. You can record this and then look at the VBA: Range("I65536").Select Selection.End(xlUp).Select --Bruce "aftamath" wrote: > How do I use VBA code to lookup the last entry in a column. ...

Loop through columns and sum them at bottom
Hi, first time poster, so be kind. I've been working on coding a summary sheet that pulls data from worksheets in the active workbook. I'm almost done, but am stumped at writing some code that will loop through the columns that I have copied and total them. I recorded a macro, but nothing happened when I ran it. What I have now is this code snippet that I found which works perfectly when I put my cursor in the active cell. I am not sure how I should write it into my sub. code that I found that works: Set rng1 = ActiveCell.End(xlDown) Set rng2 = rng1.End(xlUp)(2, 1...

copy rows with values
Hi First step is no problem: in Sheet1 col AI this formula is inserted with VB to add age groups =OM(OCH(Age>=0;Age<7);"a. 0-6";OM(OCH(Age>6;Age<16);"b. 7-15";OM(OCH(Age>15;Age<26);"c. 16-25";OM(OCH(Age>25);"d. 26-";"")))) Col is autofilled to last used row. In next step I loop thru these rows to find certain rows and copy them to Sheet2. Problem is that formula on each row is then copied. Instead I need it to be values only! Needs to work in Excel 2003 and 2007. Sheet1 can have >20 000 rows Any s...

overcoming zero values in line graph
I have a 12-month line graph where some of the earliest and most recent data is still zero. Is there a way to have Excel not plot zero values - to avoid the big rises and drop offs they cause - and, rather, just have the graph start and end wherever there is positive data? (NOTE: for multiple reasons, I can't erase the formulae in the cells yielding the zero values, or have them display the N/A message....) Thanks!!! Marika :) There is no reason why you cannot have a 2nd data set that has the N/A values. Plot this second set and use the first for whatever you are using it ...

returning the value in Colunm C using Colunm A as Ref
Hi I am trying to format a spread sheet and need to return the value of colunm C but the problem is Colunm A is blank as it runs in sequence (see below) It looks like the following A B C 1, P100AA Info 300 2, P100AB Info 150 3, "Blank" "" 100 4, "Blank" "" 50 5, P100AC Info 300 How do i return the last figure for the item in Row 2 (colunm A) i.e. 50? If i do a logical test how do ...

Expand and Shrink Rows and columns to hide information.
I have a spreadsheet with rows 1 & 2 and columns numbered 1 & 2. In column 1 I have the ability to click a plus sign (+) to expand information in hidden rows. Along the top I also have the ability to click a plus (+) sign in column 1 to expand hidden columns. It appears that column 2 denotes those rows to be hidden by a "." Ok how do I set this up? Data>Group and Outline Gord Dibben MS Excel MVP On Tue, 5 Jan 2010 07:29:02 -0800, jf1847 <jf1847@discussions.microsoft.com> wrote: >I have a spreadsheet with rows 1 & 2 and columns number...

Recalculate a field when another field's value changes
I have the following fields I'm working with; they are all on the same Form which comes from a query. MultiClassDisc TotalTuitions TotalTuitions is calculated by the sum of (4) other Tuition fields. I have a check box to trigger the calculation of the MultiClassDisc field which works off of the following code. If MultiClass.Value = vbTrue Then MultiClassDisc.Value = TotalTuitions * 0.1 Else MultiClassDisc.Value = 0 End If It works Ok.....when I check the box it calculates MultiClassDisc to be 10% of the TotalTuitions and when I click the box off it chan...

Budget Question- Income disappears after i entered it
I set up the Essential budget by entering my income and expense. When I go back to check the budget it shows that I have 0$ income for both Actual & Budgeted. This happend twice within the same month. Any ideas why this would happen? Thanks! ...

How can I add values from several worksheets in a line chart seri.
I have an Excel document with 12 (monthly) worksheets. I want to make a line chart pulling data in one cell (same cell on each sheet) from each of the twelve sheets. In other words, Jan F2 + Feb F2 + Mar F2, etc. Can I do that on the chart's Source Data menu in the Series/VALUES field? If so, what is the proper language/formula. If not, what are my options? Do I have to make a total page and pull from it? Also, is it possible to make the Category (X) axis label be the Worksheet Name (Jan, Feb, Mar) Lastly, is there a place that gives sample formulas for someone trying to brush...

Cells that "appear" empty plot as zero. Do not want cells to plot.
I read thru some of the posts about plotting empty cells and can't seem to find the right answer to my problem. I have a bar chart (Jan thru Dec) which I currently show data running through June. All cells contain the same formula (therefore none are "really" empty). My formula uses the " " to make the cells for July thru Dec "appear" empty, therefore the graph is plotting these cells as zero. I know that I could use 'NA()' and force a #NA error, but I don't want that error message to appear in my cells. Is there a way to not get the ...

Sorting 7 columns
How do I sort more then 4 columns? I've set up a worksheet that sort columns in a base 1000 number system. I want to use the sheet t organize Ideas that I get for creative writing, so to compile the Idea in chronological order. I need to sort more then 4 columns to make it usable worksheet. How Do I sort more then 4 columns, or get around th problem +------------------------------------------------------------------- |Filename: testtwo.txt |Download: http://www.excelforum.com/attachment.php?postid=3471 +-----------------------------...

When refreshed, some Pivot Table columns retain formatting, some d
I have not had trouble with retaining column widths and column heading formats (font size, word wrap, alignment) in Pivot Tabl. When refreshed, they retain formatting. Here's the weird part, when I add or insert a new data column into the Pivot Table by checking from field list, that new data column does not retain column heading formats (font size, word wrap and alignment settign) like the original columns do. It does however preserver its column width and number formatting. Why is that? How do I tell Excel to preserve column heading formats for newly added ones? I...

Hiding a column of hyperlinks behind a column of numbers
I have two adjacent columns in Excel 2007: one with numbers, one with hyperlinks. I wish to "place" the column of hyperlinks behind the column of numbers, so if one clicks on the "number" the hyperlink will open. I know how to do this to each individual cell one at a time, but the columns have a couple thousand numbers and hyperlinks. Is there a way I can combine or merge the columns all at once to do what I want? Thank you for any response. Are your hyperlinks the result of the =HYPERLINK() function or are they Inserted hyperlinks?? -- Gary''s Stud...

Copying values between worksheets
This seems a simple problem, but I can't find the answer: I am setting up multiple worksheets (one per month); the closing balance on Jan becomes the opening balance on Feb; if, for example, the opening balance is in cell F38 and the closing one in F42, then in cell F38 in Feb I enter =, then click on F42 in the Jan sheet. However copying across to the other sheets results in all sheets referencing Jan!F42. I understand why sheets can't be treated in a relative way, but is there an easy way of copying this without resorting to VBA? Cheers, ABriggs -- ABriggs -----------------...

include string or text value in cell reference
Hi, does anyone know how/if i can include a cell value within a path in a function/formula so i can update it by simply changing cell value, e.g. SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1) so that the 'June' part can change according to a cell value, say F1. So if I change F1 from "June" to "July" then the SUM formula would change to show ....'\stats July' . many thanks in advance, Nelly Use the INDIRECT() function -- Gary''s Student "nelly" wrote: > Hi, > > does anyone know how/if i can include a ce...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

datagridview Combobox column
Hi all, [vb2008] I need help with the following, I have a grid that i have put on there 2 textbox's and a combobox column which works ok. When i then add the data by using the following code below, it works, my problem is with the combobox "YesNo" it put the complete contents of the datatable into the combobox for each row. My question is how can i limit the drop down to 1 yes and 1 no. But at the same time display the correct value according to the datatable? Many Thanks Simon DataGridView1.Columns(0).Visible = False DataGridView1.Columns(1).Visibl...

Find 2 values closest that are higher
Hi Got an equation nightmare on my hands. So i have 3 columns of data A: State Abbreviate B: Benchmark Number C: New Number I am trying to get an equation that in column D will return the 2 closest values in column B for a value in a cell in Column C. So if column C1 is 100 And column D1:D5 is 101, 115, 98, 5,92 It will find values 101 and 115 and put them in column D. The trick being was that i was hoping it would also return the State Abbreviate for this record. So if Fl is 101 it would return Fl: 101 Any ideas on how to do this??? caleb wrote: > Hi > > Got an equation nightm...

Add Value of Cells by Color
How do I sum the values of cells with a specific color?? Hi see: http://www.cpearson.com/excel/colors.htm -- Regards Frank Kabel Frankfurt, Germany B2 wrote: > How do I sum the values of cells with a specific color?? ...

comparing columns
I have a master Excel worksheet, which has six columns:- C:D:E:F:G:H and there are approximately one thousand rows. On a daily basis I import data into Column A. of this master worksheet. The data consists of lots of different 10 digit numbers, so I have a different 10 digit number in each row of Column A., an example of which follows:- A 1234567896 1721626196 3333445556 7768754321 9675332699 The amount of rows that I import also varies daily, it can be as low as five rows of 10 digit numbers imported, or approximately 999 rows of 10 digit numbers imported. The rest of th...

Setting up Simple Budget in money 2003 = Impossible!
Here's my criteria...up until this point I've been doing this by hand in a basic ledger book, and the only part that really sucks is the Deposit Entry. I was hoping MONEY 2003 would be the solution to all my problems by AUTOMATING this process, but alas.... I must be a financial retard because I just can't get this thing to work the way I'd like it to. My Scenario is quite simple...all I want money to do is track a simple budget for me. Example: I have four monthly bills: Telephone Cable Mortgage Electricity ON average, my MONTHLY expenses for these categories are: Telephon...

When I click on a column, that column and one to its right is sele
For some reason, on my first spreadsheet, when I click on a column, the one to its right is also selected. It doesn;t happen on all the columns. What did I do to cause that? Thank you for any assistance. I bet you merged some cells. Select those two columns and then Rightclick on any of the selected cells Format cells|Alignment tab Look for merged cells. You'll see that it's a mixture of merged cells (either a green square or a grey checkbox -- depends on the version). You can remove the merged cells by clearing that checkbox. But you may want to look to see if you removed som...

From Row to Column?
Hello all, I have a large spreadsheet of data, unfortunately when I set it up originally I put the data into rows (I.E. left to right) I'm trying to correct that now, Is there any way I can highlight a row of data and move it into a colum, in the same order it is in the rows? Thank you, Confused Man Yep, select all the cells in your row, and copy then select the first cell in your destination column and select "Paste Special" then transpose in the Paste Special dialog and ... job done!! "confused man" <bfbj@iup.edu> wrote in message news:9cadnVInq98Y6ZjcRVn-u...