multiple resultsets: column

Showing posts with label column. Show all posts

Wednesday, March 28, 2012

multityped column

Hi!
I have a typed xml column whith two schemas in the corresponding xml
schema collection (multityped column). Is there a simple way of
creating a new column, showing the name of the xml schema that each
row is typed to.
This is very useful to implement in cases of multiple versions of a
schema in a schema collection. Your application code could then more
easily apply schema specific logic.
I guess I could always create a computed column that uses a sql
function that figures it out, but since it seemes like a standard
problem, I first want to make sure there are no neat, built in, out of
the box solutions for this problem.
Thanks,
Paul
The easiest way to check is to use the namespace-uri() function on the
top-level elements in your XML column. There is no such built-in.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179156961.804277.146930@.y80g2000hsf.googlegr oups.com...
> Hi!
> I have a typed xml column whith two schemas in the corresponding xml
> schema collection (multityped column). Is there a simple way of
> creating a new column, showing the name of the xml schema that each
> row is typed to.
> This is very useful to implement in cases of multiple versions of a
> schema in a schema collection. Your application code could then more
> easily apply schema specific logic.
> I guess I could always create a computed column that uses a sql
> function that figures it out, but since it seemes like a standard
> problem, I first want to make sure there are no neat, built in, out of
> the box solutions for this problem.
> Thanks,
> Paul
>
|||On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegr oups.com...
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)
|||Did you define the function as schema bound? That should make it
deterministic I think.
Also, you could promote the value out on insert instead of using triggers.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179224729.491872.79810@.w5g2000hsg.googlegrou ps.com...
On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegr oups.com...
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)
|||On 20 Maj, 22:50, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you define the function as schema bound? That should make it
> deterministic I think.
> Also, you could promote the value out on insert instead of using triggers.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179224729.491872.79810@.w5g2000hsg.googlegrou ps.com...
> On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
> Thanks!
> I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
> function uses xpath with the namespace-uri() function to determine the
> namespace. I wasnt allowed to make the column persisted though because
> its "non-deterministic".
> This leaves us with a design choice:
> If we use this solution, will it result in a noticable performance
> impact? Maybe a insert/update trigger that fills the value is a more
> performance friendly solution?
> (We expect more queries than non-queries on this table)- Dlj citerad text -
> - Visa citerad text -
Thanks for all your help!
I think we will decide to promote the value out on insert, seems like
the "keep it simple and safe" -way to do it

multityped column

Hi!
I have a typed xml column whith two schemas in the corresponding xml
schema collection (multityped column). Is there a simple way of
creating a new column, showing the name of the xml schema that each
row is typed to.
This is very useful to implement in cases of multiple versions of a
schema in a schema collection. Your application code could then more
easily apply schema specific logic.
I guess I could always create a computed column that uses a sql
function that figures it out, but since it seemes like a standard
problem, I first want to make sure there are no neat, built in, out of
the box solutions for this problem.
Thanks,
PaulThe easiest way to check is to use the namespace-uri() function on the
top-level elements in your XML column. There is no such built-in.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179156961.804277.146930@.y80g2000hsf.googlegroups.com...
> Hi!
> I have a typed xml column whith two schemas in the corresponding xml
> schema collection (multityped column). Is there a simple way of
> creating a new column, showing the name of the xml schema that each
> row is typed to.
> This is very useful to implement in cases of multiple versions of a
> schema in a schema collection. Your application code could then more
> easily apply schema specific logic.
> I guess I could always create a computed column that uses a sql
> function that figures it out, but since it seemes like a standard
> problem, I first want to make sure there are no neat, built in, out of
> the box solutions for this problem.
> Thanks,
> Paul
>|||On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)|||Did you define the function as schema bound? That should make it
deterministic I think.
Also, you could promote the value out on insert instead of using triggers.
Best regards
Michael
<paululvinius@.hotmail.com> wrote in message
news:1179224729.491872.79810@.w5g2000hsg.googlegroups.com...
On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> The easiest way to check is to use the namespace-uri() function on the
> top-level elements in your XML column. There is no such built-in.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179156961.804277.146930@.y80g2000hsf.googlegroups.com...
>
>
>
>
>
>
> - Visa citerad text -
Thanks!
I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
function uses xpath with the namespace-uri() function to determine the
namespace. I wasnt allowed to make the column persisted though because
its "non-deterministic".
This leaves us with a design choice:
If we use this solution, will it result in a noticable performance
impact? Maybe a insert/update trigger that fills the value is a more
performance friendly solution?
(We expect more queries than non-queries on this table)|||On 20 Maj, 22:50, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you define the function as schema bound? That should make it
> deterministic I think.
> Also, you could promote the value out on insert instead of using triggers.
> Best regards
> Michael
> <paululvin...@.hotmail.com> wrote in message
> news:1179224729.491872.79810@.w5g2000hsg.googlegroups.com...
> On 14 Maj, 21:58, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> Thanks!
> I used a computed column like: "dbo.GetNamespaceUri(MyXmlColumn)". The
> function uses xpath with the namespace-uri() function to determine the
> namespace. I wasnt allowed to make the column persisted though because
> its "non-deterministic".
> This leaves us with a design choice:
> If we use this solution, will it result in a noticable performance
> impact? Maybe a insert/update trigger that fills the value is a more
> performance friendly solution?
> (We expect more queries than non-queries on this table)- D=F6lj citerad t=
ext -
> - Visa citerad text -
Thanks for all your help!
I think we will decide to promote the value out on insert, seems like
the "keep it simple and safe" -way to do it :)

Wednesday, March 21, 2012

Multiple values in one column

I'm trying to write a query which allows that multiple values from one
column are placed in one record.

ex:
table
NrLetters
1A
2A
2B
2C
3A
3B
3C
3D
3E
4A

The result I want to get from an select:
NrAll Letters
1A
2A, B, C
3A, B, C, D, E
4A

OlivierOlivier (olivier.lammens@.belgacom.be) writes:
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A

You cannot do this in a single query in SQL 2000. And this is not a
thing you are supposed to do in a relational database, because repeating
values in a column is not supposed to occur.

You can achieve it by an iterative solution and aggregat into a temp
table. (Some people may suggest shortcuts for this, but they are not
reliable.) But in essence, this is really something that should be performed
on the client side.

On a side note, this is actually possible to do in SQL2005, currently in
beta, by using some XML extensions. But it still not really a relational
thing to do, so the client is still the best place for the work.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||olivier.lammens@.belgacom.be (Olivier) wrote in message news:<66db75c6.0412020207.4eb3a569@.posting.google.com>...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> NrLetters
> 1A
> 2A
> 2B
> 2C
> 3A
> 3B
> 3C
> 3D
> 3E
> 4A
> The result I want to get from an select:
> NrAll Letters
> 1A
> 2A, B, C
> 3A, B, C, D, E
> 4A
> Olivier

Hey, man!
Try to have a look at http://www.aspfaq.com/show.asp?id=2529
You have to write a UDF to concatenate first.
HTH|||"Olivier" <olivier.lammens@.belgacom.be> wrote in message news:66db75c6.0412020207.4eb3a569@.posting.google.c om...
> I'm trying to write a query which allows that multiple values from one
> column are placed in one record.
> ex:
> table
> Nr Letters
> 1 A
> 2 A
> 2 B
> 2 C
> 3 A
> 3 B
> 3 C
> 3 D
> 3 E
> 4 A
> The result I want to get from an select:
> Nr All Letters
> 1 A
> 2 A, B, C
> 3 A, B, C, D, E
> 4 A
> Olivier

Just get a copy of iAnywhere's Adaptive Server Anywhere (ASA), which includes the List() aggregate function (and has had
it for about 8 years).

-Paul-|||The result you want isn't much use in a database - it's really a
formatted report for display or printing. For this reason it's probably
best done in your presentation tier rather than in SQL. If you have no
other option, however, you may be able to do something like this:

SELECT T.nr,
MAX(CASE WHEN seq=1 THEN letter END)+
MAX(CASE WHEN seq=2 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=3 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=4 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=5 THEN ','+letter ELSE '' END)+
MAX(CASE WHEN seq=6 THEN ','+letter ELSE '' END)
/* ... etc ... */
FROM
(SELECT T1.nr, T1.letter, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.nr = T2.nr
AND T1.letter >= T2.letter
GROUP BY T1.nr, T1.letter) AS T
GROUP BY T.nr

I'm assuming here that (nr,letter) is unique and non-NULL.
--
David Portas
SQL Server MVP
--

multiple values for single column in where clause

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')

if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

See BOL for more.|||yup, thanks dudesql

Monday, March 19, 2012

multiple updates to table

Hello all,

I have a parallel process updating a single table from various sources using update table statements using a key column.

I'm afraid the process will fail when an update will occur to a record with the same key simultaneously.

Does anyone have a suggestion how to accomplish this? Is there a way timing the updates in queue?

Thanks.

There is no concurrent update in SQL Server, DML statement are queued and executed one by one (if they are not in a transaction). There are sure scenarios which would lead to concurrency conflicts, but this has to be handled by your frontedn application.

HTH, Jens Suessmeyer.'

http://www.sqlserver2005.de

|||

I assume you have multiple clients pumping data into a single table, right?

The next question is what you mean by key? Like an identity key? If so, that won't happen. SQL Server single threads identity key generation so that no two rows will get the same key.

Multiple updates and Identity fields

I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What is\are the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.

TIA,

Barkingdog

Are you using SQL2k5 ? THen you can use the new OUTPUT clause. Otherwise you should have a look on SCOPE_IDENTITY() which should fit your needs. But the new OUTPUT function should be more straight forward in your case.

HTH, JEns K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens,

I looked up OUTPUT in sql 2k5 BOL: Here's an example that I found:

>>>
Copy Code
USE AdventureWorks;
GO
DECLARE @.MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @.MyTableVar
VALUES (N'Operator error', GETDATE());

--1. Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @.MyTableVar;

--2. Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
>>>>

So with OUTPUT the ScrapReasonID from @.MyTableVar will hold the identity of the row just inserted. Is this correct?

What is the difference in output between queries 1 and 2? Shouldn't they be the same?

TIA,

Barkingdog

Monday, March 12, 2012

Multiple tables in dataset.

I have a table that has a single column that contains the name of other tables. I would like to form a DataSet that has the contents of this table as well as the contents of each of the tables listed by name in this table. For example I have a table:

TableList

TableA
TableB
TableC

TableA

Key1 Description1
Key2 Description2

. . .

I am thinking something like:

SELECT * FROM (SELECT * FROM TableList)

But this doesn't work

SELECT * FROM TableA

Unfortunately, the way these table are set up, you'll have explicity determine the table.

...or you could always loop through each entry for TableList.

Adamus

|||

Would you mind giving me a sample of "looping through the table list"? I am not aware of how to code this and then return the resultant data set?

Thank you.

Kevin

Friday, March 9, 2012

Multiple Table Problem

Hi and Thanks to all in advance for help,
I have 2 tables. What we want to do is grab the columns listed and then
if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
items that have ONHAND(int) in the other table (STOCK) is greater than
0.
SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
KEYWORDITEMS
WHERE
STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
AND
STOCK.WH = 1
AND
(Here is the confusion)
What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
I attempted to use CASE statements but it was not working because it
wanted a result if the condition was false. I am sure I didn't do it
right though.
Other things I am trying to do:
CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only
the returned result that is to become the new table) = "BEST DEAL" ELSE
''.
I would like to drop all STOCK columns after it is returned except
[DESCRIPTION] and [STNS]
Thanks again for your help.
Kelly"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
> the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
>
Try:
AND (ProductGroupNumber < 4999 OR Stock.OnHand >0)
JBK|||How are the two tables related? What does their DDL (create table) statement
s
look like?
Thomas
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
the
> PRODUCTGROUP(int) column is larger than 4999 we only want to grab items th
at
> have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD, STOCK.ITEMNUMB
ER,
> STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK, KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it wante
d a
> result if the condition was false. I am sure I didn't do it right though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only the
> returned result that is to become the new table) = "BEST DEAL" ELSE ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>|||I don't understand exactly what you are trying to do. Part of my confusion
is caused by your incomplete select statement. Take a look at this example
that I wrote and see if it gets you pointed in the right direction.
USE Northwind
GO
SELECT A.OrderID, A.CustomerID, A.EmployeeID,
A.OrderDate, A.RequiredDate, A.ShippedDate,
'EmployeeORShip' = CASE WHEN A.ShippedDate IS NULL THEN 'emp-' +
C.FirstName + ' ' + C.LastName ELSE 'shp-' + B.ContactName END
FROM Orders A
JOIN Customers B ON A.CustomerID = B.CustomerID
JOIN Employees C ON A.EmployeeID = C.EmployeeID
Keith
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:ecLPziATFHA.3464@.tk2msftngp13.phx.gbl...
> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then if
> the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it
> wanted a result if the condition was false. I am sure I didn't do it right
> though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only the
> returned result that is to become the new table) = "BEST DEAL" ELSE ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>|||Try,
SELECT
PRODUCTGROUPNUMBER,
ITEMNO,
STOCK.[DESCRIPTION],
KYWD,
STOCK.ITEMNUMBER,
STOCK.WH,
STOCK.ONHAND,
STOCK.STNS
FROM
STOCK
inner join
KEYWORDITEMS
on STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO AND STOCK.WH = 1
WHERE
(STOCK.ONHAND > 0 and PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999)
or
PRODUCTGROUP.PRODUCTGROUPNUMBER <= 4999
AMB
"scorpion53061" wrote:

> Hi and Thanks to all in advance for help,
> I have 2 tables. What we want to do is grab the columns listed and then
> if the PRODUCTGROUP(int) column is larger than 4999 we only want to grab
> items that have ONHAND(int) in the other table (STOCK) is greater than
> 0.
> SELECT PRODUCTGROUPNUMBER, ITEMNO, STOCK.DESCRIPTION, KYWD,
> STOCK.ITEMNUMBER, STOCK.WH, STOCK.ONHAND, STOCK.STNS FROM STOCK,
> KEYWORDITEMS
> WHERE
> STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO
> AND
> STOCK.WH = 1
> AND
> (Here is the confusion)
> What I am trying to say is only accept the row if STOCK.ONHAND > 0 when
> PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999;
> If PRODUCTGROUPNUMBER is below 4999 this rule should be ignored.
> I attempted to use CASE statements but it was not working because it
> wanted a result if the condition was false. I am sure I didn't do it
> right though.
> Other things I am trying to do:
> CASE STOCK.STNS = "OBS" Then STOCK.STNS (don't change the column only
> the returned result that is to become the new table) = "BEST DEAL" ELSE
> ''.
> I would like to drop all STOCK columns after it is returned except
> [DESCRIPTION] and [STNS]
>
> Thanks again for your help.
> Kelly
>
>|||I love what you did here though I have run the query for 10 minutes and
it is still running.
Here is what I have now with the corrected table names and fields....
SELECT
DISTINCT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM
STOCK2
inner join
KEYWORDITEMS11
on STOCK2.NUMBER = KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE
(STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
or
KEYWordITEMS11.PRODUCTGROUP <= 4999
ORDER By PRODUCTGROUP, ITEMNO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message news:AlejandroMesa@.discussions.microsoft.com:
> Try,
> SELECT
> PRODUCTGROUPNUMBER,
> ITEMNO,
> STOCK.[DESCRIPTION],
> KYWD,
> STOCK.ITEMNUMBER,
> STOCK.WH,
> STOCK.ONHAND,
> STOCK.STNS
> FROM
> STOCK
> inner join
> KEYWORDITEMS
> on STOCK.ITEMNUMBER = PRODUCTGROUP.ITEMNO AND STOCK.WH = 1
> WHERE
> (STOCK.ONHAND > 0 and PRODUCTGROUP.PRODUCTGROUPNUMBER > 4999)
> or
> PRODUCTGROUP.PRODUCTGROUPNUMBER <= 4999
>
> AMB
>
> "scorpion53061" wrote:
>|||You might get a better perf if you convert your "DISTINCT" and "OR" to
"UNION" query.
e.g.
SELECT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM STOCK2 inner join KEYWORDITEMS11 on STOCK2.NUMBER =
KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE (STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
UNION
SELECT
PRODUCTGROUP,
ITEMNO,
STOCK2.[DESCRIPTION],
KYWD,
STOCK2.NUMBER,
STOCK2.WH,
STOCK2.ONHAND,
STOCK2.STNS
FROM STOCK2 inner join KEYWORDITEMS11 on STOCK2.NUMBER =
KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
WHERE KEYWordITEMS11.PRODUCTGROUP <= 4999
ORDER By PRODUCTGROUP, ITEMNO
-oj
"scorpion53061" <scorpion_nospamhere53061@.yahoo.com> wrote in message
news:%23BTFhOBTFHA.2872@.TK2MSFTNGP14.phx.gbl...
>I love what you did here though I have run the query for 10 minutes and it
>is still running.
> Here is what I have now with the corrected table names and fields....
> SELECT
> DISTINCT
> PRODUCTGROUP,
> ITEMNO,
> STOCK2.[DESCRIPTION],
> KYWD,
> STOCK2.NUMBER,
> STOCK2.WH,
> STOCK2.ONHAND,
> STOCK2.STNS
> FROM
> STOCK2
> inner join
> KEYWORDITEMS11
> on STOCK2.NUMBER = KEYWORDITEMS11.ITEMNO AND STOCK2.WH = 1
> WHERE
> (STOCK2.ONHAND > 0 and KEYWORDITEMS11.PRODUCTGROUP > 4999)
> or
> KEYWordITEMS11.PRODUCTGROUP <= 4999
> ORDER By PRODUCTGROUP, ITEMNO
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:AlejandroMesa@.discussions.microsoft.com:
>

Multiple Stored Procedure Execute Together - How?

Hi,
I have multiple stored procedures (SP) running in multiple databases. The
output of all the databases has same column names, same number of columns an
d
column types.
I want to run all these different SP's as one SP and combine the output as
one result.
I tried creating one new SP as SPAll and calling all the SP's in SPAll.
eg.
Create SPAll @.Parameter int
AS
Exec SP1 @.Parameter
Exec SP2 @.Parameter
GO
But when I execute SPAll from 'sql server reporting services' (vs.net), it
executes and displays results from SP1 also. I want to display results from
both SP1 and SP2.
Thanks in advance.Hello GJ.
execute the stored procedures saving the result sets to a temporary table
and then select the results from the temporary table to give you a single
result set
IE:
use Northwind
GO
set nocount on
if object_id('tempdb..#results') is not null drop table #results
create table #results(
index_name sysname,/* Index name. */
index_description varchar(210),/* Index description. */
index_keys nvarchar(2078)/* Table or view column(s) upon which the index is
built. */
)
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Categories'
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Customers'
select * from #results
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"GJ" wrote:

> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results fro
m
> both SP1 and SP2.
> Thanks in advance.|||Best way, based on what you are telling us:
Create SPAll @.Parameter int
AS
create table #spAllReturn
(
<columns that match procs>
)
insert into #spAllReturn
Exec SP1 @.Parameter
insert into #spAllReturn
Exec SP2 @.Parameter
select * from #spAllReturn
GO
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
> and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
> from
> both SP1 and SP2.
> Thanks in advance.|||GJ
create table #test
(
col ...
....
....
)
insert into #test exec sp1
insert into #test exec sp2
select * from #test
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
from
> both SP1 and SP2.
> Thanks in advance.

Saturday, February 25, 2012

Multiple selects on same column, same table, one query

I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?

For example, how would combine these two selects into one query that will
list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY locationYou can use subqueries like this:

select location,
actioncount1 = (select count(actions)
from mytable T1
where T1.location = mytable.location
),
actioncount2 = (select count(actions)
from mytable T2
where mycondition is null
and T2.location = mytable.location
)
from mytable
group by location

Shervin

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> I want to get a column count several times in one query using different
> filters but can't work out how to do it - can anyone point me in the right
> direction?
> For example, how would combine these two selects into one query that will
> list the total and filtered actions:
> SELECT COUNT(actions) as actioncount, location
> FROM mytable
> GROUP BY location
> SELECT COUNT(actions) as actioncount, location
> FROM mytable
> WHERE mycondition IS NULL
> GROUP BY location|||SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
actioncount1,
COUNT(actions) AS actioncount2,
location
FROM mytable
GROUP BY location

--
David Portas
----
Please reply only to the newsgroup|||Nice! :-)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.giganews.com...
> SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
> actioncount1,
> COUNT(actions) AS actioncount2,
> location
> FROM mytable
> GROUP BY location
> --
> David Portas
> ----
> Please reply only to the newsgroup|||Thanks Shervin,
Works exactly as I need, I am indebted!
Cheers,
Jack
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:vn6fu8bgg3qt64@.corp.supernews.com...
> You can use subqueries like this:
> select location,
> actioncount1 = (select count(actions)
> from mytable T1
> where T1.location = mytable.location
> ),
> actioncount2 = (select count(actions)
> from mytable T2
> where mycondition is null
> and T2.location = mytable.location
> )
> from mytable
> group by location
> Shervin
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> > I want to get a column count several times in one query using different
> > filters but can't work out how to do it - can anyone point me in the
right
> > direction?
> > For example, how would combine these two selects into one query that
will
> > list the total and filtered actions:
> > SELECT COUNT(actions) as actioncount, location
> > FROM mytable
> > GROUP BY location
> > SELECT COUNT(actions) as actioncount, location
> > FROM mytable
> > WHERE mycondition IS NULL
> > GROUP BY location|||You're welcome my friend, but take a look at David's code. I prefer his way
:-) Subqueries are not the most efficient way to do thing in most cases. But
they are so easy to use. They are for lazy guys like me ;-)

Shervin

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:vpHcb.266$%f1.56@.newsfep1-gui.server.ntli.net...
> Thanks Shervin,
> Works exactly as I need, I am indebted!
> Cheers,
> Jack
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:vn6fu8bgg3qt64@.corp.supernews.com...
> > You can use subqueries like this:
> > select location,
> > actioncount1 = (select count(actions)
> > from mytable T1
> > where T1.location = mytable.location
> > ),
> > actioncount2 = (select count(actions)
> > from mytable T2
> > where mycondition is null
> > and T2.location = mytable.location
> > )
> > from mytable
> > group by location
> > Shervin
> > "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> > news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> > > I want to get a column count several times in one query using
different
> > > filters but can't work out how to do it - can anyone point me in the
> right
> > > direction?
> > > > For example, how would combine these two selects into one query that
> will
> > > list the total and filtered actions:
> > > > SELECT COUNT(actions) as actioncount, location
> > > FROM mytable
> > > GROUP BY location
> > > > SELECT COUNT(actions) as actioncount, location
> > > FROM mytable
> > > WHERE mycondition IS NULL
> > > GROUP BY location
> >|||Wow!
Cheers,
Jack

Multiple sections in report?

Perhaps I'm only overlooking something very simple?
I want to display different columns from my resultset, depending of the data
in one column. I also want to apply different styles depending on that
"controlling" column. I understand that I in a Table can define attributes
for the whole row (same as when you select the "left" portion of a row),
which can be based of the value of that row. But what if I want to display
different columns, based on the data?
Example:
* A dataset with a datetime column. We want to sort by this in the report.
Earliest event at the top.
* Each row represent different activities over the day. One column define
type of activity.
* Depending of the type of activity, I want to display different columns
from the dataset. Ideally, I want to control horizontal position depending
on the type of activity.
I believe that Crystal is using the term "Multiple Sections" for this, but
I'm not sure about the terminology.
Thanks
TiborTibor,
You can base the column field on a expression,e.g.
=Iif(Fields!A.Value=<expression>, Fields!B.Value, Fields!C.Value).
<expression> can reference to a field value or call to an embedded or
external function.
In addition, you can implement conditional formatting, e.g. identity the
column value based on the condition, coloring cells, etc.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com : http://tinyurl.com/3l49j
Home page and blog: http://www.prologika.com/
----
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Perhaps I'm only overlooking something very simple?
> I want to display different columns from my resultset, depending of the
data
> in one column. I also want to apply different styles depending on that
> "controlling" column. I understand that I in a Table can define attributes
> for the whole row (same as when you select the "left" portion of a row),
> which can be based of the value of that row. But what if I want to display
> different columns, based on the data?
> Example:
> * A dataset with a datetime column. We want to sort by this in the report.
> Earliest event at the top.
> * Each row represent different activities over the day. One column define
> type of activity.
> * Depending of the type of activity, I want to display different columns
> from the dataset. Ideally, I want to control horizontal position depending
> on the type of activity.
> I believe that Crystal is using the term "Multiple Sections" for this, but
> I'm not sure about the terminology.
> Thanks
> Tibor
>|||Thanks Teo,
I understand those options, but it will become too much of a mess as you want to do more changes.
I had problems understanding what the customer wanted until he showed me this in Crystal. Imagine that you
have several sections (like page header/body/footer). In effect, you have several bodies, and position the
columns freely into each section. In section A I want column x and z, with this formatting. In section B I
want columns z and y and g, having the desired formatting. Etc. Then you say a condition which determines
which section is to be used for the row data. For example, if the value for column A is this, use section A
for the row. If the value is B, use section F.
I very much doubt this can be done using the table control, as you cannot align columns conditionally (column
width etc based on a value in the row). Methinks.
Possibly by just positioning out text boxes etc, but I can't really see how you can define several "sections",
and control which section is to be used for the row data.
I have a feeling this cannot be done using current version of RS. This is fine, considering it is a 1.0
product after all. The customer understand this as well. I just wanted to see whether I missed something.
(None of the three books I have mention anything along this line. I recall asking for your book as well, but
the store didn't have it. Looking forward to go through that too when I get my hands on it. :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> Tibor,
> You can base the column field on a expression,e.g.
> =Iif(Fields!A.Value=<expression>, Fields!B.Value, Fields!C.Value).
> <expression> can reference to a field value or call to an embedded or
> external function.
> In addition, you can implement conditional formatting, e.g. identity the
> column value based on the condition, coloring cells, etc.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com : http://tinyurl.com/3l49j
> Home page and blog: http://www.prologika.com/
> ----
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Perhaps I'm only overlooking something very simple?
> >
> > I want to display different columns from my resultset, depending of the
> data
> > in one column. I also want to apply different styles depending on that
> > "controlling" column. I understand that I in a Table can define attributes
> > for the whole row (same as when you select the "left" portion of a row),
> > which can be based of the value of that row. But what if I want to display
> > different columns, based on the data?
> >
> > Example:
> > * A dataset with a datetime column. We want to sort by this in the report.
> > Earliest event at the top.
> > * Each row represent different activities over the day. One column define
> > type of activity.
> > * Depending of the type of activity, I want to display different columns
> > from the dataset. Ideally, I want to control horizontal position depending
> > on the type of activity.
> >
> > I believe that Crystal is using the term "Multiple Sections" for this, but
> > I'm not sure about the terminology.
> >
> > Thanks
> > Tibor
> >
> >
>|||Tibor,
Looks like nested list regions may do the trick for you, where the outermost
list region will display the dataset rows, while the inner list regions are
the sections which visibility could be changed conditionally. Have you
explored this opton?
BTW, my book is shipping... see the report footer.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com : http://tinyurl.com/3l49j
Home page and blog: http://www.prologika.com/
----
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eymIZXFjEHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thanks Teo,
> I understand those options, but it will become too much of a mess as you
want to do more changes.
> I had problems understanding what the customer wanted until he showed me
this in Crystal. Imagine that you
> have several sections (like page header/body/footer). In effect, you have
several bodies, and position the
> columns freely into each section. In section A I want column x and z, with
this formatting. In section B I
> want columns z and y and g, having the desired formatting. Etc. Then you
say a condition which determines
> which section is to be used for the row data. For example, if the value
for column A is this, use section A
> for the row. If the value is B, use section F.
> I very much doubt this can be done using the table control, as you cannot
align columns conditionally (column
> width etc based on a value in the row). Methinks.
> Possibly by just positioning out text boxes etc, but I can't really see
how you can define several "sections",
> and control which section is to be used for the row data.
> I have a feeling this cannot be done using current version of RS. This is
fine, considering it is a 1.0
> product after all. The customer understand this as well. I just wanted to
see whether I missed something.
> (None of the three books I have mention anything along this line. I recall
asking for your book as well, but
> the store didn't have it. Looking forward to go through that too when I
get my hands on it. :-) ).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> > Tibor,
> >
> > You can base the column field on a expression,e.g.
> > =Iif(Fields!A.Value=<expression>, Fields!B.Value, Fields!C.Value).
> > <expression> can reference to a field value or call to an embedded or
> > external function.
> >
> > In addition, you can implement conditional formatting, e.g. identity the
> > column value based on the condition, coloring cells, etc.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > Perhaps I'm only overlooking something very simple?
> > >
> > > I want to display different columns from my resultset, depending of
the
> > data
> > > in one column. I also want to apply different styles depending on that
> > > "controlling" column. I understand that I in a Table can define
attributes
> > > for the whole row (same as when you select the "left" portion of a
row),
> > > which can be based of the value of that row. But what if I want to
display
> > > different columns, based on the data?
> > >
> > > Example:
> > > * A dataset with a datetime column. We want to sort by this in the
report.
> > > Earliest event at the top.
> > > * Each row represent different activities over the day. One column
define
> > > type of activity.
> > > * Depending of the type of activity, I want to display different
columns
> > > from the dataset. Ideally, I want to control horizontal position
depending
> > > on the type of activity.
> > >
> > > I believe that Crystal is using the term "Multiple Sections" for this,
but
> > > I'm not sure about the terminology.
> > >
> > > Thanks
> > > Tibor
> > >
> > >
> >
> >
>|||The report object model collections (e.g. Fields, Parameters) allow you to
perform dynamic access by using field names that are determined dynamically
at runtime.
E.g.
=Fields(Fields!FieldName.Value).Value
=Fields(Parameters!P1.Value).Value
You can even use complex expressions to generate field names.
Is this what you are looking for?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Perhaps I'm only overlooking something very simple?
> I want to display different columns from my resultset, depending of the
data
> in one column. I also want to apply different styles depending on that
> "controlling" column. I understand that I in a Table can define attributes
> for the whole row (same as when you select the "left" portion of a row),
> which can be based of the value of that row. But what if I want to display
> different columns, based on the data?
> Example:
> * A dataset with a datetime column. We want to sort by this in the report.
> Earliest event at the top.
> * Each row represent different activities over the day. One column define
> type of activity.
> * Depending of the type of activity, I want to display different columns
> from the dataset. Ideally, I want to control horizontal position depending
> on the type of activity.
> I believe that Crystal is using the term "Multiple Sections" for this, but
> I'm not sure about the terminology.
> Thanks
> Tibor
>|||> Is this what you are looking for?
Not really, I'm afraid. Thing is you have to "program" all this (using expressions). Compare that to having
several sections (several bodies) where you *visually* layout the elements (text-boxes, possibly data-bound,
defining font style etc). Then you just state that if col1 = a then use section z for the row, if col1 = b
then use section y for the row, etc.
You might want to check out how Crystal is doing it. I had a hard time understand until my customer showed me.
After that I clearly understood. A really neat feature.
Thanks for the reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u0bze$KjEHA.3696@.TK2MSFTNGP15.phx.gbl...
> The report object model collections (e.g. Fields, Parameters) allow you to
> perform dynamic access by using field names that are determined dynamically
> at runtime.
> E.g.
> =Fields(Fields!FieldName.Value).Value
> =Fields(Parameters!P1.Value).Value
> You can even use complex expressions to generate field names.
> Is this what you are looking for?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Perhaps I'm only overlooking something very simple?
> >
> > I want to display different columns from my resultset, depending of the
> data
> > in one column. I also want to apply different styles depending on that
> > "controlling" column. I understand that I in a Table can define attributes
> > for the whole row (same as when you select the "left" portion of a row),
> > which can be based of the value of that row. But what if I want to display
> > different columns, based on the data?
> >
> > Example:
> > * A dataset with a datetime column. We want to sort by this in the report.
> > Earliest event at the top.
> > * Each row represent different activities over the day. One column define
> > type of activity.
> > * Depending of the type of activity, I want to display different columns
> > from the dataset. Ideally, I want to control horizontal position depending
> > on the type of activity.
> >
> > I believe that Crystal is using the term "Multiple Sections" for this, but
> > I'm not sure about the terminology.
> >
> > Thanks
> > Tibor
> >
> >
>|||Interesting. I'll check it out. I guess I have to look into whether a list region can display rows like a
table does it. Seems I have some reading up to do. Any pointers where I can find some basic steps for how to
accomplish nested list regions?
Judging by the publishers website, seems your book is one notch up from the basic books? (I prefer to buy
books from a physical book store, to support them. I'd hate to see all them go out of business. But where I
can't find a book, yes, I do buy it off the net. Yours is on the list. :-) )
Thanks!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message news:el71IoFjEHA.384@.TK2MSFTNGP10.phx.gbl...
> Tibor,
> Looks like nested list regions may do the trick for you, where the outermost
> list region will display the dataset rows, while the inner list regions are
> the sections which visibility could be changed conditionally. Have you
> explored this opton?
> BTW, my book is shipping... see the report footer.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com : http://tinyurl.com/3l49j
> Home page and blog: http://www.prologika.com/
> ----
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eymIZXFjEHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Thanks Teo,
> >
> > I understand those options, but it will become too much of a mess as you
> want to do more changes.
> >
> > I had problems understanding what the customer wanted until he showed me
> this in Crystal. Imagine that you
> > have several sections (like page header/body/footer). In effect, you have
> several bodies, and position the
> > columns freely into each section. In section A I want column x and z, with
> this formatting. In section B I
> > want columns z and y and g, having the desired formatting. Etc. Then you
> say a condition which determines
> > which section is to be used for the row data. For example, if the value
> for column A is this, use section A
> > for the row. If the value is B, use section F.
> >
> > I very much doubt this can be done using the table control, as you cannot
> align columns conditionally (column
> > width etc based on a value in the row). Methinks.
> >
> > Possibly by just positioning out text boxes etc, but I can't really see
> how you can define several "sections",
> > and control which section is to be used for the row data.
> >
> > I have a feeling this cannot be done using current version of RS. This is
> fine, considering it is a 1.0
> > product after all. The customer understand this as well. I just wanted to
> see whether I missed something.
> > (None of the three books I have mention anything along this line. I recall
> asking for your book as well, but
> > the store didn't have it. Looking forward to go through that too when I
> get my hands on it. :-) ).
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> > > Tibor,
> > >
> > > You can base the column field on a expression,e.g.
> > > =Iif(Fields!A.Value=<expression>, Fields!B.Value, Fields!C.Value).
> > > <expression> can reference to a field value or call to an embedded or
> > > external function.
> > >
> > > In addition, you can implement conditional formatting, e.g. identity the
> > > column value based on the condition, coloring cells, etc.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ----
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > Home page and blog: http://www.prologika.com/
> > > ----
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > > Perhaps I'm only overlooking something very simple?
> > > >
> > > > I want to display different columns from my resultset, depending of
> the
> > > data
> > > > in one column. I also want to apply different styles depending on that
> > > > "controlling" column. I understand that I in a Table can define
> attributes
> > > > for the whole row (same as when you select the "left" portion of a
> row),
> > > > which can be based of the value of that row. But what if I want to
> display
> > > > different columns, based on the data?
> > > >
> > > > Example:
> > > > * A dataset with a datetime column. We want to sort by this in the
> report.
> > > > Earliest event at the top.
> > > > * Each row represent different activities over the day. One column
> define
> > > > type of activity.
> > > > * Depending of the type of activity, I want to display different
> columns
> > > > from the dataset. Ideally, I want to control horizontal position
> depending
> > > > on the type of activity.
> > > >
> > > > I believe that Crystal is using the term "Multiple Sections" for this,
> but
> > > > I'm not sure about the terminology.
> > > >
> > > > Thanks
> > > > Tibor
> > > >
> > > >
> > >
> > >
> >
> >
>|||Tibor,
I attached a sample report that demonstrates nested lists. The Adventure
Works database has two types of customers: stores and individuals. The idea
here is that if the customer is individual the report will show his first
and last name. If the customer is store, store-specific details are shown.
This is accomplished by having two overlapping lists lstIndividual and
lstStore nested inside lstCustomer.
Not sure I get the idea of the book support that the brick-and-mortar stores
have but online don't. Please educate me on this subject some more so I
could pass it along to the publisher. Do you mean to return the book?
Hopefully, you won't have to. And yes, I tried my best to keep it above
basic :-)
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OaaxQZQjEHA.3476@.tk2msftngp13.phx.gbl...
> Interesting. I'll check it out. I guess I have to look into whether a list
region can display rows like a
> table does it. Seems I have some reading up to do. Any pointers where I
can find some basic steps for how to
> accomplish nested list regions?
> Judging by the publishers website, seems your book is one notch up from
the basic books? (I prefer to buy
> books from a physical book store, to support them. I'd hate to see all
them go out of business. But where I
> can't find a book, yes, I do buy it off the net. Yours is on the list.
:-) )
> Thanks!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:el71IoFjEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Tibor,
> >
> > Looks like nested list regions may do the trick for you, where the
outermost
> > list region will display the dataset rows, while the inner list regions
are
> > the sections which visibility could be changed conditionally. Have you
> > explored this opton?
> >
> > BTW, my book is shipping... see the report footer.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:eymIZXFjEHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Teo,
> > >
> > > I understand those options, but it will become too much of a mess as
you
> > want to do more changes.
> > >
> > > I had problems understanding what the customer wanted until he showed
me
> > this in Crystal. Imagine that you
> > > have several sections (like page header/body/footer). In effect, you
have
> > several bodies, and position the
> > > columns freely into each section. In section A I want column x and z,
with
> > this formatting. In section B I
> > > want columns z and y and g, having the desired formatting. Etc. Then
you
> > say a condition which determines
> > > which section is to be used for the row data. For example, if the
value
> > for column A is this, use section A
> > > for the row. If the value is B, use section F.
> > >
> > > I very much doubt this can be done using the table control, as you
cannot
> > align columns conditionally (column
> > > width etc based on a value in the row). Methinks.
> > >
> > > Possibly by just positioning out text boxes etc, but I can't really
see
> > how you can define several "sections",
> > > and control which section is to be used for the row data.
> > >
> > > I have a feeling this cannot be done using current version of RS. This
is
> > fine, considering it is a 1.0
> > > product after all. The customer understand this as well. I just wanted
to
> > see whether I missed something.
> > > (None of the three books I have mention anything along this line. I
recall
> > asking for your book as well, but
> > > the store didn't have it. Looking forward to go through that too when
I
> > get my hands on it. :-) ).
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> > > > Tibor,
> > > >
> > > > You can base the column field on a expression,e.g.
> > > > =Iif(Fields!A.Value=<expression>, Fields!B.Value, Fields!C.Value).
> > > > <expression> can reference to a field value or call to an embedded
or
> > > > external function.
> > > >
> > > > In addition, you can implement conditional formatting, e.g. identity
the
> > > > column value based on the condition, coloring cells, etc.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ----
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > > Home page and blog: http://www.prologika.com/
> > > > ----
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > > message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > > > Perhaps I'm only overlooking something very simple?
> > > > >
> > > > > I want to display different columns from my resultset, depending
of
> > the
> > > > data
> > > > > in one column. I also want to apply different styles depending on
that
> > > > > "controlling" column. I understand that I in a Table can define
> > attributes
> > > > > for the whole row (same as when you select the "left" portion of a
> > row),
> > > > > which can be based of the value of that row. But what if I want to
> > display
> > > > > different columns, based on the data?
> > > > >
> > > > > Example:
> > > > > * A dataset with a datetime column. We want to sort by this in the
> > report.
> > > > > Earliest event at the top.
> > > > > * Each row represent different activities over the day. One column
> > define
> > > > > type of activity.
> > > > > * Depending of the type of activity, I want to display different
> > columns
> > > > > from the dataset. Ideally, I want to control horizontal position
> > depending
> > > > > on the type of activity.
> > > > >
> > > > > I believe that Crystal is using the term "Multiple Sections" for
this,
> > but
> > > > > I'm not sure about the terminology.
> > > > >
> > > > > Thanks
> > > > > Tibor
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
begin 666 ListDemo.rdl
M/#]X;6P@.=F5R<VEO;CTB,2XP(B!E;F-O9&EN9STB=71F+3@.B/SX-"CQ297!O
M<G0@.>&UL;G,](FAT=' Z+R]S8VAE;6%S+FUI8W)O<V]F="YC;VTO<W%L<V5R
M=F5R+W)E<&]R=&EN9R\R,# S+S$P+W)E<&]R=&1E9FEN:71I;VXB('AM;&YS
M.G)D/2)H='1P.B\O<V-H96UA<RYM:6-R;W-O9G0N8V]M+U-13%-E<G9E<B]R
M97!O<G1I;F<O<F5P;W)T9&5S:6=N97(B/@.T*(" \4FEG:'1-87)G:6X^,6EN
M/"]2:6=H=$UA<F=I;CX-"B @./$)O9'D^#0H@.(" @./%)E<&]R=$ET96US/@.T*
M(" @.(" @./$QI<W0@.3F%M93TB;&ES=$]U=&5R(CX-"B @.(" @.(" @./%-T>6QE
M("\^#0H@.(" @.(" @.(#Q(96EG:'0^,"XR-6EN/"](96EG:'0^#0H@.(" @.(" @.
M(#Q4;W ^,"XQ,C5I;CPO5&]P/@.T*(" @.(" @.(" \1W)O=7!I;F<@.3F%M93TB
M;&ES=$]U=&5R7T1E=&%I;'-?1W)O=7 B/@.T*(" @.(" @.(" @.(#Q'<F]U<$5X
M<')E<W-I;VYS/@.T*(" @.(" @.(" @.(" @./$=R;W5P17AP<F5S<VEO;CX]1FEE
M;&1S(4-U<W1O;65R240N5F%L=64\+T=R;W5P17AP<F5S<VEO;CX-"B @.(" @.
M(" @.(" \+T=R;W5P17AP<F5S<VEO;G,^#0H@.(" @.(" @.(#PO1W)O=7!I;F<^
M#0H@.(" @.(" @.(#Q:26YD97@.^,3PO6DEN9&5X/@.T*(" @.(" @.(" \1&%T85-E
M=$YA;64^9'-#=7-T;VUE<CPO1&%T85-E=$YA;64^#0H@.(" @.(" @.(#Q297!O
M<G1)=&5M<SX-"B @.(" @.(" @.(" \5&5X=&)O>"!.86UE/2)#=7-T;VUE<E1Y
M<&4B/@.T*(" @.(" @.(" @.(" @./%-T>6QE/@.T*(" @.(" @.(" @.(" @.(" \4&%D
M9&EN9TQE9G0^,G!T/"]0861D:6YG3&5F=#X-"B @.(" @.(" @.(" @.(" @./%!A
M9&1I;F=";W1T;VT^,G!T/"]0861D:6YG0F]T=&]M/@.T*(" @.(" @.(" @.(" @.
M(" \4&%D9&EN9U1O<#XR<'0\+U!A9&1I;F=4;W ^#0H@.(" @.(" @.(" @.(" @.
M(#Q0861D:6YG4FEG:'0^,G!T/"]0861D:6YG4FEG:'0^#0H@.(" @.(" @.(" @.
M(" \+U-T>6QE/@.T*(" @.(" @.(" @.(" @./%I);F1E>#XR/"]:26YD97@.^#0H@.
M(" @.(" @.(" @.(" \<F0Z1&5F875L=$YA;64^0W5S=&]M97)4>7!E/"]R9#I$
M969A=6QT3F%M93X-"B @.(" @.(" @.(" @.(#Q7:61T:#XR+C$R-6EN/"]7:61T
M:#X-"B @.(" @.(" @.(" @.(#Q#86Y'<F]W/G1R=64\+T-A;D=R;W<^#0H@.(" @.
M(" @.(" @.(" \5F%L=64^/49I<G-T*$9I96QD<R%#=7-T;VUE<E1Y<&4N5F%L
M=64I/"]686QU93X-"B @.(" @.(" @.(" @.(#Q,969T/C%I;CPO3&5F=#X-"B @.
M(" @.(" @.(" \+U1E>'1B;W@.^#0H@.(" @.(" @.(" @./$QI<W0@.3F%M93TB;'-T
M0W5S=&]M97(B/@.T*(" @.(" @.(" @.(" @./%-T>6QE("\^#0H@.(" @.(" @.(" @.
M(" \6DEN9&5X/C$\+UI);F1E>#X-"B @.(" @.(" @.(" @.(#Q297!O<G1)=&5M
M<SX-"B @.(" @.(" @.(" @.(" @./%1E>'1B;W@.@.3F%M93TB3&%S=$YA;64B/@.T*
M(" @.(" @.(" @.(" @.(" @.(#Q3='EL93X-"B @.(" @.(" @.(" @.(" @.(" @.(#Q0
M861D:6YG3&5F=#XR<'0\+U!A9&1I;F=,969T/@.T*(" @.(" @.(" @.(" @.(" @.
M(" @./%!A9&1I;F=";W1T;VT^,G!T/"]0861D:6YG0F]T=&]M/@.T*(" @.(" @.
M(" @.(" @.(" @.(" @./%!A9&1I;F=4;W ^,G!T/"]0861D:6YG5&]P/@.T*(" @.
M(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=2:6=H=#XR<'0\+U!A9&1I;F=2:6=H
M=#X-"B @.(" @.(" @.(" @.(" @.(" \+U-T>6QE/@.T*(" @.(" @.(" @.(" @.(" @.
M(#Q:26YD97@.^,3PO6DEN9&5X/@.T*(" @.(" @.(" @.(" @.(" @.(#QR9#I$969A
M=6QT3F%M93Y,87-T3F%M93PO<F0Z1&5F875L=$YA;64^#0H@.(" @.(" @.(" @.
M(" @.(" @./%=I9'1H/C$N-S5I;CPO5VED=&@.^#0H@.(" @.(" @.(" @.(" @.(" @.
M/$-A;D=R;W<^=')U93PO0V%N1W)O=SX-"B @.(" @.(" @.(" @.(" @.(" \5F%L
M=64^/49I96QD<R%,87-T3F%M92Y686QU93PO5F%L=64^#0H@.(" @.(" @.(" @.
M(" @.(" @./$QE9G0^,2XS-S5I;CPO3&5F=#X-"B @.(" @.(" @.(" @.(" @./"]4
M97AT8F]X/@.T*(" @.(" @.(" @.(" @.(" \5&5X=&)O>"!.86UE/2)&:7)S=$YA
M;64B/@.T*(" @.(" @.(" @.(" @.(" @.(#Q3='EL93X-"B @.(" @.(" @.(" @.(" @.
M(" @.(#Q0861D:6YG3&5F=#XR<'0\+U!A9&1I;F=,969T/@.T*(" @.(" @.(" @.
M(" @.(" @.(" @./%!A9&1I;F=";W1T;VT^,G!T/"]0861D:6YG0F]T=&]M/@.T*
M(" @.(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=4;W ^,G!T/"]0861D:6YG5&]P
M/@.T*(" @.(" @.(" @.(" @.(" @.(" @./%!A9&1I;F=2:6=H=#XR<'0\+U!A9&1I
M;F=2:6=H=#X-"B @.(" @.(" @.(" @.(" @.(" \+U-T>6QE/@.T*(" @.(" @.(" @.
M(" @.(" @.(#QR9#I$969A=6QT3F%M93Y&:7)S=$YA;64\+W)D.D1E9F%U;'1.
M86UE/@.T*(" @.(" @.(" @.(" @.(" @.(#Q7:61T:#XQ+C,W-6EN/"]7:61T:#X-
M"B @.(" @.(" @.(" @.(" @.(" \0V%N1W)O=SYT<G5E/"]#86Y'<F]W/@.T*(" @.
M(" @.(" @.(" @.(" @.(#Q686QU93X]1FEE;&1S(49I<G-T3F%M92Y686QU93PO
M5F%L=64^#0H@.(" @.(" @.(" @.(" @.(#PO5&5X=&)O>#X-"B @.(" @.(" @.(" @.
M(#PO4F5P;W)T271E;7,^#0H@.(" @.(" @.(" @.(" \3&5F=#XS+C(U:6X\+TQE
M9G0^#0H@.(" @.(" @.(" @.(" \5FES:6)I;&ET>3X-"B @.(" @.(" @.(" @.(" @.
M/$AI9&1E;CX]("A&:65L9',A0W5S=&]M97)4>7!E+E9A;'5E+E1O3&]W97(H
M*29L=#LF9W0[)W,G*3PO2&ED9&5N/@.T*(" @.(" @.(" @.(" @./"]6:7-I8FEL
M:71Y/@.T*(" @.(" @.(" @.(#PO3&ES=#X-"B @.(" @.(" @.(" \5&5X=&)O>"!.
M86UE/2)#=7-T;VUE<DE$(CX-"B @.(" @.(" @.(" @.(#Q3='EL93X-"B @.(" @.
M(" @.(" @.(" @./%!A9&1I;F=,969T/C)P=#PO4&%D9&EN9TQE9G0^#0H@.(" @.
M(" @.(" @.(" @.(#Q497AT06QI9VX^4FEG:'0\+U1E>'1!;&EG;CX-"B @.(" @.
M(" @.(" @.(" @./%!A9&1I;F=";W1T;VT^,G!T/"]0861D:6YG0F]T=&]M/@.T*
M(" @.(" @.(" @.(" @.(" \4&%D9&EN9U1O<#XR<'0\+U!A9&1I;F=4;W ^#0H@.
M(" @.(" @.(" @.(" @.(#Q0861D:6YG4FEG:'0^,G!T/"]0861D:6YG4FEG:'0^
M#0H@.(" @.(" @.(" @.(" \+U-T>6QE/@.T*(" @.(" @.(" @.(" @./')D.D1E9F%U
M;'1.86UE/D-U<W1O;65R240\+W)D.D1E9F%U;'1.86UE/@.T*(" @.(" @.(" @.
M(" @./%=I9'1H/C%I;CPO5VED=&@.^#0H@.(" @.(" @.(" @.(" \0V%N1W)O=SYT
M<G5E/"]#86Y'<F]W/@.T*(" @.(" @.(" @.(" @./%9A;'5E/CU&:65L9',A0W5S
M=&]M97))1"Y686QU93PO5F%L=64^#0H@.(" @.(" @.(" @./"]497AT8F]X/@.T*
M(" @.(" @.(" \+U)E<&]R=$ET96US/@.T*(" @.(" @./"],:7-T/@.T*(" @.(" @.
M/$QI<W0@.3F%M93TB;'-T4W1O<F4B/@.T*(" @.(" @.(" \4W1Y;&4@.+SX-"B @.
M(" @.(" @./$AE:6=H=#XP+C(U:6X\+TAE:6=H=#X-"B @.(" @.(" @./%1O<#XP
M+C<U:6X\+U1O<#X-"B @.(" @.(" @./%=I9'1H/C(N,3(U:6X\+U=I9'1H/@.T*
M(" @.(" @.(" \3&5F=#XS+C8R-6EN/"],969T/@.T*(" @.(" @./"],:7-T/@.T*
M(" @.(#PO4F5P;W)T271E;7,^#0H@.(" @./%-T>6QE("\^#0H@.(" @./$AE:6=H
M=#XR:6X\+TAE:6=H=#X-"B @./"]";V1Y/@.T*(" \5&]P36%R9VEN/C%I;CPO
M5&]P36%R9VEN/@.T*(" \1&%T85-O=7)C97,^#0H@.(" @./$1A=&%3;W5R8V4@.
M3F%M93TB061V96YT=7)E5V]R:W,B/@.T*(" @.(" @./')D.D1A=&%3;W5R8V5)
M1#XP.3=E-6)B,RUF-#(V+30W93(M.38V,"UD-35B-3(Y93,V96,\+W)D.D1A
M=&%3;W5R8V5)1#X-"B @.(" @.(#Q$871A4V]U<F-E4F5F97)E;F-E/D%D=F5N
M='5R95=O<FMS/"]$871A4V]U<F-E4F5F97)E;F-E/@.T*(" @.(#PO1&%T85-O
M=7)C93X-"B @./"]$871A4V]U<F-E<SX-"B @./%=I9'1H/C<N-S5I;CPO5VED
M=&@.^#0H@.(#Q$871A4V5T<SX-"B @.(" \1&%T85-E="!.86UE/2)D<T-U<W1O
M;65R(CX-"B @.(" @.(#Q&:65L9',^#0H@.(" @.(" @.(#Q&:65L9"!.86UE/2)#
M=7-T;VUE<DE$(CX-"B @.(" @.(" @.(" \1&%T849I96QD/D-U<W1O;65R240\
M+T1A=&%&:65L9#X-"B @.(" @.(" @.(" \<F0Z5'EP94YA;64^4WES=&5M+DEN
M=#,R/"]R9#I4>7!E3F%M93X-"B @.(" @.(" @./"]&:65L9#X-"B @.(" @.(" @.
M/$9I96QD($YA;64](D-U<W1O;65R5'EP92(^#0H@.(" @.(" @.(" @./$1A=&%&
M:65L9#Y#=7-T;VUE<E1Y<&4\+T1A=&%&:65L9#X-"B @.(" @.(" @.(" \<F0Z
M5'EP94YA;64^4WES=&5M+E-T<FEN9SPO<F0Z5'EP94YA;64^#0H@.(" @.(" @.
M(#PO1FEE;&0^#0H@.(" @.(" @.(#Q&:65L9"!.86UE/2)!8V-O=6YT3G5M8F5R
M(CX-"B @.(" @.(" @.(" \1&%T849I96QD/D%C8V]U;G1.=6UB97(\+T1A=&%&
M:65L9#X-"B @.(" @.(" @.(" \<F0Z5'EP94YA;64^4WES=&5M+DEN=#,R/"]R
M9#I4>7!E3F%M93X-"B @.(" @.(" @./"]&:65L9#X-"B @.(" @.(" @./$9I96QD
M($YA;64](D9I<G-T3F%M92(^#0H@.(" @.(" @.(" @./$1A=&%&:65L9#Y&:7)S
M=$YA;64\+T1A=&%&:65L9#X-"B @.(" @.(" @.(" \<F0Z5'EP94YA;64^4WES
M=&5M+E-T<FEN9SPO<F0Z5'EP94YA;64^#0H@.(" @.(" @.(#PO1FEE;&0^#0H@.
M(" @.(" @.(#Q&:65L9"!.86UE/2),87-T3F%M92(^#0H@.(" @.(" @.(" @./$1A
M=&%&:65L9#Y,87-T3F%M93PO1&%T849I96QD/@.T*(" @.(" @.(" @.(#QR9#I4
M>7!E3F%M93Y3>7-T96TN4W1R:6YG/"]R9#I4>7!E3F%M93X-"B @.(" @.(" @.
M/"]&:65L9#X-"B @.(" @.(" @./$9I96QD($YA;64](D5M86EL061D<F5S<R(^
M#0H@.(" @.(" @.(" @./$1A=&%&:65L9#Y%;6%I;$%D9')E<W,\+T1A=&%&:65L
M9#X-"B @.(" @.(" @.(" \<F0Z5'EP94YA;64^4WES=&5M+E-T<FEN9SPO<F0Z
M5'EP94YA;64^#0H@.(" @.(" @.(#PO1FEE;&0^#0H@.(" @.(" @.(#Q&:65L9"!.
M86UE/2).86UE(CX-"B @.(" @.(" @.(" \1&%T849I96QD/DYA;64\+T1A=&%&
M:65L9#X-"B @.(" @.(" @.(" \<F0Z5'EP94YA;64^4WES=&5M+E-T<FEN9SPO
M<F0Z5'EP94YA;64^#0H@.(" @.(" @.(#PO1FEE;&0^#0H@.(" @.(" @.(#Q&:65L
M9"!.86UE/2).=6UB97)%;7!L;WEE97,B/@.T*(" @.(" @.(" @.(#Q$871A1FEE
M;&0^3G5M8F5R16UP;&]Y965S/"]$871A1FEE;&0^#0H@.(" @.(" @.(" @./')D
M.E1Y<&5.86UE/E-Y<W1E;2Y);G0S,CPO<F0Z5'EP94YA;64^#0H@.(" @.(" @.
M(#PO1FEE;&0^#0H@.(" @.(" \+T9I96QD<SX-"B @.(" @.(#Q1=65R>3X-"B @.
M(" @.(" @./$1A=&%3;W5R8V5.86UE/D%D=F5N='5R95=O<FMS/"]$871A4V]U
M<F-E3F%M93X-"B @.(" @.(" @./$-O;6UA;F1497AT/E-%3$5#5" @.(" @.5$]0
M(#$P,"!#=7-T;VUE<BY#=7-T;VUE<DE$+"!#=7-T;VUE<BY#=7-T;VUE<E1Y
M<&4L($-U<W1O;65R+D%C8V]U;G1.=6UB97(L($EN9&EV:61U86PN1FER<W1.
M86UE+"!);F1I=FED=6%L+DQA<W1.86UE+" -"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.("!);F1I=FED=6%L+D5M86EL061D<F5S<RP@.4W1O<F4N3F%M92P@.4W1O
M<F4N3G5M8F5R16UP;&]Y965S#0I&4D]-(" @.(" @.(" @.0W5S=&]M97(@.3$5&
M5"!/551%4B!*3TE.#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.26YD:79I9'5A
M;"!/3B!#=7-T;VUE<BY#=7-T;VUE<DE$(#T@.26YD:79I9'5A;"Y#=7-T;VUE
M<DE$($Q%1E0@.3U5415(@.2D])3@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(%-T
M;W)E($].($-U<W1O;65R+D-U<W1O;65R240@./2!3=&]R92Y#=7-T;VUE<DE$
M/"]#;VUM86YD5&5X=#X-"B @.(" @.(#PO475E<GD^#0H@.(" @./"]$871A4V5T
M/@.T*(" \+T1A=&%3971S/@.T*(" \3&5F=$UA<F=I;CXQ:6X\+TQE9G1-87)G
M:6X^#0H@.(#QR9#I3;F%P5&]'<FED/G1R=64\+W)D.E-N87!4;T=R:60^#0H@.
M(#QR9#I$<F%W1W)I9#YT<G5E/"]R9#I$<F%W1W)I9#X-"B @./')D.E)E<&]R
M=$E$/C X-#5F-#=E+39D-F4M-#=B82TY-38R+3=F.65E,C0S-#-F9#PO<F0Z
M4F5P;W)T240^#0H@.(#Q";W1T;VU-87)G:6X^,6EN/"]";W1T;VU-87)G:6X^
I#0H@.(#Q,86YG=6%G93YE;BU54SPO3&%N9W5A9V4^#0H\+U)E<&]R=#X`
`
end|||Hi Teo!
What you are describing sound very much what I try to do. Unfortunately, I couldn't run your code. I
modified the connection information so the query ran fine (I get both "I" and "S" in the
CustomerType column, for different rows).
However, when I run the query, I get compilation error:
"C:\Projects\MutipleLists\ListDemo.rdl The hidden expression for the list 'lstCustomer' contains an
error: [BC30201] Expression expected."
Sorry to be a pain here, seems like I'm very close to what I want to achieve... :-)
What I mean by the book store, is that I enjoy walking around in a physical book store, flipping the
pages. Mainly for "ordinary" books, but as well for computer books. I'd just hate to see that option
go away in some far future, due to online book stores has driven physical stores bust.
I'm no fanatic, though. I do buy online as well, especially when there are hard to get books, I'm
tight on time to go shopping etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:ulTGY9RjEHA.2652@.TK2MSFTNGP15.phx.gbl...
> Tibor,
> I attached a sample report that demonstrates nested lists. The Adventure
> Works database has two types of customers: stores and individuals. The idea
> here is that if the customer is individual the report will show his first
> and last name. If the customer is store, store-specific details are shown.
> This is accomplished by having two overlapping lists lstIndividual and
> lstStore nested inside lstCustomer.
> Not sure I get the idea of the book support that the brick-and-mortar stores
> have but online don't. Please educate me on this subject some more so I
> could pass it along to the publisher. Do you mean to return the book?
> Hopefully, you won't have to. And yes, I tried my best to keep it above
> basic :-)
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OaaxQZQjEHA.3476@.tk2msftngp13.phx.gbl...
> > Interesting. I'll check it out. I guess I have to look into whether a list
> region can display rows like a
> > table does it. Seems I have some reading up to do. Any pointers where I
> can find some basic steps for how to
> > accomplish nested list regions?
> >
> > Judging by the publishers website, seems your book is one notch up from
> the basic books? (I prefer to buy
> > books from a physical book store, to support them. I'd hate to see all
> them go out of business. But where I
> > can't find a book, yes, I do buy it off the net. Yours is on the list.
> :-) )
> >
> > Thanks!
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> news:el71IoFjEHA.384@.TK2MSFTNGP10.phx.gbl...
> > > Tibor,
> > >
> > > Looks like nested list regions may do the trick for you, where the
> outermost
> > > list region will display the dataset rows, while the inner list regions
> are
> > > the sections which visibility could be changed conditionally. Have you
> > > explored this opton?
> > >
> > > BTW, my book is shipping... see the report footer.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ----
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > Home page and blog: http://www.prologika.com/
> > > ----
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:eymIZXFjEHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > > Thanks Teo,
> > > >
> > > > I understand those options, but it will become too much of a mess as
> you
> > > want to do more changes.
> > > >
> > > > I had problems understanding what the customer wanted until he showed
> me
> > > this in Crystal. Imagine that you
> > > > have several sections (like page header/body/footer). In effect, you
> have
> > > several bodies, and position the
> > > > columns freely into each section. In section A I want column x and z,
> with
> > > this formatting. In section B I
> > > > want columns z and y and g, having the desired formatting. Etc. Then
> you
> > > say a condition which determines
> > > > which section is to be used for the row data. For example, if the
> value
> > > for column A is this, use section A
> > > > for the row. If the value is B, use section F.
> > > >
> > > > I very much doubt this can be done using the table control, as you
> cannot
> > > align columns conditionally (column
> > > > width etc based on a value in the row). Methinks.
> > > >
> > > > Possibly by just positioning out text boxes etc, but I can't really
> see
> > > how you can define several "sections",
> > > > and control which section is to be used for the row data.
> > > >
> > > > I have a feeling this cannot be done using current version of RS. This
> is
> > > fine, considering it is a 1.0
> > > > product after all. The customer understand this as well. I just wanted
> to
> > > see whether I missed something.
> > > > (None of the three books I have mention anything along this line. I
> recall
> > > asking for your book as well, but
> > > > the store didn't have it. Looking forward to go through that too when
> I
> > > get my hands on it. :-) ).
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> > > news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> > > > > Tibor,
> > > > >
> > > > > You can base the column field on a expression,e.g.
> > > > > =Iif(Fields!A.Value=<expression>, Fields!B.Value, Fields!C.Value).
> > > > > <expression> can reference to a field value or call to an embedded
> or
> > > > > external function.
> > > > >
> > > > > In addition, you can implement conditional formatting, e.g. identity
> the
> > > > > column value based on the condition, coloring cells, etc.
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > ----
> > > > > Teo Lachev, MCSD, MCT
> > > > > Author: "Microsoft Reporting Services in Action"
> > > > > Publisher website: http://www.manning.com/lachev
> > > > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > > > Home page and blog: http://www.prologika.com/
> > > > > ----
> > > > >
> > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > > in
> > > > > message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > > > > Perhaps I'm only overlooking something very simple?
> > > > > >
> > > > > > I want to display different columns from my resultset, depending
> of
> > > the
> > > > > data
> > > > > > in one column. I also want to apply different styles depending on
> that
> > > > > > "controlling" column. I understand that I in a Table can define
> > > attributes
> > > > > > for the whole row (same as when you select the "left" portion of a
> > > row),
> > > > > > which can be based of the value of that row. But what if I want to
> > > display
> > > > > > different columns, based on the data?
> > > > > >
> > > > > > Example:
> > > > > > * A dataset with a datetime column. We want to sort by this in the
> > > report.
> > > > > > Earliest event at the top.
> > > > > > * Each row represent different activities over the day. One column
> > > define
> > > > > > type of activity.
> > > > > > * Depending of the type of activity, I want to display different
> > > columns
> > > > > > from the dataset. Ideally, I want to control horizontal position
> > > depending
> > > > > > on the type of activity.
> > > > > >
> > > > > > I believe that Crystal is using the term "Multiple Sections" for
> this,
> > > but
> > > > > > I'm not sure about the terminology.
> > > > > >
> > > > > > Thanks
> > > > > > Tibor
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>|||Tibor,
Strangely, I encountered this error myself. I am almost positive that it is
related to a bug with RS. For some reason, RS thinks that
=Fields!CustomerType.Value = "i" is not a valid expression. I tried several
alterations and I don't recall exactly what I did to get rid off the error
message. The expression on the list visibility is perfectly fine and runs
fine on my computer :-(. Try to close your report or restart VS.NET to see
if it will help. Or, change to another expression and then replace it again.
Got your point about brick-and-mortar stores. Hopefully, the samples
chapters will boost your confidence level...
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lYA18ojEHA.2812@.tk2msftngp13.phx.gbl...
> Hi Teo!
> What you are describing sound very much what I try to do. Unfortunately, I
couldn't run your code. I
> modified the connection information so the query ran fine (I get both "I"
and "S" in the
> CustomerType column, for different rows).
> However, when I run the query, I get compilation error:
> "C:\Projects\MutipleLists\ListDemo.rdl The hidden expression for the list
'lstCustomer' contains an
> error: [BC30201] Expression expected."
> Sorry to be a pain here, seems like I'm very close to what I want to
achieve... :-)
>
> What I mean by the book store, is that I enjoy walking around in a
physical book store, flipping the
> pages. Mainly for "ordinary" books, but as well for computer books. I'd
just hate to see that option
> go away in some far future, due to online book stores has driven physical
stores bust.
> I'm no fanatic, though. I do buy online as well, especially when there are
hard to get books, I'm
> tight on time to go shopping etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> news:ulTGY9RjEHA.2652@.TK2MSFTNGP15.phx.gbl...
> > Tibor,
> >
> > I attached a sample report that demonstrates nested lists. The Adventure
> > Works database has two types of customers: stores and individuals. The
idea
> > here is that if the customer is individual the report will show his
first
> > and last name. If the customer is store, store-specific details are
shown.
> >
> > This is accomplished by having two overlapping lists lstIndividual and
> > lstStore nested inside lstCustomer.
> >
> > Not sure I get the idea of the book support that the brick-and-mortar
stores
> > have but online don't. Please educate me on this subject some more so I
> > could pass it along to the publisher. Do you mean to return the book?
> > Hopefully, you won't have to. And yes, I tried my best to keep it above
> > basic :-)
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OaaxQZQjEHA.3476@.tk2msftngp13.phx.gbl...
> > > Interesting. I'll check it out. I guess I have to look into whether a
list
> > region can display rows like a
> > > table does it. Seems I have some reading up to do. Any pointers where
I
> > can find some basic steps for how to
> > > accomplish nested list regions?
> > >
> > > Judging by the publishers website, seems your book is one notch up
from
> > the basic books? (I prefer to buy
> > > books from a physical book store, to support them. I'd hate to see all
> > them go out of business. But where I
> > > can't find a book, yes, I do buy it off the net. Yours is on the list.
> > :-) )
> > >
> > > Thanks!
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:el71IoFjEHA.384@.TK2MSFTNGP10.phx.gbl...
> > > > Tibor,
> > > >
> > > > Looks like nested list regions may do the trick for you, where the
> > outermost
> > > > list region will display the dataset rows, while the inner list
regions
> > are
> > > > the sections which visibility could be changed conditionally. Have
you
> > > > explored this opton?
> > > >
> > > > BTW, my book is shipping... see the report footer.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ----
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > > Home page and blog: http://www.prologika.com/
> > > > ----
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > > message news:eymIZXFjEHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > > > Thanks Teo,
> > > > >
> > > > > I understand those options, but it will become too much of a mess
as
> > you
> > > > want to do more changes.
> > > > >
> > > > > I had problems understanding what the customer wanted until he
showed
> > me
> > > > this in Crystal. Imagine that you
> > > > > have several sections (like page header/body/footer). In effect,
you
> > have
> > > > several bodies, and position the
> > > > > columns freely into each section. In section A I want column x and
z,
> > with
> > > > this formatting. In section B I
> > > > > want columns z and y and g, having the desired formatting. Etc.
Then
> > you
> > > > say a condition which determines
> > > > > which section is to be used for the row data. For example, if the
> > value
> > > > for column A is this, use section A
> > > > > for the row. If the value is B, use section F.
> > > > >
> > > > > I very much doubt this can be done using the table control, as you
> > cannot
> > > > align columns conditionally (column
> > > > > width etc based on a value in the row). Methinks.
> > > > >
> > > > > Possibly by just positioning out text boxes etc, but I can't
really
> > see
> > > > how you can define several "sections",
> > > > > and control which section is to be used for the row data.
> > > > >
> > > > > I have a feeling this cannot be done using current version of RS.
This
> > is
> > > > fine, considering it is a 1.0
> > > > > product after all. The customer understand this as well. I just
wanted
> > to
> > > > see whether I missed something.
> > > > > (None of the three books I have mention anything along this line.
I
> > recall
> > > > asking for your book as well, but
> > > > > the store didn't have it. Looking forward to go through that too
when
> > I
> > > > get my hands on it. :-) ).
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> > > > news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> > > > > > Tibor,
> > > > > >
> > > > > > You can base the column field on a expression,e.g.
> > > > > > =Iif(Fields!A.Value=<expression>, Fields!B.Value,
Fields!C.Value).
> > > > > > <expression> can reference to a field value or call to an
embedded
> > or
> > > > > > external function.
> > > > > >
> > > > > > In addition, you can implement conditional formatting, e.g.
identity
> > the
> > > > > > column value based on the condition, coloring cells, etc.
> > > > > >
> > > > > > --
> > > > > > Hope this helps.
> > > > > >
> > > > > > ----
> > > > > > Teo Lachev, MCSD, MCT
> > > > > > Author: "Microsoft Reporting Services in Action"
> > > > > > Publisher website: http://www.manning.com/lachev
> > > > > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > > > > Home page and blog: http://www.prologika.com/
> > > > > > ----
> > > > > >
> > > > > > "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
> > wrote
> > > > in
> > > > > > message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > > > > > Perhaps I'm only overlooking something very simple?
> > > > > > >
> > > > > > > I want to display different columns from my resultset,
depending
> > of
> > > > the
> > > > > > data
> > > > > > > in one column. I also want to apply different styles depending
on
> > that
> > > > > > > "controlling" column. I understand that I in a Table can
define
> > > > attributes
> > > > > > > for the whole row (same as when you select the "left" portion
of a
> > > > row),
> > > > > > > which can be based of the value of that row. But what if I
want to
> > > > display
> > > > > > > different columns, based on the data?
> > > > > > >
> > > > > > > Example:
> > > > > > > * A dataset with a datetime column. We want to sort by this in
the
> > > > report.
> > > > > > > Earliest event at the top.
> > > > > > > * Each row represent different activities over the day. One
column
> > > > define
> > > > > > > type of activity.
> > > > > > > * Depending of the type of activity, I want to display
different
> > > > columns
> > > > > > > from the dataset. Ideally, I want to control horizontal
position
> > > > depending
> > > > > > > on the type of activity.
> > > > > > >
> > > > > > > I believe that Crystal is using the term "Multiple Sections"
for
> > this,
> > > > but
> > > > > > > I'm not sure about the terminology.
> > > > > > >
> > > > > > > Thanks
> > > > > > > Tibor
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>|||One more thing you can try is coexrce the expression to bool, such as:
=CBool(Fields!CustomerType.Value = "i")
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lYA18ojEHA.2812@.tk2msftngp13.phx.gbl...
> Hi Teo!
> What you are describing sound very much what I try to do. Unfortunately, I
couldn't run your code. I
> modified the connection information so the query ran fine (I get both "I"
and "S" in the
> CustomerType column, for different rows).
> However, when I run the query, I get compilation error:
> "C:\Projects\MutipleLists\ListDemo.rdl The hidden expression for the list
'lstCustomer' contains an
> error: [BC30201] Expression expected."
> Sorry to be a pain here, seems like I'm very close to what I want to
achieve... :-)
>
> What I mean by the book store, is that I enjoy walking around in a
physical book store, flipping the
> pages. Mainly for "ordinary" books, but as well for computer books. I'd
just hate to see that option
> go away in some far future, due to online book stores has driven physical
stores bust.
> I'm no fanatic, though. I do buy online as well, especially when there are
hard to get books, I'm
> tight on time to go shopping etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> news:ulTGY9RjEHA.2652@.TK2MSFTNGP15.phx.gbl...
> > Tibor,
> >
> > I attached a sample report that demonstrates nested lists. The Adventure
> > Works database has two types of customers: stores and individuals. The
idea
> > here is that if the customer is individual the report will show his
first
> > and last name. If the customer is store, store-specific details are
shown.
> >
> > This is accomplished by having two overlapping lists lstIndividual and
> > lstStore nested inside lstCustomer.
> >
> > Not sure I get the idea of the book support that the brick-and-mortar
stores
> > have but online don't. Please educate me on this subject some more so I
> > could pass it along to the publisher. Do you mean to return the book?
> > Hopefully, you won't have to. And yes, I tried my best to keep it above
> > basic :-)
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OaaxQZQjEHA.3476@.tk2msftngp13.phx.gbl...
> > > Interesting. I'll check it out. I guess I have to look into whether a
list
> > region can display rows like a
> > > table does it. Seems I have some reading up to do. Any pointers where
I
> > can find some basic steps for how to
> > > accomplish nested list regions?
> > >
> > > Judging by the publishers website, seems your book is one notch up
from
> > the basic books? (I prefer to buy
> > > books from a physical book store, to support them. I'd hate to see all
> > them go out of business. But where I
> > > can't find a book, yes, I do buy it off the net. Yours is on the list.
> > :-) )
> > >
> > > Thanks!
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:el71IoFjEHA.384@.TK2MSFTNGP10.phx.gbl...
> > > > Tibor,
> > > >
> > > > Looks like nested list regions may do the trick for you, where the
> > outermost
> > > > list region will display the dataset rows, while the inner list
regions
> > are
> > > > the sections which visibility could be changed conditionally. Have
you
> > > > explored this opton?
> > > >
> > > > BTW, my book is shipping... see the report footer.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ----
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > > Home page and blog: http://www.prologika.com/
> > > > ----
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > > message news:eymIZXFjEHA.2524@.TK2MSFTNGP11.phx.gbl...
> > > > > Thanks Teo,
> > > > >
> > > > > I understand those options, but it will become too much of a mess
as
> > you
> > > > want to do more changes.
> > > > >
> > > > > I had problems understanding what the customer wanted until he
showed
> > me
> > > > this in Crystal. Imagine that you
> > > > > have several sections (like page header/body/footer). In effect,
you
> > have
> > > > several bodies, and position the
> > > > > columns freely into each section. In section A I want column x and
z,
> > with
> > > > this formatting. In section B I
> > > > > want columns z and y and g, having the desired formatting. Etc.
Then
> > you
> > > > say a condition which determines
> > > > > which section is to be used for the row data. For example, if the
> > value
> > > > for column A is this, use section A
> > > > > for the row. If the value is B, use section F.
> > > > >
> > > > > I very much doubt this can be done using the table control, as you
> > cannot
> > > > align columns conditionally (column
> > > > > width etc based on a value in the row). Methinks.
> > > > >
> > > > > Possibly by just positioning out text boxes etc, but I can't
really
> > see
> > > > how you can define several "sections",
> > > > > and control which section is to be used for the row data.
> > > > >
> > > > > I have a feeling this cannot be done using current version of RS.
This
> > is
> > > > fine, considering it is a 1.0
> > > > > product after all. The customer understand this as well. I just
wanted
> > to
> > > > see whether I missed something.
> > > > > (None of the three books I have mention anything along this line.
I
> > recall
> > > > asking for your book as well, but
> > > > > the store didn't have it. Looking forward to go through that too
when
> > I
> > > > get my hands on it. :-) ).
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
> > > > news:OF$jESEjEHA.3232@.TK2MSFTNGP10.phx.gbl...
> > > > > > Tibor,
> > > > > >
> > > > > > You can base the column field on a expression,e.g.
> > > > > > =Iif(Fields!A.Value=<expression>, Fields!B.Value,
Fields!C.Value).
> > > > > > <expression> can reference to a field value or call to an
embedded
> > or
> > > > > > external function.
> > > > > >
> > > > > > In addition, you can implement conditional formatting, e.g.
identity
> > the
> > > > > > column value based on the condition, coloring cells, etc.
> > > > > >
> > > > > > --
> > > > > > Hope this helps.
> > > > > >
> > > > > > ----
> > > > > > Teo Lachev, MCSD, MCT
> > > > > > Author: "Microsoft Reporting Services in Action"
> > > > > > Publisher website: http://www.manning.com/lachev
> > > > > > Buy it from Amazon.com : http://tinyurl.com/3l49j
> > > > > > Home page and blog: http://www.prologika.com/
> > > > > > ----
> > > > > >
> > > > > > "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
> > wrote
> > > > in
> > > > > > message news:uCjSU4CjEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > > > > > > Perhaps I'm only overlooking something very simple?
> > > > > > >
> > > > > > > I want to display different columns from my resultset,
depending
> > of
> > > > the
> > > > > > data
> > > > > > > in one column. I also want to apply different styles depending
on
> > that
> > > > > > > "controlling" column. I understand that I in a Table can
define
> > > > attributes
> > > > > > > for the whole row (same as when you select the "left" portion
of a
> > > > row),
> > > > > > > which can be based of the value of that row. But what if I
want to
> > > > display
> > > > > > > different columns, based on the data?
> > > > > > >
> > > > > > > Example:
> > > > > > > * A dataset with a datetime column. We want to sort by this in
the
> > > > report.
> > > > > > > Earliest event at the top.
> > > > > > > * Each row represent different activities over the day. One
column
> > > > define
> > > > > > > type of activity.
> > > > > > > * Depending of the type of activity, I want to display
different
> > > > columns
> > > > > > > from the dataset. Ideally, I want to control horizontal
position
> > > > depending
> > > > > > > on the type of activity.
> > > > > > >
> > > > > > > I believe that Crystal is using the term "Multiple Sections"
for
> > this,
> > > > but
> > > > > > > I'm not sure about the terminology.
> > > > > > >
> > > > > > > Thanks
> > > > > > > Tibor
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>

Wednesday, March 28, 2012

multityped column

multityped column

Wednesday, March 21, 2012

Multiple values in one column

multiple values for single column in where clause

Monday, March 19, 2012

multiple updates to table

Multiple updates and Identity fields

Monday, March 12, 2012

Multiple tables in dataset.

Friday, March 9, 2012

Multiple Table Problem

Multiple Stored Procedure Execute Together - How?

Saturday, February 25, 2012

Multiple selects on same column, same table, one query

Multiple sections in report?

multiple resultsets

Blog Archive

About Me