Hi all, Can anybody say how to get the total on grouped field and also grand total for the same in the report. Best regards, Hari
Hi Hari, the simple way is to do the following: 1) Select a key to sort that contains the grouped field in the dataitem properties (DataItemTableView). 2) enter the grouped field name into the GroupTotalFields property 3) On the view → sections add a GroupFooter element for the dataitem. 4) Place an instance of the grouped field onto the GroupFooter just like you would a normal field. 5) Add a Footer element for the dataitem. 6) Place an instance of the grouped field onto the Footer, similar to step 4. Now when you run the report, the GroupFooter will display the grouped total and the Footer will display the grand total. I tried to find a suitable example in Cronus database but was unable to! I also just realised that if you use the report wizard to create a new report, it may be able to talk you through adding a Grouped total field. Regards, Edward. EXTRA NOTES ----------- If you are grouping multiple fields at once, you have to insert the following line of code into the GroupFooter PreSection trigger: CurrReport.SHOWOUTPUT := (CurrReport.TOTALSCAUSEDBY = “TableName”.FIELDNO(“GroupedField”)); This will mean that the section will only be outputted if the GroupFooter was triggered by the field that you are interested in.
I missed something important out! Step 2.5: Add the fields that you want to maintain totals for into the TotalFields property. Step 4 & 6 should read “Place an instance of the field to be totalled” Regards, Edward.
I will try and get back with the result. Best Regards, Hari
Hi, I just tried the way you told but i could not able to get the result. The problem behind this is the data Type of the groupedfield is code, so it is not possible to add the field called “qualification” into the Totalfields property.Let me give you a clear picture, I want to get the total for the groupedfield “Qualification”.Say for eg.,if the “Qualification” is BE it should count no of BE from the table. Best Regards, Hari
Hi Edward, Is it possible to get the result with the codings? If so please help me. Best Regards, Hari
Hi Hari, the instructions I gave out are confusing but in your case, it would have been no use anyways. To create a sample report from which you can work out whats going on do the following: 1) Open Object Designer (Object designer form opens) 2) Click on “reports” button (Object designer filters only reports) 3) Select “New” (Report wizard opens) 4) In “table” field, select or type in “customer” 5) From the options, choose “Create report using wizard” and highlight “tablular type report”. 6) Click “next” (Part 2 of wizard opens) 7) Use the “>” button to select a few fields (I used “No.” “Name” & “Address”) 8) Press “Next” (part 3 of wizard opens) 9) Select "Yes I want to sort. 10) From the available keys, select “Customer Posting Group” 11) Press “Next” (part 4 of wizard opens) 12) Select “Yes I want to group my data”. Make sure that "Customer posting group is highlighted in blue. 13) Select “Next”. (part 5 of wizard opens, last part) 14) For style of report, select “List style”. 15) Click on “Finish” button. (The wizard closes and the dataitem view of your report is displayed) 16) Select File->Run (Ctrl+R), preview your report. The completed report should show customers grouped by posting group. To modify the report so that it will count the number of customers per posting group, do the following: 1) Select View->Globals (C/AL globals window opens) 2) Create a new global variable called “Counter” of type “Integer” 3) Close the C/AL Globals window. 4) Select View->Sections (Section Designer opens) 5) Highlight the GroupHeader with the field “Customer Posting Group” underneath. 6) Press F9 (GroupHeader C/AL editor opens) 7) In the PreSection trigger type in Counter := 0; 8) Press Esc to return to the section designer. 9) Highlight the body section and press F9 (Body C/AL editor opens) 10) In the PreSection trigger type in Counter += 1; 11) Press Esc to return to the section designer. 12) From the toolbar, place a new field on the GroupFooter section. 13) Click on the new field and select View->Properties (Shift+F4) 14) Set the SourceExpr of the new field to read “Counter”. Close and save your report. Hopefully when you run the report the groupfooter will display the counter value. To sum it up, what you need to do is to is to set Counter to 0 (Counter := 0) every time you get a groupheader, and increment counter (Counter += 1) every time you fetch a new record. Sorry that the instructions are detailed to the point of being patronising, I didnt know how else to explain without screenshots to assist! Regards, Edward.
Hi Edward, It’s great.Thanks for your help.Now i am getting the count. Best regards. Hari.
Hi, Also write in Pre Data Item as CurrReport.CreateTotals(Field name); Try
Hi rakesh, Let me give a try and get back to you. Thanks Best Regards, Hari.
Hi Rajesh, The data type of the field “Qualification code” is code.The data type for the passing field to the function createTotals should be a decimal.So,this is not possible in this case. Regards, Hari.
Hi All, In addtion to the above queries, how to get category wise count.For eg, M.C.A,B.E,M.B.A falls under the category “Master/Proff”. Iam getting the total for qualification code(No.of M.C.A,B.E,…)and grandtotal as well. Best Regards, Hari.