Report - Stored Procedure - Grouping

#1 (**permalink**) 28-Jul-2006, 08:14 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10584-report-stored-procedure-grouping.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
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

#2 (**permalink**) 28-Jul-2006, 08:14 AM

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"ZRexRider" wrote in message
news:1149081774.297133.29210@g10g2000cwb.googlegro ups.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> and Grouping" could be applied to the current RECORDSET of the report.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
>
>
> Am I correct? Should I be able to apply the sorting and grouping to a
> recordset that has already been retrieved?
>
> Thanks
>

#3 (**permalink**) 28-Jul-2006, 08:14 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
It may be easier to save multiples of the report with different
sorting/grouping options and open whichever is appropriate

ZRexRider wrote:
> Hi,
>
> I have an MS-Access ADP connecto SQL Server. I use a parameter driven
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> 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

#4 (**permalink**) 28-Jul-2006, 08:14 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
(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

Douglas J Steele wrote:
> Yes, that's the way it works. The actual sort order of the recordset is
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> ignored by the report: it only cares about what's specified in the Sorting
> and Grouping.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "ZRexRider" wrote in message
> news:1149081774.297133.29210@g10g2000cwb.googlegro ups.com...
> > 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
> >

#5 (**permalink**) 28-Jul-2006, 08:14 AM

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

BillCo wrote:
> 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 wrote:
> > Hi,
> >
> > I have an MS-Access ADP connecto SQL Server. I use a parameter driven
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> > stored procedure that is driven by a form that allows the user to come
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> > 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

#6 (**permalink**) 28-Jul-2006, 08:14 AM

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

ZRexRider wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> 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

#7 (**permalink**) 28-Jul-2006, 08:14 AM

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@hotmail.com wrote:
> are you guys sure that he is talking about a recordSET and not a
> recordSOURCE?
>
>
>
>
> ZRexRider wrote:
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> > 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

#8 (**permalink**) 28-Jul-2006, 08:14 AM

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 wrote:
> 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. ;-)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
>
>
> dbahooker@hotmail.com wrote:
> > are you guys sure that he is talking about a recordSET and not a
> > recordSOURCE?
> >
> >
> >
> >
> > ZRexRider wrote:
> > > 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.
> > >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> > > 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

#9 (**permalink**) 28-Jul-2006, 08:14 AM

Thanks,

If I get the recordset working I'll let you know. Gonna give it to the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
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@hotmail.com wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10584
> 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 wrote:
> > 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@hotmail.com wrote:
> > > are you guys sure that he is talking about a recordSET and not a
> > > recordSOURCE?
> > >
> > >
> > >
> > >
> > > ZRexRider wrote:
> > > > 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