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
>
>

History Cross-Tab via Cursors

I have a table with a PK of id and timestamp, plus other variables: an
update of a row adds another row with the same id but a later timestamp. I
need to create a report that lists the variables that have changed. The
following proof of concept code works fine, but I was wondering if the same
thing can be done more simply--perhaps without cursors.
Thanks for taking a look. All comments appreciated.
--FINAL RESULT TABLE FOR HISTORY REPORTING
drop table rpt_tbl
go
create table rpt_tbl
(fld_name varchar(20),
old_rpt_id int,
old_rpt_timestamp datetime,
old_fld varchar(20),
new_rpt_id int,
new_rpt_timestamp datetime,
new_fld varchar(20))
--TEST DATA
create table PROD
(prod_id int,
prod_timestamp datetime,
prod_name varchar(20),
prod_categ varchar(20))
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/07/05', 'Acme', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/08/08', 'Best', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2004/07/10', 'Pink', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/01/05', 'Red', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/02/17', 'Fuchsia', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(3, '2005/02/17', 'Ivory', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(4, '2005/4/22', 'Yellow', 'Color')
--VIEW
--GET ROWS IN PROD TABLE WITH UDPATE HISTORY
--that is, multiple rows for same prod_id, with different timestamps
create view DUPES as
select * from prod
where prod_id in
(select prod_id from prod
group by prod_id
having count(*) > 1)
--CREATE 1-ROW TABLE FOR COMPARE
create function create_table
(@.prod_id int,
@.prod_timestamp datetime)
returns table
as return
(select
prod_id, prod_timestamp,
prod_name, prod_categ
from dupes where
(prod_id = @.prod_id and prod_timestamp = @.prod_timestamp))
--COMPARE 2 ROWS
create proc compare_2_rows
(@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime)
as
declare
@.a_prod_name varchar(20),
@.a_prod_categ varchar(20),
@.b_prod_name varchar(20),
@.b_prod_categ varchar(20)
declare xc2 cursor for
select * from
create_table (@.a_prod_id, @.a_prod_timestamp) t1
join
create_table (@.b_prod_id, @.b_prod_timestamp) t2
on t1.prod_id = t2.prod_id
open xc2
fetch next from xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
while @.@.fetch_status = 0
begin
if @.a_prod_name <> @.b_prod_name
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('name',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_name,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_name)
if @.a_prod_categ <> @.b_prod_categ
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('categ',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_categ)
fetch xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
end
close xc2
deallocate xc2
create proc history_proc as
--OVERALL PROC
--1. empty report table
--2. use cursor to find paired PK's
--3. exec proc compare_2_rows with PK params for paired data
-- a. use cursor to join function tables for paired data
-- b. compare 2 prod_names and insert paired data into
-- rpt_tbl
-- c. compare 2 prod_categs and insert paired data into
-- rpt_tbl
truncate table rpt_tbl --empty report table
declare --the keys for the rows to be compared
--@.a refers to the older row
--@.b refers to the newer (more recent) row
@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime
declare xc cursor for select prod_id, prod_timestamp
from dupes
open xc
--1st record (older row)
fetch xc into @.a_prod_id, @.a_prod_timestamp
--2nd record (newer row): has to be at least a pair for same id
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
while @.@.fetch_status = 0
begin
--if a break in keys, don't compare them,
--and move newer key to older key variables
if @.b_prod_id <> @.a_prod_id
begin
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
goto skip
end
exec compare_2_rows
@.a_prod_id, @.a_prod_timestamp,
@.b_prod_id, @.b_prod_timestamp
--after compare, move newer key to older key variables
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
skip:
--get key for new row
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
end
close xc
deallocate xc
--THIS RUNS THE OVERALL PROC
exec history_proc
--THIS SHOWS WHAT'S IN THE FINAL RESULTS TABLE
select * from rpt_tblBased on your example data, the primary key should be prod_id ,
prod_categ and prod_timestamp.
Try this:
select ProdNew.prod_id
, ProdNew.prod_categ
, ProdNew.prod_timestamp
, ProdNew.prod_name
, ProdOld.prod_timestamp
, ProdOld.prod_name
from (select Prod6.prod_id, Prod6.prod_categ ,
max(Prod6.prod_timestamp)
from Prod as Prod6
group by Prod6.prod_id, Prod6.prod_categ
) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
join Prod as ProdNew
on ProdNew.prod_id = ProdLastest.prod_id
and ProdNew.prod_categ = ProdLastest.prod_categ
and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
left outer join
(
select Prod1.prod_id
, Prod1.prod_categ
, Prod1.prod_timestamp
, Prod1.prod_name
from Prod as Prod1
join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
from Prod
join (select prod_id, prod_categ , max(prod_timestamp)
from Prod
group by Prod.prod_id, Prod.prod_categ
) as Prod3 ( prod_id, prod_categ , prod_timestamp)
on Prod.prod_id = Prod3.prod_id
and Prod.prod_categ = Prod3.prod_categ
and Prod.prod_timestamp < Prod3.prod_timestamp
group by Prod.prod_id, Prod.prod_categ
) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
on Prod1.prod_id = ProdOlder.prod_id
and Prod1.prod_categ = ProdOlder.prod_categ
and Prod1.prod_timestamp = ProdOlder.prod_timestamp
) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
on ProdOld.prod_id = ProdNew.prod_id
and ProdOld.prod_categ = ProdNew.prod_categ
order by ProdNew.prod_id
, ProdNew.prod_categ
*** Sent via Developersdex http://www.examnotes.net ***|||Check out the doc for the RAC utility.
Focus on the 'What' instead of the 'How'.
www.rac4sql.net|||Thanks--I'll have to take a look at your code in the morning. I'm too blear
y.
The pk is prod_id/prod_timestamp. In the real world, the timestamp would
have year-month-day-time.
There's no limit to the possible number of rows per id. I need to compare
every column (there will be about 10 or 20, in the real world) in every row
for the same prod_id, so that the final table has a row for every old/new
column pair which are different.
"Carl Federl" wrote:

> Based on your example data, the primary key should be prod_id ,
> prod_categ and prod_timestamp.
> Try this:
> select ProdNew.prod_id
> , ProdNew.prod_categ
> , ProdNew.prod_timestamp
> , ProdNew.prod_name
> , ProdOld.prod_timestamp
> , ProdOld.prod_name
> from (select Prod6.prod_id, Prod6.prod_categ ,
> max(Prod6.prod_timestamp)
> from Prod as Prod6
> group by Prod6.prod_id, Prod6.prod_categ
> ) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
> join Prod as ProdNew
> on ProdNew.prod_id = ProdLastest.prod_id
> and ProdNew.prod_categ = ProdLastest.prod_categ
> and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
> left outer join
> (
> select Prod1.prod_id
> , Prod1.prod_categ
> , Prod1.prod_timestamp
> , Prod1.prod_name
> from Prod as Prod1
> join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
> from Prod
> join (select prod_id, prod_categ , max(prod_timestamp)
> from Prod
> group by Prod.prod_id, Prod.prod_categ
> ) as Prod3 ( prod_id, prod_categ , prod_timestamp)
> on Prod.prod_id = Prod3.prod_id
> and Prod.prod_categ = Prod3.prod_categ
> and Prod.prod_timestamp < Prod3.prod_timestamp
> group by Prod.prod_id, Prod.prod_categ
> ) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
> on Prod1.prod_id = ProdOlder.prod_id
> and Prod1.prod_categ = ProdOlder.prod_categ
> and Prod1.prod_timestamp = ProdOlder.prod_timestamp
> ) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
> on ProdOld.prod_id = ProdNew.prod_id
> and ProdOld.prod_categ = ProdNew.prod_categ
> order by ProdNew.prod_id
> , ProdNew.prod_categ
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

highlighting a table row

Have generated a report that uses a table. In the table are 2 fields, the
first is a severity and the last is the timeOfResponse which is a numeric. I
would like to highlight a row if the combination of severity and
timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
a matrix?
The function would be like
iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)In the properties window, put your IIF statement under background color. So
it would be something like
= iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
"Glass" wrote:
> Have generated a report that uses a table. In the table are 2 fields, the
> first is a severity and the last is the timeOfResponse which is a numeric. I
> would like to highlight a row if the combination of severity and
> timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> a matrix?
> The function would be like
> iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)|||scraejtp:
I actually have this in the background color property and it colors all rows
of the table pink.
= iif( (fields!timeOfResponse > 900),"pink","transparent")
A warning is also issued:
Value of the background color property for the textbox 'timeOfResponse' is
"transparent", which is not a valid background color.
I tried changing "transparent" to "blue". No warnings but all rows returned
pink.
Glass
"scraejtp" wrote:
> In the properties window, put your IIF statement under background color. So
> it would be something like
> = iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
> "Glass" wrote:
> > Have generated a report that uses a table. In the table are 2 fields, the
> > first is a severity and the last is the timeOfResponse which is a numeric. I
> > would like to highlight a row if the combination of severity and
> > timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> > a matrix?
> >
> > The function would be like
> > iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)|||Okay... problem solved. Have placed the expression under the background
properties for 'tablerow'. Also, discovered that cannot use 'transparent' or
'nothing' so used 'white'.
"Glass" wrote:
> scraejtp:
> I actually have this in the background color property and it colors all rows
> of the table pink.
> = iif( (fields!timeOfResponse > 900),"pink","transparent")
> A warning is also issued:
> Value of the background color property for the textbox 'timeOfResponse' is
> "transparent", which is not a valid background color.
> I tried changing "transparent" to "blue". No warnings but all rows returned
> pink.
> Glass
> "scraejtp" wrote:
> > In the properties window, put your IIF statement under background color. So
> > it would be something like
> > = iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
> >
> > "Glass" wrote:
> >
> > > Have generated a report that uses a table. In the table are 2 fields, the
> > > first is a severity and the last is the timeOfResponse which is a numeric. I
> > > would like to highlight a row if the combination of severity and
> > > timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> > > a matrix?
> > >
> > > The function would be like
> > > iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)|||Yeah, I thought you were already under table row, or an individual cell.
That is weird that it will not allow transparent because that looks to be
the default background for mine.
"Glass" wrote:
> Okay... problem solved. Have placed the expression under the background
> properties for 'tablerow'. Also, discovered that cannot use 'transparent' or
> 'nothing' so used 'white'.
>
> "Glass" wrote:
> > scraejtp:
> >
> > I actually have this in the background color property and it colors all rows
> > of the table pink.
> >
> > = iif( (fields!timeOfResponse > 900),"pink","transparent")
> >
> > A warning is also issued:
> >
> > Value of the background color property for the textbox 'timeOfResponse' is
> > "transparent", which is not a valid background color.
> >
> > I tried changing "transparent" to "blue". No warnings but all rows returned
> > pink.
> >
> > Glass
> >
> > "scraejtp" wrote:
> >
> > > In the properties window, put your IIF statement under background color. So
> > > it would be something like
> > > = iif( (fields!severity = 1 and timeOfResponse > 900),"pink","transparent")
> > >
> > > "Glass" wrote:
> > >
> > > > Have generated a report that uses a table. In the table are 2 fields, the
> > > > first is a severity and the last is the timeOfResponse which is a numeric. I
> > > > would like to highlight a row if the combination of severity and
> > > > timeOfResponse exceeds a maximum. Is this possible for a table? If not, in
> > > > a matrix?
> > > >
> > > > The function would be like
> > > > iif( (fields!severity = 1 and timeOfResponse > 900),"pink",nothing)

Highlighting a row

Hi,
While previewing the report, Is there any way to highlight the selected row as we do in SpreadSheets (Like Excel)?
I need to hightlight the row which was selected by the user and not a particular row.
If you have any idea, pls help me
ThanksHi,

Yes, it's possible in RDC Method of Crystal Report Design Document Module.

you can catch the table.field click event at run time, in that event make code for change the field background color.

Thanks.

Originally posted by harmonycitra
Hi,

While previewing the report, Is there any way to highlight the selected row as we do in SpreadSheets (Like Excel)?

I need to hightlight the row which was selected by the user and not a particular row.

If you have any idea, pls help me

Thanks|||Thanks,

But I'm using ASP to run my report, using RDC Code and Crystal Report ActiveXViewer Control to display the report.

