hmm,

You have a row of data set up like this a,b,c,d these are the columns. The
values are a=3, b=1, c=5, d=4 and I need these put in descending order but
this is one row and I need it to come back looking like multiple rows. How
would you suggest I go about this?
Shawntry..
select a as Result from table
union all
select b from table
union all
select c from table
union all
select d from table
order by Result
might be a better way to do this, anyone?
"Shawn Mason" wrote:

> You have a row of data set up like this a,b,c,d these are the columns. Th
e
> values are a=3, b=1, c=5, d=4 and I need these put in descending order but
> this is one row and I need it to come back looking like multiple rows. Ho
w
> would you suggest I go about this?
> Shawn
>
>|||So this is the reverse of the ubiquitous post "How do I combine multiple
rows into one row?":
select a as x from mytable
union all
select b as x from mytable
union all
select c as x from mytable
union all
select d as x from mytable
order by x desc
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
> The values are a=3, b=1, c=5, d=4 and I need these put in descending order
> but this is one row and I need it to come back looking like multiple rows.
> How would you suggest I go about this?
> Shawn
>
>|||Try this:
select a as Result
union
select b
union
select c
union
select d
order by Result
ML|||Sorry, correction:
select a as Result
from <table_name>
union
select b
from <table_name>
union
select c
from <table_name>
union
select d
from <table_name>
order by Result desc
ML|||Have you looked up UNION ALL in BOL?
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
The
> values are a=3, b=1, c=5, d=4 and I need these put in descending order but
> this is one row and I need it to come back looking like multiple rows.
How
> would you suggest I go about this?
> Shawn
>
>|||Please post DDL, sample data, and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006)
Ideally, if each column is the same type of "thing", you would normalize the
table design such that these values *are* in multiple rows.
Barring that, you can do some kind of kludge like this:
SELECT BadColumn FROM
(SELECT ColumnA AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnB AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnC AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnD AS BadColumn FROM BadTable) ReallyBadTable
ORDER BY BadColumn DESC
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
> The values are a=3, b=1, c=5, d=4 and I need these put in descending order
> but this is one row and I need it to come back looking like multiple rows.
> How would you suggest I go about this?
> Shawn
>
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Exampl
e
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.co...echspace1.shtml
Thanks
GYK
"John" wrote:

> I have a number of tables with filled with duplicate indexed columns (Exam
ple
> 1 below). Is it better to have a column in one index per table rather the
n
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column orde
r
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

Hitting on indexes

I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2
It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com...chspace1.shtml
Thanks
GYK
"John" wrote:

> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>

hierarchical output

I have a Table with 2 columns 1)Parent column-ASSEMBLY_ID and 2)child
column-COMPONENT_ID
I want the output as a hierarchy given below. Any idea on how to get the
output?(I am using a Stored Procedure on SQL Server 2000 to generate the
output)
CREATE TABLE [dbo].[IPDS_MBOM_INTERFACE]
(
[ASSEMBLY_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[COMPONENT_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
----
---
INSERT INTO IPDS_MBOM_INTERFACE (ASSEMBLY_ID, COMPONENT_ID)
VALUES ('320192 - 3', 'AS12438791')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('320192 - 3', 'AS12438792')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438792', 'AS12438793')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438793', 'AS12438794')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438794', 'AS12438795')
INSERT INTO
IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
VALUES ('AS12438794', 'AS12438796')
----
---
EXPECTED OUTPUT WITH HEADINGS
ASSEMBLY_ID LEVEL1 LEVEL2 LEVEL3
LEVEL4
320192 - 3 AS12438791
320192 - 3 AS12438792 AS12438793 AS12438794
AS12438795
320192 - 3 AS12438792 AS12438793 AS12438794
AS12438796http://www.google.com/url?sa=D&q=ht..._qd_14_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Nishanth" <cvnishanth@.hotmail.com> schrieb im Newsbeitrag
news:eCZfUHrWFHA.2796@.TK2MSFTNGP09.phx.gbl...
>I have a Table with 2 columns 1)Parent column-ASSEMBLY_ID and 2)child
> column-COMPONENT_ID
> I want the output as a hierarchy given below. Any idea on how to get the
> output?(I am using a Stored Procedure on SQL Server 2000 to generate the
> output)
> CREATE TABLE [dbo].[IPDS_MBOM_INTERFACE]
> (
> [ASSEMBLY_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [COMPONENT_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> )
> ----
--
> ---
> INSERT INTO IPDS_MBOM_INTERFACE (ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438791')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438792')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438792', 'AS12438793')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438793', 'AS12438794')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438795')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438796')
> ----
--
> ---
> EXPECTED OUTPUT WITH HEADINGS
> ASSEMBLY_ID LEVEL1 LEVEL2 LEVEL3
> LEVEL4
> 320192 - 3 AS12438791
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438795
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438796
>
>|||Try:
SELECT
a.assembly_id,
a.component_id AS level1,
b.component_id AS level2,
c.component_id AS level3,
d.component_id AS level4
FROM #ipds_mbom_interface a
LEFT JOIN #ipds_mbom_interface b ON a.component_id = b.assembly_id
LEFT JOIN #ipds_mbom_interface c ON b.component_id = c.assembly_id
LEFT JOIN #ipds_mbom_interface d ON c.component_id = d.assembly_id
WHERE NOT EXISTS
(
-- Record is root parent, ie has children but no parent ids above it
SELECT *
FROM #ipds_mbom_interface
WHERE component_id = a.assembly_id
)
This query works for your sample data, you'll need to make sure it works for
your real data!
Let me know how you get on.
Damien
"Nishanth" wrote:

