Calling SQL Rreporting Services

Hi guys,
I have reports that are pre-configured on a remote MS Reporting Server
(MSRS 2005). There is also a subscription that a group of subscribers
subscribes to so they can get the reports in question. I know that I
can schedule a report/group of reports to be delivered @. a certain
time, but what I really want to do is initiate the delivery (email in
this case) from an outside application. So my question is:
Is there any API call that I can use to do that (via RS WebService
perhaps?) and what is the [web]method that I need to call to do so?
Any help is greatly appreciated...
Thanks,
G.Hi,
Ys can be done. On SSRS help just search for
"ReportingService2005.CreateDataDrivenSubscription Method"
you can see some example written in c# as well. good one. I think this will
be suitable for your requirement.
Amarnath
"vajarov" wrote:
> Hi guys,
> I have reports that are pre-configured on a remote MS Reporting Server
> (MSRS 2005). There is also a subscription that a group of subscribers
> subscribes to so they can get the reports in question. I know that I
> can schedule a report/group of reports to be delivered @. a certain
> time, but what I really want to do is initiate the delivery (email in
> this case) from an outside application. So my question is:
> Is there any API call that I can use to do that (via RS WebService
> perhaps?) and what is the [web]method that I need to call to do so?
> Any help is greatly appreciated...
> Thanks,
> G.
>|||Thanks a lot! This is what I needed.
G.|||Here is the actual link :
http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx

Calling reports from C#

I am sure there is a way to call reports from C# code and store them as a snapshot on the SQL Reporting Server. Can anybody refer me to an article or website on how this is done?

Thanks for the information.

Have you tried using a reportviewer component? I'm not exactly sure what you're asking for.

http://www.microsoft.com/downloads/details.aspx?familyid=F38F7037-B0D1-47A3-8063-66AF555D13D9&displaylang=en

|||

I know what you are saying, but we have a batch process that uses stored procedures that will calculate aggregates (we are not using OLAP right now). This is a process that will probably take overnight to complete. We are calling this process from code and on completion of the process, we would like the report to be automatically generated and stored as a snapshot. We need all of the pre-processing to happen during this batch process. We don't want the report to be generating the aggregates. A couple of these reports will be about 1,000 pages long. They are yearly summary reports.

The UI will have a button to Queue the batch process and store it in a queue with other batch processes. I just need to figure out how to have the code call the report and store it as a snapshot when the process is complete.

Thanks for the information.

|||

So you want the snapshot funtionality that report manager uses to be run from your code.

http://technet.microsoft.com/en-us/library/ms159217.aspx

Calling Reporting Services from SSIS

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

bhalchandra.kunte wrote:

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

What do you mean by "call Reporting Services"?

RS is a web service that you can call from anythig that supports calling web services. including SSIS.

-Jamie

|||

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

|||

bhalchandra.kunte wrote:

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

There are no tasks within SSIS to do anything with Reporting Services. However, RS has an API which you can call from SSIS using the Web Service Task or (I suspect) a Script Task.

It would help if you could be more specific about what you want to do.

-Jamie

|||

Thanks

I have already tried that but not managed to get it done.

I will try again.

Thanks again.

|||did anyone sucessfully run the .rdl file from ssis|||

What do you mean by "run an .rdl file". What exactly do you want to do?

-Jamie

|||i want to run MS Reporting Services file from SSIS Task and subscribe the reportfile.rdl.data file to a local or network folder.|||

I got working, I scheduled a report on report server and in turn it creates a job on sql agent and from ssis I used execute job task and subscribed report file to a folder. i have email task to send the report as an attachment.

Thanks for the help.

|||I managed to generate a SSRS report directly from a script task within SSIS :

i set varSSRS_URL, varSSRS_LOGIN, varSSRS_PASSWORD & varSSRS_DOMAIN in the DTSCONFIG file

varSSRS_URL should be the first part of your SSRS Server URL : http://localhost/ReportServer

varSSRS_LOGIN/varSSRS_PASSWORD/varSSRS_DOMAIN : A windows user log/pass allowed to generate reports on the server (used for authentification)

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub|||I got an error on 'Save File'. It said declaration expected. I maybe missing some import statement that you have.|||here is my import list :

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime|||Well I have the exact same thing. Did you have to declare SaveFile?|||please post your full class and FULL error message, it will be easier to help you|||

