Change in legend of chart based on values?

I am developing a chart with type as column and subtype as stacked. The values (different columns from my dataset) are shown as series in the chart. If I have an entire column with no values, nothing in shown in the graph but the column name comes in the legend. I do not want to show the particular column in legend if all the values in that column are 0 or null. Is it possible through an expression or any other way?

Please let me know.

Thanks in advance !!!

If you use a dynamic series grouping in the chart and you want to get rid of a particular series grouping instance, you could use a filter expression on the series grouping, e.g.
Filter expression: =Sum(Fields!Name.Value, "DynamicSeriesGroupingName")
Filter operator: >
Filter value: =0

Again, the filter approach will only work in the case of dynamic series groupings.

-- Robert

Change image properties at runtime

Hi,
I'm using Reporting Services for SQL2K. My report will need to dynamically
call a custom assembly for each row in a dataset, and then show the image
file that the custom assembly creates. I was thinking of using an Image
control in the report and then changing the path to the image file at
runtime. But I can not figure out how to access the image1.Value property.
Any tips on this or perhaps an alternative solution?
TIA
JonasI found the answer, and it was very easy ;-)
When the control is selected in the Report Designer, it it possible to edit
the properties as usual. The property for Value can also use an Expression,
and voila, this can point to a variable which contains a dynamically created
path.
Brgds
Jonas
"Jonas" <Jonas@.nospam.pl> wrote in message
news:%23z53z3cHGHA.240@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using Reporting Services for SQL2K. My report will need to dynamically
> call a custom assembly for each row in a dataset, and then show the image
> file that the custom assembly creates. I was thinking of using an Image
> control in the report and then changing the path to the image file at
> runtime. But I can not figure out how to access the image1.Value property.
> Any tips on this or perhaps an alternative solution?
> TIA
> Jonas
>
>

change Datasource at runtime

Hi

Is it possible to change the datasource dynamically in reporting services.

For example if I have a dataset named DataSet1 whose datasource1 connects to server1, how can I change the same DataSet1 to use datasource2 to points to a different server. I would like to know if this datasource change is possible out side the dataset (dynamically).
Thanks

Hi pzmrcd
I've had the same problem. I found that it was not possible without re-rendering the SRS code(XML). It is not possible in SRS since you can't use an expression to change the dataset. Also the field names of datasets may differ.

Change Dataset on existing reports

Hi All,

I have a set of reports already built. I want to use the same reports using a different dataset(different server). I have tried to add a new datasource and data set, then set the data tab of the report to reflect the new dataset. All seems fine, but when I deploy the report, it is deployed with the new dataset into the new location, but the data displayed is from the original dataset. Any pointers?

regards

Have you tried getting out of report manager and going back in? Refreshing. Something sounds like it's pointing to the wrong server. The report is doing what you have told it to do. Check your data sources and check your report on the report manager to see where the data source is pointing. It may be pointing somewhere else.|||I have done what you suggested. Still not working. Odd thing is, if I go to the report properties in the RS website and look at the data sources, both data sources are listed. Let me be clear on what I am looking for here, so I dont waste anyones time. I have 2databases I want to access and have 1 RS web server. I want to set up 2 folders on the RS and in each of the folders are the same reports , pointing at different data sources. So in the report designer I created a report and deployed it for datasource#1. Then I added a new datasource (#2) and created a new dataset with the same query from #1, but used #2 as the datasource. This is now selectable from the dropdown on the data tab in the designer. When I run the query from the data tab in the designer, the results are correct. They reflect the data from the selected datasource. But in deployed and preview mode, the report is reporting the data from the original datasource.|||

Did you delete the original dataset from this. if not delete the original data set and keep only the new dataset you added.

Then select the layout tab and select the table properties and change the Datasetname to the new dataset name.

|||Problem is, I am trying to d this and keep both datasets. Any time I have to deploy report updates, I have to push it for both datasources. I would rather not delete and creaqte for each deployment. But, if thats the way it is... thats the way it is.|||

Often the report properties persist from old versions. You may want to delete the report on report server using the manager webpage, and then repost the report. As long as you are using two different named datasources, you should be ok. If the datasources have the same name, make sure the deploy path for the second one is going to a different folder. Remember that the deploy path for the report and datasource are separate properties of the VS2005 project.

Hope that helps.

sql

Change Dataset font size

Is there there a way to change font size of the text of the data set while working in the Report Designer?

In the menu: Go to "Tools" --> "Options"; then under "Environment" --> "Fonts and Colors"

You can edit alot of elements within the dev. studio. (I just didn't find the way to change the font size of the generic query builder (maybe that only happens after restart which I didn't do))

change connectionstring for dataset

hi,

I'm having this application using the express way to create the dategridview by having the query string builder. However, my computer recently crashed and I have no idea to change the connection string. so when I load the whole windows application, I am unable to view the information that are supposed to be in the datagridview. however, it returned an exception.