In this, how can I write the code?

Thanks,

Highlight row

Hi,

After the report is rendered in the report viewer control (In windows forms application), I want to provide user with a facility to highlight rows (data). Is it possible with SSRS/Report viewer?

For example, amongst the list of 100 invoices, user should be able to highlight 10 invoices.

Thanks in advance.

Paresh

Not real easily. The only approach I can think of is to have a multi-value parameter on the report which contains invoice numbers. The user would need to select invoice report parameter values equal to the invoice numbers to highlight, then rerun the report checking to see if the row matched the selected report parameter values.

In other words, not real easily.

hiding Table Rows

Is it possible to determine whether or not another row in a table is visible? The example is in a multiple detail row table, where I want to display a 'header' row (really just another detail row), if any of the other detail rows in the 'section' are visible.  Is there some syntax like ReportItems!TableRow7.property("Hidden")=?? that I could use to determine the visibility state of a row?Thanks
Anil

Hi,
in Layout Tab, select the row and in the Property Panel select Visibility -> Hidden -> Expression.
Best Regards
|||

Thanks for your response but it does not solve my problem.

My Problem is I have master detail records.

If detail records are not existing then I have to make the master row invisible

Could anybody help me out fom this problem.

Ofcourse I solved it by modifying the database query but still want the solution of this problem

Thanks

Anil

|||

Hi...Yes u can hide the Rows using If Expressions...do onething...what ever the textbox u want hide...go.
1)..selct Property Window(F4) ...
2) then click Visibility -> hidden -> Select <Expressin..>
then write like this....I am writting One Example Only.....use this...

=iif(ReportItems!textbox2.Value="Y",False,True)
False-- Visible
True- Hide of textbox
U cAN WRITE FOR ANY THING....
Ok..Good Luck...

Hiding Table Header on First Page

I would like to hide a header row on page one and only display it on the
consecutive pages. Since I can't refer to the global page number variable,
I can't think of any way to accomplish this.
Basically, my end result is that I want to have a report header that
contains a bold graphic and other details like report name and execution
time, which will only display on the first page. Then I want to have the
same header display without the graphic on consecutive pages.. I tried
simply supressing the graphic on pages > 1, but then there is still empty
space since their is no way of dynamically sizing the page header. My goal
is to conserve real estate.
So then I tried adding a table into my body that mirrors my page header,
sans graphic, but then I run into the issue not being able to supress it on
page one.
Does anyone know of an alternate solution for what I am trying to do?
Thanks,
LisaThe technique here is to
* Add a page header that to the report that does not contain the graphic.
Set the PrintOnFirstPage to false. This will supress it from appearing on
page 1 of the report.
* Add a report header to the report that contains the graphic. This will be
the first item
item in the report body. In your case you would want to use a rectangle that
contains the image and other text.
* Next add the rest of your report below the report header.
The sample report that demonstrates this technique is at the end of this
posting.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:%23HnQ5i7XEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I would like to hide a header row on page one and only display it on the
> consecutive pages. Since I can't refer to the global page number
variable,
> I can't think of any way to accomplish this.
> Basically, my end result is that I want to have a report header that
> contains a bold graphic and other details like report name and execution
> time, which will only display on the first page. Then I want to have the
> same header display without the graphic on consecutive pages.. I tried
> simply supressing the graphic on pages > 1, but then there is still empty
> space since their is no way of dynamically sizing the page header. My
goal
> is to conserve real estate.
> So then I tried adding a table into my body that mirrors my page header,
> sans graphic, but then I run into the issue not being able to supress it
on
> page one.
> Does anyone know of an alternate solution for what I am trying to do?
> Thanks,
> Lisa
>
ReportHeader.PageHeader.rdl
----
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<PageHeader>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.75in</Width>
<CanGrow>true</CanGrow>
<Value>def</Value>
</Textbox>
</ReportItems>
<PrintOnLastPage>true</PrintOnLastPage>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Height>0.25in</Height>
</PageHeader>
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<ZIndex>1</ZIndex>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<Top>0.75in</Top>
<Width>6.70833in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.23611in</Width>
</TableColumn>
<TableColumn>
<Width>2.23611in</Width>
</TableColumn>
<TableColumn>
<Width>2.23611in</Width>
</TableColumn>
</TableColumns>
</Table>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox2</rd:DefaultName>
<Height>0.625in</Height>
<CanGrow>true</CanGrow>
<Value>abc</Value>
</Textbox>
</ReportItems>
<Style />
<Height>2.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5c316211-903e-46bc-822a-eeb33657050e</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.75in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>c669cf6f-4e17-4f54-9599-e857f8ed9a14</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||Thank you very much. This brings me much closer to the results I want to
achieve. Although, I am noticing that although the Page Header is not
displayed on the first page, it is still taking up a bit of space. On page
one, my "psuedo" page header is beginning about an inch below the top. I
don't want my margin to be this large. I checked both the report margins
and the body and page header margins. All are set to the lowest possible
top height. Maybe I am missing something?
Thanks,
Lisa
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:uFSEjQ9XEHA.3988@.tk2msftngp13.phx.gbl...
> The technique here is to
> * Add a page header that to the report that does not contain the graphic.
> Set the PrintOnFirstPage to false. This will supress it from appearing on
> page 1 of the report.
> * Add a report header to the report that contains the graphic. This will
be
> the first item
> item in the report body. In your case you would want to use a rectangle
that
> contains the image and other text.
> * Next add the rest of your report below the report header.
> The sample report that demonstrates this technique is at the end of this
> posting.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
> news:%23HnQ5i7XEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I would like to hide a header row on page one and only display it on the
> > consecutive pages. Since I can't refer to the global page number
> variable,
> > I can't think of any way to accomplish this.
> >
> > Basically, my end result is that I want to have a report header that
> > contains a bold graphic and other details like report name and execution
> > time, which will only display on the first page. Then I want to have
the
> > same header display without the graphic on consecutive pages.. I tried
> > simply supressing the graphic on pages > 1, but then there is still
empty
> > space since their is no way of dynamically sizing the page header. My
> goal
> > is to conserve real estate.
> >
> > So then I tried adding a table into my body that mirrors my page header,
> > sans graphic, but then I run into the issue not being able to supress it
> on
> > page one.
> >
> > Does anyone know of an alternate solution for what I am trying to do?
> >
> > Thanks,
> >
> > Lisa
> >
> >
> ReportHeader.PageHeader.rdl
> ----
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <PageHeader>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>2.75in</Width>
> <CanGrow>true</CanGrow>
> <Value>def</Value>
> </Textbox>
> </ReportItems>
> <PrintOnLastPage>true</PrintOnLastPage>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> <Height>0.25in</Height>
> </PageHeader>
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>0.75in</Height>
> <ZIndex>1</ZIndex>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Company Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox8">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox8</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>Northwind</DataSetName>
> <Top>0.75in</Top>
> <Width>6.70833in</Width>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Footer>
> <TableColumns>
> <TableColumn>
> <Width>2.23611in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.23611in</Width>
> </TableColumn>
> <TableColumn>
> <Width>2.23611in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <Height>0.625in</Height>
> <CanGrow>true</CanGrow>
> <Value>abc</Value>
> </Textbox>
> </ReportItems>
> <Style />
> <Height>2.25in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>5c316211-903e-46bc-822a-eeb33657050e</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>6.75in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="ContactName">
> <DataField>ContactName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="ContactTitle">
> <DataField>ContactTitle</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Address">
> <DataField>Address</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="City">
> <DataField>City</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Region">
> <DataField>Region</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="PostalCode">
> <DataField>PostalCode</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Country">
> <DataField>Country</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Phone">
> <DataField>Phone</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Fax">
> <DataField>Fax</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>select * from customers</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>c669cf6f-4e17-4f54-9599-e857f8ed9a14</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
>

Hiding subtotal rows when there is only 1 row in the group

How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.

If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.

Try this:

Click on the whole row for your group footer
Go to the properties
Put this in the 'Visibility - Hidden' expression.

=IIf(CountRows("GroupName") > 1, False, True)

I tried this on one of my reports and it removed the space used by the group footer, it didn't just blank it out. If there was only one row in that group, the footer wasn't shown, but if there were more than 1 row, it would. Just as a test, you might want to create a new row below your group footer and just add some text in there so that it will show below your subtotals (if you have any). In my case, the row below my group footer was 'moved up' to be directly below the details if there was only one row displayed, otherwise, it was displayed directly below the subtotals.

Hope this helps.

Jarret

|||

It's in a matrix, so group headers and footers aren't apparent options. :(

I could see how that would work in a table though.

|||

When you choose the subtotal option for a group in a matrix, a row does get added. To affect only the subtotal cell in a matrix you need to use the InScope() function. The main thing to understand in the logic is that the subtotal cell for a group is not in scope of that group and hence the function return false for the cell.

For example, say on your rows you have 2 groups called region_group and country_group. You right-click the country textbox and select Subtotal. This adds an additional row containing just the header textbos for the subtotal. You now 3 stages for hiding the subtotal.

1.You now need to add an expression to the details cell for the Visibility -> Hidden property. The expression should be:

=Not InScope("country_group")

This should evaluate to Hidden = True for the total row as it is not in scope of the country_group. If you run this you will probably find that the details cell disappears but the heading remains.

2.Now if you try applying the same expression for Visibility to the subtotal header textbox it should also disappear but will probably leave a blank gap in it's place.

3.If you can apply this same expression to the entire subtotal row (by clicking on the row header) then this should also remove the visible gap.

I'm not sure if the last step is possible as I am unable to test this at the moment (on client site), the first 2 steps should work though.

Hope this helps. Please post the results of your attempts.

|||

Those are great suggestions, but there are no header or footer rows in a matrix.
If I select the entire row that contains the subtotal, a visible property is not exposed.

There are also column groupings after the one I'm mentioning - and if I mess with the group visibility, the successive columns are hidden or blanked out.
I'll see what else can be done to acheive the row hiding.

|||Try taking a look at Actions

Hiding Sub Total Rows depending upon the certain fields

I am trying to hide a sub total row depending upon certain fields . How would i go about doing that?

Thanks in Advance

This is how the report looks like......

Jan Feb March

Revenue R1 2 3 4

want to hide this line subtotal 2 3 4

R2 5 6 7

Total 7 9 11

Hi,

For the table row has visibility property.Write the condition in the expression of the Visibilty Property for that row.

Hope this helps

|||Its not a table . Its a matrix|||

You can select edit the matrix group, in the visibility property, select expression and used the 'runningvalue' to determine when you want not to displace your group subtotal

Hiding Rows in a Table

Is it possible to hide a row of data if for instance no data exists in that
row. so my report isn't 300 pages long when it should just be 30?
I was looking at the visibility element but didn't see anything to hide
based on expression.
Thanks,
CJThis should be fairly straight forward to accomplish. If we assume that you
want to hide a detail row when fieldx is null you would set the visibility
on the detail row as follows:
=iif(Fields!fieldsx.Value is Nothing, true, false)
Table row visibility is exposed in the Properties window.
The attached report demonstrates this technique.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> Is it possible to hide a row of data if for instance no data exists in
> that
> row. so my report isn't 300 pages long when it should just be 30?
> I was looking at the visibility element but didn't see anything to hide
> based on expression.
> Thanks,
> CJ
>
HidingTableDetailRow.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Region</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Region">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Region</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Region.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<Hidden>=iif(Fields!Region.Value is Nothing, true,
false)</Hidden>
</Visibility>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<Width>3.33334in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.00001in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT *
FROM Customers</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Hi,
Have you tried the properties box for the row/cells? If you right-click the
row/cell in question, you can click on the Advanced button. This will bring
up several tabs, one of which will be Visibility. In the first grouping of
choices, "Initial Visibility", there is an Expression option that you could
use. I'm guessing you could enter your expression there to determine the
row/cell's initial visibility.
I'm pretty new as RS, so I'm not sure if I've helped. :)
-Kelly
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> Is it possible to hide a row of data if for instance no data exists in
that
> row. so my report isn't 300 pages long when it should just be 30?
> I was looking at the visibility element but didn't see anything to hide
> based on expression.
> Thanks,
> CJ
>|||Why was the row created, if there is no data? Perhaps you need to work on
the underlying SQL?
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> Is it possible to hide a row of data if for instance no data exists in
that
> row. so my report isn't 300 pages long when it should just be 30?
> I was looking at the visibility element but didn't see anything to hide
> based on expression.
> Thanks,
> CJ
>|||Not every report is just *simple* SQL. It's a recursive sub report...
I have a complex
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> Why was the row created, if there is no data? Perhaps you need to work on
> the underlying SQL?
>
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > Is it possible to hide a row of data if for instance no data exists in
> that
> > row. so my report isn't 300 pages long when it should just be 30?
> >
> > I was looking at the visibility element but didn't see anything to hide
> > based on expression.
> >
> > Thanks,
> > CJ
> >
> >
>|||Regardless, creating and then hiding blank rows implies a fundamental design
flaw. Both take unneccesary time.
Jeff
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> Not every report is just *simple* SQL. It's a recursive sub report...
>
> I have a complex
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > Why was the row created, if there is no data? Perhaps you need to work
on
> > the underlying SQL?
> >
> >
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > Is it possible to hide a row of data if for instance no data exists in
> > that
> > > row. so my report isn't 300 pages long when it should just be 30?
> > >
> > > I was looking at the visibility element but didn't see anything to
hide
> > > based on expression.
> > >
> > > Thanks,
> > > CJ
> > >
> > >
> >
> >
>|||How do you figure?
Say we have a tree structure we are trying to report, such as?
Line A
-- Line B (child to a)
-- Line C (child to b)
-- Line D( child to A)
-- blank (no children)
Thats a fundamental design flaw?
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> Regardless, creating and then hiding blank rows implies a fundamental
design
> flaw. Both take unneccesary time.
> Jeff
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > Not every report is just *simple* SQL. It's a recursive sub report...
> >
> >
> >
> > I have a complex
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > Why was the row created, if there is no data? Perhaps you need to work
> on
> > > the underlying SQL?
> > >
> > >
> > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > Is it possible to hide a row of data if for instance no data exists
in
> > > that
> > > > row. so my report isn't 300 pages long when it should just be 30?
> > > >
> > > > I was looking at the visibility element but didn't see anything to
> hide
> > > > based on expression.
> > > >
> > > > Thanks,
> > > > CJ
> > > >
> > > >
> > >
> > >
> >
> >
>|||This is a classic drill down. Doing a drill down you will not have any
blanks. In drill down you can set it either be expanded or not (for instance
if you wanted to show line A and they have to click on the + to expand it.
Or you can show Line A, Line B, Line D and they click on the + for Line B to
expand it.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> How do you figure?
> Say we have a tree structure we are trying to report, such as?
> Line A
> -- Line B (child to a)
> -- Line C (child to b)
> -- Line D( child to A)
> -- blank (no children)
> Thats a fundamental design flaw?
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > Regardless, creating and then hiding blank rows implies a fundamental
> design
> > flaw. Both take unneccesary time.
> >
> > Jeff
> >
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > Not every report is just *simple* SQL. It's a recursive sub report...
> > >
> > >
> > >
> > > I have a complex
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > Why was the row created, if there is no data? Perhaps you need to
work
> > on
> > > > the underlying SQL?
> > > >
> > > >
> > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > Is it possible to hide a row of data if for instance no data
exists
> in
> > > > that
> > > > > row. so my report isn't 300 pages long when it should just be 30?
> > > > >
> > > > > I was looking at the visibility element but didn't see anything to
> > hide
> > > > > based on expression.
> > > > >
> > > > > Thanks,
> > > > > CJ
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes a flaw, thankyou. Don't show blanks when there are no children (')
Takes time to build a blank row, and time to hide it.
Duh
Jeff
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> How do you figure?
> Say we have a tree structure we are trying to report, such as?
> Line A
> -- Line B (child to a)
> -- Line C (child to b)
> -- Line D( child to A)
> -- blank (no children)
> Thats a fundamental design flaw?
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > Regardless, creating and then hiding blank rows implies a fundamental
> design
> > flaw. Both take unneccesary time.
> >
> > Jeff
> >
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > Not every report is just *simple* SQL. It's a recursive sub report...
> > >
> > >
> > >
> > > I have a complex
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > Why was the row created, if there is no data? Perhaps you need to
work
> > on
> > > > the underlying SQL?
> > > >
> > > >
> > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > Is it possible to hide a row of data if for instance no data
exists
> in
> > > > that
> > > > > row. so my report isn't 300 pages long when it should just be 30?
> > > > >
> > > > > I was looking at the visibility element but didn't see anything to
> > hide
> > > > > based on expression.
> > > > >
> > > > > Thanks,
> > > > > CJ
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Super, thanks for being so courteous about it...
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:esNoOFitEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Yes a flaw, thankyou. Don't show blanks when there are no children (')
> Takes time to build a blank row, and time to hide it.
> Duh
> Jeff
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> > How do you figure?
> >
> > Say we have a tree structure we are trying to report, such as?
> >
> > Line A
> > -- Line B (child to a)
> > -- Line C (child to b)
> > -- Line D( child to A)
> > -- blank (no children)
> >
> > Thats a fundamental design flaw?
> >
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > > Regardless, creating and then hiding blank rows implies a fundamental
> > design
> > > flaw. Both take unneccesary time.
> > >
> > > Jeff
> > >
> > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > > Not every report is just *simple* SQL. It's a recursive sub
report...
> > > >
> > > >
> > > >
> > > > I have a complex
> > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > > Why was the row created, if there is no data? Perhaps you need to
> work
> > > on
> > > > > the underlying SQL?
> > > > >
> > > > >
> > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > > Is it possible to hide a row of data if for instance no data
> exists
> > in
> > > > > that
> > > > > > row. so my report isn't 300 pages long when it should just be
30?
> > > > > >
> > > > > > I was looking at the visibility element but didn't see anything
to
> > > hide
> > > > > > based on expression.
> > > > > >
> > > > > > Thanks,
> > > > > > CJ
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Learning can be hard.
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:#l04yBqtEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Super, thanks for being so courteous about it...
>
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:esNoOFitEHA.3788@.TK2MSFTNGP09.phx.gbl...
> > Yes a flaw, thankyou. Don't show blanks when there are no children (')
> > Takes time to build a blank row, and time to hide it.
> >
> > Duh
> >
> > Jeff
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> > > How do you figure?
> > >
> > > Say we have a tree structure we are trying to report, such as?
> > >
> > > Line A
> > > -- Line B (child to a)
> > > -- Line C (child to b)
> > > -- Line D( child to A)
> > > -- blank (no children)
> > >
> > > Thats a fundamental design flaw?
> > >
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > > > Regardless, creating and then hiding blank rows implies a
fundamental
> > > design
> > > > flaw. Both take unneccesary time.
> > > >
> > > > Jeff
> > > >
> > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > > > Not every report is just *simple* SQL. It's a recursive sub
> report...
> > > > >
> > > > >
> > > > >
> > > > > I have a complex
> > > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > > > Why was the row created, if there is no data? Perhaps you need
to
> > work
> > > > on
> > > > > > the underlying SQL?
> > > > > >
> > > > > >
> > > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > > > Is it possible to hide a row of data if for instance no data
> > exists
> > > in
> > > > > > that
> > > > > > > row. so my report isn't 300 pages long when it should just be
> 30?
> > > > > > >
> > > > > > > I was looking at the visibility element but didn't see
anything
> to
> > > > hide
> > > > > > > based on expression.
> > > > > > >
> > > > > > > Thanks,
> > > > > > > CJ
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Alright, so then I don't understand the drill down then... How does this
create a visual heirachial grouping?
does that make sense? at least in report services? I can do it in sql no
problem, just how to get reporting to respond accordinginly... curretnly,
I'm doing it with a subreport, within a sub report (that references the
parent subreport to create the recursive definition)
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:uouebyrtEHA.1272@.TK2MSFTNGP10.phx.gbl...
> Learning can be hard.
>
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:#l04yBqtEHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Super, thanks for being so courteous about it...
> >
> >
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:esNoOFitEHA.3788@.TK2MSFTNGP09.phx.gbl...
> > > Yes a flaw, thankyou. Don't show blanks when there are no children
(')
> > > Takes time to build a blank row, and time to hide it.
> > >
> > > Duh
> > >
> > > Jeff
> > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> > > > How do you figure?
> > > >
> > > > Say we have a tree structure we are trying to report, such as?
> > > >
> > > > Line A
> > > > -- Line B (child to a)
> > > > -- Line C (child to b)
> > > > -- Line D( child to A)
> > > > -- blank (no children)
> > > >
> > > > Thats a fundamental design flaw?
> > > >
> > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > > news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > > > > Regardless, creating and then hiding blank rows implies a
> fundamental
> > > > design
> > > > > flaw. Both take unneccesary time.
> > > > >
> > > > > Jeff
> > > > >
> > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > > > > Not every report is just *simple* SQL. It's a recursive sub
> > report...
> > > > > >
> > > > > >
> > > > > >
> > > > > > I have a complex
> > > > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in
message
> > > > > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > > > > Why was the row created, if there is no data? Perhaps you need
> to
> > > work
> > > > > on
> > > > > > > the underlying SQL?
> > > > > > >
> > > > > > >
> > > > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > > > > Is it possible to hide a row of data if for instance no data
> > > exists
> > > > in
> > > > > > > that
> > > > > > > > row. so my report isn't 300 pages long when it should just
be
> > 30?
> > > > > > > >
> > > > > > > > I was looking at the visibility element but didn't see
> anything
> > to
> > > > > hide
> > > > > > > > based on expression.
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > CJ
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Hiding rows in a matrix and still showing subtotals for that row

Hi
I'm trying to tidy up a report that has a detail row that's not needed but
the subtotal row for that row is... hope that makes sense. The subtotal is
in fact is an average of the detail row. If I hide the detail row, the AVG
row also disappears! This is more or less what it looks like:
Machine Name | Cloth | Utilised Looms | 1 3 5 6
Available Looms | 6 4 7 6
Total Utilised Looms | 1 3 5 6
Available Looms | 6 4 7 6
What I'm trying to do is hide the second row...
Any ideas would be appreciated.
Many thanks
Rob
--
Message posted via http://www.sqlmonster.comIf you edit the properties of de detail-cel an set the visibility/hidden
property =TRUE it should work
"robhob via SQLMonster.com" wrote:
> Hi
> I'm trying to tidy up a report that has a detail row that's not needed but
> the subtotal row for that row is... hope that makes sense. The subtotal is
> in fact is an average of the detail row. If I hide the detail row, the AVG
> row also disappears! This is more or less what it looks like:
> Machine Name | Cloth | Utilised Looms | 1 3 5 6
> Available Looms | 6 4 7 6
> Total Utilised Looms | 1 3 5 6
> Available Looms | 6 4 7 6
> What I'm trying to do is hide the second row...
> Any ideas would be appreciated.
> Many thanks
> Rob
> --
> Message posted via http://www.sqlmonster.com
>

Hiding main report row if subreport is empty

I have a main report with a list of people, and a subreport that has
data about that person. How can I hide the person's name (the main
report row) if the subreport has no data about that person?
Thanks,
Hanhannibal,
I answered this yesterday. See your initial post.
Michael
"Hannibal111111" wrote:
> I have a main report with a list of people, and a subreport that has
> data about that person. How can I hide the person's name (the main
> report row) if the subreport has no data about that person?
> Thanks,
> Han
>

Hiding Main Report row if subreport is empty

I have a main report which has a list of people, and a subreport with
some data about each person. How do I hide that person's name (ie the
main report row) if the subreport of that person does not contain any
data?
Thanks,
HanHey Hannibal,
There are probably a few ways to deal with this, but what I usually do is
just add a reference in your main reports dataset to the people_detail table.
Then add an expression that evaluates that reference. For example:
if your dataset is
SELECT pkyPeople, firstname, lastname FROM People
modify it to be
SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
FROM People as p LEFT OUTER JOIN People_Details as pd on
p.pkyPeople=pd.fkyPeople
Then, when your running your report you just need an expression in the
'visibility' sections 'Hidden' field that reads
=IIf(Fields!fkyPeople.value = "", True, False)
Michael
"Hannibal111111" wrote:
> I have a main report which has a list of people, and a subreport with
> some data about each person. How do I hide that person's name (ie the
> main report row) if the subreport of that person does not contain any
> data?
> Thanks,
> Han
>|||On Jul 18, 7:16 pm, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Hey Hannibal,
> There are probably a few ways to deal with this, but what I usually do is
> just add a reference in your main reports dataset to the people_detail table.
> Then add an expression that evaluates that reference. For example:
> if your dataset is
> SELECT pkyPeople, firstname, lastname FROM People
> modify it to be
> SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
> FROM People as p LEFT OUTER JOIN People_Details as pd on
> p.pkyPeople=pd.fkyPeople
> Then, when your running your report you just need an expression in the
> 'visibility' sections 'Hidden' field that reads
> =IIf(Fields!fkyPeople.value = "", True, False)
> Michael
>
> "Hannibal111111" wrote:
> > I have a main report which has a list of people, and a subreport with
> > some data about each person. How do I hide that person's name (ie the
> > main report row) if the subreport of that person does not contain any
> > data?
> > Thanks,
> > Han- Hide quoted text -
> - Show quoted text -
I would love to do that, unfortunately the detail data is contained on
a different server, which is the reason why I had to use the
subreport. Any other ideas?|||As a matter of fact I do!
i would change my nesting from
Main Report (people data)
--> SubReport (people details)
To be
1. Main Report (Parameter Data)
2. --> SubReport (People Data filtered by paramters. 1 person at a
time)
3. --> subSubReport (Details)
Now, level 1 simply passes the current person to level 2. At level 2 you
need two datasources. The first one looks at the People Server the second
one at the Details Server. The first dataset for the level 2 report looks
just like it does currently, except you need to add a WHERE PeopleId =@.People (to filter for the current person). Then you need to add a second
dataset, whose DataSource is the Detail Server. In this dataset you filter
for the details of that same person. Then just evaluate
First(Fields!PersonId.Value,"PersonDetails") to see if there is any data. At
this point you will then know whether or not to hide the details subreport.
Please feel free to ask for more description if this doesn't make sense! I
hope this helps though.
Michael
> On Jul 18, 7:16 pm, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Hey Hannibal,
> > There are probably a few ways to deal with this, but what I usually do is
> > just add a reference in your main reports dataset to the people_detail table.
> > Then add an expression that evaluates that reference. For example:
> >
> > if your dataset is
> >
> > SELECT pkyPeople, firstname, lastname FROM People
> >
> > modify it to be
> >
> > SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
> > FROM People as p LEFT OUTER JOIN People_Details as pd on
> > p.pkyPeople=pd.fkyPeople
> >
> > Then, when your running your report you just need an expression in the
> > 'visibility' sections 'Hidden' field that reads
> >
> > =IIf(Fields!fkyPeople.value = "", True, False)
> >
> > Michael
> >
> >
> >
> > "Hannibal111111" wrote:
> > > I have a main report which has a list of people, and a subreport with
> > > some data about each person. How do I hide that person's name (ie the
> > > main report row) if the subreport of that person does not contain any
> > > data?
> >
> > > Thanks,
> >
> > > Han- Hide quoted text -
> >
> > - Show quoted text -
> I would love to do that, unfortunately the detail data is contained on
> a different server, which is the reason why I had to use the
> subreport. Any other ideas?
>|||Ah...I just figured out why this may not work because I've never deployed
anything like that...however can you assign 2 datasources in ReportManager to
a single report? Interesting problem....
"Hannibal111111" wrote:
> On Jul 18, 7:16 pm, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Hey Hannibal,
> > There are probably a few ways to deal with this, but what I usually do is
> > just add a reference in your main reports dataset to the people_detail table.
> > Then add an expression that evaluates that reference. For example:
> >
> > if your dataset is
> >
> > SELECT pkyPeople, firstname, lastname FROM People
> >
> > modify it to be
> >
> > SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
> > FROM People as p LEFT OUTER JOIN People_Details as pd on
> > p.pkyPeople=pd.fkyPeople
> >
> > Then, when your running your report you just need an expression in the
> > 'visibility' sections 'Hidden' field that reads
> >
> > =IIf(Fields!fkyPeople.value = "", True, False)
> >
> > Michael
> >
> >
> >
> > "Hannibal111111" wrote:
> > > I have a main report which has a list of people, and a subreport with
> > > some data about each person. How do I hide that person's name (ie the
> > > main report row) if the subreport of that person does not contain any
> > > data?
> >
> > > Thanks,
> >
> > > Han- Hide quoted text -
> >
> > - Show quoted text -
> I would love to do that, unfortunately the detail data is contained on
> a different server, which is the reason why I had to use the
> subreport. Any other ideas?
>

hiding header row in a group

I have a table with a group. I have one row of the group that is the header for the detail section. How can I suppress the row header in the group if there is no data in the detail section for a group value? I was thinking something along the line of setting the visibilty of the row header to an expression based on the existence of data in the detail, but don't know how to go about this.

Thanks.

Hi steve,

You can try using the Count function for any fields.

The expression in the HIdden property of the table header would be
=IIF(Count(Fields![WhateverField]) = 0, True, False)

I tried it out and it seemed to work on my test.
Hope this works !

BErnard Ong

|||

To check for the existence of records in a given group, you can use count function with scope parameter like this in the visibility expression of the group header row:

=IIf(Count(Fields!YourField.Value, "Group1")=0, true, false)

Where Group1 is the name of the group that you have in your table.

Shyam

Hiding duplicates in a details row and hiding the row when it's em

I have a report that is using the drill down feature. So i have 2 detail
rows. The first row is field labels for the 2nd details row. What i would
like to do is only show the first details row once. I know i can set the
properties for each field to hide duplicates but then i'm still stuck with it
showing the blank row. So if i could find a way to suppress the first row
when it's blank that would be good or find a way to only print the first
details row once.well i answered my own question. i moved the field labels to the bottom of
the last grouping before the details, made sure all of the textbox fields
were marked hide duplicates, set the row height to 0, set visibility for the
row to true, and set the toggle setting to be the same as the detail toggle
setting.
"deniseamat" wrote:
> I have a report that is using the drill down feature. So i have 2 detail
> rows. The first row is field labels for the 2nd details row. What i would
> like to do is only show the first details row once. I know i can set the
> properties for each field to hide duplicates but then i'm still stuck with it
> showing the blank row. So if i could find a way to suppress the first row
> when it's blank that would be good or find a way to only print the first
> details row once.