I am using what you had posted:

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub

As soon as I paste it, I get an error on SaveFile, it says, "declaration expected."

sql

Calling Reporting Services from SSIS

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

bhalchandra.kunte wrote:

Hi

I have created a packages which pull and push the data to SAP server.

I want to create a report every day and send that report to the manager.

For the same i want to call reporting services in my SSIS package.

I know i can write a SQL script and export the report in excel but i want to use Reporting services.

Have any one call reporting services from ssis.

What do you mean by "call Reporting Services"?

RS is a web service that you can call from anythig that supports calling web services. including SSIS.

-Jamie

|||

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

|||

bhalchandra.kunte wrote:

In SSIS we can call/work on analysis within SSIS package

by using tools which are provided in toolbox...

--ToolBox -- Control Flow Items -- Analysis Services Execute DDL Task -- Provides ability to process DDL query statement against Analysis Services.

--ToolBox -- Control Flow Items--Analysis Services Processing Task -- Provides ability to process objects like cubes

Can we work / call Reporting Services within SSIS package?

There are no tasks within SSIS to do anything with Reporting Services. However, RS has an API which you can call from SSIS using the Web Service Task or (I suspect) a Script Task.

It would help if you could be more specific about what you want to do.

-Jamie

|||

Thanks

I have already tried that but not managed to get it done.

I will try again.

Thanks again.

|||did anyone sucessfully run the .rdl file from ssis|||

What do you mean by "run an .rdl file". What exactly do you want to do?

-Jamie

|||i want to run MS Reporting Services file from SSIS Task and subscribe the reportfile.rdl.data file to a local or network folder.|||

I got working, I scheduled a report on report server and in turn it creates a job on sql agent and from ssis I used execute job task and subscribed report file to a folder. i have email task to send the report as an attachment.

Thanks for the help.

|||I managed to generate a SSRS report directly from a script task within SSIS :

i set varSSRS_URL, varSSRS_LOGIN, varSSRS_PASSWORD & varSSRS_DOMAIN in the DTSCONFIG file

varSSRS_URL should be the first part of your SSRS Server URL : http://localhost/ReportServer

varSSRS_LOGIN/varSSRS_PASSWORD/varSSRS_DOMAIN : A windows user log/pass allowed to generate reports on the server (used for authentification)

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub|||I got an error on 'Save File'. It said declaration expected. I maybe missing some import statement that you have.|||here is my import list :

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime|||Well I have the exact same thing. Did you have to declare SaveFile?|||please post your full class and FULL error message, it will be easier to help you|||

I am using what you had posted:

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=EXCEL", outpath + "FILENAME.xls")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub

As soon as I paste it, I get an error on SaveFile, it says, "declaration expected."

Calling non-static methods in dll

Hello,

I have a dll which is developed in C#. I have made a reference to it from Reporting Services under Report Properties.

My problem is, that the methods in this dll are not static. Therefore under the Classes in Report Properties I have written the class-name and the instance-names of the instances that I need to use from this class (as I understand it, this is what you have to do if using non-static methods). In a text-box, I call one of the instances in the class, but I get this error:

[rsInvalidName] 'MyMethod()' is not a valid code class name. Names of objects must be CLS-compliant identifiers.

What exactly have I done wrong here?

Thanks
/Peter

Hi Peter,

In the code window, you'll need something similar to:

Dim InstanceName As Instance

Then you should be able to set the value of your textbox to: =Code.InstanceName.FunctionName(Params).

Keep in mind that all of these lines are case-sensitive.

If this isn't working, could you post a snippet of what you're doing, so we can take a further look at it?

-Jessica

|||

Hi Jessica. Thanks for your reply :-)

I have a class (let's call it myClass) and a method in that class (let's call it myMethod).

The Class is declared as: public class myClass and the method is declared as: public string myMethod(params).

