Report Writer and Calculated Fields

I'm modifying a report on Great Plains (I'm new to it!!).  I need to
add new fields to the report footer.  These fields should refer to
other existing fields in the report footer.  The fields that I need to
refer are related to total amounts.  The total amount is the sum (Field
Type = 'Sum') of the field values.  Now, for the new field I am adding
I need to refer the total amount displayed in the amount field.  For
some reason it is not calculating it right.

To put an example:
In the report footer I have a field named 'Amount1' and its value
should be the total amount for 1.   The way that it is calculated is by
selecting 'Sum' as 'Field Type'.

I need to add a new field called 'NewField1' and its formula is:
 NewField1 = Amount1/TotalAmount

But actually the value I need to use in the formula is not 'Amount1' by
itself, but is the value displayed in the report (the sum of the
values).

I don't have any idea on how to take this value or an approach to get
it.  If someone can help me I would appreciate it a lot!

Thanks in advance!

-mt

0
10/18/2005 9:31:48 PM
greatplains 29623 articles. 6 followers. Follow

7 Replies
787 Views

Similar Articles

[PageSpeed] 7

Maite,

Add a calculated field Amount2 right next to the Amount1 which holds the 
value of Amount1; then add another field to calculate your formulated value 
using Amount2 instead of Amount1.
I hope this will work or send me your report and I will fix it.

"maite" wrote:

> I'm modifying a report on Great Plains (I'm new to it!!).  I need to
> add new fields to the report footer.  These fields should refer to
> other existing fields in the report footer.  The fields that I need to
> refer are related to total amounts.  The total amount is the sum (Field
> Type = 'Sum') of the field values.  Now, for the new field I am adding
> I need to refer the total amount displayed in the amount field.  For
> some reason it is not calculating it right.
> 
> To put an example:
> In the report footer I have a field named 'Amount1' and its value
> should be the total amount for 1.   The way that it is calculated is by
> selecting 'Sum' as 'Field Type'.
> 
> I need to add a new field called 'NewField1' and its formula is:
>  NewField1 = Amount1/TotalAmount
> 
> But actually the value I need to use in the formula is not 'Amount1' by
> itself, but is the value displayed in the report (the sum of the
> values).
> 
> I don't have any idea on how to take this value or an approach to get
> it.  If someone can help me I would appreciate it a lot!
> 
> Thanks in advance!
> 
> -mt
> 
> 
0
Shariq (273)
10/18/2005 10:17:03 PM
Shariq,

Thanks for the suggestions... I tried it but still have the same
problem.  The only way I can get the amount I want in 'Amount2' is if I
select the option of 'Sum' as Field Type so it is still showing the
wrong value.

Anyway, thanks for you help,

-mt

Shariq wrote:
> Maite,
>
> Add a calculated field Amount2 right next to the Amount1 which holds the
> value of Amount1; then add another field to calculate your formulated value
> using Amount2 instead of Amount1.
> I hope this will work or send me your report and I will fix it.
>
> "maite" wrote:
>
> > I'm modifying a report on Great Plains (I'm new to it!!).  I need to
> > add new fields to the report footer.  These fields should refer to
> > other existing fields in the report footer.  The fields that I need to
> > refer are related to total amounts.  The total amount is the sum (Field
> > Type = 'Sum') of the field values.  Now, for the new field I am adding
> > I need to refer the total amount displayed in the amount field.  For
> > some reason it is not calculating it right.
> >
> > To put an example:
> > In the report footer I have a field named 'Amount1' and its value
> > should be the total amount for 1.   The way that it is calculated is by
> > selecting 'Sum' as 'Field Type'.
> >
> > I need to add a new field called 'NewField1' and its formula is:
> >  NewField1 = Amount1/TotalAmount
> >
> > But actually the value I need to use in the formula is not 'Amount1' by
> > itself, but is the value displayed in the report (the sum of the
> > values).
> >
> > I don't have any idea on how to take this value or an approach to get
> > it.  If someone can help me I would appreciate it a lot!
> > 
> > Thanks in advance!
> > 
> > -mt
> > 
> >

