Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report - Stored Procedure - Grouping

Discussion in 'Information Technology' started by ZRexRider, Jul 28, 2006.

  1. ZRexRider

    ZRexRider
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <jerryg@ptd.net> wrote in message
    news:1149081774.297133.29210@g10g2000cwb.googlegroups.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
    >
     
  4. BillCo

    BillCo
    Expand Collapse
    Guest

    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
    > 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. ZRexRider

    ZRexRider
    Expand Collapse
    Guest

    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


    Douglas J Steele wrote:
    > 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" <jerryg@ptd.net> wrote in message
    > news:1149081774.297133.29210@g10g2000cwb.googlegroups.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
    > >
     
  6. ZRexRider

    ZRexRider
    Expand Collapse
    Guest

    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
    > > 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
     
  7. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    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
     
  8. ZRexRider

    ZRexRider
    Expand Collapse
    Guest

    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
     
  9. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    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. ;-)
    >
    >
    > 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
     
  10. ZRexRider

    ZRexRider
    Expand Collapse
    Guest

    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@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
    > 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
     
  11. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    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 wrote:
    > 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@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
    > > 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
     
  12. ZRexRider

    ZRexRider
    Expand Collapse
    Guest

    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@hotmail.com wrote:
    > 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 wrote:
    > > 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@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
    > > > 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
     
  13. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    fabulous thanks!!!

    one of those things that I needed a reminder on

    -Aaron





    ZRexRider wrote:
    > 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@hotmail.com wrote:
    > > 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 wrote:
    > > > 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@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
    > > > > 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
     

Share This Page