combining select staements

Folks

I have three select statements. I want to display q_text based on
the respective where condition. How do i combine these three and write
as one select statement.

select q_text Questions from question
where new_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '1'

select q_text Questions from question
where disc_account_flag = '0'

Remember that all the queries returns more than 1 value.

I tried to use
select (query1),
(query2),
(query3)
but because it is returning more than one value, there is error.

Can any suggest me any other syntax??

Thanksselect [q_text Questions] from question
where new_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '1'
UNION
select [q_text Questions] from question
where disc_account_flag = '0'|||I think it should be UNION ALL, since either condition may produce duplicate results. UNION will eliminate duplicates.sqlsql

Column Widths

Is there a way to have columns automatically size themselves based on the
contents of the column name and data? I guess I'm looking for something
similar to Excel's Format|Column|AutoFit Selection option.
What I see happening with our reports is that a column name of...
Reservation Departure Date
...appears as...
Reservation
Depart Da
te
I would prefer to see the column heading in a single line of...
Reservation Departure Date
...however, I don't want to have to resize every report column to make the
contents of the column appear like this.
Is there a simple report setting to do an autofit?Yes Andy, I face this problem too!
Up to now, do you have any ways to fix this problem'
Super thanks if anyone can fix it!
"Andy" wrote:
> Is there a way to have columns automatically size themselves based on the
> contents of the column name and data? I guess I'm looking for something
> similar to Excel's Format|Column|AutoFit Selection option.
> What I see happening with our reports is that a column name of...
> Reservation Departure Date
> ...appears as...
> Reservation
> Depart Da
> te
> I would prefer to see the column heading in a single line of...
> Reservation Departure Date
> ...however, I don't want to have to resize every report column to make the
> contents of the column appear like this.
> Is there a simple report setting to do an autofit?
>
>

Column Width

How can you widen or shorten a column's width when the report is run?
The width doesn't allow custom code or expressions.
Basically, based on a parameter I pass in, I want to widen or shrink a
column's width.Anyone?
"JSF" wrote:
> How can you widen or shorten a column's width when the report is run?
> The width doesn't allow custom code or expressions.
> Basically, based on a parameter I pass in, I want to widen or shrink a
> column's width.|||JSF,
It doesn't look like it's possible to dynamically change column width.
I just did another search through the group and found nothing new.

Column Value Lookup

