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.Smells like homework.
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.|||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.|||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.|||You should post new questions as new threads.

You need a comma after [TotalAmount]

Use CASE instead of IF within a SELECT statement:sum(dbo.p21_view_ar_receipts_detail.paym ent_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