Report - Stored Procedure - Grouping

[ZRexRider]

Hi,

I have an MS-Access ADP connecto SQL Server. I use a parameter driven
stored procedure that is driven by a form that allows the user to come
up with various filters for pulling data. I pass the resulting
recordset to various reports.

Since the stored procedure is generic, it only retrieves a basic data
set. I would like each report to specify grouping and or sorting using
the "Sorting And Grouping" configuration. I was hoping that "Sorting
and Grouping" could be applied to the current RECORDSET of the report.


Am I correct? Should I be able to apply the sorting and grouping to a
recordset that has already been retrieved?

Thanks

 

[Douglas J Steele]

Yes, that's the way it works. The actual sort order of the recordset is
ignored by the report: it only cares about what's specified in the Sorting
and Grouping.

 

[BillCo]

sorting and grouping can indeed be applied to a recordset . However,
the sorting/grouping in a report is seperate to the datasource. if you
want to make this work, you will have to somehow open the report in
design mode in the background and make changes to it at run-time, save
the changes and preview/print the report.
It may be easier to save multiples of the report with different
sorting/grouping options and open whichever is appropriate

 

[ZRexRider]

Thanks - I thought so. Now I know I have something else to figure out
because when I run my report with a SQL command in the "Record Source"
property of the report I get a nice list of groups with my Sum/counts).
However, if I cut and paste the exact same SQL command into VB module,
execute to a record set and pass the results to the report I get 1 page
with one line"

#Name? 1

The field that should have printed (instead of #Name?) is in the SQL
(same SQL as what is embedded in the property) and if I set a
breakpoint in the Report's VBA and print out the
me.Recordset.fields("FieldNameImGroupingOn") it shows that not only
does it exist but it contains data.

If I take grouping out of the scenario altogether and simply create and
pass a recordset the ungrouped data prints.

That's why was thinking that maybe grouping "after the fact" as a no
no.

Thanks for your help

 

[ZRexRider]

Ugh! Sounds right though. My problem is I don't know what the
recordset looks like until the user fills out a "filter" form and calls
my stored procedure. Soooo.... multiple reports won't help but maybe I
can figure out how to "modify on the fly"

Thanks for your help

 

[dbahooker]

are you guys sure that he is talking about a recordSET and not a
recordSOURCE?

 

[ZRexRider]

Thanks - fortunately I was pretty clear about RECORDSET. Gotta go
home and crack some books to figure out what I'm going to do with this
one. ;-)

 

[dbahooker]

I would reccomend staying away from recordset binding; i haven't had
the best stability with that.

there has to be a simple solution.

i mean-- you know that you can pass a filter and a whereclause to the
report via docmd.openReport right??

you sure that won't do the trick for you?
cartesian the crap out of your data so that a simple variable can
filter it back down to the records you want.

please let me know if you go forward with recordsets and it works well.
i just haven't had the best stability with them.

-Aaron

 

[ZRexRider]

Thanks,

If I get the recordset working I'll let you know. Gonna give it to the
end of the day then give up.

Unfortunately I've create a nice dynamic SPROC using COALESCE and CASE.
Turned out nice and is much faster than repeatedly sending SQL to the
DB. I was hoping to use the recordset for the report and just group
it. Once I had that working I had to create 4 or 5 reports that group
or display differently but use the same SPROC.

Believe me though, I've been around long enough to know that if Access
doesn't like something then its a bad thing to force it. I take 3 or 4
backups a day and every now and then I'm damn glad I did!

Thanks

 

[dbahooker]

lol i do that also.

change one thing; hit save. change one thing, hit save.

close out and re-enter.

rinse and repeat.

-Aaron

 

[ZRexRider]

Well here's what I did. Not exactly binding a recordset but it allows
me to use a stored procedure in a report and have the Grouping still
work.

After user enters choices into various filters I call a routine that
builds and EXEC for my SQL sproc. Result ends up looking something
like this:

EXEC usp_ReportDynamic
0,NULL,NULL,'3800000008',NULL,NULL,NULL,NULL,NULL,NULL

This string is passed to my report

DoCmd.OpenReport strReportName, acPreview, , , , strSPROCExec

The Report_Open sub in my report has the following 2 lines:

strSPROCExec = Me.OpenArgs
Me.RecordSource = strSPROCExec

This seems to work fine and allows me to use a dynamic Stored Procedure
to return content for the report with grouping applied.

 

[dbahooker]

fabulous thanks!!!

one of those things that I needed a reminder on

-Aaron