What is the best way to lookup a value in a column from another data set?
I am trying to locate and display the employee name based on the employee id
found in another data set.
I.E.
13 = Terry Ward
14 = Peter Jackson
Thank you.I might be wrong, but I don't think you can do that, unfortunately.
You need to make the EmployeeID a parameter that you can use to query your
employee database table for names.
What are you trying to do? Might be a different way of doing it.
Kaisa M. Lindahl Lervik
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> What is the best way to lookup a value in a column from another data set?
> I am trying to locate and display the employee name based on the employee
> id
> found in another data set.
> I.E.
> 13 = Terry Ward
> 14 = Peter Jackson
> Thank you.|||In the Task table, employees are identified as numeric values. References to
the actual employee names are found in the Employee table.
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To =dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
"Kaisa M. Lindahl Lervik" wrote:
> I might be wrong, but I don't think you can do that, unfortunately.
> You need to make the EmployeeID a parameter that you can use to query your
> employee database table for names.
> What are you trying to do? Might be a different way of doing it.
> Kaisa M. Lindahl Lervik
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > What is the best way to lookup a value in a column from another data set?
> >
> > I am trying to locate and display the employee name based on the employee
> > id
> > found in another data set.
> >
> > I.E.
> > 13 = Terry Ward
> > 14 = Peter Jackson
> >
> > Thank you.
>
>|||Can you join the Task table to the emloyee table similar to the way the
tblIssue table is joined to the tblEmployee table is below to return the
employee name?
e.g. Something like this...
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Terry" wrote:
> In the Task table, employees are identified as numeric values. References to
> the actual employee names are found in the Employee table.
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> "Kaisa M. Lindahl Lervik" wrote:
> > I might be wrong, but I don't think you can do that, unfortunately.
> > You need to make the EmployeeID a parameter that you can use to query your
> > employee database table for names.
> >
> > What are you trying to do? Might be a different way of doing it.
> >
> > Kaisa M. Lindahl Lervik
> > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > What is the best way to lookup a value in a column from another data set?
> > >
> > > I am trying to locate and display the employee name based on the employee
> > > id
> > > found in another data set.
> > >
> > > I.E.
> > > 13 = Terry Ward
> > > 14 = Peter Jackson
> > >
> > > Thank you.
> >
> >
> >|||Do I need to create a new dataset and include the employee name in the report
in order to obtain the employee name along with the existing dataset called
IT_Projects?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Can you join the Task table to the emloyee table similar to the way the
> tblIssue table is joined to the tblEmployee table is below to return the
> employee name?
> e.g. Something like this...
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Terry" wrote:
> > In the Task table, employees are identified as numeric values. References to
> > the actual employee names are found in the Employee table.
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> > > I might be wrong, but I don't think you can do that, unfortunately.
> > > You need to make the EmployeeID a parameter that you can use to query your
> > > employee database table for names.
> > >
> > > What are you trying to do? Might be a different way of doing it.
> > >
> > > Kaisa M. Lindahl Lervik
> > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > What is the best way to lookup a value in a column from another data set?
> > > >
> > > > I am trying to locate and display the employee name based on the employee
> > > > id
> > > > found in another data set.
> > > >
> > > > I.E.
> > > > 13 = Terry Ward
> > > > 14 = Peter Jackson
> > > >
> > > > Thank you.
> > >
> > >
> > >|||Ok, it sounds you are"...trying to locate and display the employee name based
on the employee id found in another data set."
So, it sounds like you have 1 dataset that does not return the employee name
and another dataset that does return the employee name. Instead of trying to
perform a lookup between the 2 datasets that you have, the best thing to do
would be to modify the query that currently does not include the employee
name to include the employee name in the select statement. Does that make
more sense?
If you want to post the 2 dataset queries that you have, that may help me
understand your situation better.
"Terry" wrote:
> Do I need to create a new dataset and include the employee name in the report
> in order to obtain the employee name along with the existing dataset called
> IT_Projects?
> DATASET 1:
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> DATASET 2:
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Matt" wrote:
> > Can you join the Task table to the emloyee table similar to the way the
> > tblIssue table is joined to the tblEmployee table is below to return the
> > employee name?
> >
> > e.g. Something like this...
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Terry" wrote:
> >
> > > In the Task table, employees are identified as numeric values. References to
> > > the actual employee names are found in the Employee table.
> > >
> > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > dbo.tblIssue.Opened_By,
> > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > dbo.tblIssue.Closed_Date
> > > FROM dbo.tblIssue
> > > INNER JOIN
> > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > dbo.tblEmployee.Employee_ID
> > > INNER JOIN
> > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > employee database table for names.
> > > >
> > > > What are you trying to do? Might be a different way of doing it.
> > > >
> > > > Kaisa M. Lindahl Lervik
> > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > What is the best way to lookup a value in a column from another data set?
> > > > >
> > > > > I am trying to locate and display the employee name based on the employee
> > > > > id
> > > > > found in another data set.
> > > > >
> > > > > I.E.
> > > > > 13 = Terry Ward
> > > > > 14 = Peter Jackson
> > > > >
> > > > > Thank you.
> > > >
> > > >
> > > >|||Thank you for your speedy response.
However, please review the following 2 dataset queries being used.
How can I include the employee name without causes JOIN conflicts?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Ok, it sounds you are"...trying to locate and display the employee name based
> on the employee id found in another data set."
> So, it sounds like you have 1 dataset that does not return the employee name
> and another dataset that does return the employee name. Instead of trying to
> perform a lookup between the 2 datasets that you have, the best thing to do
> would be to modify the query that currently does not include the employee
> name to include the employee name in the select statement. Does that make
> more sense?
> If you want to post the 2 dataset queries that you have, that may help me
> understand your situation better.
> "Terry" wrote:
> > Do I need to create a new dataset and include the employee name in the report
> > in order to obtain the employee name along with the existing dataset called
> > IT_Projects?
> >
> > DATASET 1:
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > DATASET 2:
> >
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Matt" wrote:
> >
> > > Can you join the Task table to the emloyee table similar to the way the
> > > tblIssue table is joined to the tblEmployee table is below to return the
> > > employee name?
> > >
> > > e.g. Something like this...
> > > SELECT dbo.tblEmployee.Name
> > > FROM tblTask
> > > JOIN tblEmployee
> > > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> > >
> > > "Terry" wrote:
> > >
> > > > In the Task table, employees are identified as numeric values. References to
> > > > the actual employee names are found in the Employee table.
> > > >
> > > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > > dbo.tblIssue.Opened_By,
> > > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > > dbo.tblIssue.Closed_Date
> > > > FROM dbo.tblIssue
> > > > INNER JOIN
> > > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > > dbo.tblEmployee.Employee_ID
> > > > INNER JOIN
> > > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > > >
> > > > "Kaisa M. Lindahl Lervik" wrote:
> > > >
> > > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > > employee database table for names.
> > > > >
> > > > > What are you trying to do? Might be a different way of doing it.
> > > > >
> > > > > Kaisa M. Lindahl Lervik
> > > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > > What is the best way to lookup a value in a column from another data set?
> > > > > >
> > > > > > I am trying to locate and display the employee name based on the employee
> > > > > > id
> > > > > > found in another data set.
> > > > > >
> > > > > > I.E.
> > > > > > 13 = Terry Ward
> > > > > > 14 = Peter Jackson
> > > > > >
> > > > > > Thank you.
> > > > >
> > > > >
> > > > >

Column Sorting

I want to be able to allow a user of a report to sort the records returnedc in a table control based on the column heading they select.

ie say the report returns a list of properties as row headings then a list of cost categories as column headings with cost values as the data. I want the user to be able to click on a column heading say a cost castegory of 'Cleaning' i then want the report to order the properties by 'Cleaning Value'

Is that possoble?
cheers.... anyone...

In the June 2005 CTP, there's a new feature called Interactive Sort that does precisely what you're talking about. To access it, open up the properties of the header's text box and navigate to the Interactive Sort tab of the dialog.|||Sorry whats June 2005 CTP is it to do with SQL Reporting Services?

Regards,
Geoff|||

CTP - Community Technology Preview. It's a post-Beta release of the new Reporting Services 2005 due out this year. More information at -> http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

As far as i know, the interactive sort was not available previous to this version. I was using a previous beta release of the 2005 edition and it did not have this capability.

|||cheers for this i guess i will have to just wait!

Column index

Hi there:

Is there any way to retrieve the column index based on its name? I tried using the ColumnCollection property of the Table object, but it is not a "real" collection, so the IndexOf["MyColumnName"] doesn't exist.

I have the Database, Table, ColumnCollection and Column objects available, is there any other way I can retrieve a column's index in the table?

Thank you

Maybe this code will help - it's not exactly a look up, but it'll get you to the info pretty quickly.

Dim colTbl As TableCollection
Dim tbl As Table
Dim colIdx As IndexCollection
Dim idx As Index
db = New Database(srv, "AdventureWorks")
colTbl = db.Tables
For Each tbl In colTbl
colIdx = tbl.Indexes
For Each idx In colIdx
Console.WriteLine(idx.Name)
Next
Next

|||

Hi, Allen, thank you for your reply.

While that code would work to retrieve all indexes names in a table, my problem was retrieving the position of any column in a table based on its name. Unfortunate choice of names (index), but the code I was looking for (and doesn't work) is something like:

CollumnCollection collumnColl = table.Columns;

int columnPos = columnColl.IndexOf("MyColumnName");

It seems to me that your code would properly retrieve all indexes in a table, not necessarily all column positions, no?

Thanks again.

|||

If you create a variable of type Column, say colThisOne, you can populate it by the following statement:

colThisOne = table.Columns("MyColumnName");

Does that help? It doesn't give you the order number of the column in the table, but relational theory says that the column order doesn't matter. If it does, the best I can tell you at this point is that colThisOne.ID may have the value you're looking for.

|||

Column.ID, eh? Hmm, haven't thought that it would have a meaningful value (apart from being unique). It is an int, indeed, so it may work.

I can access the column by name, however I am trying to dynamically populate a list of properties, so the column position (while indeed irrelevant for all intents and purposes) is important for my solution. I am already working on alternative approaches, so I may not need it, but this is not a bad suggestion at all, I will try it and let you know.

Thank you!

Column headers for Matrix

I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.

Can someone help?

You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.

pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank

You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be

=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on

|||

Thank You very much

It worked!

Column headers for Matrix

I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.

Can someone help?

You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.

pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank

You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be

=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on

|||

Thank You very much

It worked!

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;|||On Dec 24, 10:41=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. =A0Does=[/vbcol
]
[vbcol=seagreen]
> this do what you want? =A0Note that I'll leave all the naming convention a=[/vbcol
]
nd[vbcol=seagreen]
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> =A0 col2 bit,
> =A0 col3 bit,
> =A0 col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> =A0 CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> =A0 CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> =A0 CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?
"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;
|||On Dec 24, 10:41Xpm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. XDoes
> this do what you want? XNote that I'll leave all the naming convention and
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> X col2 bit,
> X col3 bit,
> X col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> X CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> X CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> X CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.
|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;|||On Dec 24, 10:41=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
> > Hello all,
> > This one evades but I don't think it's possible. I thought I would
> > send it out the brain trust.
> > I have 4 columns that are bits. Is it possible to make a query that
> > will return the columns where the column is true?
> > Here is an example:
> > Col1 =A0 =A0Col2 =A0 Col3 Col4
> > t =A0 =A0 =A0 =A0 =A0 =A0t =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 f
> > t =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 t
> > Is it possible to return two separate resultsets that gives me col1,
> > col in the first query
> > col 1 and col4 in the second?
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. =A0Does=
> this do what you want? =A0Note that I'll leave all the naming convention a=nd
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> =A0 col2 bit,
> =A0 col3 bit,
> =A0 col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> =A0 CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> =A0 CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> =A0 CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Colouring the chart plot area based on x-axis scale

Hi,

I have a report based on a line chart.

The values on X-axis are scaled from -100 to +100. I want to colour the band from -100 to 0 with red colour and that from 0 to +100 in green colour, and i want to provide legend for these bands/sections. How can i do this?

This doesn't look like a tough thing to do but i can't find a way to do it within the chart properties.

Basically i would like to divide the chart (plot area) into multiple vertical bars, each having a different colour. The colour is determined by the range that vertical bar covers on the x-axis

Thanks a lot!

Simranjeev

To color the bars on the chart, go to Chart Properties -> Data -> Edit button in the Values section -> Appearance Tab -> click the Series Style button -> Fill tab

You will have to enter an expression in the color property for the bar to be one solid color. The expression should look something like:
IIF(Fields!XAxisField.Value < 0, "Red", "Green")

You can do this if the axis value is a number or date. You may have to nest a couple of IIF statements if there are a lot of sections that you want to use. I don't think you can use the legend provided by reporting services for what you want it to accomplish since it is based on the series fields. You can create your own legend with a few lines, textboxes, and property changes though.

Colouring the chart plot area based on x-axis scale

Hi,

I have a report based on a line chart.

The values on X-axis are scaled from -100 to +100. I want to colour the band from -100 to 0 with red colour and that from 0 to +100 in green colour, and i want to provide legend for these bands/sections. How can i do this?

A prompt response would be really appreciated

Thanks a lot!!

Simranjeev

Hi!

This is a little urgent so i would really appreciate some response.

