DataReport Group Header/Footer 3

[MikeBronner]

How do I set up my SQL query to format the recordset in such a way, that I can use multiple the group header/footer in a datareport.

Currently my SQL query is as follows:

"SELECT Shift, Sorter, SUM(Time) FROM tblMinutes WHERE Date BETWEEN #" & dtpStartDate & "# AND #" & dtpEndDate & "# GROUP BY Shift, Sorter"

I currently can display data on my Data report in such a format:

Report Header
Page Header
Detail
Page Footer
Report Footer

however, I need to add another level of grouping here, and would like to display as follows:

Report Header
Page Header
Group Header
Detail
Group Footer
Page Footer
Report Footer

How can I change the above SQL statement to accomplish this?

Thanks for your help. Take Care,
Mike

 

[Phathi]

In your Data Environment (deMy)

1.Open your command and click the 'Grouping' Tab.
2.Move Fields into 'Fields User For Grouping'.
3. When you apply you will get a new command name such as 'cmmdTime grouped using cmmdTime_Grouping'

In your Data Report (.dtr)
1. In the dtr properties, select the '???_Grouping' as DataMember

2. Right Click and insert Group Header/Footer
3. All the fields you move above in 2, will reside in Group Header section and everything else in Detail
4. Knock yourself out.

Phathi.

 

[MikeBronner]

Here's the problem: I'm not using the DataEnvironment. I'm connecting via ADO connection in the code. If I have to use the DataEnvironment I guess I will.

Otherwise, if possible, is there another way?

Thanks for your input! ;-> Take Care,
Mike

 

[scorpio66]

Yes, you need to use the MSDataShape provider. Here is an example of a report that I've got which does just this:

This is just an extract - it uses an SP to return a recordset

Set objRS = new RecordSet
objRS.Open "SHAPE {GetReportData as Data COMPUTE Data By 'Product'", DBConnection, adOpenDynamic, adLockOptimistic

This allows you to group the data by Product.

In the detail section of the data report set the DataMember of each field to Data (the word after the COMPUTE if the query)

In the group section I think you just leave the DataMember field blank.

I have incredible difficulty getting my head around this - it generally takes me a while to get it right (I have a report with two levels of grouping - now that WAS painful)

Hope this helps.

Chaz

 

[MikeBronner]

Scorpio66, could you email me your form and report? i would like to see how you set it up.

webmaster@emmgee.com

Thanks a bunch! Take Care,
Mike

 

[scorpio66]

Sorry, Michael, I can't - I'm covered by a Non-Disclosure agreement at work, which is why I gave you the snippet that I did rather than a full working example.

:-(

Chaz

 

[scorpio66]

If it can wait, I'll put something together in my own time over the weekend and post it on Monday. I may even do a faq.

Let me know if that will be ok.

Chaz

 

[MikeBronner]

Sure, a FAQ would be great!!! In the mean time I'll try and see how far I can get and post my problems, as they arise.

Thanks for the insight, scorpio. I'd much rather do it in the code than in the DataEnvironment, just like you showed.

Thanks again! Take Care,
Mike

 

[MikeBronner]

Scorpio, is it possible to have multiple computes in one shape command? if so, what is the syntax? thanks! Take Care,
Mike

 

[scorpio66]

Yes, you can, but I must warn you, this is very much the blind leading the blind ...

The syntax that I used is:

SHAPE ( SHAPE {query} as Data COMPUTE Data By 'Value1', 'Value2', 'Value3') as SubQuery1 COMPUTE SubQuery1 By 'Value2', 'Value3'

Please not that the brackets around query are squiggly but the brackets inbetween the two shape commands are normal.

Chaz

 

[briggsy79]

I like the idea of using shapes in other ways but is there any way you can count the amount of records in each group and get the value that is repeated in that group.
Thanks

David

 

[MikeBronner]

Thanks Scorpio!

Briggs: I believe the DataReport has functionfields that will let you cound the number of records in a certain group. I don't know how to count repeating values from within the shape command though.

If all you do is want to avoid repeated values, use the DISTINCT command in the SQL statement. Take Care,
Mike

 

[scorpio66]

This is from memory ...

If you examine the recordset returned from a shape command you'll see that certain fields actually contain other recordsets. You may be able to use RecordCount on those.

Chaz

 

[MikeBronner]

Doign a recordcount would require that you create another recordset that contains just the duplicates though, might not be so easy...
I'm sure there are ways to do it, but it might jsut be easier to to do the following:

- call a SQL statement: "SELECT field FROM table"
- read all recordset elements that you want to compare for duplicates into an array of the size .RecordCount
- when reading into the array, check array for previous duplicates, if no dup, then write into the array, if is dup, then increase dup counter and not write into array.

that would leave you with a "clean" array, as well as the number of dups. Take Care,
Mike

 

[briggsy79]

Thanks,
I will try the arary idea. Sounds fairly easy.


David