The resulting dll (let's call it myDLL) is added to the references-tab of Report Properties. In the Code-tab I have written (I have tried many combinations): Public Dim class1 As myDLL.myClass. I have used the Public-keyword because it complained that class1 was declared private. In the textbox-controller I have written: =code.class1.myMethod(params). But it doesn't work. The text-box writes: #Error and I get the warning:

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The Value expression for the textbox 'textbox5' contains an error: Object reference not set to an instance of an object.

So it appears that class1 is not an instance of myDLL.myClass. I have tried many combinations and also with and without adding "Class name" and "Instance name" in the References tab of Report Properties.

Any suggestions ?

Thanks

/Peter

|||

Hi again,

I works now. I removed the code in the Code-tab and added the class to the References-tab under "Class name" and "Instance name". I then wrote: =code.InstanceName.MethodName(param) in the text-box and it works. I didn't have the "code" part to begin with, so that was my problem.

Thanks for your input. I appreciate it :-)

/Peter

Calling all SQL and Informix experts!

Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.

SQL is an obvious choice because we have it already for our retail system.

However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.

We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.

What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.

The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.

What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.

We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.

I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.

With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link.....Hmmmm!

DTS is very robust. Using an activex script transformation you can pretty much order and amend the data as you see fit, including the decision of which transacts to load or just skip over because you've already loaded it.

Howevere it's generally better to limit what you pull in. I am geussing your transactions are timestamped in some way?. You can adjust the the SQL in the transform task dynamically using a parameter whihc reads its value out of a global variable.

You can set the global variable dynamically using a piece of script. This will limit the data pulled through and should prevent you needing to figure out whihc data is duplicated and which isn't.

You could run the DTS multiple times, once for each store using a different parameter to restrict your data selection to just the current store.

If a store fails to import or returns 0 records to import you could always re-run that store later either manually or programtically.

Timothy Peterson has an excellent book out about DTS programming whihc covers this stuff in a far more approcahable manner than the Books Online docs.

Originally posted by FunkyD
Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.

SQL is an obvious choice because we have it already for our retail system.

However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.

We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.

What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.

The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.

What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.

We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.

I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.

With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link.....

Calling a web service from RS

Is it possible to call a web service in a reporting services report?
I was thinking along the lines of somehow adding a web reference like you
would for a normal project and then using it in the Code part of the report.
Cheers, Dune.You might be able to create a custom assembly that calls a web service.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Dune" <Dune@.discussions.microsoft.com> wrote in message
news:19CE2430-1EA4-4669-B346-A31230030F9F@.microsoft.com...
> Is it possible to call a web service in a reporting services report?
> I was thinking along the lines of somehow adding a web reference like you
> would for a normal project and then using it in the Code part of the
> report.
> Cheers, Dune.

Calling a Reporting Services report from a stored procedure

Hello and thank you for any assistance that you can provide.
I am looking for a way to simply print out a Reporting Services report
from a stored procedure with zero user interaction.
I have very little knowledge of advanced stored procedure usage other
than T-SQL commands but I imagine it cant be very hard to do.
Thanks again for any help you got.On Feb 20, 6:17 pm, derrick.cha...@.cox.net wrote:
> Hello and thank you for any assistance that you can provide.
> I am looking for a way to simply print out a Reporting Services report
> from a stored procedure with zero user interaction.
> I have very little knowledge of advanced stored procedure usage other
> than T-SQL commands but I imagine it cant be very hard to do.
> Thanks again for any help you got.
I'm a little fuzzy in this area; but you should be able to use: exec
xp_cmdshell "rs.exe -i LocationOfRDLFile -s ServerURL -v
VariablesAndValues" inside a stored procedure and then have a way to
print it afterwards. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Call to Webservice from a Reporting Action in SSAS

Is it possible to call a webserive from a reporting action in SSAS?

What I am trying to do is create a reporting action that will schedule a report to run at certain time by calling the CreateSubscription Web service of Reporting services.

Any advice would be much appreciated. Does anyone have any suggestions or perhaps another angle that I could approach this problem?

Thanks

You can't use a report action since the report action uses URL addressibility. Instead consider a regular action that invokes a web page, e.g. ASP.NET page. The page can call down to the Report Server.

Another approach could be to use a report action which calls a dummy report that invokes some custom code. The report can forward the parameter values while the custom code can invoke the API.

|||

Thank you for your reply. I wanted to try and schedule this as a report to run off peak hours as it was a large report. But after testing the subscription based delivery method, I have encountered memory problems which lead me to believe that SSRS may not be the way to go. I am trying to generate a report that is trying to return approx. 500 Mb of data. When the report gets triggered the memory usage of ReportingServicesService.exe climbs until it reaches approximately 14 Gigs (I have a machine with 20 Gigs of memory). After the memory usage reaches 12 Gigs the report service stops servicing any new requests which makes sense since MemoryLimit is set to 60%. This is where the problem begins. It seems that after the report has finished executing it does not want to release this memory and no new requests can be serviced.

