SQL SQLExpress: report

Showing posts with label report. Show all posts

Thursday, March 22, 2012

on subreport

Hi
I have a main Report MR - i added a subreport to it SR1 - and it works fine
but when i try to add another subreport SR2 - it gives me an error
ERROR: subreport could not be shown. It only hapens with this particular rdl.
The subreport thing works for other rdls. the supreport SR2 works fine as
standalone.
Pls helpgot it - i was not passing the parameters
"RP" wrote:
> Hi
> I have a main Report MR - i added a subreport to it SR1 - and it works fine
> but when i try to add another subreport SR2 - it gives me an error
> ERROR: subreport could not be shown. It only hapens with this particular rdl.
> The subreport thing works for other rdls. the supreport SR2 works fine as
> standalone.
> Pls helpsql

Embed PDF inside of SQL Server Report Servics Report

Hi ...

I posted this same question on the ASP.NET forum and haven't received an answer, so I thought I'ld move the discussion over to this forum. My question has been read several times, so I know I'm not the only one facing this problem, but nobody seems to know how to make it happen.

Anyhow, I'm building a system where we collect a great deal of meta data inside of SQL Server. The meta data is associated wtih files that the users can attach - image files and PDFs. I can get the image files to display properly, however I can find no way to actually display the contents of the PDF file as part of the report.

We want to print out the meta data as a header to the PDF and then print the PDF in it's entirety. As an example, we would print out:

Emergency Plan Name: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Facility Name: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Last Review Date: 99/99/9999 Reviewed By: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

HERE IS WHERE I WOULD WANT TO PRINT OUT THE ASSOCIATED PDF's --

-- Emergency Plan --

-- Evacuation Plan 1 --

-- Evacuation Plan 2 --

I can print the file names - they are fields in the database, but how do I embed the actual PDF in to the report? They can print on different pages - I don't care about that, but I need to get them to be a part of the report.

Thanks ...

David

Any day above ground is a good day!

It's not possible to embed a PDF in a RS report.

If they're single page PDF's then you could try and code something clever that outputs them as images and embed the images.

Other than that I don't see a way of doing this.

|||Ok, so there is no built in functionality to handle embedding and outputting PDF's within reports built via SSRS. Has anyone seen any Custom Report Items that have been built that would allow me to have PDF's print in-line as part of the report?

Thanks ...

David
Any day above ground is a good day!|||

Beyond PDF embedding - where is a good site to review Custom Report Items? Either for purchase or open source?

Thanks ...

David

Any day above ground is a good day!

sql

Tuesday, March 20, 2012

