Hi all:
As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table
Thanks.:confused:Yes. Look at DatePart in BOL.
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Thursday, March 29, 2012
Friday, March 23, 2012
Extent Fragmentation High Just After Reindexing
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.
Code Snippet
use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)
This may show you a more application fragmentation percent.
Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.
Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||
W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce
ExtentFragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.
Step 1: Ran a SHOWCONTIG to see the fragmentation level.
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.
Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.
Code Snippet
ALTER INDEX XPKtMyTable ON dbo.tMyTableREBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO
Step 3: Ran another SHOWCONTIG
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.
Step 4: Dropped and recreated the 3 indexes
Code Snippet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Step 5: Ran the SHOWCONTIG again
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.
Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)
Code Snippet
select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX
alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032
fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL
ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...
ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?
Other ideas/comments are appreciated!! Thanks, Bruce
|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.
Bruce dBA wrote:
IF the autogrow was very small in the past and expanded many times, how would I see that now?"
You should be able to see "Autogrow of file" in the log if you're auditing that.|||
W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||
Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.
Extent Fragmentation High Just After Reindexing
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.
Code Snippet
use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)
This may show you a more application fragmentation percent.
Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.
Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||
W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce
ExtentFragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.
Step 1: Ran a SHOWCONTIG to see the fragmentation level.
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.
Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.
Code Snippet
ALTER INDEX XPKtMyTable ON dbo.tMyTableREBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO
Step 3: Ran another SHOWCONTIG
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.
Step 4: Dropped and recreated the 3 indexes
Code Snippet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Step 5: Ran the SHOWCONTIG again
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.
Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)
Code Snippet
select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX
alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032
fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL
ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...
ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?
Other ideas/comments are appreciated!! Thanks, Bruce
|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.
IF the autogrow was very small in the past and expanded many times, how would I see that now?"
You should be able to see "Autogrow of file" in the log if you're auditing that.|||
W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||
Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.
Extensive Use pf Case Statement
Hi,
I want to generate a table output as per table a based on table b. In this case Range field os populated based on Shape and Process field values. You can see in both processes range is different. Now this is varies from shapes & processes which is stored in seperate master table which contains range ex. For Process_1 it is 0.5 & for Process_2 it is 0.10.
I have tried used case statement but I am unable to generate this dynamically in a single query. Can anyone Help me How can I achive this task?
Table A
Table B
Nilkanth Desai
create table TableB(Barcode int, Shape char(6), Process varchar(8), Weight decimal(5,2))
insert into TableB(Barcode ,Shape , Process ,Weight)
select 1 , 'Shap_1', 'Proc_1' , 0.12 union all
select 2 , 'Shap_1', 'Proc_1' , 0.16 union all
select 3 , 'Shap_1', 'Proc_1' , 0.06 union all
select 4 , 'Shap_1', 'Proc_1' , 0.21 union all
select 5 , 'Shap_1', 'Proc_1' , 0.13 union all
select 6 , 'Shap_1', 'Proc_2' , 0.18 union all
select 7 , 'Shap_1', 'Proc_2' , 0.13 union all
select 8 , 'Shap_1', 'Proc_2', 0.24 union all
select 9 , 'Shap_1', 'Proc_2', 0.07 union all
select 10 , 'Shap_1', 'Proc_2', 0.33
create table Master(Process varchar(8), Range decimal(5,2))
insert into Master(Process, Range) values('Proc_1',0.05)
insert into Master(Process, Range) values('Proc_2',0.10)
select t.Barcode,
t.Shape,
t.Process,
t.Weight,
floor(t.Weight/m.Range)*m.Range+0.01 as RangeFrom,
floor(t.Weight/m.Range)*m.Range+m.Range as RangeTo
from TableB t
inner join Master m on m.Process=t.Process
order by t.Barcode
Monday, March 12, 2012
extend to another table
Hi,
I have a table with 3 fileds that are only filled in a few circunstances,
let say 5 out of 100. Is it ok to separate those values into another table?
And in the case it is separated, is it ok to define a different primary key
or it would be the same primary key as the primary table?
Now it looks to me to have 2 options:
First, set a different primary key to the auxiliary table and set a FK to
the primary table.
Second, set the primary key of the auxiliary table the same as the primary
key of the primary table.
I had always thought that 1 to 1 relationship between tables have no sense,
but now I don't know how to design this.
Thanks for any help
apuyinc> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another
> table?
> And in the case it is separated, is it ok to define a different primary
> key
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
What would you set as the primary key, other than the naturally obvious
choice?
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no
> sense,
When it's potentially 1 to 0, this makes sense. Why have a bunch of columns
that are NULL 95 out of 100 times? It makes joins more complex, yes, but
you can solve this once with a view.
It can also make sense if you are dealing with two or three columns 90% of
the time, and you only care about the other 80 columns 10% of the time. Why
not leave those other columns out of all the engine's work except when you
actually need them?
A|||What I write below is my opinion :)
Logically, a table should be an entity by itself. I would say its a bad
design to split a few columns because it doesn't usually get filled.
Say for example, middle name doesn't get filled 90% of the time.
So we can say that customer_id and middle_name alone can be moved to a new
table because it will save space.
But email addess, address, telephone may be moved to a seperate table along
with customer_id. In this case you have moved the customer details away from
the customer master. It can make sense.
I would say its better sometimes to denormalize the table.
Do I make sense?
"apuyinc" wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
> Thanks for any help
> apuyinc|||apuyinc wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
>
If the 3 columns in the second table are optional then such a
relationship wouldn't be 1 -> 1. It would be 1 -> {0|1}. Yes, it makes
perfect sense but don't forget the foreign key. Example:
CREATE TABLE t1 (key_col INT NOT NULL PRIMARY KEY, ... other columns);
CREATE TABLE t2 (key_col INT NOT NULL PRIMARY KEY REFERENCES t1
(key_col), col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> > I have a table with 3 fileds that are only filled in a few circunstances,
> What would you set as the primary key, other than the naturally obvious
> choice?
I thought about an identity...
> When it's potentially 1 to 0, this makes sense. Why have a bunch of colum
ns
> that are NULL 95 out of 100 times? It makes joins more complex, yes, but
> you can solve this once with a view.
Mmm... you are right, I will split it and use a view.
Now I have another question regarding views. If the secondary table have a
FK to another table. The view will have it? As far as I know you can't
create a FK relationship using a view, at least in SQL 2k which I am using.
> It can also make sense if you are dealing with two or three columns 90% of
> the time, and you only care about the other 80 columns 10% of the time. W
hy
> not leave those other columns out of all the engine's work except when you
> actually need them?
> A
>
>|||>> What would you set as the primary key, other than the naturally obvious
> I thought about an identity...
That's not really a key, what is your primary key in the first table?
> Now I have another question regarding views. If the secondary table have
> a
> FK to another table. The view will have it? As far as I know you can't
> create a FK relationship using a view, at least in SQL 2k which I am
> using.
Your view doesn't have the constraint; the view is just a query. You would
just write the view as:
CREATE VIEW dbo.MyView
AS
SELECT
t1.col1,
t1.col2,
col3 = COALESCE(t2.col3, '')
-- or just t.col3 if you want NULL to appear
FROM
t1
LEFT OUTER JOIN
t2
ON
t1.PrimaryKey = t2.PrimaryKey
GO|||Thanks for the help..
"Aaron Bertrand [SQL Server MVP]" wrote:
> That's not really a key, what is your primary key in the first table?
The primary key is codeId so... the primary key of the second table will be
codeId too.
> Your view doesn't have the constraint; the view is just a query. You woul
d
> just write the view as:
> CREATE VIEW dbo.MyView
> AS
> SELECT
> t1.col1,
> t1.col2,
> col3 = COALESCE(t2.col3, '')
> -- or just t.col3 if you want NULL to appear
> FROM
> t1
> LEFT OUTER JOIN
> t2
> ON
> t1.PrimaryKey = t2.PrimaryKey
> GO
>
>|||what do you mean by file sorted?
--
"apuyinc" wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
> Thanks for any help
> apuyinc|||I am terribly sorry. Wrong post.. again :P
--
"Omnibuzz" wrote:
> what do you mean by file sorted?
> --
>
>
> "apuyinc" wrote:
>
I have a table with 3 fileds that are only filled in a few circunstances,
let say 5 out of 100. Is it ok to separate those values into another table?
And in the case it is separated, is it ok to define a different primary key
or it would be the same primary key as the primary table?
Now it looks to me to have 2 options:
First, set a different primary key to the auxiliary table and set a FK to
the primary table.
Second, set the primary key of the auxiliary table the same as the primary
key of the primary table.
I had always thought that 1 to 1 relationship between tables have no sense,
but now I don't know how to design this.
Thanks for any help
apuyinc> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another
> table?
> And in the case it is separated, is it ok to define a different primary
> key
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
What would you set as the primary key, other than the naturally obvious
choice?
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no
> sense,
When it's potentially 1 to 0, this makes sense. Why have a bunch of columns
that are NULL 95 out of 100 times? It makes joins more complex, yes, but
you can solve this once with a view.
It can also make sense if you are dealing with two or three columns 90% of
the time, and you only care about the other 80 columns 10% of the time. Why
not leave those other columns out of all the engine's work except when you
actually need them?
A|||What I write below is my opinion :)
Logically, a table should be an entity by itself. I would say its a bad
design to split a few columns because it doesn't usually get filled.
Say for example, middle name doesn't get filled 90% of the time.
So we can say that customer_id and middle_name alone can be moved to a new
table because it will save space.
But email addess, address, telephone may be moved to a seperate table along
with customer_id. In this case you have moved the customer details away from
the customer master. It can make sense.
I would say its better sometimes to denormalize the table.
Do I make sense?
"apuyinc" wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
> Thanks for any help
> apuyinc|||apuyinc wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
>
If the 3 columns in the second table are optional then such a
relationship wouldn't be 1 -> 1. It would be 1 -> {0|1}. Yes, it makes
perfect sense but don't forget the foreign key. Example:
CREATE TABLE t1 (key_col INT NOT NULL PRIMARY KEY, ... other columns);
CREATE TABLE t2 (key_col INT NOT NULL PRIMARY KEY REFERENCES t1
(key_col), col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> > I have a table with 3 fileds that are only filled in a few circunstances,
> What would you set as the primary key, other than the naturally obvious
> choice?
I thought about an identity...
> When it's potentially 1 to 0, this makes sense. Why have a bunch of colum
ns
> that are NULL 95 out of 100 times? It makes joins more complex, yes, but
> you can solve this once with a view.
Mmm... you are right, I will split it and use a view.
Now I have another question regarding views. If the secondary table have a
FK to another table. The view will have it? As far as I know you can't
create a FK relationship using a view, at least in SQL 2k which I am using.
> It can also make sense if you are dealing with two or three columns 90% of
> the time, and you only care about the other 80 columns 10% of the time. W
hy
> not leave those other columns out of all the engine's work except when you
> actually need them?
> A
>
>|||>> What would you set as the primary key, other than the naturally obvious
> I thought about an identity...
That's not really a key, what is your primary key in the first table?
> Now I have another question regarding views. If the secondary table have
> a
> FK to another table. The view will have it? As far as I know you can't
> create a FK relationship using a view, at least in SQL 2k which I am
> using.
Your view doesn't have the constraint; the view is just a query. You would
just write the view as:
CREATE VIEW dbo.MyView
AS
SELECT
t1.col1,
t1.col2,
col3 = COALESCE(t2.col3, '')
-- or just t.col3 if you want NULL to appear
FROM
t1
LEFT OUTER JOIN
t2
ON
t1.PrimaryKey = t2.PrimaryKey
GO|||Thanks for the help..
"Aaron Bertrand [SQL Server MVP]" wrote:
> That's not really a key, what is your primary key in the first table?
The primary key is codeId so... the primary key of the second table will be
codeId too.
> Your view doesn't have the constraint; the view is just a query. You woul
d
> just write the view as:
> CREATE VIEW dbo.MyView
> AS
> SELECT
> t1.col1,
> t1.col2,
> col3 = COALESCE(t2.col3, '')
> -- or just t.col3 if you want NULL to appear
> FROM
> t1
> LEFT OUTER JOIN
> t2
> ON
> t1.PrimaryKey = t2.PrimaryKey
> GO
>
>|||what do you mean by file sorted?
--
"apuyinc" wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
> Thanks for any help
> apuyinc|||I am terribly sorry. Wrong post.. again :P
--
"Omnibuzz" wrote:
> what do you mean by file sorted?
> --
>
>
> "apuyinc" wrote:
>
Expressions not evaluating values?
Hi,
I use an expression in a column text box to dynamically compute the column title.
The problem must have something linked to the way expressions generally works. I do not understand it clearly.
In this example, I use a SWITCH function to test the numerical value of a 1 row 1 column dataset.
The problem is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.
How can I get this test to work if the value tested is greater than the value in the dataset?
Thanks
Philippe
Bellow is the code.
-
=switch(
First(Fields!HeaderCount.Value, "HeadersCount") < 2
, nothing
, First(Fields!HeaderCount.Value, "HeadersCount") = 2
, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",2,")-3)
, First(Fields!HeaderCount.Value, "HeadersCount") > 2
, Parameters!Headers.Value.Substring(
Parameters!Headers.Value.IndexOf(",2,")+3
, Parameters!Headers.Value.IndexOf(",3,")-Parameters!Headers.Value.IndexOf(",2,")-3
)
)
is that I can test the number only if it is lower or equal to the number in the dataset. if I test a number greater than the number in the dataset, I get an error.
Note, If the dataset contains 2, the expression will return an error because I try to test >2 in the last case.
if the dataset contains 3 or greater, it works fine.
it is clearly the test which fails because if I replace the action by a fixed string it still return an error.
|||You are using the switch() function. Since it is a function, all arguments are evaluated before the switch functionality is executed.
I recommend to write a custom code function that uses the VB switch statement and call the custom code function from the expression.
-- Robert
|||Hi,
I have made some research on the Custom Code however I could not find documentation nor examples that show how to use parameters or dataset values within the custom code. If I create a function like this How can I access the report items?
Public Function Headers(ByVal Column as Integer) As String
Return CStr(Microsoft.VisualBasic.Switch( _
First(Fields!HeaderCount.Value, "HeadersCount") < Column _
, nothing _
, First(Fields!HeaderCount.Value, "HeadersCount") = Column _
, Right(Parameters!Headers.Value, Len(Parameters!Headers.Value) - Parameters!Headers.Value.IndexOf(",Column,")-3) _
, First(Fields!HeaderCount.Value, "HeadersCount") > Column _
, Parameters!Headers.Value.Substring( _
Parameters!Headers.Value.IndexOf(",Column,")+3 _
, Parameters!Headers.Value.IndexOf(",Column + 1,")-Parameters!Headers.Value.IndexOf(",Column,")-3 _
) _
) _
)
End Function
I did find a much simpler solution though.
The string I use contains a variable number of names separated by indexes, i.e.
,1,Charles,2,Tom,3,Laura,4,Rick
I have a report with a fixed number of columns, i.e 50 columns and I populate the columns title by pulling from the string, i.e. Column 3 title will be Laura.
I have converted the string so each name will have trailing spaces. Since now each item has the same lenght, I do not need anymore the switch, I can do simply this in each column with just another index number. Then I use an expression to control the visibility of the column.
Title
=Parameters!Headers.Value.Substring(Parameters!Headers.Value.IndexOf(",3,")+3, 85)
Visibility
=iif(First(Fields!HeaderCount.Value, "HeadersCount")<3,True,False)
If I spend the time to build this instead of using the Matrix report, it is because the Matrix report has 2 majors issues for me.
1) You cannot have column titles for your categories
2) You cannot have the categories values repeated
Because of the strict format requirement I have, I am obliged to use the PIVOT SQL operator and the dynamic column population. My users want a pivoted flat file they can put in Excel and build a pivot table with it. You cannot do that with a matrix report, too bad. That would be so much easy.
Philippe
Expressions for parameter values?
Hello all... im trying to figure out... how can i use an expression in a
parameter formula?
I have a report which takes a date parameter. I want to make a subscription
using last week as the date. So for example, i want to set up the
subscription to run every Sunday, and use a value of TODAY() - 7. If i enter
that as a value though, it says its an invalid type. I also tried it with an
equal sign on the front ( =TODAY()-6 ).
Is there a way to do this?
Thanks in advance,
- Arthur Dent.You can use the DateAdd function to add a day/hour/minute/month etc to a
date.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello all... im trying to figure out... how can i use an expression in a
> parameter formula?
> I have a report which takes a date parameter. I want to make a
> subscription using last week as the date. So for example, i want to set up
> the subscription to run every Sunday, and use a value of TODAY() - 7. If i
> enter that as a value though, it says its an invalid type. I also tried it
> with an equal sign on the front ( =TODAY()-6 ).
> Is there a way to do this?
> Thanks in advance,
> - Arthur Dent.
>|||Thanks for the reply. Unfortunately, that doesnt seem to work either. When i
type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
The value provided for the report parameter 'ForWeekOf' is not valid for its
type. (rsReportParameterTypeMismatch)
TIA-
"Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> You can use the DateAdd function to add a day/hour/minute/month etc to a
> date.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in a
>> parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>|||It sounds like you are trying to type an expression into the date field in
report manager. This won't work - you can only type date constants there.
You should rather load the report in report designer and set the default
value of the date parameter to something like =Today.AddDays(-7). Before
publishing to the report server, make sure to delete the existing report
*before* publishing.
Then, you can create a subscription which will use an expression-based
default value for the date parameter.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. Unfortunately, that doesnt seem to work either. When
> i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
> The value provided for the report parameter 'ForWeekOf' is not valid for
> its type. (rsReportParameterTypeMismatch)
> TIA-
>
> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>|||Ah, yes, that sounds like it would do exactly what i need.
Thanks!!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O69jArtSFHA.2556@.TK2MSFTNGP12.phx.gbl...
> It sounds like you are trying to type an expression into the date field in
> report manager. This won't work - you can only type date constants there.
> You should rather load the report in report designer and set the default
> value of the date parameter to something like =Today.AddDays(-7). Before
> publishing to the report server, make sure to delete the existing report
> *before* publishing.
> Then, you can create a subscription which will use an expression-based
> default value for the date parameter.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
>> Thanks for the reply. Unfortunately, that doesnt seem to work either.
>> When i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error
>> as so:
>> The value provided for the report parameter 'ForWeekOf' is not valid for
>> its type. (rsReportParameterTypeMismatch)
>> TIA-
>>
>> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
>> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() -
>> 7. If i enter that as a value though, it says its an invalid type. I
>> also tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>>
>
parameter formula?
I have a report which takes a date parameter. I want to make a subscription
using last week as the date. So for example, i want to set up the
subscription to run every Sunday, and use a value of TODAY() - 7. If i enter
that as a value though, it says its an invalid type. I also tried it with an
equal sign on the front ( =TODAY()-6 ).
Is there a way to do this?
Thanks in advance,
- Arthur Dent.You can use the DateAdd function to add a day/hour/minute/month etc to a
date.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello all... im trying to figure out... how can i use an expression in a
> parameter formula?
> I have a report which takes a date parameter. I want to make a
> subscription using last week as the date. So for example, i want to set up
> the subscription to run every Sunday, and use a value of TODAY() - 7. If i
> enter that as a value though, it says its an invalid type. I also tried it
> with an equal sign on the front ( =TODAY()-6 ).
> Is there a way to do this?
> Thanks in advance,
> - Arthur Dent.
>|||Thanks for the reply. Unfortunately, that doesnt seem to work either. When i
type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
The value provided for the report parameter 'ForWeekOf' is not valid for its
type. (rsReportParameterTypeMismatch)
TIA-
"Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
> You can use the DateAdd function to add a day/hour/minute/month etc to a
> date.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in a
>> parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>|||It sounds like you are trying to type an expression into the date field in
report manager. This won't work - you can only type date constants there.
You should rather load the report in report designer and set the default
value of the date parameter to something like =Today.AddDays(-7). Before
publishing to the report server, make sure to delete the existing report
*before* publishing.
Then, you can create a subscription which will use an expression-based
default value for the date parameter.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply. Unfortunately, that doesnt seem to work either. When
> i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error as so:
> The value provided for the report parameter 'ForWeekOf' is not valid for
> its type. (rsReportParameterTypeMismatch)
> TIA-
>
> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() - 7.
>> If i enter that as a value though, it says its an invalid type. I also
>> tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>|||Ah, yes, that sounds like it would do exactly what i need.
Thanks!!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O69jArtSFHA.2556@.TK2MSFTNGP12.phx.gbl...
> It sounds like you are trying to type an expression into the date field in
> report manager. This won't work - you can only type date constants there.
> You should rather load the report in report designer and set the default
> value of the date parameter to something like =Today.AddDays(-7). Before
> publishing to the report server, make sure to delete the existing report
> *before* publishing.
> Then, you can create a subscription which will use an expression-based
> default value for the date parameter.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:eYecTDtSFHA.3184@.TK2MSFTNGP14.phx.gbl...
>> Thanks for the reply. Unfortunately, that doesnt seem to work either.
>> When i type in "DATEADD(d,-7,TODAY())" for my parameter, i get an error
>> as so:
>> The value provided for the report parameter 'ForWeekOf' is not valid for
>> its type. (rsReportParameterTypeMismatch)
>> TIA-
>>
>> "Rajeev Karunakaran" <rajeevkarunakaran@.online.microsoft.com> wrote in
>> message news:edMnSbsSFHA.3672@.TK2MSFTNGP10.phx.gbl...
>> You can use the DateAdd function to add a day/hour/minute/month etc to a
>> date.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
>> news:u7NwrsrSFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> Hello all... im trying to figure out... how can i use an expression in
>> a parameter formula?
>> I have a report which takes a date parameter. I want to make a
>> subscription using last week as the date. So for example, i want to set
>> up the subscription to run every Sunday, and use a value of TODAY() -
>> 7. If i enter that as a value though, it says its an invalid type. I
>> also tried it with an equal sign on the front ( =TODAY()-6 ).
>> Is there a way to do this?
>> Thanks in advance,
>> - Arthur Dent.
>>
>>
>
Friday, March 9, 2012
Expression problem
Using this expression to get the percentage of two fields. The problem is
when both fields contain values of 0 then I get 'NaN%' .
How can I rewrite the expression to give 0%
=ROUND(((SUM(Fields!P2_OPENED.Value)/SUM(Fields!P2_CLOSED.Value)) *100),2) &
"%" & vbcrlf & ""
Thanks,=IIF(SUM(Fields!P2_CLOSED.Value) <> 0,
ROUND(((SUM(Fields!P2_OPENED.Value)/SUM(Fields!P2_CLOSED.Value)) *100),2) ,
0) &
"%" & vbcrlf & ""
"cheilig" wrote:
> Using this expression to get the percentage of two fields. The problem is
> when both fields contain values of 0 then I get 'NaN%' .
> How can I rewrite the expression to give 0%
> =ROUND(((SUM(Fields!P2_OPENED.Value)/SUM(Fields!P2_CLOSED.Value)) *100),2) &
> "%" & vbcrlf & ""
> Thanks,
>
when both fields contain values of 0 then I get 'NaN%' .
How can I rewrite the expression to give 0%
=ROUND(((SUM(Fields!P2_OPENED.Value)/SUM(Fields!P2_CLOSED.Value)) *100),2) &
"%" & vbcrlf & ""
Thanks,=IIF(SUM(Fields!P2_CLOSED.Value) <> 0,
ROUND(((SUM(Fields!P2_OPENED.Value)/SUM(Fields!P2_CLOSED.Value)) *100),2) ,
0) &
"%" & vbcrlf & ""
"cheilig" wrote:
> Using this expression to get the percentage of two fields. The problem is
> when both fields contain values of 0 then I get 'NaN%' .
> How can I rewrite the expression to give 0%
> =ROUND(((SUM(Fields!P2_OPENED.Value)/SUM(Fields!P2_CLOSED.Value)) *100),2) &
> "%" & vbcrlf & ""
> Thanks,
>
Wednesday, March 7, 2012
expression based on multiple values in a dataset
Hi
I am trying to seth the background colour of a textbox to either red or
green depending on the multiple values in my dataset.
Each row of my dataset has a boolean value for Pass. The background colour
of the text box needs to be green if all rows in my dataset have a Pass
value of True but if only one row in the dataset has a Pass value of False,
the background colour needs to be set to red.
I have tries the obvious expression =IIF(Fields!Pass.Value = True, "Green",
"Red") but this will set the background to green again if their is a row in
the dataset with a Pass value of True after the row which had the Pass value
of False.
I have also tried counting the number of rows with a specific value but this
does not seem to work.
=IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
Does anyone have any ideas how to get what I want?
Thanks
Lewis Holmes
eNateTry this for multiple values:
=iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"l.holmes" <enate@.newsgroups.nospam> wrote in message
news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Hi
> I am trying to seth the background colour of a textbox to either red or
> green depending on the multiple values in my dataset.
> Each row of my dataset has a boolean value for Pass. The background colour
> of the text box needs to be green if all rows in my dataset have a Pass
> value of True but if only one row in the dataset has a Pass value of
> False, the background colour needs to be set to red.
> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
> "Green", "Red") but this will set the background to green again if their
> is a row in the dataset with a Pass value of True after the row which had
> the Pass value of False.
> I have also tried counting the number of rows with a specific value but
> this does not seem to work.
> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
> Does anyone have any ideas how to get what I want?
> Thanks
> Lewis Holmes
> eNate
>|||Hi Robert
Thanks for the reply.
I do not think this will work still. For example say my dataset has three
rows which have Pass values of True, False and True.
As i understand, using this expression for the first row, the expression
will evaluate to Green as Value is True. Then for the second row the
expression will evaluate to Red as value is False (this is all correct).
However, my problem is that now after evaluating the expression for third
row, the value returned will be Green as Value is true and so sum returns 1.
This is not the behaviour I want as the background colour should be Red if
one or more of the Pass values is false.Using this expression, the result
from the third row is setting the background colour back to green.
Hope this explains the problem better.
Kind Regards
Lewis Holmes
eNate
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eO1NksR2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Try this for multiple values:
> =iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "l.holmes" <enate@.newsgroups.nospam> wrote in message
> news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> I am trying to seth the background colour of a textbox to either red or
>> green depending on the multiple values in my dataset.
>> Each row of my dataset has a boolean value for Pass. The background
>> colour of the text box needs to be green if all rows in my dataset have a
>> Pass value of True but if only one row in the dataset has a Pass value of
>> False, the background colour needs to be set to red.
>> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
>> "Green", "Red") but this will set the background to green again if their
>> is a row in the dataset with a Pass value of True after the row which had
>> the Pass value of False.
>> I have also tried counting the number of rows with a specific value but
>> this does not seem to work.
>> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
>> Does anyone have any ideas how to get what I want?
>> Thanks
>> Lewis Holmes
>> eNate
>|||Hi Lewis,
In you case, I understood if there is one backgroup to be Red (false), you
want all following backgroup to be shown as Red (false). If I have
misunderstood your concern, please feel free to point it out.
I am afraid there won't be an easy to accomplish this. You may try using
.net assembly to identify the color according to row number with custom
function.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
I am trying to seth the background colour of a textbox to either red or
green depending on the multiple values in my dataset.
Each row of my dataset has a boolean value for Pass. The background colour
of the text box needs to be green if all rows in my dataset have a Pass
value of True but if only one row in the dataset has a Pass value of False,
the background colour needs to be set to red.
I have tries the obvious expression =IIF(Fields!Pass.Value = True, "Green",
"Red") but this will set the background to green again if their is a row in
the dataset with a Pass value of True after the row which had the Pass value
of False.
I have also tried counting the number of rows with a specific value but this
does not seem to work.
=IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
Does anyone have any ideas how to get what I want?
Thanks
Lewis Holmes
eNateTry this for multiple values:
=iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"l.holmes" <enate@.newsgroups.nospam> wrote in message
news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Hi
> I am trying to seth the background colour of a textbox to either red or
> green depending on the multiple values in my dataset.
> Each row of my dataset has a boolean value for Pass. The background colour
> of the text box needs to be green if all rows in my dataset have a Pass
> value of True but if only one row in the dataset has a Pass value of
> False, the background colour needs to be set to red.
> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
> "Green", "Red") but this will set the background to green again if their
> is a row in the dataset with a Pass value of True after the row which had
> the Pass value of False.
> I have also tried counting the number of rows with a specific value but
> this does not seem to work.
> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
> Does anyone have any ideas how to get what I want?
> Thanks
> Lewis Holmes
> eNate
>|||Hi Robert
Thanks for the reply.
I do not think this will work still. For example say my dataset has three
rows which have Pass values of True, False and True.
As i understand, using this expression for the first row, the expression
will evaluate to Green as Value is True. Then for the second row the
expression will evaluate to Red as value is False (this is all correct).
However, my problem is that now after evaluating the expression for third
row, the value returned will be Green as Value is true and so sum returns 1.
This is not the behaviour I want as the background colour should be Red if
one or more of the Pass values is false.Using this expression, the result
from the third row is setting the background colour back to green.
Hope this explains the problem better.
Kind Regards
Lewis Holmes
eNate
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eO1NksR2FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Try this for multiple values:
> =iif(Sum(iif(Fields!Pass.Value, 1, 0)) > 0, "Green", "Red")
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "l.holmes" <enate@.newsgroups.nospam> wrote in message
> news:uMp0e%23J2FHA.3816@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> I am trying to seth the background colour of a textbox to either red or
>> green depending on the multiple values in my dataset.
>> Each row of my dataset has a boolean value for Pass. The background
>> colour of the text box needs to be green if all rows in my dataset have a
>> Pass value of True but if only one row in the dataset has a Pass value of
>> False, the background colour needs to be set to red.
>> I have tries the obvious expression =IIF(Fields!Pass.Value = True,
>> "Green", "Red") but this will set the background to green again if their
>> is a row in the dataset with a Pass value of True after the row which had
>> the Pass value of False.
>> I have also tried counting the number of rows with a specific value but
>> this does not seem to work.
>> =IIF(count(Fields!Pass.Value = False) = 0, "Green", "Red")
>> Does anyone have any ideas how to get what I want?
>> Thanks
>> Lewis Holmes
>> eNate
>|||Hi Lewis,
In you case, I understood if there is one backgroup to be Red (false), you
want all following backgroup to be shown as Red (false). If I have
misunderstood your concern, please feel free to point it out.
I am afraid there won't be an easy to accomplish this. You may try using
.net assembly to identify the color according to row number with custom
function.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Expressing Values as Percentages
I have two values and I want to express a third derived value as a
percentage of the other two values. I thought it would be a simple
division of the first two numbers and then a multiplication by 100 to
give me a percentage, but all I get is 0.
percentage of the other two values. I thought it would be a simple
division of the first two numbers and then a multiplication by 100 to
give me a percentage, but all I get is 0.
Here is my select statement,
SELECT dbo.Eligble.GRADETotal,
dbo.nil1234_Faculties_Totals.FACTotal,
dbo.nil1234_Faculties_Totals.FACTotal /
dbo.Eligble.GRADETotal * 100 AS [PERCENT]
FROM dbo.Eligble CROSS JOIN
dbo.nil1234_Faculties_Totals
Can anyone point out where I'm going wrong here?
Thanks in advance"Bryan" <bmcguire@.rics.org.uk> wrote in message
news:99dcdbd.0309081104.359f931a@.posting.google.co m...
> I have two values and I want to express a third derived value as a
> percentage of the other two values. I thought it would be a simple
> division of the first two numbers and then a multiplication by 100 to
> give me a percentage, but all I get is 0.
>
> Here is my select statement,
> SELECT dbo.Eligble.GRADETotal,
> dbo.nil1234_Faculties_Totals.FACTotal,
> dbo.nil1234_Faculties_Totals.FACTotal /
> dbo.Eligble.GRADETotal * 100 AS [PERCENT]
> FROM dbo.Eligble CROSS JOIN
> dbo.nil1234_Faculties_Totals
>
> Can anyone point out where I'm going wrong here?
> Thanks in advance
It would be useful to see your table DDL (CREATE TABLE statements), but the
most likely reason is that you're dividing two integers. Dividing integers
always gives an integer result:
select 6 / 10 -- gives 0
select 6.0 / 10.0 -- gives 0.6
In your case, you could use CAST() or CONVERT() to change the values to
decimal with a suitable precision/scale for your calculations, eg.:
(cast(FACTotal as decimal(10,4)) / cast(GRADETotal as decimal(10,4)))* 100
AS [PERCENT]
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3f5cd50c_3@.news.bluewin.ch>...
> "Bryan" <bmcguire@.rics.org.uk> wrote in message
> news:99dcdbd.0309081104.359f931a@.posting.google.co m...
> > I have two values and I want to express a third derived value as a
> > percentage of the other two values. I thought it would be a simple
> > division of the first two numbers and then a multiplication by 100 to
> > give me a percentage, but all I get is 0.
> > Here is my select statement,
> > SELECT dbo.Eligble.GRADETotal,
> > dbo.nil1234_Faculties_Totals.FACTotal,
> > dbo.nil1234_Faculties_Totals.FACTotal /
> > dbo.Eligble.GRADETotal * 100 AS [PERCENT]
> > FROM dbo.Eligble CROSS JOIN
> > dbo.nil1234_Faculties_Totals
> > Can anyone point out where I'm going wrong here?
> > Thanks in advance
> It would be useful to see your table DDL (CREATE TABLE statements), but the
> most likely reason is that you're dividing two integers. Dividing integers
> always gives an integer result:
> select 6 / 10 -- gives 0
> select 6.0 / 10.0 -- gives 0.6
> In your case, you could use CAST() or CONVERT() to change the values to
> decimal with a suitable precision/scale for your calculations, eg.:
> (cast(FACTotal as decimal(10,4)) / cast(GRADETotal as decimal(10,4)))* 100
> AS [PERCENT]
> Simon
Simon,
Thanks for the response. Your solution worked a treat :)
thanks again
Bryan
Subscribe to:
Posts (Atom)