I have a few questions that maybe you could help me out with:

1. My first question is why is the report server using up so much memory? It is a simple report with one table, no special formatting, no aggregations being done by the report server.

2. Sql server will release its memory when other processes require it, should Reporting services do the same thing?

3. What are the recommended Memory settings of MemoryLimit and MaxMemoryLimit? Are they the defaults of 60 and 80?

4. I have yet to test setting the Memory Limit settings to a value greater than 100 so that the report server can use virtual memory. What are the implications of doing this? Would you recommend doing this?

My organization deals with Billions of rows of data so it is not uncommon for someone to request a hundreds of thousands of rows for further analysis. If reporting services is not the way to go then I will have to look into creating some SSIS packages or some custom .Net code to export this data.

Any advice would be much appreciated.

Thanks.

|||

1. A 500Mb report will do for a Halloween trick. It will be probably useful to look at the ExecutionLog table and see how much time was spent in data retrieval, processing, and rendering. To answer your question, I suspect that the high memory consumption is caused by loading the large dataset in memory. I assume you have applied the latest service packs. I am really curious how would the business users analyze hundreds of thousands of rows.

2. It should. The Windows service (ReportingServicesService.exe) is written in managed code. When the memory is not used, it should be reclaimed by the .NET garbage collector. You may want to report this to the Product Feedback Center. It could be related to the 64-bit version.

3. See http://msdn2.microsoft.com/en-us/library/ms159206.aspx.

4. Again, see the above link.

As a side note, you may want to look at the Analysis Services if you need to push such big datasets.

|||

Hi Teo,

1. I've checked my ExecutionLog Table. Here are the values for TimeDataRetrieval, TimeProcessing and TimeRendering (in Minutes): 19.37min, 12.33 min, 7.38 min. Byte count was 516277912 (492Mb) and Rowcount was 642703. If only 492 Mb are retrieved, I still don't understand why the memory consumption is so high (14 Gigs). This report is actually a drillthrough action that exprots the data to a CSV file. My client requires that when they observe suspicious numbers for certain time periods, stores, products etc. they want the ability to drillthrough the underlying records to do some further analysis, send the data back to the data suppliers so they can explain why the volumes are high or low. They may even want to sell the data to other organizations.

2. I will try to report this issue to the Product Feedback Center.

3 + 4. That article is good, but it makes no recommendations whether or not we should set the memory limit values greater than 100 to use virtual memory. I will run some tests to try and see the affects of changing these properties.

What do you mean by "look at the Analysis Services if you need to push such big datasets"? Are you refering to the performance? Or just another mechanism to export the data. Please elaborate. My end users require the ability to browse cubes and then drillthrough to large datasets.

Any advice would be much appreciated.

Thanks.

|||

1. I am not exluding the possibility this to be a bug. We also ran into an issue with large reports where the reports will time out. The forthcoming SP2 is expected to fix this.

2. Also, I would suggest you contact the Support Center ASAP and request a hotfix. The best thing will be if you could send them a test harness.

What I meant is that SSAS could be a better choice if the users want to aggregate large volumes of data, e.g. for historical analysis. But sounds like in your case, the end users wants to see a detail report so scratch out SSAS.

|||Also, do you have the same issue if you request the report directly from the Report Server (e.g. using the Report Manager) as opposed to subscribed delivery? In this case, the Windows service will be bypassed and if you face the same issue, the ASP.NET process memory would grow.|||

Yes. The same issue occurs with requesting the report directly from the report server. In this case the memory usage of IIS (w3wp.exe) grows to approximated the same as it did with the ReportServer Service. I will continue to search for a solution and post any of my findings.

Thanks.

Call to Webservice from a Reporting Action in SSAS

Is it possible to call a webserive from a reporting action in SSAS?

What I am trying to do is create a reporting action that will schedule a report to run at certain time by calling the CreateSubscription Web service of Reporting services.

Any advice would be much appreciated. Does anyone have any suggestions or perhaps another angle that I could approach this problem?

