This topic is locked

Custom Views with parameters (SQL statement)

6/7/2007 6:26:06 PM
ASPRunnerPro General questions
Alberto author

Sergey,
Is it posible to create a Custom View **? Using a SQL statement like the one below (It uses sum and group by)

This is the query I would like to run and show the results on the screen but they whant to be able to:

  1. Enter the parameters on the screen
  2. Run the query
  3. Show the results with the title [b][color=#48D1CC]showing the parameter they just entered as title.
    Do you think we can do somthing like that?
    I am using this query bellow but I have to hard code the date range acording to the need. Thanks
    Does any one found a solution like this without having to break the code that ASPRUNNER automatically generates? Thanks
    Declare @FromDate as Smalldatetime

    Declare @ToDate as Smalldatetime

    Set @FromDate = '1/1/2007 8:00:00 AM'

    Set @ToDate = '5/1/2007 8:00:00 AM'

    SELECT

    P.NAME AS 'ProductName',

    SUM(OI.EXTD_QTY) AS 'Qty',

    SUM(OI.ONETIME_CHG_SUBTOT) AS 'Amount'

    FROM

    siebeldb.dbo.S_PROD_INT P,

    siebeldb.dbo.S_ORDER O,

    siebeldb.dbo.S_ORDER_ITEM OI,

    siebeldb.dbo.S_ORDER_ITEM_X OIX,

    siebeldb.dbo.S_CONTACT C1,

    siebeldb.dbo.S_SRV_REGN R

    WHERE

    O.ROW_ID = OI.ORDER_ID AND

    OI.ROW_ID = OIX.PAR_ROW_ID AND

    P.ROW_ID = OI.PROD_ID AND

    P.NAME NOT IN ('Refund','On Account') AND

    C1.ROW_ID = O.CONTACT_ID AND

    R.ROW_ID = OIX.ATTRIB_07 AND

    OI.REQ_SHIP_DT >
    AND

    OI.REQ_SHIP_DT < [b][color=#0000FF]@ToDate** AND

    OI.STATUS_CD IN ('In Review','In Audit','Audited','Done','Posted')

    GROUP BY P.NAME ORDER BY P.NAME ASC

S
stealthwifi 6/8/2007

I'm no programer but in looking at what you have I would think creating a page that the client can enter the values they need (make them session variables) then after add (so after they hit enter or whatever) it transfers them to there "custom" page that uses the session varaible for your parameter.
So:
Home Page -> Session Parameter Page -> (after update/add event) -> Custom page using session varaible.
Once again I am no programer and just taking a shot in the dark.
Hope this helps,

Stealth-

Admin 6/8/2007

Not really sure how do you want to integrate this into ASPRunnerPro generated application.

It looks like a separate task.

Alberto author 6/8/2007

Thanks a lot for the ideas. Yes the idea is to use ASPRunner auto generated applications with no manual changes as posible.
Sergey then, is there a way of doing this with ASPRunner?
Say

  1. The user get to the screen to enter the parameters

    From Date: 2007-01-01 To Date: 2007-05-31
  2. After the user click run we need to show the _list.asp page with the regular title plust the date range as subtitle
    Transactions in a date range Range
    From Date: 2007-01-01 To Date: 2007-05-31

    The table has hundred of thousands records.

    Thanks

Admin 6/9/2007

You can use Advanced Search page for this purpose.