0
10/19/2005 12:04:25 AM
If you like; attach your report to this post and I will take a look.


"maite" wrote:

> Shariq,
> 
> Thanks for the suggestions... I tried it but still have the same
> problem.  The only way I can get the amount I want in 'Amount2' is if I
> select the option of 'Sum' as Field Type so it is still showing the
> wrong value.
> 
> Anyway, thanks for you help,
> 
> -mt
> 
> Shariq wrote:
> > Maite,
> >
> > Add a calculated field Amount2 right next to the Amount1 which holds the
> > value of Amount1; then add another field to calculate your formulated value
> > using Amount2 instead of Amount1.
> > I hope this will work or send me your report and I will fix it.
> >
> > "maite" wrote:
> >
> > > I'm modifying a report on Great Plains (I'm new to it!!).  I need to
> > > add new fields to the report footer.  These fields should refer to
> > > other existing fields in the report footer.  The fields that I need to
> > > refer are related to total amounts.  The total amount is the sum (Field
> > > Type = 'Sum') of the field values.  Now, for the new field I am adding
> > > I need to refer the total amount displayed in the amount field.  For
> > > some reason it is not calculating it right.
> > >
> > > To put an example:
> > > In the report footer I have a field named 'Amount1' and its value
> > > should be the total amount for 1.   The way that it is calculated is by
> > > selecting 'Sum' as 'Field Type'.
> > >
> > > I need to add a new field called 'NewField1' and its formula is:
> > >  NewField1 = Amount1/TotalAmount
> > >
> > > But actually the value I need to use in the formula is not 'Amount1' by
> > > itself, but is the value displayed in the report (the sum of the
> > > values).
> > >
> > > I don't have any idea on how to take this value or an approach to get
> > > it.  If someone can help me I would appreciate it a lot!
> > > 
> > > Thanks in advance!
> > > 
> > > -mt
> > > 
> > >
> 
> 
0
Shariq (273)
10/19/2005 2:36:13 PM
Basicly what I'm looking is a way I can read the value displayed in a
field instead of the value is calculated from its formula.  Is there a
way to read this?  Can I specify it inside a calculated field?

Thanks in advance!

-mt

maite wrote:
> Shariq,
>
> Thanks for the suggestions... I tried it but still have the same
> problem.  The only way I can get the amount I want in 'Amount2' is if I
> select the option of 'Sum' as Field Type so it is still showing the
> wrong value.
>
> Anyway, thanks for you help,
>
> -mt
>
> Shariq wrote:
> > Maite,
> >
> > Add a calculated field Amount2 right next to the Amount1 which holds the
> > value of Amount1; then add another field to calculate your formulated value
> > using Amount2 instead of Amount1.
> > I hope this will work or send me your report and I will fix it.
> >
> > "maite" wrote:
> >
> > > I'm modifying a report on Great Plains (I'm new to it!!).  I need to
> > > add new fields to the report footer.  These fields should refer to
> > > other existing fields in the report footer.  The fields that I need to
> > > refer are related to total amounts.  The total amount is the sum (Field
> > > Type = 'Sum') of the field values.  Now, for the new field I am adding
> > > I need to refer the total amount displayed in the amount field.  For
> > > some reason it is not calculating it right.
> > >
> > > To put an example:
> > > In the report footer I have a field named 'Amount1' and its value
> > > should be the total amount for 1.   The way that it is calculated is by
> > > selecting 'Sum' as 'Field Type'.
> > >
> > > I need to add a new field called 'NewField1' and its formula is:
> > >  NewField1 = Amount1/TotalAmount
> > >
> > > But actually the value I need to use in the formula is not 'Amount1' by
> > > itself, but is the value displayed in the report (the sum of the
> > > values).
> > >
> > > I don't have any idea on how to take this value or an approach to get
> > > it.  If someone can help me I would appreciate it a lot!
> > > 
> > > Thanks in advance!
> > > 
> > > -mt
> > > 
> > >