Hiding a table row depending on page number?

Hi, Everyone.
i am wondering if there is some way to hide a table row depending on the page number.
I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.

Hi Palmie,

Unfortunatelly, global variables can be used only in the page header and footer. Maybe, if you know the number of columns in a page, then you can operate with the RowNumber variable (just an idea ).

Regards,

Janos

|||Do you mean something like this ? :
IIF(RowNumber(Nothing) > [a number], True, False)

When i use that expression it hides the row on every page.
|||

Are you trying to show something only on page 1, or what exactly?

>L<

|||Yes, sorry if i wasnt being clear about that, i have a Table header row that i only want to show on the first page of the report.
|||

OK then let's try the obvious things first:

If it is a group header, try hiding duplicates for a full dataset scope. If it is really a table header, make sure that "repeat on new page" is turned off.

You may have to turn "Can Shrink" on for all such items to get the effect you are after...

If none of these ideas work for you, tell me more about this table header and how it is placed, and I'll give it a shot...

For example maybe one of the following holds true:

it is a group header, and you want multiple repetitions, just not on any pages but #1 it is a table header and the table is contained in another control, so the table repeats multiple times in the report|||Hey again and sorry for the late reply.

The table with the header row that i want to hide looks like this:
The table is not inside another control.
It is not a group header, just a table header.