> I have a Table with 2 columns 1)Parent column-ASSEMBLY_ID and 2)child
> column-COMPONENT_ID
> I want the output as a hierarchy given below. Any idea on how to get the
> output?(I am using a Stored Procedure on SQL Server 2000 to generate the
> output)
> CREATE TABLE [dbo].[IPDS_MBOM_INTERFACE]
> (
> [ASSEMBLY_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [COMPONENT_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> ----
--
> ---
> INSERT INTO IPDS_MBOM_INTERFACE (ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438791')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('320192 - 3', 'AS12438792')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438792', 'AS12438793')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438793', 'AS12438794')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438795')
> INSERT INTO
> IPDS_MBOM_INTERFACE(ASSEMBLY_ID, COMPONENT_ID)
> VALUES ('AS12438794', 'AS12438796')
> ----
--
> ---
> EXPECTED OUTPUT WITH HEADINGS
> ASSEMBLY_ID LEVEL1 LEVEL2 LEVEL3
> LEVEL4
> 320192 - 3 AS12438791
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438795
> 320192 - 3 AS12438792 AS12438793 AS12438794
> AS12438796
>
>|||Damien,
The problem is the data that I had sent is for 4 levels
but in reality I will have 'n' levels so the query you have sent may not
suit my needs completely.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:D8C8A4BF-71BF-4440-A32A-DC52A27CFF8D@.microsoft.com...
> Try:
> SELECT
> a.assembly_id,
> a.component_id AS level1,
> b.component_id AS level2,
> c.component_id AS level3,
> d.component_id AS level4
> FROM #ipds_mbom_interface a
> LEFT JOIN #ipds_mbom_interface b ON a.component_id = b.assembly_id
> LEFT JOIN #ipds_mbom_interface c ON b.component_id = c.assembly_id
> LEFT JOIN #ipds_mbom_interface d ON c.component_id = d.assembly_id
> WHERE NOT EXISTS
> (
> -- Record is root parent, ie has children but no parent ids above it
> SELECT *
> FROM #ipds_mbom_interface
> WHERE component_id = a.assembly_id
> )
> This query works for your sample data, you'll need to make sure it works
for
> your real data!
> Let me know how you get on.
>
> Damien
> "Nishanth" wrote:
>
NULL
NULL
> ----
--
> ----
--|||Get a copy of TRESS & HIERARCHIES IN SQL. UYouc an use the nested sets
model and do this in one query without any proprietary code.|||I'm sure the others will tell me off for doing this, but try the following
script:
DECLARE @.select VARCHAR( 8000 )
DECLARE @.from VARCHAR( 8000 )
DECLARE @.where VARCHAR( 8000 )
DECLARE @.alias1 VARCHAR( 3 )
DECLARE @.alias2 VARCHAR( 3 )
DECLARE @.i TINYINT
-- Initialise
SET @.i = 1
SET @.alias1 = 1 -- will be used for table aliases
SET @.alias2 = @.alias1 + 1
SET @.select =
'SELECT
t1.assembly_id,
t1.component_id AS level1,
'
SET @.from =
'FROM #ipds_mbom_interface t1'
-- Add required levels
WHILE @.i < 99
BEGIN
-- Build SELECT clause
SET @.select = @.select + SPACE(4) +
't' + @.alias2 + '.component_id AS level' + CONVERT( VARCHAR(2), @.i + 1 )
+ ', ' + CHAR( 10 )
-- Build FROM clause
SET @.from = @.from + CHAR( 10 ) + SPACE(4) +
'LEFT JOIN #ipds_mbom_interface t' + @.alias2 + ' ON t' + @.alias1 +
'.component_id = t' + @.alias2 + '.assembly_id'
-- Increment variables
SET @.i = @.i + 1
SET @.alias1 = @.alias1 + 1
SET @.alias2 = @.alias1 + 1
END
-- Trim last comma and trailing space
SET @.select = SUBSTRING( @.select, 1, LEN( @.select ) - 3 ) + CHAR(10)
-- Add WHERE clause
SET @.where =
'
WHERE NOT EXISTS
(
-- Record is root assembly_id, ie has component_idren but no assembly_id
ids above it
SELECT *
FROM #ipds_mbom_interface
WHERE component_id = t1.assembly_id
)
'
-- Print the dynamic SQL
-- PRINT @.select + @.from + @.where
-- Execute the dynamic SQL
EXEC( @.select + @.from + @.where )
Practically, you shouldn't really be doing something like this for so many
reasons, but let me know how you get on.
Damien
"Nishanth" wrote:

> Damien,
> The problem is the data that I had sent is for 4 levels
> but in reality I will have 'n' levels so the query you have sent may not
> suit my needs completely.
> "Damien" <Damien@.discussions.microsoft.com> wrote in message
> news:D8C8A4BF-71BF-4440-A32A-DC52A27CFF8D@.microsoft.com...
> for
> NULL
> NULL
> --
> --
>
>

Hiding/Showing columns based on the columns present in the dataset

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that?

Any reply showing me the right way is appricited.

-Thanks,

Digs

In the Column properties, expand the Visibility section, and then click on Hidden, and choose Expression.

In the expression, use the same input parameters that you are using to build the column list in your SQL Statement to set the property.

An example expression might look like this:

=iif(Parameters!ShowColumn1.Value= True, false, true)

Where Parameters!ShowColumn1 is a report parameter that you have defined,

This should get you started...

BobP

|||

Hi,

Thanks for the reply..Need bit change =IIf(Fields!Collection.IsMissing=True,True,False) In Visibility -- > Hidden

-Thanks,

Digs

Hiding/Showing Columns based on Parameters

I have a report that has a matrix in it. It is a multidimensional report with
drill down effects resulting from an MDX query. It has 2 columns (measures).
I have created a parameter in order to hide/show a column. I have written the
following expression for the Visibility property of my Heading and Field
Values of a column:
=iif(Parameters!Show.Value="Measures_COUNT",True, False)
What happens is: My column heading and the values both disappear (appears
blank) but the column remains there. i.e the column width doesnt become zero.
Has someone faced a similar problem' I could not find an expression for the
"width" property of my textboxes so that I could set them to zero.
Also, what I would like to get done:
User selects a list of fields from a page that will act as a filter for my
report data (i.e. where clause of the query ) can i modify my MDX query of
the report to take these values into account?
Also, the user selects a list of fields that he/she wants appearing in the
report. Can i pass these fields to the report and manipulate the columns at
run time. This is what i was trying to achieve by hiding/showing columns?
Thanks,
RishitHi,
The "width" property for a textbox can be founf by expanding the "size"
property. In order to do what you want to do you have to do the following:
Set the visibility of the column - not the individual fields that make up
the column.
Hope this helps.
"Rishit" wrote:
> I have a report that has a matrix in it. It is a multidimensional report with
> drill down effects resulting from an MDX query. It has 2 columns (measures).
> I have created a parameter in order to hide/show a column. I have written the
> following expression for the Visibility property of my Heading and Field
> Values of a column:
> =iif(Parameters!Show.Value="Measures_COUNT",True, False)
> What happens is: My column heading and the values both disappear (appears
> blank) but the column remains there. i.e the column width doesnt become zero.
> Has someone faced a similar problem' I could not find an expression for the
> "width" property of my textboxes so that I could set them to zero.
> Also, what I would like to get done:
> User selects a list of fields from a page that will act as a filter for my
> report data (i.e. where clause of the query ) can i modify my MDX query of
> the report to take these values into account?
> Also, the user selects a list of fields that he/she wants appearing in the
> report. Can i pass these fields to the report and manipulate the columns at
> run time. This is what i was trying to achieve by hiding/showing columns?
> Thanks,
> Rishit|||I'm sorry. I am not that familiar with using matrices and didn't realise they
are handled differently. Please let me know if you find an answer.
"Rishit" wrote:
> Hi,
> I checked the properties for the matrix column. The Visibility propert isnt
> present. Besides, the Width property of the field cannot be set to zero using
> an expression. It doesnt allow width to be set to zero thru an expression.
> Mithun
> "Andrew Byrne" wrote:
> > Hi,
> >
> > The "width" property for a textbox can be founf by expanding the "size"
> > property. In order to do what you want to do you have to do the following:
> >
> > Set the visibility of the column - not the individual fields that make up
> > the column.
> >
> >
> >
> > Hope this helps.
> >
> > "Rishit" wrote:
> >
> > > I have a report that has a matrix in it. It is a multidimensional report with
> > > drill down effects resulting from an MDX query. It has 2 columns (measures).
> > > I have created a parameter in order to hide/show a column. I have written the
> > > following expression for the Visibility property of my Heading and Field
> > > Values of a column:
> > > =iif(Parameters!Show.Value="Measures_COUNT",True, False)
> > > What happens is: My column heading and the values both disappear (appears
> > > blank) but the column remains there. i.e the column width doesnt become zero.
> > > Has someone faced a similar problem' I could not find an expression for the
> > > "width" property of my textboxes so that I could set them to zero.
> > >
> > > Also, what I would like to get done:
> > > User selects a list of fields from a page that will act as a filter for my
> > > report data (i.e. where clause of the query ) can i modify my MDX query of
> > > the report to take these values into account?
> > > Also, the user selects a list of fields that he/she wants appearing in the
> > > report. Can i pass these fields to the report and manipulate the columns at
> > > run time. This is what i was trying to achieve by hiding/showing columns?
> > >
> > > Thanks,
> > > Rishit|||you must go to the properties pane of the Matrix itself. Then go to
groupings. Go to the column grouping properties for visibility. Put your
coditional statement there.
"Andrew Byrne" wrote:
> I'm sorry. I am not that familiar with using matrices and didn't realise they
> are handled differently. Please let me know if you find an answer.
> "Rishit" wrote:
> > Hi,
> > I checked the properties for the matrix column. The Visibility propert isnt
> > present. Besides, the Width property of the field cannot be set to zero using
> > an expression. It doesnt allow width to be set to zero thru an expression.
> >
> > Mithun
> >
> > "Andrew Byrne" wrote:
> >
> > > Hi,
> > >
> > > The "width" property for a textbox can be founf by expanding the "size"
> > > property. In order to do what you want to do you have to do the following:
> > >
> > > Set the visibility of the column - not the individual fields that make up
> > > the column.
> > >
> > >
> > >
> > > Hope this helps.
> > >
> > > "Rishit" wrote:
> > >
> > > > I have a report that has a matrix in it. It is a multidimensional report with
> > > > drill down effects resulting from an MDX query. It has 2 columns (measures).
> > > > I have created a parameter in order to hide/show a column. I have written the
> > > > following expression for the Visibility property of my Heading and Field
> > > > Values of a column:
> > > > =iif(Parameters!Show.Value="Measures_COUNT",True, False)
> > > > What happens is: My column heading and the values both disappear (appears
> > > > blank) but the column remains there. i.e the column width doesnt become zero.
> > > > Has someone faced a similar problem' I could not find an expression for the
> > > > "width" property of my textboxes so that I could set them to zero.
> > > >
> > > > Also, what I would like to get done:
> > > > User selects a list of fields from a page that will act as a filter for my
> > > > report data (i.e. where clause of the query ) can i modify my MDX query of
> > > > the report to take these values into account?
> > > > Also, the user selects a list of fields that he/she wants appearing in the
> > > > report. Can i pass these fields to the report and manipulate the columns at
> > > > run time. This is what i was trying to achieve by hiding/showing columns?
> > > >
> > > > Thanks,
> > > > Rishit

Hiding without grouping

hi,
I'm using a matrix to try and display web traffic. For the rows I
can drill down by date. For the columns I am trying to make them be
able to expand based on categories that I define. For example, I would
like to be able to expand/collapse all columns that have to do with
traffic from google. However, I can't seem to check the toggle box. I'm
assuming it's because the columns are not grouped by the item that
toggles them, the way dates are grouped by year. The item that toggles
their visibility is a static text box. The closest I can get is to
toggle the visibility of the column names. Any suggestions?Nevermind, I was just using the wrong table. turns out you can hide
entire columns in the tabular table based on arbitrary toggle items.
You just have to do it in the property window on the side, rather than
right clicking. Has anyone noticed that there's extra unsupported chart
types there? What's a polar graph?|||"What's a polar graph?"
A circular plot showing amplitudes as a function of angle.
http://www.dplot.com/polar.htm

Hiding Table Grid Lines

I have a table that duplicate data in some of the columns. I have the
property set to hide the duplicates, but I also want to turn off the borders
for those cells that are duplicated so I gives the same appearance as a
Matrix report when it has duplicated items in a column. Does anyone have an
expression that will turn off the grid lines for a cell if there are
duplicate items and change the background colors of the whole row that are
duplicates?
I am using SSRS 2005.
John WrightOn May 1, 11:27 am, "John Wright" <riley_wrig...@.hotmail.com> wrote:
> I have a table that duplicate data in some of the columns. I have the
> property set to hide the duplicates, but I also want to turn off the borders
> for those cells that are duplicated so I gives the same appearance as a
> Matrix report when it has duplicated items in a column. Does anyone have an
> expression that will turn off the grid lines for a cell if there are
> duplicate items and change the background colors of the whole row that are
> duplicates?
> I am using SSRS 2005.
> John Wright
I'm afraid not; however, if you can determine the duplicates in the
query/stored procedure that is sourcing the report, you can avoid the
duplicates entirely and/or return a flag column (i.e., IsDuplicate) to
the report that can be used for conditional border hiding. Then as
part of the cell properties under BorderStyle for Left/Right/Top/
Bottom you can use an expression like the following to suppress the
borders:
=iif(Fields!IsDuplicate.Value = 1, "None", "Solid")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||If you use the previous function for each grid line (top,bottom, right,left)
and use an IIF statement you can hide or show the lines just fine. Thanks
for the help.
John
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178073564.091263.171200@.n59g2000hsh.googlegroups.com...
> On May 1, 11:27 am, "John Wright" <riley_wrig...@.hotmail.com> wrote:
>> I have a table that duplicate data in some of the columns. I have the
>> property set to hide the duplicates, but I also want to turn off the
>> borders
>> for those cells that are duplicated so I gives the same appearance as a
>> Matrix report when it has duplicated items in a column. Does anyone have
>> an
>> expression that will turn off the grid lines for a cell if there are
>> duplicate items and change the background colors of the whole row that
>> are
>> duplicates?
>> I am using SSRS 2005.
>> John Wright
>
> I'm afraid not; however, if you can determine the duplicates in the
> query/stored procedure that is sourcing the report, you can avoid the
> duplicates entirely and/or return a flag column (i.e., IsDuplicate) to
> the report that can be used for conditional border hiding. Then as
> part of the cell properties under BorderStyle for Left/Right/Top/
> Bottom you can use an expression like the following to suppress the
> borders:
> =iif(Fields!IsDuplicate.Value = 1, "None", "Solid")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Hiding table columns creates solid black columns in PDF

Hello all,

I am having a slight problem when dynamically hiding columns in a data table.

When I use the set the "Hidden" property to true, I seem to end up with a solid black column on the far right of the report... but only when generating the report as a PDF.

I've searched the internet quite a bit trying to track down the issue, but I can't seem to find anything.

Has anyone encountered this problem before? Suggestions?

Thanks,

David

check the background color

it should be transperant

|||

All the cells and columns are set to have a background of transparent

no luck :(

|||Anyone else have any thoughts on this problem?|||

ur best bet would be to hide table row itself rather than just hiding a column and u can give a condition instead of just setting the hidden column to true.

Hiding table columns :: urgent

Hi All .. I want to hide columns in a Table based on the level of drilldowns.
Say I have a report with Country,State,City,Count first level I want to show
only Country,Count and second level Country,State,Count and so on. I tried
giving the toggle item property of the Column to a textbox in the first group
but gives an error saying
Toggle items must be text boxes that share the same scope as the hidden item
or are in a scope that contains the hidden item, and cannot be contained
within the current report item unless current grouping scope has a Parent.
Also no items are available in the drop down for the toggle item. Am I doign
something wrong or is this a Reporting svc limitation. I am stuck with this
pls help ..
Thnx in advance
--
Happy Hacking
ThajeerYou need to change "Group" property instead of text item property.
Henry|||Thanx henry .. but there is group involved here . What I am trying to do is
hide a column just like hiding a row or a group and toggle the visibility
using another item. .. but the drop down itslef wont populate with any item
and if I enter the textbox name it gives me a compile error .. hope I am
clear ..
"fanh@.tycoelectronics.com" wrote:
> You need to change "Group" property instead of text item property.
> Henry
>

Hiding Subtotal in Matrix

Hi,
I have one column group and 3 columns under it in a matrix. I added subtotal
to that column group and now all the 3 columns are summarized and shown. Now
I want to hide one column summary(The other 2 column summary should be
shown). How to do that?
TIA,
SamYou will need to control the Visibility of the textboxes that makeup the
column by using an expression similar to
=iif(InScope("MatrixColumnGroupName"), false, true).
The scope portion of the Inscope() can be the name of a DatasSet, Grouping,
or DataRegion.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||There's no good way to do this in the current version.
But for a sleazy hack workaround, take a look at my reply on the thread from
yesterday titled "Matrix SubTotal"
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||Hi Chris and Bruce,
Thanks - It works
Samuel
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23TEUIwpaEHA.2812@.tk2msftngp13.phx.gbl...
> You will need to control the Visibility of the textboxes that makeup the
> column by using an expression similar to
> =iif(InScope("MatrixColumnGroupName"), false, true).
> The scope portion of the Inscope() can be the name of a DatasSet,
Grouping,
> or DataRegion.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Samuel" <samuel@.photoninfotech.com> wrote in message
> news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have one column group and 3 columns under it in a matrix. I added
> subtotal
> > to that column group and now all the 3 columns are summarized and shown.
> Now
> > I want to hide one column summary(The other 2 column summary should be
> > shown). How to do that?
> >
> > TIA,
> >
> > Sam
> >
> >
>

Hiding NULL columns from result set

Hello colleagues, I have the following table, that has two flags - show
quantity, price or both. If a flag is not set I would like the corresponding
column to not be included in the result set.
CREATE TABLE Table1 (
Id int IDENTITY (1, 1) NOT NULL ,
Name nvarchar(20) NOT NULL,
Qty int NOT NULL,
Price int NOT NULL,
ShowQty int NOT NULL,
ShowPrice int NOT NULL
)
go
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
106, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second', 22,
120, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
134, 0, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth', 44,
90, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
72, 0, 0)
SELECT
SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
null - so it should be hidden*/
FROM
Table1
CheersYOu should handle that from your client application, you cant hide an
expression if you name it in the Select statement. You can check the value
in your client application and reformat the resultset as needed.
May the forces be with you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"keber" <k@.c.com> schrieb im Newsbeitrag
news:ub9REUiZFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hello colleagues, I have the following table, that has two flags - show
> quantity, price or both. If a flag is not set I would like the
> corresponding column to not be included in the result set.
> CREATE TABLE Table1 (
> Id int IDENTITY (1, 1) NOT NULL ,
> Name nvarchar(20) NOT NULL,
> Qty int NOT NULL,
> Price int NOT NULL,
> ShowQty int NOT NULL,
> ShowPrice int NOT NULL
> )
> go
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
> 106, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second',
> 22, 120, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
> 134, 0, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth',
> 44, 90, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
> 72, 0, 0)
>
> SELECT
> SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
> SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
> null - so it should be hidden*/
> FROM
> Table1
> Cheers
>|||You can accomplish a "variable result set" using a stored procedure as follo
ws:
create procedure usp_return_values
as
if (SELECT SUM(CASE WHEN ShowPrice=1 THEN Price END) FROM Table1) is null
SELECT SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty' FROM Table1
else
SELECT
SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price'
FROM
Table1
go
exec usp_return_values
Edgardo Valdez
MCSD, MCDBA, MCSE
"keber" wrote:

> Hello colleagues, I have the following table, that has two flags - show
> quantity, price or both. If a flag is not set I would like the correspondi
ng
> column to not be included in the result set.
> CREATE TABLE Table1 (
> Id int IDENTITY (1, 1) NOT NULL ,
> Name nvarchar(20) NOT NULL,
> Qty int NOT NULL,
> Price int NOT NULL,
> ShowQty int NOT NULL,
> ShowPrice int NOT NULL
> )
> go
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
> 106, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second', 22
,
> 120, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
> 134, 0, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth', 44
,
> 90, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
> 72, 0, 0)
>
> SELECT
> SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
> SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
> null - so it should be hidden*/
> FROM
> Table1
> Cheers
>
>|||In a tiered arachitecture, display is done in the front end and not the
database. This is a fundamental programming principle .. far more
fundamental than SQL.
We do not use assembly language styles flags in good SQL. Nor do we
use an IDENTITY columns when we have a relational key. Also why is a
price INTEGER and not DECIMAL()? Why do you think that a column is a
field, when they are totally different?
Look at what you wrote; each row would use zero, one or both of the
values in the summations. The results would be meaningless because you
have no data integrity.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117587045.951057.122160@.g14g2000cwa.googlegroups.com...
[ snip ]