0
10/19/2005 2:38:37 PM
I'm unable to do it because the report is not mine,  I'm only helping
on it... sorry!

0
10/19/2005 2:41:48 PM
Now I am not sure what you are looking for.
Basically, a calculated field uses formulas which uses either a field from 
the database orcalculated fields etc... If you open up the formula, you will 
see the field name which you can use in your calculated field or by itself.

Whats the report name that you are working with?

"maite" wrote:

> Basicly what I'm looking is a way I can read the value displayed in a
> field instead of the value is calculated from its formula.  Is there a
> way to read this?  Can I specify it inside a calculated field?
> 
> Thanks in advance!
> 
> -mt
> 
> maite wrote:
> > Shariq,
> >
> > Thanks for the suggestions... I tried it but still have the same
> > problem.  The only way I can get the amount I want in 'Amount2' is if I
> > select the option of 'Sum' as Field Type so it is still showing the
> > wrong value.
> >
> > Anyway, thanks for you help,
> >
> > -mt
> >
> > Shariq wrote:
> > > Maite,
> > >
> > > Add a calculated field Amount2 right next to the Amount1 which holds the
> > > value of Amount1; then add another field to calculate your formulated value
> > > using Amount2 instead of Amount1.
> > > I hope this will work or send me your report and I will fix it.
> > >
> > > "maite" wrote:
> > >
> > > > I'm modifying a report on Great Plains (I'm new to it!!).  I need to
> > > > add new fields to the report footer.  These fields should refer to
> > > > other existing fields in the report footer.  The fields that I need to
> > > > refer are related to total amounts.  The total amount is the sum (Field
> > > > Type = 'Sum') of the field values.  Now, for the new field I am adding
> > > > I need to refer the total amount displayed in the amount field.  For
> > > > some reason it is not calculating it right.
> > > >
> > > > To put an example:
> > > > In the report footer I have a field named 'Amount1' and its value
> > > > should be the total amount for 1.   The way that it is calculated is by
> > > > selecting 'Sum' as 'Field Type'.
> > > >
> > > > I need to add a new field called 'NewField1' and its formula is:
> > > >  NewField1 = Amount1/TotalAmount
> > > >
> > > > But actually the value I need to use in the formula is not 'Amount1' by
> > > > itself, but is the value displayed in the report (the sum of the
> > > > values).
> > > >
> > > > I don't have any idea on how to take this value or an approach to get
> > > > it.  If someone can help me I would appreciate it a lot!
> > > > 
> > > > Thanks in advance!
> > > > 
> > > > -mt
> > > > 
> > > >
> 
> 
0
Shariq (273)
10/19/2005 4:25:11 PM
I solved it!!

The problem was that for the formula of the calculated field I was
creating was based on other 'Calculated fields'.  I needed to change
those fields with 'Report Fields' (were the ones I was asking for).

Thanks again for your patience... Have a nice day

0
10/19/2005 6:50:30 PM
Reply:

Similar Artilces:

Designing Report Troubles
Hi, We just tried to customize a report with Visual Studio 2003 We followed the instructions from the book Working with Dynamics CRM 3.0 from MS Press. We downloaded the reports, we set up a project,.. ... not made any changes yet .. ... but the report is not able to preview and brings up the following error "No connection to Adventure_works_cycle" even we made a connection to sa .. and the connection was tested successfully. Why is the report drying to use a database that is not installed? (I know what the adeventure works cycle database is :-)) Why is AWC named in the "...

Report Writer Text Spacing Problems While Modifying a PO form
Due to Report Writer static text field size limitations, I am forced to try to use two adjacent text fields to complete a sentence. I am having an inordinate amout of problems trying to get words to align in sentences with the correct amount of character spacing between words that span the text field boundaries. And, the process of toggling between Report Writer and Dyamics to get the Purchase Order form to reprint is time consuming. What am I missing (besides patience)? -- Jay Jay, 1) Setup a calculated field for your static text. Set the result type to String, and use the Constant...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