Thanks

You can't use a report action since the report action uses URL addressibility. Instead consider a regular action that invokes a web page, e.g. ASP.NET page. The page can call down to the Report Server.

Another approach could be to use a report action which calls a dummy report that invokes some custom code. The report can forward the parameter values while the custom code can invoke the API.

|||

Thank you for your reply. I wanted to try and schedule this as a report to run off peak hours as it was a large report. But after testing the subscription based delivery method, I have encountered memory problems which lead me to believe that SSRS may not be the way to go. I am trying to generate a report that is trying to return approx. 500 Mb of data. When the report gets triggered the memory usage of ReportingServicesService.exe climbs until it reaches approximately 14 Gigs (I have a machine with 20 Gigs of memory). After the memory usage reaches 12 Gigs the report service stops servicing any new requests which makes sense since MemoryLimit is set to 60%. This is where the problem begins. It seems that after the report has finished executing it does not want to release this memory and no new requests can be serviced.

I have a few questions that maybe you could help me out with:

1. My first question is why is the report server using up so much memory? It is a simple report with one table, no special formatting, no aggregations being done by the report server.

2. Sql server will release its memory when other processes require it, should Reporting services do the same thing?

3. What are the recommended Memory settings of MemoryLimit and MaxMemoryLimit? Are they the defaults of 60 and 80?

4. I have yet to test setting the Memory Limit settings to a value greater than 100 so that the report server can use virtual memory. What are the implications of doing this? Would you recommend doing this?

My organization deals with Billions of rows of data so it is not uncommon for someone to request a hundreds of thousands of rows for further analysis. If reporting services is not the way to go then I will have to look into creating some SSIS packages or some custom .Net code to export this data.

Any advice would be much appreciated.

Thanks.

|||

1. A 500Mb report will do for a Halloween trick. It will be probably useful to look at the ExecutionLog table and see how much time was spent in data retrieval, processing, and rendering. To answer your question, I suspect that the high memory consumption is caused by loading the large dataset in memory. I assume you have applied the latest service packs. I am really curious how would the business users analyze hundreds of thousands of rows.

2. It should. The Windows service (ReportingServicesService.exe) is written in managed code. When the memory is not used, it should be reclaimed by the .NET garbage collector. You may want to report this to the Product Feedback Center. It could be related to the 64-bit version.

3. See http://msdn2.microsoft.com/en-us/library/ms159206.aspx.

4. Again, see the above link.

As a side note, you may want to look at the Analysis Services if you need to push such big datasets.

|||

Hi Teo,

1. I've checked my ExecutionLog Table. Here are the values for TimeDataRetrieval, TimeProcessing and TimeRendering (in Minutes): 19.37min, 12.33 min, 7.38 min. Byte count was 516277912 (492Mb) and Rowcount was 642703. If only 492 Mb are retrieved, I still don't understand why the memory consumption is so high (14 Gigs). This report is actually a drillthrough action that exprots the data to a CSV file. My client requires that when they observe suspicious numbers for certain time periods, stores, products etc. they want the ability to drillthrough the underlying records to do some further analysis, send the data back to the data suppliers so they can explain why the volumes are high or low. They may even want to sell the data to other organizations.

2. I will try to report this issue to the Product Feedback Center.

3 + 4. That article is good, but it makes no recommendations whether or not we should set the memory limit values greater than 100 to use virtual memory. I will run some tests to try and see the affects of changing these properties.

What do you mean by "look at the Analysis Services if you need to push such big datasets"? Are you refering to the performance? Or just another mechanism to export the data. Please elaborate. My end users require the ability to browse cubes and then drillthrough to large datasets.

Any advice would be much appreciated.

Thanks.

|||

1. I am not exluding the possibility this to be a bug. We also ran into an issue with large reports where the reports will time out. The forthcoming SP2 is expected to fix this.

2. Also, I would suggest you contact the Support Center ASAP and request a hotfix. The best thing will be if you could send them a test harness.

What I meant is that SSAS could be a better choice if the users want to aggregate large volumes of data, e.g. for historical analysis. But sounds like in your case, the end users wants to see a detail report so scratch out SSAS.

|||Also, do you have the same issue if you request the report directly from the Report Server (e.g. using the Report Manager) as opposed to subscribed delivery? In this case, the Windows service will be bypassed and if you face the same issue, the ASP.NET process memory would grow.|||