> The results would be meaningless because you
> have no data integrity.
Ahhh ... but for some companies I've done work
for in the past, those are just the type of results
they're looking for. Meaningless.|||Celko, my young friend, I advice you to lose your attitude and not turn this
into a psychopathic discussion of how and what should look like in your
opinion. This is a simplified sample query used for dynamic reporting with
or without any front-end and several entry points. The flags are not a
business rule, they meant to directly affect the resultset.
cheers
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117587045.951057.122160@.g14g2000cwa.googlegroups.com...
> In a tiered arachitecture, display is done in the front end and not the
> database. This is a fundamental programming principle .. far more
> fundamental than SQL.
> We do not use assembly language styles flags in good SQL. Nor do we
> use an IDENTITY columns when we have a relational key. Also why is a
> price INTEGER and not DECIMAL()? Why do you think that a column is a
> field, when they are totally different?
> Look at what you wrote; each row would use zero, one or both of the
> values in the summations. The results would be meaningless because you
> have no data integrity.
>

Hiding columns until toggled

Hi all,

Sorry if this question is a bit basic but I'm fairly new to reporting Services, I've searched for the answer but cannot find a definative answer.

I have created a table report with three groups, Product Group, Product Type and Location. When the report is run there is obviously no data displayed in the report except for the first group, Product Group. What I would like to do is hide the columns without data until the first group is expanded and so on with all three groups.

