Change Datasource connectionstring

Hey,

I have a SQL Server 2005, an Reporting Services is also installed.
Now I have a report 'Report1' and a datasource 'DS1', the report is build and deployed.
The report works fine when i see it in a browser.

Now I have made a vb.net application with a dataviewer, the dataviewer shows the report 'Report1'.
But I want to be able to change the datasource 'DS1' from my application code.

How can I do this?

thanks

Hi,

you could use a hidden parameter which will influence the beahaviour of a dynamic evaluated connectring string, though connection strings can be designed dynamically like ="Data Source" + IIF(Parameters!SomeParam.Value = 1, "SomeServer",SomeOtherServer") That is a thing which is most common, enabling you to do this also on the brwoser interface by just manipulating the URL of the Report.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Cell Drillthrough in Reporting Services 2005

Hi,

I’ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I’ve enabled drillthrough.

I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that’s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?

Thanks,

AbdelSSAS actions are not carried to SSRS reports. One workaround is to create a drillthrough report which gets the context from the master report and executes the DRILLTHROUGH MDX query.|||

Hi Abdel ,

You have written U know how to define a drillthrough in the report by clicking on one of the members of the dimension hierarchy . can you please tell me how we will do that ?

I am trying to make a drillthrough report ,can you please give me a step by step process for enabling drill through in the reports .

I want to jump to a child report & pass the parameters of the parent report to the child report , I am able to get the parameters value in the child report ,but not able to use those values in the dataset query of the child report .

can you please tell me if this is possible or not ? & if yes how ?

Thanks

Rashmi

|||Please note the the original question was asking about a drillthrough action in the cube. If you just want the user to drilll through to another report, you can use the SSRS navigation feature. To do so, right-click on the textbox which will be use for drillthrough and choose Properties. Then go to the Navigation tab and use the Jump to Report feature. Note that that if the second report will retrieve data from the cube as well, you need to pass the parameters in the required format, e.g.; [Date].[Date].&[20070523]

Cell Drillthrough in Reporting Services 2005

Hi,

I’ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I’ve enabled drillthrough.

I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that’s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?

Thanks,

AbdelSSAS actions are not carried to SSRS reports. One workaround is to create a drillthrough report which gets the context from the master report and executes the DRILLTHROUGH MDX query.|||

Hi Abdel ,

You have written U know how to define a drillthrough in the report by clicking on one of the members of the dimension hierarchy . can you please tell me how we will do that ?

I am trying to make a drillthrough report ,can you please give me a step by step process for enabling drill through in the reports .

I want to jump to a child report & pass the parameters of the parent report to the child report , I am able to get the parameters value in the child report ,but not able to use those values in the dataset query of the child report .

can you please tell me if this is possible or not ? & if yes how ?

Thanks

Rashmi

|||Please note the the original question was asking about a drillthrough action in the cube. If you just want the user to drilll through to another report, you can use the SSRS navigation feature. To do so, right-click on the textbox which will be use for drillthrough and choose Properties. Then go to the Navigation tab and use the Jump to Report feature. Note that that if the second report will retrieve data from the cube as well, you need to pass the parameters in the required format, e.g.; [Date].[Date].&[20070523]

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

PublicSub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

EndSub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

PublicClass ScriptMain

PublicSub Main()

Dim dtAsNew System.Data.DataTable

Dim oleadAsNew Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim drAs DataRow

ForEach drIn dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

EndSub

EndClass

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class