Yes. The same issue occurs with requesting the report directly from the report server. In this case the memory usage of IIS (w3wp.exe) grows to approximated the same as it did with the ReportServer Service. I will continue to search for a solution and post any of my findings.

Thanks.

Call stored procedure within reporting service

I'm trying to generate a report based on a stored procedure which will
return a result set. The DBMS is Sybase SQL Server.
Can reporting service do it?
Thanks a lot for your help.Create ODBC connection to your sybase file and
create dataset based on ODBC connection
John Wang wrote:
> I'm trying to generate a report based on a stored procedure which will
> return a result set. The DBMS is Sybase SQL Server.
> Can reporting service do it?
> Thanks a lot for your help.

call report with datas updated

Hello,
I use sql reporting services to show reports with url and to create snapshot with his web services.
My problem is the following:
- i show a report (linked to a database with data source and dataset) with an url like :
http://srv/Reportserver/BOURGOGNE/Perso&rs:Format=HTML4.0
&rs:Command=Render&annee=2004&id=0580639E
- i change data in the database
- when a recall the report with the previous url, the data has not changed, and i must click on refresh button next the export button.
- i would like that this refresh made automatically when i call a report. for more precisions, the property/execution on this report is set to 'Render this report with the most recent data' with '' 'Do not cache temporary copies of this report'... so i don't understantd..
thank to help me and sorry for my english...
floben.

It seems like you are rendering from session. On your URL above, append the following argument: rs:ClearSession=true

This will make sure the report gets reprocessed entirely.
See also: http://msdn2.microsoft.com/ms224719

-- Robert

|||thank, it was that..

Calendar Reporting Control

I have a request to have a report created in a calendar layout. Does anyone
know of a charting component that can do this or an example that would help
me get started.
Thanks,
DavidHi David,
Welcome to MSDN Managed Newsgroup!
Unfortunately, I am afraid we do not have direct solution for this calendar
layout report in Microsoft. You may have to handle the layout yourself and
write custom code if required.
Let's wait to see whether other community members have such experience. You
may also search the google.com for related information.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Calendar picker?

Hi there
I am trying to set up some sort of date picker for my reports in Reporting
Services and found an article which basically uses an asp.net calendar
control as a plug-in to the report (see link below).
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/6e8cee511769b6c/1ddce2c2c6523fc0?q=&_done=/groups?enc_author=uFKGehIAAAAdZvNtw6tRAoeBJxgAOEvh8rhlH0Pnl47z4AZhN98BFg&&_doneTitle=Back%20to%20Search&d=&
Unfortunately I am not very experienced in programming, so i don't fully
understand how you would integrate the asp page with the report.
My reports are based on an Analysis Services cube time dimension. i have an
MDX statement in the Data query window in .NET which populates a Matrix
table. The query looks like this:
SELECT { Measures.members } on Columns ,
NON EMPTY {{[Business].[User].[Business Entity Id].[891].Children}
* {[Time].[2004].[Quarter 1].[1].[January]:[Time].[2005].[Quarter
1].[1].[January]}} on Rows
FROM UsageStats_Phase1
I then deploy the report to the Reporting Server manually. How would I
integrate an asp calendar control into this set up? Any help would be most
appreciated!!!
Thanks and regardsRight now, there's not a good way to customize the parameter input process
other than programming. The hyperlink you posted is a very good starting
point.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Neile" <Neile@.discussions.microsoft.com> wrote in message
news:BE9EB249-A29F-47A2-9D17-18632D47A1E6@.microsoft.com...
> Hi there
> I am trying to set up some sort of date picker for my reports in Reporting
> Services and found an article which basically uses an asp.net calendar
> control as a plug-in to the report (see link below).
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/6e8cee511769b6c/1ddce2c2c6523fc0?q=&_done=/groups?enc_author=uFKGehIAAAAdZvNtw6tRAoeBJxgAOEvh8rhlH0Pnl47z4AZhN98BFg&&_doneTitle=Back%20to%20Search&d=&
> Unfortunately I am not very experienced in programming, so i don't fully
> understand how you would integrate the asp page with the report.
> My reports are based on an Analysis Services cube time dimension. i have
> an
> MDX statement in the Data query window in .NET which populates a Matrix
> table. The query looks like this:
> SELECT { Measures.members } on Columns ,
> NON EMPTY {{[Business].[User].[Business Entity Id].[891].Children}
> * {[Time].[2004].[Quarter 1].[1].[January]:[Time].[2005].[Quarter
> 1].[1].[January]}} on Rows
> FROM UsageStats_Phase1
> I then deploy the report to the Reporting Server manually. How would I
> integrate an asp calendar control into this set up? Any help would be most
> appreciated!!!
> Thanks and regards
>