Report Format
Env: CRM 3, VS 2003, remote connect to CRM server. I've created a QUOTE in VS2003 and when viewed using PREVIEW from within the VS environment it produces a beautiful QUOTE (at least in my opinion). When printed, again from the VS environment, it is perfect. When I upload the RDL file to the CRM server, create an new REPORT using this RDL then produce a REPORT, the formatting is all over the place. When I inspect the source (in VS) there are no fields that extend past the defined size. It appers that the CRM report engine randomly adds CRLFs all thru the report ... ??? ...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Cannot delete report
Hi All, I have install the report manager sometime ago, it is working fine. Until recently we found that when i click the action menu, nothing is come out. I can't delete or rename the report. But I can add new report. Do anyone encouter this before? Thanks. -- Regards, Venedict ...

Log ON Report Manager
Install Report Manger in the Server CRM Satisfactorily, but I have problems when I do log on to Crystal APS, I am using an account of domain administrator and it does not allow me to accesar to the APS. APS User Name: CCSGR3000\crmadmin APS password: ********* APS Server:servcrm I need Help, Thanks. The problem is with the login. Use the following: APS User Name: administrator Password: (should be blank) APS server: localhost (if it's local) I did run into a problem that Report Manager couldn't be accessed from anywhere on the network. It was an IIS configuration problem. &q...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

Payables Trial Balance Report with Options
I am trying to run an A/P Trial Balance Report today that will give me the A/P balance at January 31, 2007. I keep getting the balance as as February 28, 2007. I've run the same report for A/R and am able to get the balance as at January 31, 2007. Currently using GP 9.0. With my previous employer who was also using GP 9.0, I sure that we were able to run an A/P Trial Balance report for a previous period. Does anyone have any suggestions as to what I my be missing or what may be set up incorrectly? -- JR JR, The only date that you can spcify on the Aged Trial Balance With Options, ...

Change of field separator
When I open CSV files in Excel all data is put in one column. Can anyone tell me, where I change my set up, so I get another field separator? Please be specific, because my Excel is a Danish version, and sometimes I have a hard time following the English instructions. Thanks! Jane Hi you specify this in the Windows regional settings. Another idea: - rename your *.csv file to a *.txt file - now open it with excel. The Import wizard should appear and should allow you to specify a different delimiter "Janepige" wrote: > When I open CSV files in Excel all data is put in one co...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

Display Report Header/Footer
Can somebody please give me the telephone number of the smartass MS VP who came up with the bright idea for the Ribbon so that I can call her and have her tell me how to display the Report Header & Footer in a report? Obviously, I must be an idiot because I can't figure out how to do it with this simplied, intuitive, help-ya out m***** fu***** ribbon. Oh stupid me, its under 'ARRANGE'. How idiotic of me to think that it'd under 'DESIGN' since I'm 'designing' a report as opposed to 'arrangeing' a report. "microsoft"...

Crystal Report not found on File Repository Server
I recently upgraded our MSCRM server to a new server. We don't typically use the internal CRM reports, we have some custom ones created with PowerTrack that we primarily use. The CEO tried today though to open one of the canned MSCRM reports and got this error message. I tried the others and I get it on all of them. CrystalReportViewer File frs://Input/a_063/021/000/5439/6ac34043c58b1112.rpt not found on File Repository Server. I checked the input folder in c:\program files\crystal ent 9\filestore\input and it is empty. I would have copied them from the old server, but it is ...

eXtender and Crystal Reports
I've created a Crystal Report to print eOne solutions eXtender fields. They store inaformation in 4 different tables: EXT00101 (String), EXT00102(Numbers), EXT00103(Date),EXT00104(Time).. I have created a View that unions all these tables - and convert them into a Varchar.. Once that is done, I've created a function called GetExtFieldValue where i pass the extender window id and field number and it returns the value for that string. This report was working fine and all of a sudden it became so slow that now it takes 7 minutes to get one record. If I delete all the data from EXT...

Annoying problem with lookup field
On a client, I have a MOSS 2007 portal with two lists which are not wroking as expected. One contains people and the other one contains a lookup field based on data from the first list. When someone which hasn't got administrative rights logs in and tries to see the properties of any element in the second list (on DispForm.aspx), they can't see the lookup field value, and if they try to edit the element, the dropdown control used to choose a person from the first list appears empty. Administrator don't have this problem. The users having the problem have collaboration rig...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

Calculate Subreport totals in a main report Group footer
Hi all Apologies if this has been answered before but I can’t find it. I have a main Report with a Group called “Product_Category” which lists a number of “Products” in the Detail I have a Subreport named “product_costs” which has a record for each date and Text Boxes named “materials” and “fuel” (there are more but I’ll keep it simple). The Subreport sums all costs and has Text Boxes named “summaterials” and “sumfuel” in the footer (all with a height of 0.1cm) The Subreport is embedded in the Detail of the Categories and linked by Product_id In the Detail of the Main Rep...

EDC Reports
Is there a way to gain access to the EDC report(s) in SO Manager without having to configure it for EDC? I need the accounting department and back office mangers to be able to look at and print these reports without them actually having to or needing to do any EDC processing. You could use the Tender Summary report (Reports | Misc | Tender Summary) to get credit card totals by type & register. Marc "Alex" <Alex@no.com> wrote in message news:edLaU8DUHHA.2212@TK2MSFTNGP02.phx.gbl... > Is there a way to gain access to the EDC report(s) in SO Manager without >...

How to change the FROM field in email campaign activity?
How to change the FROM field in email campaign activity? I would like to create an email campaign activity and change the TO field of the email to be abother name instead of mine I would also like to add attachments I can't find a way to do this Any help is very much appreciated Thank you Stella Hi Stella, There are many ways to send marketing emails from CRM: Quick Campaigns Quick Campaigns with mail merge using Microsoft Word E-mail Campaign Activities E-mail Campaign Activities with mail merge using Microsoft Word Direct e-mail using templates Send a workflow e-mail Integrate...

Report Query in RMS
Is there somewhere that I could get some information as to how the reports in RMS are composed. When you look at the various reports they all seem fairly simple in there basic structure. Are the any examples of how these reports are composed. The //--- Columns ---// iformation seems to be the same in most reports, same as the //--- Title Rows ---//. Setting up the flitters and the TablesQueried I need to understand. I have the schema for 1.2. How do I better understand the following or what do I need to learn. I wish I was in college again and had all the time in the world. //--- Repo...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <MikeW@discussions.microsoft.com> wrote in message news:...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

Exporting Daily Z-reports from MRS to Quickbooks
Hello all, Basicly im trying to figure out how to export the daily Z-reports from Microsoft Retail Systems 1.2 to Quickbooks (I believe 2005) in 3 steps or fewer. Ideally, there should be a way to do this without exporting the data to a file, but rather do it directly from MRS after posting the z-report. If anyone can help please let me know if you are using the US version of Quickbooks, the ability to post directly from RMS to QB is built in - you just need to set it up and assign your accounts. Ask your RMS reseller and Accountant/Bookkeeper for help - usually takes about 2 hours t...

Sum of each grouped item in a report
I have a form that logs time spent on auditing activities for the Internal Auditors in my company. Each time the auditor performs an audit related task, they enter a new record under their name. This means they could have many occurences of the same function. I then created a query/report that breaks down the different auditing activities for each internal auditor. Management has now asked for a report that gives a total for each activity for each internal auditor (example - John Smith: Audit Prep - 2.25, Follow-up - 3.75, Audit - 7.50). I want to figure out how to do this on a rep...