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

Report Subscription sends wrong version

I have a report that's scheduled to run once a month on a timed subscription (using a shared schedule). The Report Delivery Options are set to include the report as a PDF embedded within the email notification. The report has also been set to record a snapshot each time it is run.

The first month, the report was embedded as planned.

The second month, the wrong report was sent. The PDF that was generated in the prior month was resent, not a new instance of a PDF based on the current data. The emailed link to the report on the server worked fine, and the history shows that the snapshot was recorded at the appropriate time?

Wa happen? Why would it embed the prior/old instance of the report?Hi there, I'm trying to recreate this problem so I can try and figure it out but I need a little bit more info.

When you say you have set it to record a snapshot every time the report is run do you mean that it records a snapshot on the same schedule as the subscription?|||Specifically, on the History Properties of the report, I have the following options selected:

Allow history to be created manually.
Store all report execution snapshots in history.
Use the following schedule to add snapshots to report history
Shared Schedule: MonthEndProcessing-At 2:00 AM on day(s) 1 of every month starting 6/3/2005.
Keep an unlimited number of snapshots in report history.

On the Execution Properties, I have the following options selected:

Render this report from an execution snapshot
Use the following schedule to create execution snapshots:
Shared Schedule: MonthEndProcessing-At 2:00 AM on day(s) 1 of every month starting 6/3/2005.
Report Execution Timeout: Use default setting

On the Subscription Processing options:
Delivered by: Report Server E-Mail
Include Report Render Format: Acrobat (PDF) file
Run the subscription:
On a shared schedule: MonthEndProcessing-At 2:00 AM on day(s) 1 of every month|||I've not had time to try and test this theory but I believe it may be somethig to do with the time it takes to process the report.

I think what may be happening is that when the schedule kicks in and starts to generate the report the subscription doesn't wait for the report to be completed because of the time it takes to generate it so it sends the last generated report.

It was probably fine for the first report because by default when you create a snapshot schedule it automatically creates a snapshot when you apply the settings but when it was run the next time it sent the first report while the second was being generated.

If this is the problem you should be able to fix it by creating a schedule that runs before the subscriptions to generate the report so it is ready when the subscription schedule kicks in.|||

This is the behavior I am experiencing with large reports, and it is getting me burned!

The "Render the report with most recent data" switch does nothing in this case, as in our environment all reports run with most recent data and snapshots are not useful.

So for all my monthly and weelky reports I have to schedule a snapshot (with a limit history of 1) a fixed time (10minutes?, 1 hour?) on the report history page before building the subscription separately to send them?

If Render most recent is on, the sub should wait for the report to finish, before sending.

THIS IS A BUG. No matter how it's sliced.

Report Subscription sends wrong version

I have a report that's scheduled to run once a month on a timed subscription (using a shared schedule). The Report Delivery Options are set to include the report as a PDF embedded within the email notification. The report has also been set to record a snapshot each time it is run.

The first month, the report was embedded as planned.

The second month, the wrong report was sent. The PDF that was generated in the prior month was resent, not a new instance of a PDF based on the current data. The emailed link to the report on the server worked fine, and the history shows that the snapshot was recorded at the appropriate time?

Wa happen? Why would it embed the prior/old instance of the report?Hi there, I'm trying to recreate this problem so I can try and figure it out but I need a little bit more info.

When you say you have set it to record a snapshot every time the report is run do you mean that it records a snapshot on the same schedule as the subscription?|||Specifically, on the History Properties of the report, I have the following options selected:

Allow history to be created manually.
Store all report execution snapshots in history.
Use the following schedule to add snapshots to report history
Shared Schedule: MonthEndProcessing-At 2:00 AM on day(s) 1 of every month starting 6/3/2005.
Keep an unlimited number of snapshots in report history.

On the Execution Properties, I have the following options selected:

Render this report from an execution snapshot
Use the following schedule to create execution snapshots:
Shared Schedule: MonthEndProcessing-At 2:00 AM on day(s) 1 of every month starting 6/3/2005.
Report Execution Timeout: Use default setting

On the Subscription Processing options:
Delivered by: Report Server E-Mail
Include Report Render Format: Acrobat (PDF) file
Run the subscription:
On a shared schedule: MonthEndProcessing-At 2:00 AM on day(s) 1 of every month|||I've not had time to try and test this theory but I believe it may be somethig to do with the time it takes to process the report.

I think what may be happening is that when the schedule kicks in and starts to generate the report the subscription doesn't wait for the report to be completed because of the time it takes to generate it so it sends the last generated report.

It was probably fine for the first report because by default when you create a snapshot schedule it automatically creates a snapshot when you apply the settings but when it was run the next time it sent the first report while the second was being generated.

If this is the problem you should be able to fix it by creating a schedule that runs before the subscriptions to generate the report so it is ready when the subscription schedule kicks in.|||

This is the behavior I am experiencing with large reports, and it is getting me burned!

The "Render the report with most recent data" switch does nothing in this case, as in our environment all reports run with most recent data and snapshots are not useful.

So for all my monthly and weelky reports I have to schedule a snapshot (with a limit history of 1) a fixed time (10minutes?, 1 hour?) on the report history page before building the subscription separately to send them?

If Render most recent is on, the sub should wait for the report to finish, before sending.

THIS IS A BUG. No matter how it's sliced.

report services

hi all I'm in the Microsoft Visual Studio creating a report and I noticed I cant bring a function over, am I doing something wrong. I wanted to make a report based on a function. its a table valued function

ALTER FUNCTION [dbo].[Beginning_EndingFinals](@.StartFINALSUITDONE datetime,
@.EndFINALSUITDONE datetime)
RETURNS TABLE
AS
RETURN ( SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE BETWEEN @.StartFINALSUITDONE AND @.EndFINALSUITDONE) )why not make it a sproc?

I don't know if that matters or not|||Are you attempting to execute the ALTER FUNCTION code within text of the dataset?

Or are you making the call to the function within the text (ie, SELECT * from dbo.MyFunction(MyParameter)).

You might consider setting up a stored procedure instead (just set the command type in the Data Set to StoredProcedure).

Regards,

hmscott|||You might consider setting up a stored procedure instead (just set the command type in the Data Set to StoredProcedure).

Brilliant!|||this is the message I'm getting inside the Sql Server business intelligence studio|||this is the message I'm getting inside the Sql Server business intelligence studio

Ummmmm..what message|||Ummmmm..what message

My Bad, hope this helps