Calendar parameter input

Hi,
We have several challenges because of lack of calendar parameter input for
report parameters in Reporting Service 2000
Do we have Calendar parameter input in Reporting Services 2005?
Thank you,
Alanyes,you have datepicker.
"A.M-SG" wrote:
>
> Hi,
>
> We have several challenges because of lack of calendar parameter input for
> report parameters in Reporting Service 2000
>
> Do we have Calendar parameter input in Reporting Services 2005?
>
> Thank you,
> Alan
>
>

calendar control in reporting services?

Is there anyway to use a calendar control in reporting services 2000? how?
thanksNo. RS 2005 has a calendar control. As well as multi-select parameters and
end user sorting. Plus better performance etc.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"netasp" <netasp@.newsgroups.nospam> wrote in message
news:utaMRWppGHA.3288@.TK2MSFTNGP03.phx.gbl...
> Is there anyway to use a calendar control in reporting services 2000? how?
> thanks
>|||Hello Netasp,
As Bruce has said, the Calendar control is not available in SQL Server
reporting service 2000. In addition to the Reporting Service 2005 option,
I'm wondering what's the functionality you want to add in your SQL Server
2000 reporting service report(through the calendar control). Are you going
to use the calender to provide datetime parameter? If so, do you think it
possible that we create an ASP.NET web application and dynamically request
the reporting service report in the ASP.NET page and display it. Thus, we
can use the ASP.NET web calender control on the page to get datetime input
from client user and send report request (with the datetime paramter get
from the calender).
Please feel free to post here if you have any other concerns or have any
other consideration on this.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Netasp,
How are you doing on this issue? Have you got any progress or does my last
reply helps you a little? If you think the approach I mentioned doable or
need any further assistance on this, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Calendar Control in Reporting Services 2000

Hi All

Is it possible to add datetime picker (Calendar Control)

in 2000 reporting services

Cheers

The calendar control came out with Reporting Services 2005. It can't be used in 2000 as far as I know.|||I can second that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Calendar Control in Reporting Services 2000

Hi All

Is it possible to add datetime picker (Calendar Control)

in 2000 reporting services

Cheers

The calendar control came out with Reporting Services 2005. It can't be used in 2000 as far as I know.|||I can second that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

calendar control for parameters in SQL 2000 Reporting?