The table has 3 header rows
Row 1 contains a subreport with a few textboxes and an image. This should be visible on all the pages. Row 2 contains a rectangle with some textboxes, This is the row i only want to show at the first page. Row 3 is the actual header for the detail rows and should be repeated on every page.|||

OK, gotcha. Assuming I have repro'd your situation correctly, this should work:

1. Add the following to your Report code:

Code Snippet

Public hideRow As Boolean = False

Function hideSpecialRow() As String
hideRow = True
Return ""
End Function

2. Add an expression to some textbox in header row #3 ( the one with the "actual header for the detail rows) or anyplace after row #2 I think would work. If it is an empty textbox, just make the expression

=Code.hideSpecialRow()

... but if you do not have an empty textbox, use this (make sure that you use & and not + in case, as in my example, the other value is not a string):

Code Snippet

=Max(Fields!Locale.Value) & Code.hideSpecialRow()

3. Select the row (not the textboxes) in row # 2. Set its Visibility property to:

Code Snippet

=Code.hideRow

Whaddya think?

>L<

|||This looked like it could solve the problem, but I cant get it to work.

I put the call for the function below the header row that i want
to hide, and I set the visibility property to the bool hideRow and it
is still repeating itself.

I checked the value of the bool before and after the call and it changes value
from False to True.

When i put a textbox with "=Code.hideRow.ToString" in the rows i get that
it is false in the row that is supposed to be hidden and true in the rows below that, and the header rows looks the same on every page.|||

>>When i put a textbox with "=Code.hideRow.ToString" in the rows i get that
it is false in the row that is supposed to be hidden and true in the rows below that, and the header rows looks the same on every page.

<<

It should be false in the row that is supposed to be hidden on page 1, and true *always* after that. For example it should be true in the first header row on page 2, as well as all subsequent rows. Can you verify that it is?

Assuming that it is, then there might be something else stopping the Visibility thing from working properly for you.

>>and I set the visibility property to the bool hideRow

What happens if you just edit the visibility property you have set here to =False? You should not see that row at all, even on page 1. Does this work as expected for you? (I just want to verify that it's being set in the right place and that nothing else is involved. -- I did test what I wrote and it did work <s>.)

>L<

|||

Lisa Nicholls wrote:

>>It should be false in the row that is supposed to be hidden on page 1, and true *always* after that. For example it should be true in the first header row on page 2, as well as all subsequent rows. Can you verify that it is?<<

It is false on the first page and its the same on the other pages.
It looks like RS copies the table header from the first page to the others.

Lisa Nicholls wrote:

What happens if you just edit the visibility property you have set here to =False? You should not see that row at all, even on page 1. Does this work as expected for you? (I just want to verify that it's being set in the right place and that nothing else is involved. -- I did test what I wrote and it did work.

If i set the "Hidden" property to =True (if you meant True instead of False), The row is hidden on all the pages.
|||

Yes, I did mean =True <s> Sorry.

>>

It is false on the first page and its the same on the other pages.
It looks like RS copies the table header from the first page to the others.

<<

Then something is wrong. If I put =Code.hideRow in the first header row, it is False on page 1, False on page 2, and then True on all other pages. This is because I set it in the third header row on page 2, so it is not True until *after* header row 1 on page 2.

OK, I see something else is involved here. The report I happened to pick to try this has the table inside a list element. I am not sure why that makes a difference but when I remove the list element I see what you describe. I'm sorry about this. I will try to figure out why it doesn't behave the same way and write back.

>L<

|||

OK -- I have pretty much confirmed that this works *IF* the table is in a list. This requires the list to have a group expression.

What happens is that the table's header row is suppressed for all the instances of the table inside that list. You might have multiple instances of the table on one page or, if you say "page break after" on the table, you would only see the header row in question on page 1. Which works exactly the way you want.

This is the bummer part -- it doesn't seem to work if you use a constant for the list's group expression, or anything that evaluates to a constant value, so if you don't actually need a group at all it won't work.

My original example happened to have a group, and I didn't notice that it had the table inside a list... because I generated the example using the report wizard <sigh>.

I don't know if the behavior (the table header seeming to be copied from the first page, without re-evaluation, if there is no group or list) is a bug or an optimization.

I don't suppose your table *has* a group and putting it inside a list is a viable possibility?

>L<

|||Nope, the table doesnt have a group. I tried putting the table in a list and just set an Id value for the report as group expression in the List but i still got that same problem where the report seems to copy the first page.
|||

Well, like I said, this only is workable when the table *does* have a group and it can't be a group that doesn't change (I did try that <sigh>...) I'm not sure why this is because I am definitely using table header, not group header, but it made all the difference.

If I can think of a different approach I will post back here.

>L<