Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    3

    Question Unanswered: Report Syntax help !!!!!!!

    Hi,

    I restored a wrong copy of a db, since then I that restored the correct
    version. during the execution of the reports in report services I get an
    error that read *** 'reportServerTempdb.dbo.persitedstream' ** this message
    is sparatic, at time the reports are displayed other time I get that error,
    the db backup was done an 2005, after I restored the latest version of the db
    I reapplied RP services sp2 and I still get the error. Please if this is a
    setting that I am missing or what I screwed up.

    Thanks in advance.
    Last edited by tips54; 05-02-06 at 14:56.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Smells like homework.
    Quote Originally Posted by tips54
    I would a link to a place with reports similar
    to this one I have to do for ideas.Thanks in advance.
    Try www.ask.com.

    If you post the code you have tried, we can help you debug it or recommend a different approach.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2006
    Posts
    3
    Help......
    I restored a wrong copy of a db, since then I that restored the correct
    version. during the execution of the reports in report services I get an
    error that read *** 'reportServerTempdb.dbo.persitedstream' ** this message
    is sparatic, at time the reports are displayed other time I get that error,
    the db backup was done an 2005, after I restored the latest version of the db
    I reapplied RP services sp2 and I still get the error. Please if this is a
    setting that I am missing or what I screwed up.

    Thanks in advance.

  4. #4
    Join Date
    Apr 2006
    Posts
    3

    syntax help

    error on ---> sum(3 * .01)AS CommissionPercent


    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    -- -- RA 4/25/06 - Adding code to calculate base on three commission values. 6% - 5%- 3%, and Target = 5,300,000
    -- If the invoice was received prior to 04/01/2006 calculate base on the 6% anything after that date is multiplied by 3%
    -- unless they reach amt calculate by 5%
    -------------------------------------------------------------------------------------------------------------

    --DROP PROCEDURE dbo.sp_Sales_Commission_for_export
    ALTER PROCEDURE dbo.sp_Sales_Commission_for_export2(@StartDate smalldatetime,
    @EndDate smalldatetime, @SalesRepID int, @TargetAmt int)
    -- TargetAmt is 3,500,000.00
    AS

    -------------------------------------------------------------------------------------------------------------
    -- SELECT ADDED TO KEEP A TALY OF THE AMT PAID WHICH WILL BE USED TO VALIDATE IF SALESREP REACH TARGET AMT STARTING FROM 1/1/06.
    SELECT sum(dbo.p21_view_ar_receipts_detail.payment_amount - (dbo.p21_view_invoice_hdr.freight * (dbo.p21_view_ar_receipts_detail.payment_amount / dbo.p21_view_invoice_hdr.total_amount))) as TotalAmount1 --totaAmount1 is the amt to check against the Targetamt.
    FROM dbo.p21_view_ar_receipts_detail INNER JOIN dbo.p21_view_invoice_hdr ON dbo.p21_view_ar_receipts_detail.invoice_no = dbo.p21_view_invoice_hdr.invoice_no
    WHERE dbo.p21_view_invoice_hdr.invoice_date BETWEEN '01/01/2006' AND '12/30/2006' AND dbo.p21_view_invoice_hdr.salesrep_id = '1001'

    SELECT DISTINCT TOP 100 PERCENT
    dbo.p21_view_ar_receipts_detail.invoice_no AS InvoiceNumber,
    dbo.p21_view_invoice_hdr.customer_id AS CustomerNumber,
    dbo.p21_view_invoice_hdr.bill2_name AS BillToName, dbo.p21_view_invoice_hdr.bill2_address1 AS BillToAddress,
    dbo.p21_view_invoice_hdr.bill2_city AS BillToCity, dbo.p21_view_invoice_hdr.bill2_state AS BillToState,
    dbo.p21_view_invoice_hdr.bill2_postal_code AS BillToZipCode,
    dbo.p21_view_invoice_hdr.ship2_name AS ShipToName, dbo.p21_view_invoice_hdr.ship2_address1 AS ShipToAddress,
    dbo.p21_view_invoice_hdr.ship2_city AS ShipToCity, dbo.p21_view_invoice_hdr.ship2_state AS ShipToState,
    dbo.p21_view_invoice_hdr.ship2_postal_code AS ShipToZipCode,
    dbo.vw_latest_payment_by_invoice.LatestPaymentDate ,
    dbo.p21_view_invoice_hdr.invoice_date AS InvoiceDate,
    sum(dbo.p21_view_ar_receipts_detail.payment_amount - (dbo.p21_view_invoice_hdr.freight * (dbo.p21_view_ar_receipts_detail.payment_amount / dbo.p21_view_invoice_hdr.total_amount))) as [TotalAmount]
    --IF STATEMENT TO SET THE COMMISSION RATE
    IF dbo.p21_view_invoice_hdr.invoice_date BETWEEN '04/01/2006' AND '12/31/2006'
    BEGIN
    sum(3 * .01)AS CommissionPercent
    END
    ELSE IF TotalAmount1 >= @TargetAmt
    BEGIN
    sum(5 * .01) AS CommissionPercent
    END
    RETURN CommissionPercent
    IF (dbo.p21_view_invoice_hdr.invoice_date < '04/01/2006')
    BEGIN
    dbo.CMP_Sales_CommissionPercent.CommissionPercent * .01 AS CommissionPercent
    END
    CAST((CommissionPercent * .01) * (dbo.p21_view_ar_receipts_detail.payment_amount - (dbo.p21_view_invoice_hdr.freight * (dbo.p21_view_ar_receipts_detail.payment_amount / dbo.p21_view_invoice_hdr.total_amount))) AS smallmoney(6, 2)) AS CommissionDollars,
    dbo.p21_view_invoice_hdr.amount_paid AS TotalAmountPaid,
    CAST(dbo.p21_view_invoice_hdr.amount_paid / dbo.p21_view_invoice_hdr.total_amount AS real(2, 2)) AS PercentInvoicePaid

    FROM
    dbo.CMP_Sales_CommissionPercent INNER JOIN
    dbo.customer ON dbo.CMP_Sales_CommissionPercent.class_1id = dbo.customer.class_1id INNER JOIN
    dbo.p21_view_ar_receipts INNER JOIN
    dbo.p21_view_ar_receipts_detail ON dbo.p21_view_ar_receipts_detail.receipt_number = dbo.p21_view_ar_receipts.receipt_number INNER JOIN
    dbo.p21_view_invoice_hdr ON dbo.p21_view_ar_receipts_detail.invoice_no = dbo.p21_view_invoice_hdr.invoice_no ON
    dbo.customer.customer_id = dbo.p21_view_ar_receipts_detail.customer_id LEFT JOIN
    dbo.vw_latest_payment_by_invoice ON dbo.p21_view_ar_receipts_detail.invoice_no = dbo.vw_latest_payment_by_invoice.InvoiceNumber
    WHERE
    (dbo.p21_view_invoice_hdr.salesrep_id LIKE @SalesRepID ) and
    (dbo.p21_view_invoice_hdr.salesrep_id <> '1048') AND
    (dbo.p21_view_invoice_hdr.approved = 'Y') AND
    (dbo.p21_view_invoice_hdr.invoice_class <> 'FINANCE') AND
    (dbo.p21_view_invoice_hdr.invoice_adjustment_type NOT IN ('B', 'T', 'X')) AND
    (dbo.p21_view_invoice_hdr.consolidated <> 'C') AND
    (dbo.p21_view_invoice_hdr.total_amount <> 0) AND
    (dbo.p21_view_ar_receipts.date_received >= @StartDate) AND
    (dbo.p21_view_ar_receipts.date_received <= @EndDate) AND
    (dbo.p21_view_invoice_hdr.amount_paid <> 0) AND
    (dbo.p21_view_invoice_hdr.salesrep_id <> '1007')

    GROUP BY dbo.p21_view_ar_receipts_detail.invoice_no, dbo.p21_view_invoice_hdr.invoice_date, dbo.p21_view_invoice_hdr.bill2_name,
    dbo.p21_view_invoice_hdr.customer_id,dbo.p21_view_ invoice_hdr.bill2_address1, dbo.p21_view_invoice_hdr.bill2_city,
    dbo.p21_view_invoice_hdr.bill2_state, dbo.p21_view_invoice_hdr.bill2_postal_code, dbo.p21_view_invoice_hdr.ship2_name,
    dbo.p21_view_invoice_hdr.ship2_address1, dbo.p21_view_invoice_hdr.ship2_city, dbo.p21_view_invoice_hdr.ship2_state,
    dbo.p21_view_invoice_hdr.ship2_postal_code, dbo.vw_latest_payment_by_invoice.LatestPaymentDate ,
    dbo.CMP_Sales_CommissionPercent.CommissionPercent, dbo.p21_view_ar_receipts_detail.payment_amount,
    dbo.p21_view_invoice_hdr.freight, dbo.p21_view_invoice_hdr.total_amount, dbo.p21_view_invoice_hdr.amount_paid


    ORDER BY
    dbo.p21_view_ar_receipts_detail.invoice_no
    -------------------------------------------------------------------------------------------------------------

    --exec sp_Sales_Commission_for_export @StartDate = '2/1/05', @EndDate = '2/28/05', @SalesRepID = '1001'

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    ---
    I also have a few syntax error.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should post new questions as new threads.

    You need a comma after [TotalAmount]

    Use CASE instead of IF within a SELECT statement:
    Code:
    sum(dbo.p21_view_ar_receipts_detail.payment_amount - (dbo.p21_view_invoice_hdr.freight * (dbo.p21_view_ar_receipts_detail.payment_amount / dbo.p21_view_invoice_hdr.total_amount))) as [TotalAmount],
    --CASE STATEMENT TO SET THE COMMISSION RATE 
    case	when dbo.p21_view_invoice_hdr.invoice_date BETWEEN '04/01/2006' AND '12/31/2006'
    		then sum(3 * .01)
    	else	case	when TotalAmount1 >= @TargetAmt
    			then sum(5 * .01)
    		end
    	end as CommisionPercent
    from	dbo.p21_view_invoice_hdr
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •