Combining two fields into one

I have a small problem.
In my query I am calling a field of status and a field of statusyearcount. I
need to get these into one cell in a table, called statusyearcount.
ie. Status = C
StatusYearCount = 10
Need to show C10 in the table.
I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
getting an "incorrect format string".
Im gathering it because one is an int and the other varchar. Can somebody
please point me in the right direction here.
Thanks muchly.You can cast the Field.
=Fields!Status.Value + Int(Fields!StatusYearCount.Value)
--
| Thread-Topic: Combining two fields into one
| thread-index: AcWo+8ArbHZHrGD+TI2fppOvcbIgrg==| X-WBNR-Posting-Host: 202.175.143.143
| From: "=?Utf-8?B?TmF0IEpvaG5zb24=?="
<NatJohnson@.discussions.microsoft.com>
| Subject: Combining two fields into one
| Date: Wed, 24 Aug 2005 15:32:46 -0700
| Lines: 18
| Message-ID: <3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51089
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a small problem.
|
| In my query I am calling a field of status and a field of
statusyearcount. I
| need to get these into one cell in a table, called statusyearcount.
|
| ie. Status = C
| StatusYearCount = 10
|
| Need to show C10 in the table.
|
| I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I
keep
| getting an "incorrect format string".
|
| Im gathering it because one is an int and the other varchar. Can
somebody
| please point me in the right direction here.
|
| Thanks muchly.
|
||||This will do it:
=Fields!Status.Value & CStr(Fields!StatusYearCount.Value)
CStr converts integer to string, allowing succesful concatenation.
GeoSynch
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com...
>I have a small problem.
> In my query I am calling a field of status and a field of statusyearcount. I
> need to get these into one cell in a table, called statusyearcount.
> ie. Status = C
> StatusYearCount = 10
> Need to show C10 in the table.
> I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
> getting an "incorrect format string".
> Im gathering it because one is an int and the other varchar. Can somebody
> please point me in the right direction here.
> Thanks muchly.
>

Combining Data from MySql and SQL2005

Problem:
I have a MySql database that houses status information that is linked
by a primary key from a SQL2005 database
Using Report Designer in VS2005, I have created 3 datasets pointing to
the 2 datasources. I need to create a relationship between the MySql
based datasets and a dataset that is based on a SQL2005 datasource, so
that I can have all of the data available in one table...
MySQL Table1
DocID, JobID, FKeyToSQL
MySQL Table2
JobID,Status
SQL2005 Data
PKeyToMySQL,Description,etc.
I would like to be able to see a status report that shows...
PKeyToMySQL,Description,JobID,Status
Thanks for any help.Can you try adding MySQL database as a LinkedServer?
"Kevin.Ailes@.gmail.com" wrote:
> Problem:
> I have a MySql database that houses status information that is linked
> by a primary key from a SQL2005 database
> Using Report Designer in VS2005, I have created 3 datasets pointing to
> the 2 datasources. I need to create a relationship between the MySql
> based datasets and a dataset that is based on a SQL2005 datasource, so
> that I can have all of the data available in one table...
> MySQL Table1
> DocID, JobID, FKeyToSQL
> MySQL Table2
> JobID,Status
> SQL2005 Data
> PKeyToMySQL,Description,etc.
> I would like to be able to see a status report that shows...
> PKeyToMySQL,Description,JobID,Status
> Thanks for any help.
>|||Jaraba wrote:
> Can you try adding MySQL database as a LinkedServer?
>
> "Kevin.Ailes@.gmail.com" wrote:
> > Problem:
> > I have a MySql database that houses status information that is linked
> > by a primary key from a SQL2005 database
> >
> > Using Report Designer in VS2005, I have created 3 datasets pointing to
> > the 2 datasources. I need to create a relationship between the MySql
> > based datasets and a dataset that is based on a SQL2005 datasource, so
> > that I can have all of the data available in one table...
> >
> > MySQL Table1
> > DocID, JobID, FKeyToSQL
> > MySQL Table2
> > JobID,Status
> >
> > SQL2005 Data
> > PKeyToMySQL,Description,etc.
> >
> > I would like to be able to see a status report that shows...
> >
> > PKeyToMySQL,Description,JobID,Status
> >
> > Thanks for any help.
> >
> >
I was able to embed a sub-report and submit a parameter to it and it
seems to have accomplished what I wanted. It seems like a round about
way to do it though. I would have thought there would be some way to
build relationships between datasets since you can't combine data from
2 different datasources into 1 dataset. Oh well, now if I could just
get this sub-report to be centered in the column.(TextAlign=center on
column properties doesn't seem to be working)

Combine 2 rows from derived table into 1 row w/o repeating query?

I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name

2 1 "A"

2 2 "B"

I want to get the following:

ID Name1 Name2

2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

Here it is,

Code Snippet

Create Table #data (

[ID] int ,

[Status] int ,

[Name] Varchar(100)

);

Insert Into #data Values('2','1','A');

Insert Into #data Values('2','2','B');

Select

Id

,max(case when Status=1 Then [Name] end) [name1]

,max(case when Status=2 Then [Name] end) [name2]

from

(

Select * from #data -- Your Derived Table

) as Data

Group By

Id

|||

The solution will work, I just needed to think about how to expand it for more columns, but I got it now.

If it's very very slow, I will try something with a CTE - I think that'll work as well.

|||

The CTE was 50-60% faster than the other route! but that method is also useful if using sql2000.|||

Yeah a CTE is going to be the way to go on this in 2005 for sure. Never been a fan of using temp tables and I avoid them when I can. So here's my own variation on the above sample...

Code Snippet

SELECT
[ID],
max(case when Status=1 Then [Name] end) [name1],
max(case when Status=2 Then [Name] end) [name2]

from (
SELECT 2 As [ID], 1 As Status, 'A' As [Name]
UNION SELECT 2, 2, 'B'
) AS Data
GROUP BY [ID]