Is this possible and if so how?

Humbly yours,

Chris

Take a look at these forum threads; I think they can help you out!

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=519430&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=544713&SiteID=1

|||

Thanks. The solutions described in the links you provided were not exactly what I had in mind, but they're close enough.

Cheers,

CPH

hiding columns in a matrix

I am building a matrix report that has fiscal quarters as the column group. I
have multiple data columns within the group. I am trying to allow the report
user to choose which data is displayed by using a parameter. I am able to
hide the columns of data that are not selected but the fiscal quarter labels
remain. Is there a way to limit the column group headers to only the visible
columns?You can hide the controls by setting the Visible option for that control. You
may have to use an expression so that the control is visible when it
satisfies the condition. See how 'iif' can be used in report programming MSDN
documentation.
HTH
Rajesh
MCSD.NET
http://meenrajan.blogspot.com
"KenBo" wrote:
> I am building a matrix report that has fiscal quarters as the column group. I
> have multiple data columns within the group. I am trying to allow the report
> user to choose which data is displayed by using a parameter. I am able to
> hide the columns of data that are not selected but the fiscal quarter labels
> remain. Is there a way to limit the column group headers to only the visible
> columns?

Hiding columns does not shrink report

I am trying to hide columns on a report depending on specific parameters.
Although the columns become invisible and the table resizes as per the data,
the report width still remains the same. Hence, when the report is exported
to pdf, although the data fits on one screen, it still prints one extra empty
page. I do not want to use a matrix, because its a complex report which has
already been developed and I do not have enough time for re-development.The size of the report will be picking up on the body width. [If you can't
see this go to the property window, click the drop down at the top and select
'body']
So if you have 50 columns but are only showing 3 it will still default to
the width of the 50 columns - the body is always outside of this.
You could try having a landscape page, using smaller fonts, removing
uneccessary decimal places, rotaing column headers to be vertical, using
abbreviations in addition to making the column widths as small as they will
go.
"Madhusudan" wrote:
> I am trying to hide columns on a report depending on specific parameters.
> Although the columns become invisible and the table resizes as per the data,
> the report width still remains the same. Hence, when the report is exported
> to pdf, although the data fits on one screen, it still prints one extra empty
> page. I do not want to use a matrix, because its a complex report which has
> already been developed and I do not have enough time for re-development.|||Actually, your suggestions have already been implemented. Right now, there is
hardly any space in the report for additions. Whatever was possible has
already been made.
I was able to make out that the issue was due to the body width. Is there a
way to dynamically change the body width at runtime?
Also I have noted that, if I remove the page footer, the white space is not
printed on the html report. In this case the whole report resizes to the size
of the table. The report that I am working on, does not have a page header,
but it does have a page footer which just contains the page no. (page x of
y).
Thanks and Regards
Madhusudan
"adolf garlic" wrote:
> The size of the report will be picking up on the body width. [If you can't
> see this go to the property window, click the drop down at the top and select
> 'body']
> So if you have 50 columns but are only showing 3 it will still default to
> the width of the 50 columns - the body is always outside of this.
> You could try having a landscape page, using smaller fonts, removing
> uneccessary decimal places, rotaing column headers to be vertical, using
> abbreviations in addition to making the column widths as small as they will
> go.
> "Madhusudan" wrote:
> > I am trying to hide columns on a report depending on specific parameters.
> > Although the columns become invisible and the table resizes as per the data,
> > the report width still remains the same. Hence, when the report is exported
> > to pdf, although the data fits on one screen, it still prints one extra empty
> > page. I do not want to use a matrix, because its a complex report which has
> > already been developed and I do not have enough time for re-development.|||Madhusudan,
did you get a solution to this? I have the same issue.
Regards,
Andrew|||No Andrew, I did not get any answer to this question. Finally I had to create
a new report with the extra columns and show the respective report file
depending on the parameter selected. I know that, this would cause
maintenance issues as both the files would have to be updated if any changes
had to be done, but it was the only way out at that moment.
Regards,
Madhusudan
"Duke (AN247)" wrote:
> Madhusudan,
> did you get a solution to this? I have the same issue.
> Regards,
> Andrew
>|||Thanks Madhusudan,
that was my fallback plan. I was thinking of making the maintenance easier
by writing a utility application that would take the master version of a
given report, and then output extra RDL files with the hidden columns removed
from the RDL and the page resized.
Cheers,
Andrew

