Solved

Add group header section in Data report

Posted on 2004-04-28
7
1,470 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

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…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

772 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