As the subject says, I'd like to know if calendar controls are available for
reporting creating using VS2003 running on SQL 2000 (for internal reasons we
cannot upgrade to SQL 2005 for some time)No. You need RS 2005. Note that you can go to RS 2005 without upgrading your
database to SQL 2000. You need a SQL Server 2005 license but you can keep
your database at 2000 and just upgrade RS to RS 2005. I did this (although I
have since upgrade the db) and it works and is fully supported.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KBlount" <KBlount@.discussions.microsoft.com> wrote in message
news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
> As the subject says, I'd like to know if calendar controls are available
> for
> reporting creating using VS2003 running on SQL 2000 (for internal reasons
> we
> cannot upgrade to SQL 2005 for some time)|||Thanks for the response Bruce. I'm currently investigating using .NET
calendar controls and integrating them with SSRS 2000 - we'll see how hairy
that gets! heh
Thanks for the info about being able to use RS 2005 on SQL 2000. I'll pass
on your post to my IT team and let them investigate that further. It would be
so convenient to simply change a paramter type to DateTime ;)
Cheers
Kevin
"Bruce L-C [MVP]" wrote:
> No. You need RS 2005. Note that you can go to RS 2005 without upgrading your
> database to SQL 2000. You need a SQL Server 2005 license but you can keep
> your database at 2000 and just upgrade RS to RS 2005. I did this (although I
> have since upgrade the db) and it works and is fully supported.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
> news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
> > As the subject says, I'd like to know if calendar controls are available
> > for
> > reporting creating using VS2003 running on SQL 2000 (for internal reasons
> > we
> > cannot upgrade to SQL 2005 for some time)
>
>|||For further ammunition. RS 2005 has end user sorting, renders to pdf and
excel much better (I used to regularly have my server lock up and that does
not happen anymore). It also has multi-select parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KBlount" <KBlount@.discussions.microsoft.com> wrote in message
news:2C79A69D-226E-4D64-BF0A-20AE98D76FFE@.microsoft.com...
> Thanks for the response Bruce. I'm currently investigating using .NET
> calendar controls and integrating them with SSRS 2000 - we'll see how
> hairy
> that gets! heh
> Thanks for the info about being able to use RS 2005 on SQL 2000. I'll pass
> on your post to my IT team and let them investigate that further. It would
> be
> so convenient to simply change a paramter type to DateTime ;)
> Cheers
> Kevin
> "Bruce L-C [MVP]" wrote:
>> No. You need RS 2005. Note that you can go to RS 2005 without upgrading
>> your
>> database to SQL 2000. You need a SQL Server 2005 license but you can keep
>> your database at 2000 and just upgrade RS to RS 2005. I did this
>> (although I
>> have since upgrade the db) and it works and is fully supported.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
>> news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
>> > As the subject says, I'd like to know if calendar controls are
>> > available
>> > for
>> > reporting creating using VS2003 running on SQL 2000 (for internal
>> > reasons
>> > we
>> > cannot upgrade to SQL 2005 for some time)
>>|||Thanks for the extra info, Bruce.
I attended (and passed: 88%! hehe wooo) the LearningTree "SQL Server
Reporting Services: Hands-On" course this time last year, and naturally I've
not had a project to use what I learned since.. until now. Now that you've
mention those other benefits, especially the multi-select parameter, I can
definitely see a need for the upgrade, rather than just a :wouldn't it be
nice".. the email to my IT team was sent earlier today... time will tell.
"Bruce L-C [MVP]" wrote:
> For further ammunition. RS 2005 has end user sorting, renders to pdf and
> excel much better (I used to regularly have my server lock up and that does
> not happen anymore). It also has multi-select parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
> news:2C79A69D-226E-4D64-BF0A-20AE98D76FFE@.microsoft.com...
> > Thanks for the response Bruce. I'm currently investigating using .NET
> > calendar controls and integrating them with SSRS 2000 - we'll see how
> > hairy
> > that gets! heh
> >
> > Thanks for the info about being able to use RS 2005 on SQL 2000. I'll pass
> > on your post to my IT team and let them investigate that further. It would
> > be
> > so convenient to simply change a paramter type to DateTime ;)
> >
> > Cheers
> >
> > Kevin
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No. You need RS 2005. Note that you can go to RS 2005 without upgrading
> >> your
> >> database to SQL 2000. You need a SQL Server 2005 license but you can keep
> >> your database at 2000 and just upgrade RS to RS 2005. I did this
> >> (although I
> >> have since upgrade the db) and it works and is fully supported.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "KBlount" <KBlount@.discussions.microsoft.com> wrote in message
> >> news:B87C7B57-AF42-4C88-B762-69F3522F20FB@.microsoft.com...
> >> > As the subject says, I'd like to know if calendar controls are
> >> > available
> >> > for
> >> > reporting creating using VS2003 running on SQL 2000 (for internal
> >> > reasons
> >> > we
> >> > cannot upgrade to SQL 2005 for some time)
> >>
> >>
> >>
>
>

Calendar Control

We use the RS 2005 Calendar control for date/time parameters on our
reporting. Unfortunatley we have been running into a performance problem
becuase the Calendar control does a round trip to the server once a date is
chosen. On many of our desktops, this round trip is very evident.
Is there any way to provent the round trip to the server when a date is
chosen?
Thanks,
EricNo way except you need to write a custom code.
Amarnath
"echeeze" wrote:
> We use the RS 2005 Calendar control for date/time parameters on our
> reporting. Unfortunatley we have been running into a performance problem
> becuase the Calendar control does a round trip to the server once a date is
> chosen. On many of our desktops, this round trip is very evident.
> Is there any way to provent the round trip to the server when a date is
> chosen?
> Thanks,
> Eric