Solved

Add group header section in Data report

Posted on 2004-04-28
7
1,474 Views
Last Modified: 2010-04-29
Hi guys,
       I'm trying to create report by using data report without data environment.  I created reports before but they're like tabular reports which involve only Detail Section.  Now, I try to add a group header section to show data as a group.  I don't know if I did this wrong since start.  So, I'd really appreciate if anyone can explain me how to do this.

Let's say that below is my table

Date      Order     Cost
-------------------------
xxx          1          10
xxx          2          15
xxx          3          10
yyy          1          12
yyy          2          10

I want to show Date in group header section, Order in detail section, and TotalCost in group footer section.  What I did was I had these records stored in Recordset then set datamember and datafield as I did for tabular reports.  It didn't work.  It showed me a message error 'Reports sections do not match data source'.  So, my main questions are
1. How can I refer group header section or even add higher level to the report to my data?  
2. How can I add function to the report at the end of each section?  

Below is how the report should look like

========================
Date                          Order      Cost
========================
xxx          
                                   1          10
                                   2          15
                                   3          10
-----------------------------------------
                       Sub-Total          35
------------------------------------------
yyy
                                   1          12
                                   2          10
-----------------------------------------
                       Sub-Total          22
=======================
                              Total          57
=======================


Thank you so much in advance for any suggestion.
0
Comment
Question by:prapon77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 7

Expert Comment

by:donpricejr
ID: 10965442
Are you using Access?

-Don
0
 

Author Comment

by:prapon77
ID: 10970545
Yes.  I'm using Access as a database at this point.  I plan to move it to SQL Server eventually, but I guess there shouldn't be lots of differrences, should there?
0
 
LVL 7

Expert Comment

by:donpricejr
ID: 10970618
There are many differences between doing it Access and SQL Server, but that is for a different topic. I will approach this from the standpoint of Access.

1.) Open a new report.
2.) Right-click on the report (while in "Design View") and select "Sorting and  Grouping"
3.) Select your field which contains the date.
4.) Next, put "Yes" for "Group Header" and "Group Footer"

From there you will be able to set it up appropriately.

-Don
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:donpricejr
ID: 10970619
0
 
LVL 7

Accepted Solution

by:
donpricejr earned 125 total points
ID: 10970622
This is from MS' help topic:
----------------------------------------------
Open the report in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
Click Sorting And Grouping  on the toolbar to display the Sorting And Grouping box.
Do one or more of the following:
Change sorting or grouping order

In the Sorting And Grouping box, click the row selector (row selector: A small box or bar that, when clicked, selects an entire row in table or macro Design view, or when you sort and group records in report Design view.) of the field or expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) you want to move.
Click the selector again, and drag the row to a new location in the list.
Note  If the groups you're rearranging have headers (group header: Used to place information, such as group name or group total, at the beginning of a group of records.) or footers (group footer: Used to place information, such as group name or group total, at the end of a group of records.), Microsoft Access moves the headers, footers, and all the controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) in them to their new positions. However, you must adjust the locations of the controls in the headers and footers yourself.

Insert a sorting or grouping level

In the Sorting And Grouping box, click the row selector (row selector: A small box or bar that, when clicked, selects an entire row in table or macro Design view, or when you sort and group records in report Design view.) of the row above which you want to insert the new field or expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), and then press INSERT.
In the Field/Expression column of the blank row, select the field you want to sort on, or type an expression.
When you fill in the Field/Expression column, Microsoft Access sets the Sort Order to Ascending. To change the sort order, select Descending from the Sort Order list.

Delete a sorting or grouping level

In the Sorting And Grouping box, click the row selector (row selector: A small box or bar that, when clicked, selects an entire row in table or macro Design view, or when you sort and group records in report Design view.) of the field or expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) you want to delete, and then press DELETE.
Click Yes to delete the sorting or grouping level, or click No to keep it.
Note  If the field or expression you delete has a header or footer, Microsoft Access deletes the header or footer and its controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.).

Modify the inherited sort order in a report

Click Properties  on the toolbar to display the report's property sheet.
Click the OrderBy property box, and then specify the field and sort order you want to sort on.
To sort records in one field in ascending order, type the field name enclosed in brackets ([ ]) followed by ASC.
[fieldname1] ASC

To sort records in one field in descending order, type the field name enclosed in brackets ([ ]) followed by DESC.
[fieldname1] DESC

To sort records in more than one field in ascending or descending order, type the setting this way:
[fieldname1] ASC,[fieldname2] DESC

Note  If you don't specify ASC or DESC after field names, Microsoft Access sorts in ascending order, the first listed field first, then the next listed field, and so on.
---------------------------------------------
-Don
0
 

Author Comment

by:prapon77
ID: 10973623
Don,
       I think it was my huge mistake that I didn't say that I'm using VB6 with Access and planned to change it to VB6 with SQL Server finally.  I felt so bad that you had to write up lot of information and I couldn't use it that much.  
       Anyway, I really aprreciated your intention, I'll accept your answer.  At least I think I can use it in the future.  If you happen to know this problem regarding VB6 and Access, please help me out.
       I found something that this problem has to do with SAHPE command, but the thing is I never used that SHAPE command before.  If you know any place I can look it up.  I'll really appreciate that.  Again, sorry for my mistake and hope you can forgive me.  Thanks for all helps.
Paul
0
 
LVL 7

Expert Comment

by:donpricejr
ID: 10974070
Paul,

Thank you for your concern, but I am not upset. ;-) I am not familiar with the "Shape" command--in fact, MSDN did not have anything on it that I could find either. I have come across tools in the past that migrate all of your MS Access objects and code to a VB6 environment, but unfortunately I am not able to find any right now. I will continue to look for you and if I find anything I will let you know. Sorry that I could not be of more assistance.

-Don
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question