Hiding columns

Hello!

I want to hide/show my report's columns based on a multivalued parameter, but I don't want my table with a "gap" (this is what happens when I use a expression on the visibility property)! Is there anyway, any workaround I can do to achieve this? Is it possible to shrink table size based on this parameter?

Thank you!

A gap will exists if you put the visibility not on the column. if you put the visible / Hide property on the cells by marking them only the cells will be made invisible, but as far as 1 columns still exists, it will be shown in the report.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thank you very much Jens!|||

Hello.

I have a similar problem. I can hide my colums by putting the visibility for the column to "hidden". My problem is: How can I unhide the column in the preview tab? What I actually want to achieve is the following: if you click on a textbox in column 1 (column 1 has product categories), colum 2 (the one that was hidden, it contains the product subcategories) shall appear. I assume that it works with toogle items, but when I enter the name of the textbox of column 1 in the toogle item properties of column 2, it does not work.

Can anyone help?

Thanks a lot in advance!

Hiding columns

hi
I have 2 doubts . Need to finish some reports by tomorrow :
1.How to hide certain columns in a report based on say a value being returned from a SP.
2. I want all the recoreds in the report to be shown in a single page and without any page breaks...
Any suggestions will be very helpful
Thanks
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.1. Columns have a visibility property that can be based on any value.
2. Set the page height to something large.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OprEejuVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> hi
> I have 2 doubts . Need to finish some reports by tomorrow :
> 1.How to hide certain columns in a report based on say a value being
returned from a SP.
> 2. I want all the recoreds in the report to be shown in a single page and
without any page breaks...
> Any suggestions will be very helpful
> Thanks
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Hiding columns