after finding out the main culprit, I realised that the database is using the old sql server's login. since it is using the old database's login, therefore it is unable to log the information into the datagrid view.

Please help! Thanks.

I'm sorry to hear that your computer crashed. Did you mean to change connection strings for typed DataSet which you created via "Data Source Configuration Wizard"? If so, you only need to change the corresponding Data Connection in the Server Explorer, as all Typed DataSet generated by the wizard use connectionstrings defined in Data Connections. If you want to configure connection string for individual TableAdapter, you need to open the typed DataSet in Design view, and right click on the TableAdapter->choose Configure...->press Previous button untill you rearch the "Choose Your Data Connection" step.

Hope this helps.

|||

thanks for the guide..I'll try and see if it works in other computer..

Cheers,

Joelle

CE 3.5, VS 2008, Typed Dataset: Get the updated identity of inserted row

Hello,

Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?

also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."

(Should I post this in Orcas forum?!)

Regards,

Parham.

In order to get the last inserted identity, execute: SELECT @.@.IDENTITY against the same still open connection that executed the INSERT statement. You can only run a single statement in a command against SQL Compact (that's probably why you get the error)

|||

ErikEJ wrote:

In order to get the last inserted identity, execute: SELECT @.@.IDENTITY against the same still open connection that executed the INSERT statement. You can only run a single statement in a command against SQL Compact (that's probably why you get the error)

Tahnks Erick.

This means that if i have inserted some rows into the table and then updated the table to database with the Update command of my Table Adapter, I should REFILL the table to getback the updated identities of the inserted rows?!

Regards,

Parham.

|||

This might help you: http://groups.google.dk/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/3422d5f0774d605f/34a537895803c758?lnk=st&q=dataset+sql+ce+identity+last+inserted&rnum=1&hl=en#

Alternatively you could use uniqueidentifier columns instead, with a new value of Guid.NewGuid() (set in your code, so you will know the value)

I will do some tetsing later today and revert if there are other options.

Categorise Bar Chart

How to create a bar chart by setting the static category myself?

I mean, for example, I have a dataset which record the number of coins different people have.

I would like to draw a bar chart which shows 3 bars with the following 3 different categories:

1) n < 5,

2) n >= 5 and n <10,

3) n>=10.

where n is the number of coins.

How can this be expressed in the categories grouping?

Thanks in advance.

Sorry, this is currently not directly supported through chart groupings. You would need to write the query (or add a calculated field on the dataset) so that you get this categorization in the data and then use those fields in the chart.

-- Robert

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

cast or convert varchar to money/datetime

Hello,
I have a dataset that is all varchar with leading and
trailing spaces (comes from a mainframe). I import this
data to a table that is all varchar (using DTS). I then
insert it to a table that has the correct datatype fields,
but I have to perform a conversion. I have been using
Cast(ltrim(rtrim(colx)) As datetime)
Cast(ltrim(rtrim(colx)) As money)
I seem to be getting the correct data with datetime, and
with money I get like 306.6900. Am I supposed to get a $
symbol for money? Or just decimal? So is cast the
correct operator here or should I use convert? If
convert - how do I convert money?
Thanks,
RonThere is no reason to store the dollar sign with the numeric value, nor is
there a need to use the MONEY data type. In fact, this can cause problems.
See http://www.aspfaq.com/2503 which, among other things, describes reasons
to use DECIMAL in favor of MONEY/SMALLMONEY.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||SQL Server stores data, not the presentation of data. The client application
is what is doing the
presentation of your data. for the money datatype, the values 306.6900 and 3
06.69 are the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||Ron,
[money] values are numbers. 306.6900 is a number. The $ symbol
is not part of a money value, but just something that may be part of the
display of a number. SQL Server can convert money values to
strings with $ or with commas, using CONVERT with format codes
(see CAST AND CONVERT in Books Online).
The type of Cast(whatever as money) will definitely be [money]. If
you want to be absolutely sure, try
select cast(whatever as money) as onlyColumn
into #checktype
then look at the table structure for #checktype.
Steve Kass
Drew University
Ron wrote:

>Hello,
>I have a dataset that is all varchar with leading and
>trailing spaces (comes from a mainframe). I import this
>data to a table that is all varchar (using DTS). I then
>insert it to a table that has the correct datatype fields,
>but I have to perform a conversion. I have been using
>Cast(ltrim(rtrim(colx)) As datetime)
>Cast(ltrim(rtrim(colx)) As money)
>I seem to be getting the correct data with datetime, and
>with money I get like 306.6900. Am I supposed to get a $
>symbol for money? Or just decimal? So is cast the
>correct operator here or should I use convert? If
>convert - how do I convert money?
>Thanks,
>Ron
>