Data Report Grouping

[ZOR]

Can grouping be done on a datareport being driven by SQL, and datareport form controls being unbound. I have tried creating a row header, and putting the particular group header control in, but it throws it out. I have a With loop for the detail section1, and I tried putting another loop outside that for the Row header (Group), but it fell over, nearly like me at the moment.

 

[ZOR]

Adding a bit more. Is it possible to have a datareport with grouping levels if there is no dataenvironment installed, ie report from SQL. I cannot even find how to insert a Group heading section with just a straight line in, it reports "Report sections do not match data source", and thats just a graphical line put in, so what chance groping in the dark do I have of recognisiing data to put in the group row heading. My code so far is

StrSQL = "SELECT * FROM Episode "

Set Rs = MyConn.Execute(StrSQL)

With DataReport1
.DataMember = vbNullString
Set .DataSource = Rs
.Caption = "Title of Report"

With .Sections("Section1&quot.Controls
.Item("DD&quot.DataField = Rs.Fields(5).Name
.Item("DE&quot.DataField = Rs.Fields(6).Name
.Item("DF&quot.DataField = Rs.Fields(7).Name
End With

.Show

End With

It looks simple enough if VB was logical, to include ("Section2/3&quot whatever and stick controls there, but as usual the whole thing is designed to waste another day or two. If anyone has any ideas I would be greatful. Kept away from Dataenvironment as problems passing variables through.
Can I load a datareport from a listbox??, I could manage to fill that the way I want things. Thanks

 

[PetMel]

It is possible to do, but not simple.
What you need to do is create a hierarchical recordset using the SHAPE-APPEND command.
To enable this, first change the connection-string for SQL server to include;
Provider=MSDataShape.1;Data Provider=SQLOLEDB
Change the Query string to include the hierarchy as described in

http://support.microsoft.com/default.aspx?scid=kb;en-us;194516.

You also have to change the databindings to set the DataMember property of the items in the detail section to the child recordset. (As described here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;190411)

Hope this helps.

 

[ZOR]

Thanks PetMel, you are right, it is complicated. I will have to rethink things, maybee resorting to a data environment. I originally started that way but then had problems putting in SQL lines. When I found I could do things without a dataenvironment using SQL I thought my problems were solved. However I have come unstuck with grouping, -

Plumbing
Jack Pipe
Harry Burst

Lighting
Jack Bulb
Ken Lamp

etc.

Actually I have been going round in circles for a while. The ouput is wanted on something someone can read prior to printing. I tried putting results on Notepad, but that was unliked. The datareport looks the best if only I can get somewhere. Thought I was near, but now find I'm not. Thanks for your input.