Solved

Add group header section in Data report

Posted on 2004-04-28
7
1,451 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

13 Experts available now in Live!

Get 1:1 Help Now