Solved

Add group header section in Data report

Posted on 2004-04-28
7
1,461 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
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…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now