:( Please Help Needed on Printing Report in RS

Hi All Experts,
I caught in a big problem, previosly our application was using Crystal
Report, but on my suggestion Project Manager changed the reporting tool
for next client and we are decided to use RS and removed CR from our
application.
There are 50 Reports, we designed it well and we dont find any
problems. But out of those reports, two reports are autoprint reports.
The scenario is, i want to print a report which accepts parameter
without user interaction (there might be any number of pages) at server
side (This is a Major Feature of our application). I used Printer
Deliver Sample, but it is time or data driven , but i want to do this
job through my code, means at certain line of code i have to print the
report. Also Delivery Extension uses static parameter, how do i change
it runtime? I also used RsClientprint but it shows printer dialog which
i dont want to show.
Please help me, all eyes in my team are looking towards me like i did a
wrong decision. I am very thankful to all if i receive the solution.
Thank You
Regards
Rajone thing i forgot, i want to print this report in Printed Stationary
with duplicate on same page, means i have to set right, left, top and
bottom margin, i used ReportRender Method, in which i converted report
into EMF and send the stream to printer but i am not able to set
margins, also i am not getting report print what i designed|||Raj,
The "Print a report from a console app" download from this site might
be of help to you. http://www.gotreportviewer.com/
Andy Pottersql

Monday, March 19, 2012

.rss methods no longer supported

I am having a problem running a sql2k report service script on my new sql2005 server. It seems that the methods SessionHeader() and rs.render are no longer suport. can someone help me figure out what I need to do to continue automating my report running?

rss file contents: (the parts that should matter)

Report Parameters
Dim branch as string = nothing

Dim skipreport as string = nothing
dim skip as integer = 0

dim allbranchreport as string = nothing
dim allbranch as integer = 0

dim report as CatalogItem
dim repname as string = nothing
dim specificreport as string = nothing
dim filepath as string = nothing
dim errorpath as string = nothing
dim basefilepath as string = nothing
dim baseerrorpath as string = nothing
dim errorlog as integer = 0
Dim parameters() As ParameterValue = nothing
Dim paramcount as integer = 0

' Render arguments
Dim result As Byte() = Nothing
Dim reportPath As String = nothing
'Dim format As String = "PDF"
Dim format as string = "EXCEL"
Dim historyID As String = Nothing
Dim devInfo as string = Nothing
Dim credentials As DataSourceCredentials() = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings As Warning() = Nothing
Dim reportHistoryParameters As ParameterValue() = Nothing
Dim streamIDs As String() = Nothing
Dim sh As New SessionHeader()
rs.SessionHeaderValue = sh
Dim omitdocmap as string = "True"
'**************************************

'**************************************
'Repset run
Dim repset as string = ""
for each repset in repsetlist
specificreports = specificreportsstart
if specificreports = 0 then
select case repset.tolower
case "all"
specificreports = 0
case "brkctr"
specificreports = 1
reportcount = reportset_brkctr.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_brkctr(reportloop)
next
case "cashmgmt"
specificreports = 1
reportcount = reportset_cashmgmt.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_cashmgmt(reportloop)
next
case "com"
specificreports = 1
reportcount = reportset_com.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_com(reportloop)
next
case "com_vw"
specificreports = 1
reportcount = reportset_com_vw.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_com_vw(reportloop)
next
repset = "com"
case "comcons"
specificreports = 1
reportcount = reportset_comcons.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_comcons(reportloop)
next
case "cons"
specificreports = 1
reportcount = reportset_cons.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_cons(reportloop)
next
case "cons_vw"
specificreports = 1
reportcount = reportset_cons_vw.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_cons_vw(reportloop)
next
repset = "cons"
case "dep"
specificreports = 1
reportcount = reportset_dep.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_dep(reportloop)
next
case "staff"
specificreports = 1
reportcount = reportset_staff.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_staff(reportloop)
next
omitdocmapbranch = "False"
case "staff2"
specificreports = 1
reportcount = reportset_staff2.getupperbound(0)
redim specificreportslist(reportcount)
for reportloop = 0 to reportcount
specificreportslist(reportloop) = reportset_staff2(reportloop)
next
omitdocmapbranch = "False"
case else
specificreports = 1
redim specificreportslist(0)
specificreportslist(0) = "Undefined"
end select

if repset.tolower = "staff2" then repset = "staff"
End if

'Base values
basefilepath = basepath & client & "\" & repset & "\"
baseerrorpath = basepath & client & "\" & "error_" & repset & "_"

'Reports List
Dim reports() as CatalogItem
reports = rs.ListChildren(basereportpath, False)

'Execution
if datasourceok = 1 then
for each report in reports

skip = 0
allbranch = allbranchrun

for each skipreport in skipreports
if report.name.tolower = skipreport.tolower then skip = 1
next

if skip = 0 then
if specificreports = 1 then
skip = 1
for each specificreport in specificreportslist
if report.name.tolower = specificreport.tolower then skip = 0
next
end if
end if

if skip = 0 then
for each allbranchreport in allbranchreports
if report.name.tolower = allbranchreport.tolower then allbranch = 1
next

Redim parameters(1)
parameters(0) = New ParameterValue()
parameters(0).name = "branchall"
parameters(1) = New ParameterValue()
parameters(1).Name = "branch"

'5 parameters reports
repname = report.name
if (repname.substring(0,5).tolower = "loans" or repname.substring(0,4).tolower = "locs") then paramcount = 4
if (repname.length >= 13) then
if repname.substring(0,13).tolower = "loans_beacons" then paramcount = 1
end if

if paramcount = 4 then
paramcount = 0
redim preserve parameters(4)

parameters(2) = New ParameterValue()
parameters(2).name = "comconsall"
if repset.tolower = "cons" or repset.tolower = "com" then parameters(2).value = 0 else parameters(2).value = 1

parameters(3) = New ParameterValue()
parameters(3).Name = "comcons"
if repset.tolower = "com" then parameters(3).value = "commercial" else parameters(3).value = "consumer"

parameters(4) = New ParameterValue()
parameters(4).Name = "brokered"
parameters(4).value = 0
if repset.substring(0,3).tolower = "brk" then parameters(4).value = 1
end if

'All Branches Reports
if allbranch = 1 then

parameters(0).value = "1"
parameters(1).value = "admin"
if client = "CSCU" then parameters(1).value = "newwst"
if clienttype = "vw" then parameters(1).value = "1"

errorlog = 0
filepath = basefilepath & report.name & ".xls"
errorpath = baseerrorpath & report.name & ".txt"
reportpath = basereportpath & "/" & report.name
omitdocmap = omitdocmapall
devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"

Console.WriteLine (repset.toupper & " " & report.name & " All branches")

Try
result = rs.Render(reportPath, format, historyID, devInfo, parameters, _
credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Catch e As SoapException
errorlog = 1
Console.WriteLine(e.Detail.OuterXml)
Catch f as Exception
errorlog = 1
Console.writeline(f.message)
End Try

' Create an error log
If errorlog <> 0 then
Try
Dim stream As FileStream = File.Create(errorpath, 1024)
Console.WriteLine("Errorlog created: " & errorpath)
stream.Close()
Catch g As Exception
Console.WriteLine(g.Message)
End Try
End if

' Write the contents of the report to a file.
If errorlog <> 1 then
Try
Dim stream As FileStream = File.Create(filepath, result.Length)
stream.Write(result, 0, result.Length)
Console.WriteLine("Result written to file: " & filepath)
stream.Close()

Catch e As Exception
Console.WriteLine(e.Message)
End Try
end if

'Other Reports
else
parameters(0).value = "0"

for each branch in branches
parameters(1).value = branch

errorlog = 0
if branch <> "h/o" then
filepath = basefilepath & branch & "\" & report.name & ".xls"
else
filepath = basefilepath & "ho\" & report.name & ".xls"
end if
if branch <> "h/o" then
errorpath = baseerrorpath & branch & "_" & report.name & ".txt"
else
errorpath = baseerrorpath & "ho_" & report.name & ".txt"
end if
reportpath = basereportpath & "/" & report.name
omitdocmap = omitdocmapbranch
devinfo = "<DeviceInfo><OmitDocumentMap>" & omitdocmap & "</OmitDocumentMap></DeviceInfo>"

Console.WriteLine (repset.toupper & " " & report.name & " " & "Branch: " & branch)

Try
result = rs.Render(reportPath, format, historyID, devInfo, parameters, _
credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
Catch e As SoapException
errorlog = 1
Console.WriteLine(e.Detail.OuterXml)
Catch f as Exception
errorlog = 1
Console.writeline(f.message)
End Try

Catch e As Exception
Console.WriteLine(e.Message)
End Try
end if

next
end if

end if
next
end if
next

I get this error message from cmd when I try and run my batch:

J:\PRA Publisher>rs -i pra_publisher.rss -s http://localhost/reportserver -l 0

The specified script failed to compile with the following errors:
J:\PRA Publisher> "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\vbc.exe" /t:exe
/main:MainModule /utf8output /R:"System.dll" /R:"System.Xml.dll" /R:"System.Web
.Services.dll" /R:"C:\Program Files\Microsoft SQL Server\90\Tools\binn\rs.exe" /
out:"C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.exe" /debug-
"C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.0.vb" "C:\Docu
ments and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb"

C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(253) : error
BC30002: Type 'SessionHeader' is not defined.

Dim sh As New SessionHeader()
~~~~~~~~~~~~~
C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(254) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

rs.SessionHeaderValue = sh
~~~~~~~~~~~~~~~~~~~~~
C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(434) : error
BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005
.ReportingService2005'.

result = rs.Render(reportPath, format, historyID, de
vInfo, parameters, _
~~~~~~~~~

C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(436) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

sh.SessionId = rs.SessionHeaderValue.SessionId
~~~~~~~~~~~~~~~~~~~~~
C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(496) : error
BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005
.ReportingService2005'.

result = rs.Render(reportPath, format, historyID
, devInfo, parameters, _
~~~~~~~~~

C:\Documents and Settings\netop\Local Settings\Temp\1\bfw7qn9k.1.vb(498) : error
BC30456: 'SessionHeaderValue' is not a member of 'Microsoft.SqlServer.Reporting
Services2005.ReportingService2005'.

sh.SessionId = rs.SessionHeaderValue.SessionId
~~~~~~~~~~~~~~~~~~~~~

Any advice on how to fix this would be graetly appreciated. Thanks!

The version of rs.exe that ships with SQL 2005 is configured to run against the new 2005 management SOAP endpoint by default. The old 2000 endpoint is still supported. Just add -e mgmt2000 to the command line to use the old endpoint. You can get more information on this option by running rs.exe /? or in the MSDN help files.|||

Thank you so much!!!

That seems to be working beautifully. Just started the batch and its running smoothly so far!

|||

Hi,

my opinion is that this is not a solution, only a workaround. How would a script look like, which is using the ReportingServices2005 endpoint and methods.

I could use the endpoint by this

Dim rs As New ReportingService2005()

But now the debugger tells me that the render method is not a member of Microsoft.SqlServer.ReportingServices2005.ReportingServices2005 .

I want to render a report to the file system. This is the whole script:

' Export report to pdf format to the specified path under filePath and to the file which is specified under fileN
Dim format as string = "PDF"
Dim filePath as String = "D:\temp\"
Dim fileN as String = "reportx"
'Dim Datum as String = CString(Date)
Dim fileName as String = filePath + fileN + ".pdf"
Dim reportPath as String = "/reportx/reportx

Public Sub Main()

' Prepare Render arguments
Dim historyID as string = Nothing
Dim deviceInfo as string = Nothing
Dim showHide as string = Nothing
Dim results() as Byte
Dim encoding as string
Dim mimeType as string = "application/pdf"
Dim warnings() AS Warning = Nothing
Dim reportHistoryParameters() As ParameterValue = Nothing
Dim streamIDs() as string = Nothing
'Windows Credentials were send to Reporting server service
Dim rs As New ReportingService2005()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials

' Report Parameters
Dim parameters(0) As ParameterValue
results = rs.Render(reportPath, format, _
Nothing, Nothing, Nothing, _
Nothing, Nothing, encoding, mimeType, _
reportHistoryParameters, warnings, streamIDs)

' Open a file stream and write out the report
Dim stream As FileStream = File.OpenWrite(fileName)
stream.Write(results, 0, results.Length)
stream.Close()

End Sub

'End of script