Hide empty rows on drill down report

Is there any way to hide the expand button (the plus sign) for rows that have
no containing rows on a drill-down report? What is happening now is that the
plus signs are populated for all rows and only a small subset of them
actually have data in the to drill into.Jason,
You can go into the toggle item's Properties and under the Visibility
Tab, set the "Initial appearance of the toggle image for this report
item:" to Expression and use a variation of the following:
=IIf(Fields!Determing_Factor.Value = "Determining_Value",true,false)
for example:
=IIf(Fields!Item_Name.Value = "",true,false)
This makes it so that if my next rows Item_Name field is blank, the
toggle will be automatically set the the - symbol so that the user
knows there is nothing past this point.
Hope that helps. If I didn't make myself understandable, let me know
and I can try again.|||That's pretty good. Is there any way to make it so that the "-" symbol cannot
be expanded? It looks weird to have a "-" symbol change to a "+" symbol and
then expand with an empty line under it.
"Mal" wrote:
> Jason,
> You can go into the toggle item's Properties and under the Visibility
> Tab, set the "Initial appearance of the toggle image for this report
> item:" to Expression and use a variation of the following:
> =IIf(Fields!Determing_Factor.Value = "Determining_Value",true,false)
> for example:
> =IIf(Fields!Item_Name.Value = "",true,false)
> This makes it so that if my next rows Item_Name field is blank, the
> toggle will be automatically set the the - symbol so that the user
> knows there is nothing past this point.
> Hope that helps. If I didn't make myself understandable, let me know
> and I can try again.
>|||Jason,
To remove the additional row, you will need to set the Hidden value, of
the Visibility properties for the row, to the same as what is in the
toggle item's Initial Appearance value.
for example:
=IIf(Fields!Item_Name.Value = "",true,false)
And here is an example of a 6 level report, that may or may not have
data at all levels:
=IIf(Fields!lev1_Name.Value="",IIf(Fields!lev2_Name.Value="",IIf(Fields!lev3_Name.Value="",IIf(Fields!lev4_Name.Value="",IIf(Fields!lev5_Name.Value
= "",IIf(Fields!lev6_Name.Value ="",true,false),false),false),false),false),false)
This would be inserted into the Hidden property of Group 1 in said
report. And would need to be placed in that property value for each of
the following groups, removing the previous row from the equation each
time. So for next row, the expression would start at lev2_Name, and the
Group 3 row would start at lev3_name, etc.
Does that help?|||Jason,
You will need to place an Expression into the Hidden field for the
Row's Visibility Properties. If you are using the above Expression on
the Initial Apperance, the Expression for the Hidden property would be
the same.
So, if it is a 3 level report and level 2 is a toggle for level 3, the
Expression would go in the toggle item on row 2 and in the Hidden
Property for Row3.

Help: why SQL 2005 is slow?

Hi,
During the weekend, I heard that SQL Server 2000 is very slow with tables co
ntaining rows over millions. So I did some
tests with our new database on SQL Server 2005 Standard Edition. The machine
is Windows 2003 Standard Server box
with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID configuration. It
has drives: C: with 11GB free space
and D: with 169GB free space. SQL 2005 was installed on D: drive.
The size of the database is 10GB containing 47 tables. The main table, Items
, contains 96 columns and 30 millions rows.
All columns are in varchar data type, and 2 of them are in varchar(2500). Th
ere is no any index setup in the table neither.
The time consumed for some SQL statements with this table are the followings
:
========================================
==============
No. SQL Statement Tim
e
========================================
= =========
1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
----
-- --
2 SELECT COUNT(*) FROM Items 13 minute
s
----
-- --
3 ALTER TABLE Items
ADD rid INT PRIMARY KEY IDENTITY(1,1) 10 hours 50 minute
s
----
-- --
4 SELECT COUNT(*) FROM Items 20 minute
s
----
-- --
5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
========================================
==============
The first 2 statements were run without primary key in the table. The statem
ents #4 and #5 were run after "rid" was added as primary key.
This is the first time I work with a database in such size. But the performa
nce of SQL Server 2005 surprised me.
Would you please tell me:
1. Is such performance normal with such number of rows?
2. Do I have to do something to improve the performance with such simple sta
tement when the number of rows>1 million or >10 millions?
3. Is SQL Server 2005 the right one to handle a database with such big table
, or should I consider DB2 9 or Oracle 10g?
Thank you
HongboHongbo wrote:
> Hi,
> During the weekend, I heard that SQL Server 2000 is very slow with
> tables containing rows over millions. So I did some
> tests with our new database on SQL Server 2005 Standard Edition. The
> machine is Windows 2003 Standard Server box
> with 3.5 GB RAM and single 3.0GHZ P4 CPU. There is no RAID
> configuration. It has drives: C: with 11GB free space
> and D: with 169GB free space. SQL 2005 was installed on D: drive.
> The size of the database is 10GB containing 47 tables. The main table,
> Items, contains 96 columns and 30 millions rows.
> All columns are in varchar data type, and 2 of them are in
> varchar(2500). There is no any index setup in the table neither.
> The time consumed for some SQL statements with this table are the
> followings:
> ========================================
==============
> No. SQL
> Statement Time
> ========================================
= =========
> 1 SELECT * FROM Items WHERE item_num='10029' 16 minutes
> ----
--
> --
> 2 SELECT COUNT(*) FROM Items 13
> minutes
> ----
--
> --
> 3 ALTER TABLE Items
> ADD rid INT PRIMARY KEY IDENTITY(1,1) 10
> hours 50 minutes
> ----
--
> --
> 4 SELECT COUNT(*) FROM Items 20
> minutes
> ----
--
> --
> 5 SELECT * FROM Items WHERE item_num='10029' 18 minutes
> ========================================
==============
> The first 2 statements were run without primary key in the table. The
> statements #4 and #5 were run after "rid" was added as primary key.
> This is the first time I work with a database in such size. But the
> performance of SQL Server 2005 surprised me.
> Would you please tell me:
> 1. Is such performance normal with such number of rows?
> 2. Do I have to do something to improve the performance with such
> simple statement when the number of rows>1 million or >10 millions?
> 3. Is SQL Server 2005 the right one to handle a database with such big
> table, or should I consider DB2 9 or Oracle 10g?
> Thank you
> Hongbo
Do I read this right, you have 30 million rows an no indexes? I would
think about adding a few to help you out. SQL 2005 can handle that with
proper design and hardware.
I believe there is a tremendous amount of paging going on there to
accommodate your full table scans and on a single drive, hence your poor
performance.
Ryan Sanders
http://ryanlsanders.blogspot.com