This doesn't look like a tough thing to do but i can't find a way to do it within the chart properties.

Basically i would like to divide the chart (plot area) into multiple vertical bars, each having a different colour. The colour is determined by the range that vertical bar covers on the x-axis

Thanks!

Simranjeev

Colour of the series in legend

I have few chart based reports where I am showing different costs in series. I want to fix the colour of each type of cost so that I can maintain the same colour for each cost in different reports.

Is it possible? If yes, then please let me know.

Thanks in advance!!!

Check out my sample report on Brian Welcker's blog: http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert

collection name question

1 of my SQL 2000 servers has few databases that acts as backend for web based
..NET applications, most of them is in English and they have collection name:
SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db which
stores Italian text has collection name: <empty> .. my questions are what it
should be? are there any potential problems we can run into? can you change
it afterwords?
TIA
Hi Rafal,
Databases can not have an empty collation. You can check the collation for
the database in Enterprise Manager by right-clicking the database and
selecting Properties, or by executing
SELECT DATABASEPROPERTYEX('<db name>', 'Collation')
in Query Analyzer.
The only place I can think of where you might see an 'empty' collation, is
when you use the table designer in Enterprise Manager and you look at a
character column that has the default collation for the database.
Jacco Schalkwijk
SQL Server MVP
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:EA462D24-9ECE-4916-A41D-7CEDF1EE969D@.microsoft.com...
>1 of my SQL 2000 servers has few databases that acts as backend for web
>based
> .NET applications, most of them is in English and they have collection
> name:
> SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db
> which
> stores Italian text has collection name: <empty> .. my questions are what
> it
> should be? are there any potential problems we can run into? can you
> change
> it afterwords?
> TIA
>

collection name question

1 of my SQL 2000 servers has few databases that acts as backend for web base
d
.NET applications, most of them is in English and they have collection name
:
SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db whic
h
stores Italian text has collection name: <empty> .. my questions are what it
should be? are there any potential problems we can run into? can you change
it afterwords?
TIAHi Rafal,
Databases can not have an empty collation. You can check the collation for
the database in Enterprise Manager by right-clicking the database and
selecting Properties, or by executing
SELECT DATABASEPROPERTYEX('<db name>', 'Collation')
in Query Analyzer.
The only place I can think of where you might see an 'empty' collation, is
when you use the table designer in Enterprise Manager and you look at a
character column that has the default collation for the database.
Jacco Schalkwijk
SQL Server MVP
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:EA462D24-9ECE-4916-A41D-7CEDF1EE969D@.microsoft.com...
>1 of my SQL 2000 servers has few databases that acts as backend for web
>based
> .NET applications, most of them is in English and they have collection
> name:
> SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db
> which
> stores Italian text has collection name: <empty> .. my questions are what
> it
> should be? are there any potential problems we can run into? can you
> change
> it afterwords?
> TIA
>

collection name question

1 of my SQL 2000 servers has few databases that acts as backend for web based
.NET applications, most of them is in English and they have collection name:
SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db which
stores Italian text has collection name: <empty> .. my questions are what it
should be? are there any potential problems we can run into? can you change
it afterwords?
TIAHi Rafal,
Databases can not have an empty collation. You can check the collation for
the database in Enterprise Manager by right-clicking the database and
selecting Properties, or by executing
SELECT DATABASEPROPERTYEX('<db name>', 'Collation')
in Query Analyzer.
The only place I can think of where you might see an 'empty' collation, is
when you use the table designer in Enterprise Manager and you look at a
character column that has the default collation for the database.
--
Jacco Schalkwijk
SQL Server MVP
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:EA462D24-9ECE-4916-A41D-7CEDF1EE969D@.microsoft.com...
>1 of my SQL 2000 servers has few databases that acts as backend for web
>based
> .NET applications, most of them is in English and they have collection
> name:
> SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db
> which
> stores Italian text has collection name: <empty> .. my questions are what
> it
> should be? are there any potential problems we can run into? can you
> change
> it afterwords?
> TIA
>