Hi
One more query!!
What expression can I use to set the visibility of a list or a matrix
I tried
=IIf(Fields!count.Value > 20, 0 , 1 )
and
=IIf(Fields!count.Value > 20, "False", "True" )
and
=IIf(Fields!count.Value > 20, "Hidden", "Visible" )
none of these work.
Can anyone tell me what is wrong here?
Thanks
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.I am suspicious that you are using Fields!count.Value and not
Sum(Fields!count.Value) if you are trying to hide a list or matrix as you
will probably want to do this over multiple rows. The first one should work
as should an expression that returns a boolean, =(Fields!Count.Value>20).
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uAZQZYvVEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi
> One more query!!
> What expression can I use to set the visibility of a list or a matrix
> I tried
> =IIf(Fields!count.Value > 20, 0 , 1 )
> and
> =IIf(Fields!count.Value > 20, "False", "True" )
> and
> =IIf(Fields!count.Value > 20, "Hidden", "Visible" )
> none of these work.
> Can anyone tell me what is wrong here?
> Thanks
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

hide warning from query analyser

When I renaming tables,columns below message comes to Query Analayaser
Result Window.
Is there any way to hide it,
because this may hit performance if there are 1000's of columns updated
Thanks
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'F28'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'F27'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'F26'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
The COLUMN was renamed to 'F31'.Did you try SET ANSI_WANRINGS OFF? I forget the list of warnings that this
suppresses, I know it's not all of them...
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:OP9FBW1ODHA.3700@.tk2msftngp13.phx.gbl...
> When I renaming tables,columns below message comes to Query Analayaser
> Result Window.
> Is there any way to hide it,
> because this may hit performance if there are 1000's of columns updated
> Thanks
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F28'.
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F27'.
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F26'.
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F31'.
>|||that one's a raiserror, so no. Why would performance of an object rename
process be a concern?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:Oj3WJb1ODHA.704@.tk2msftngp13.phx.gbl...
> Did you try SET ANSI_WANRINGS OFF? I forget the list of warnings that
this
> suppresses, I know it's not all of them...
>
> "Abraham" <binu_ca@.yahoo.com> wrote in message
> news:OP9FBW1ODHA.3700@.tk2msftngp13.phx.gbl...
> > When I renaming tables,columns below message comes to Query Analayaser
> > Result Window.
> > Is there any way to hide it,
> > because this may hit performance if there are 1000's of columns updated
> > Thanks
> >
> > Caution: Changing any part of an object name could break scripts and
> stored
> > procedures.
> > The COLUMN was renamed to 'F28'.
> > Caution: Changing any part of an object name could break scripts and
> stored
> > procedures.
> > The COLUMN was renamed to 'F27'.
> > Caution: Changing any part of an object name could break scripts and
> stored
> > procedures.
> > The COLUMN was renamed to 'F26'.
> > Caution: Changing any part of an object name could break scripts and
> stored
> > procedures.
> > The COLUMN was renamed to 'F31'.
> >
> >
>|||I don't know of any way to turn off this message.
> because this may hit performance if there are 1000's of columns updated
If you are frequently renaming lots of columns then maybe you should rethink
whether that is really the best solution. I can't think of a reason why you
would want to do this regularly.
--
David Portas
--
Please reply only to the newsgroup
--
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:OP9FBW1ODHA.3700@.tk2msftngp13.phx.gbl...
> When I renaming tables,columns below message comes to Query Analayaser
> Result Window.
> Is there any way to hide it,
> because this may hit performance if there are 1000's of columns updated
> Thanks
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F28'.
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F27'.
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F26'.
> Caution: Changing any part of an object name could break scripts and
stored
> procedures.
> The COLUMN was renamed to 'F31'.
>