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 rendered via webservice not executing from cache

I set a report to 'Cache a temporary copy of the report. Expire copy of
the report on a Shared schedule'
I generate the report using the webservice function Render. All the
settings seem to be set properly, the cache options setting (using
GetCacheOptions) returns True, and the ExpirationDateTime is correct.
So how come when the report is run, it brings up a fresh rendition of
the report (according to the Globals!ExecutionTime variable)?
Could it be something I am not setting correctly when I render the
report? The only thing I am sending are the parameters (which by the
way I am forced to get directly from the database, since RS does not
retrieve second or third level parameters)
Please help!Hello Dear Hella
i need a feavour
i am Working on SQL Reporting Services since one month
i have a problem in generating report across the domains
My client database server is not configured to public domain
i am unable to access the report
So i thought of Using Web service to render the report
currently i am using Report viewer Control
You mentioned in your query that You are using web service
so can u help me in this regard
like what is the design to follow
and i need some code
waiting for your reply
have a good day
Anil Kumar
(anilkumarv@.infotechsw.com)
Hella wrote:
> I set a report to 'Cache a temporary copy of the report. Expire copy of
> the report on a Shared schedule'
> I generate the report using the webservice function Render. All the
> settings seem to be set properly, the cache options setting (using
> GetCacheOptions) returns True, and the ExpirationDateTime is correct.
> So how come when the report is run, it brings up a fresh rendition of
> the report (according to the Globals!ExecutionTime variable)?
> Could it be something I am not setting correctly when I render the
> report? The only thing I am sending are the parameters (which by the
> way I am forced to get directly from the database, since RS does not
> retrieve second or third level parameters)
> Please help!|||Anil,
Here is the code I am using:
Const REPORT_NAME = "/Reporting/Activity Types Detail"
Const FOLDER_PATH = "\\FileServer\Activity Types Detail\"
Dim strConn As String = "Integrated
Security=SSPI;server=servername;database=dbname"
Dim oConn As SqlConnection = New SqlConnection(strConn)
Dim oDataRead As SqlDataReader
Dim forRendering As Boolean = True
Dim historyID As String = Nothing
Dim values As ReportService.ParameterValue() = Nothing
Dim credentials As ReportService.DataSourceCredentials() =Nothing
Dim parameters As ReportService.ReportParameter() = Nothing
Dim catItems As ReportService.CatalogItem() = Nothing
Dim rs As New ReportService.ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.Timeout = 36000000 '10 hours
catItems = rs.ListChildren(REPORT_NAME, True)
For Each cItem As ReportService.CatalogItem In catItems
If cItem.Type.ToString.ToLower = "report" Then
Dim report As String = cItem.Path.ToString
WriteLog("Processing report: " & report, True)
If Mid(cItem.Name.ToString, 1, 7) = "Monthly" Then
Try
'gets only 1st parameter values
parameters = rs.GetReportParameters(report,
historyID, forRendering, values, credentials)
If Not (parameters Is Nothing) Then
For Each ParamValue As
ReportService.ValidValue In parameters(0).ValidValues
'txtProgressLog.Text =txtProgressLog.Text & vbCrLf & "Parameter 1: " & parameters(0).Name & "
- " & ParamValue.Value
WriteLog("Parameter 1: " &
parameters(0).Name & " - " & ParamValue.Value, False)
'get second level parameters
Dim strCommand As String = "SELECT
DISTINCT T0.objid AS EmpObjid, T0.first_name + ' ' + T0.last_name AS
EmpName FROM dbo.table_employee T0 INNER JOIN dbo.table_act_entry T1 ON
T0.objid = T1.act_entry2user WHERE (DATEPART(yy, T1.entry_time) = 2005)
AND (T1.entry_time < LTRIM(STR(DATEPART(mm, GETDATE()))) + '/1/' +
LTRIM(STR(DATEPART(yy, GETDATE())))) AND T0.x_department = '" &
ParamValue.Value & "'"
Dim oComm As SqlCommand = New
SqlCommand(strCommand, oConn)
oConn.Open()
oDataRead = oComm.ExecuteReader
'Render reports using parameter
combination
Do Until oDataRead.Read = False
'txtProgressLog.Text =txtProgressLog.Text & vbCrLf & "Parameter 2: " & parameters(1).Name & "
- " & oDataRead(0).ToString
WriteLog("Parameter 2: " &
parameters(1).Name & " - " & oDataRead(0).ToString & " - " &
parameters(2).Name & " - " & oDataRead(1).ToString, False)
'build parameter names and
values
Dim param(2) As
ReportService.ParameterValue
param(0) = New
ReportService.ParameterValue
param(1) = New
ReportService.ParameterValue
param(2) = New
ReportService.ParameterValue
param(0).Name =parameters(0).Name
param(0).Value =ParamValue.Value
param(1).Name =parameters(1).Name
param(1).Value = oDataRead(0)
param(2).Name =parameters(2).Name
param(2).Value = oDataRead(1)
Dim result As Byte() = Nothing
Dim iColon As Int16 = 0
Dim sFileName As String =cItem.Name.ToString & " - " & ParamValue.Value & " - " &
oDataRead(1).ToString & " - " & oDataRead(0).ToString
'remove colon from department name
iColon = InStr(sFileName, ":")
If iColon > 0 Then
sFileName = Mid(sFileName,
1, iColon - 1) & Mid(sFileName, iColon + 1)
End If
rs.SessionHeaderValue = New
ReportService.SessionHeader
result = rs.Render(report,
"HTML4.0", Nothing, Nothing, param, Nothing, Nothing, Nothing, Nothing,
Nothing, Nothing, Nothing)
WriteLog("Execution date and
time: " & rs.SessionHeaderValue.ExecutionDateTime & vbTab & "Is new
execution: " & rs.SessionHeaderValue.IsNewExecution.ToString, False)
Console.WriteLine(("Expiration
date and time: " & rs.SessionHeaderValue.ExpirationDateTime.ToString))
Dim ExpDef As
ReportService.ExpirationDefinition
Console.WriteLine(("Cache
Options: " & rs.GetCacheOptions(report, ExpDef).ToString))
If
rs.SessionHeaderValue.IsNewExecution = True Then
Dim stream As FileStream =File.Create(FOLDER_PATH & sFileName & ".html", result.Length)
stream.Write(result, 0,
result.Length)
stream.Close()
End If
'Process.Start(FOLDER_PATH &
sFileName & ".html")
Loop
oComm = Nothing
oConn.Close()
Next ParamValue
End If
Catch exc As SoapException
Console.WriteLine(exc.Detail.InnerXml.ToString())
WriteLog(exc.Detail.InnerXml.ToString(),
False)
Exit Sub
End Try
End If
End If
Next
Hope it helps.

Report Recovery Help ASAP!

Report A has been deployed and works fine and ties out in production. The
design copy of this report in VS has been corrupted. All other backups of
this report have been corrupted. How do I get the rdl. for Report A fixed?In report manager click on the report, then properties tab. Click on Edit
link. Give it a directory on your local PC. Different than where you project
is. Then in your project add an exisiting item to the project. It will copy
it over to the directory with all your other rdl files.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:DFA101E4-26E6-4D14-BD30-05ECE46A2DF1@.microsoft.com...
> Report A has been deployed and works fine and ties out in production. The
> design copy of this report in VS has been corrupted. All other backups of
> this report have been corrupted. How do I get the rdl. for Report A
> fixed?