CREATE PROC [sp_generate_inserts]
(
@table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data
@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
@cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement
@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement
)
AS
BEGIN
/***********************************************************************************************************
Procedure: sp_generate_inserts (Build 22)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to ALTER a database setup, where in you can
script your data along with your table definitions.
Tested on: SQL Server 7.0 and SQL Server 2000
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
like nchar and nvarchar
Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
to avoid erroneous results
EXEC sp_generate_inserts 'titles', @include_column_list = 0
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles
which contain the word 'Computer' in them:
NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
(By default TIMESTAMP column's data is not scripted)
EXEC sp_generate_inserts 'titles', @include_timestamp = 1
Example 6: To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name
To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8: To generate INSERT statements for the rest of the columns excluding images
When using this otion, DO NOT set @include_column_list parameter to 0.
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns:
(By default IDENTITY columns are included in the INSERT statement)
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10: To generate INSERT statements for the TOP 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10
Example 11: To generate INSERT statements with only those columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12: To generate INSERT statements by omitting certain columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: To exclude computed columns from the INSERT statement:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/
SET NOCOUNT ON
--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
BEGIN
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
END
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
END
--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
IF (PARSENAME(@table_name,3)) IS NOT NULL
BEGIN
RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
END
--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just ALTER a view on the system tables and script the view instead
IF @owner IS NULL
BEGIN
IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
END
END
--Variable declarations
DECLARE @Column_ID int,
@Column_List varchar(8000),
@Column_Name varchar(128),
@Start_Insert varchar(786),
@Data_Type varchar(128),
@Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements
@IDN varchar(128) --Will contain the IDENTITY column's name in the table
--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''
IF @owner IS NULL
BEGIN
SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END
ELSE
BEGIN
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END
--To get the first column's ID
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
IF @cols_to_include IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
BEGIN
GOTO SKIP_LOOP
END
END
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
BEGIN
GOTO SKIP_LOOP
END
END
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
BEGIN
IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
ELSE
GOTO SKIP_LOOP
END
--Making sure whether to output computed columns or not
IF @ommit_computed_cols = 1
BEGIN
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
BEGIN
GOTO SKIP_LOOP
END
END
--Tables with columns of IMAGE data type are not supported for obvious reasons
IF(@Data_Type in ('image'))
BEGIN
IF (@ommit_images = 0)
BEGIN
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END
--Determining the data type of the column and depending on the data type, the VALUES part of
--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
SET @Actual_Values = @Actual_Values +
CASE
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
THEN
'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('datetime','smalldatetime')
THEN
'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
WHEN @Data_Type IN ('uniqueidentifier')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('text','ntext')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
WHEN @Data_Type IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
END
WHEN @Data_Type IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
END + '+' + ''',''' + ' + '
--Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop ends here!
END
--To get rid of the extra characters that got concatenated during the last run through the loop
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
IF LTRIM(@Column_List) = ''
BEGIN
RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
END
--Forming the final string that will be executed, to output the INSERT statements
IF (@include_column_list <> 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +
' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
END
ELSE IF (@include_column_list = 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
END
--Determining whether to ouput any debug information
IF @debug_mode =1
BEGIN
PRINT '/*****START OF DEBUG INFORMATION*****'
PRINT 'Beginning of the INSERT statement:'
PRINT @Start_Insert
PRINT ''
PRINT 'The column list:'
PRINT @Column_List
PRINT ''
PRINT 'The SELECT statement executed to generate the INSERTs'
PRINT @Actual_Values
PRINT ''
PRINT '*****END OF DEBUG INFORMATION*****/'
PRINT ''
END
PRINT '--INSERTs generated by ''sp_generate_inserts'' '
PRINT 'SET NOCOUNT ON'
PRINT ''
--Determining whether to print IDENTITY_INSERT or not
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
PRINT 'GO'
PRINT ''
END
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END
PRINT 'GO'
END
PRINT ''
PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
EXEC (@Actual_Values)
PRINT 'PRINT ''Done'''
PRINT ''
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END
PRINT 'GO'
END
PRINT ''
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
PRINT 'GO'
END
PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END
Saturday, 26 February 2011
Rollup And Cube
CREATE TABLE ORDERS (CUSTOMERNAME VARCHAR(50),ITEMNAME VARCHAR(50),PRICE NUMERIC(10,2))
INSERT INTO ORDERS VALUES('JACOB','ITEM1',312.50 )
INSERT INTO ORDERS VALUES('JACOB','ITEM2', 480.00 )
INSERT INTO ORDERS VALUES('MIKE','ITEM1',75.00 )
INSERT INTO ORDERS VALUES('MIKE','ITEM2',44.00 )
----ROLLUP
SELECT
CASE
WHEN GROUPING(customername) = 1 THEN 'All Customer'
ELSE customername END CustomerName,
CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
ELSE itemname END ItemName,
SUM(Price)
FROM orders GROUP BY customername,itemname
WITH ROLLUP
---CUBE
SELECT
CASE
WHEN GROUPING(customername) = 1 THEN 'All Customer'
ELSE customername END CustomerName,
CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
ELSE itemname END ItemName,
SUM(Price)
FROM orders GROUP BY customername,itemname
WITH CUBE
INSERT INTO ORDERS VALUES('JACOB','ITEM1',312.50 )
INSERT INTO ORDERS VALUES('JACOB','ITEM2', 480.00 )
INSERT INTO ORDERS VALUES('MIKE','ITEM1',75.00 )
INSERT INTO ORDERS VALUES('MIKE','ITEM2',44.00 )
----ROLLUP
SELECT
CASE
WHEN GROUPING(customername) = 1 THEN 'All Customer'
ELSE customername END CustomerName,
CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
ELSE itemname END ItemName,
SUM(Price)
FROM orders GROUP BY customername,itemname
WITH ROLLUP
---CUBE
SELECT
CASE
WHEN GROUPING(customername) = 1 THEN 'All Customer'
ELSE customername END CustomerName,
CASE WHEN GROUPING(itemname) = 1 THEN 'All Items'
ELSE itemname END ItemName,
SUM(Price)
FROM orders GROUP BY customername,itemname
WITH CUBE
Triggers Concepts
select * from customer_test
--------------------
create table customer_test (id int identity primary key,
name varchar(100),dept varchar(100),email varchar(100))
-------------------------
create table customer_arch (pkid int identity primary key,
id int references customer_test (id),
name varchar(100),dept varchar(100),email varchar(100))
------------------
alter table customer_arch add mode varchar(10)
alter table customer_arch add description varchar(1000)
alter table customer_arch
DROP CONSTRAINT FK__customer_arc__id__53CDB2C9
-----------------------------------------
INSERT INTO customer_test
SELECT 'GAN','SALES','GAN@GMAIL.COM'
DELETE FROM CUSTOMER_TEST
UPDATE CUSTOMER_TEST SET name = name+'P'
TRUNCATE TABLE customer_arch
SELECT * FROM customer_arch
SELECT * FROM customer_test
UPDATE CUSTOMER_TEST
SET NAME = CASE WHEN NAME = 'RAMKI' THEN 'RAM' ELSE NAME END
SELECT CASE WHEN NAME = 'RAM' THEN 'RAMKI' ELSE NAME END
FROM CUSTOMER_TEST
------------------------------------
ALTER TRIGGER TRG_CUSTOMERLOG
ON customer_test
FOR UPDATE, DELETE, INSERT
AS
BEGIN
SELECT * FROM INSERTED
SELECT * FROM DELETED
IF EXISTS (SELECT 1 FROM INSERTED) AND NOT EXISTS (SELECT 1 FROM DELETED)
BEGIN
PRINT 'RECORD INSERTED'
END
ELSE IF EXISTS (SELECT 1 FROM INSERTED I JOIN DELETED D ON D.ID = I.ID
WHERE I.NAME <> D.NAME OR I.DEPT <> D.DEPT OR I.EMAIL <> D.EMAIL)
BEGIN
PRINT 'RECORD UPDATED'
INSERT INTO customer_arch SELECT D.ID,D.NAME,D.DEPT,D.EMAIL, 'UPDATED', NULL
FROM INSERTED I JOIN DELETED D ON D.ID = I.ID
WHERE I.NAME <> D.NAME OR I.DEPT <> D.DEPT OR I.EMAIL <> D.EMAIL
END
ELSE IF NOT EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
BEGIN
PRINT 'RECORD DELETED'
INSERT INTO customer_arch SELECT D.ID,D.NAME,D.DEPT,D.EMAIL,'DELETED', NULL
FROM DELETED D
END
END
--------------------
create table customer_test (id int identity primary key,
name varchar(100),dept varchar(100),email varchar(100))
-------------------------
create table customer_arch (pkid int identity primary key,
id int references customer_test (id),
name varchar(100),dept varchar(100),email varchar(100))
------------------
alter table customer_arch add mode varchar(10)
alter table customer_arch add description varchar(1000)
alter table customer_arch
DROP CONSTRAINT FK__customer_arc__id__53CDB2C9
-----------------------------------------
INSERT INTO customer_test
SELECT 'GAN','SALES','GAN@GMAIL.COM'
DELETE FROM CUSTOMER_TEST
UPDATE CUSTOMER_TEST SET name = name+'P'
TRUNCATE TABLE customer_arch
SELECT * FROM customer_arch
SELECT * FROM customer_test
UPDATE CUSTOMER_TEST
SET NAME = CASE WHEN NAME = 'RAMKI' THEN 'RAM' ELSE NAME END
SELECT CASE WHEN NAME = 'RAM' THEN 'RAMKI' ELSE NAME END
FROM CUSTOMER_TEST
------------------------------------
ALTER TRIGGER TRG_CUSTOMERLOG
ON customer_test
FOR UPDATE, DELETE, INSERT
AS
BEGIN
SELECT * FROM INSERTED
SELECT * FROM DELETED
IF EXISTS (SELECT 1 FROM INSERTED) AND NOT EXISTS (SELECT 1 FROM DELETED)
BEGIN
PRINT 'RECORD INSERTED'
END
ELSE IF EXISTS (SELECT 1 FROM INSERTED I JOIN DELETED D ON D.ID = I.ID
WHERE I.NAME <> D.NAME OR I.DEPT <> D.DEPT OR I.EMAIL <> D.EMAIL)
BEGIN
PRINT 'RECORD UPDATED'
INSERT INTO customer_arch SELECT D.ID,D.NAME,D.DEPT,D.EMAIL, 'UPDATED', NULL
FROM INSERTED I JOIN DELETED D ON D.ID = I.ID
WHERE I.NAME <> D.NAME OR I.DEPT <> D.DEPT OR I.EMAIL <> D.EMAIL
END
ELSE IF NOT EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
BEGIN
PRINT 'RECORD DELETED'
INSERT INTO customer_arch SELECT D.ID,D.NAME,D.DEPT,D.EMAIL,'DELETED', NULL
FROM DELETED D
END
END
show the report on parameters
=IIF(Parameters!k.Value =
"dollor",Fields!Discount.Value<0.01,Fields!Discount.Value>0.00)
"dollor",Fields!Discount.Value<0.01,Fields!Discount.Value>0.00)
Display the report based on passing the values
create procedure usp_commdish (@month int,@year int)
as
begin
if (@month is null and @year is null)
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
--AND datepart(month,date_paid)
--AND datepart(YEAR,DATE_PAID)
group by uname,commission_type,date_paid
end
else if (@month is null)
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year
from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
--AND datepart(month,date_paid) =@month
AND datepart(YEAR,DATE_PAID) = @year
group by uname,commission_type,date_paid
end
else if (@year is null)
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year
from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
AND datepart(month,date_paid) =@month
--AND datepart(YEAR,DATE_PAID) = @year
group by uname,commission_type,date_paid
end
else
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year
from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
AND datepart(month,date_paid) =@month
AND datepart(YEAR,DATE_PAID) = @year
group by uname,commission_type,date_paid
end
end
as
begin
if (@month is null and @year is null)
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
--AND datepart(month,date_paid)
--AND datepart(YEAR,DATE_PAID)
group by uname,commission_type,date_paid
end
else if (@month is null)
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year
from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
--AND datepart(month,date_paid) =@month
AND datepart(YEAR,DATE_PAID) = @year
group by uname,commission_type,date_paid
end
else if (@year is null)
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year
from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
AND datepart(month,date_paid) =@month
--AND datepart(YEAR,DATE_PAID) = @year
group by uname,commission_type,date_paid
end
else
begin
select count(*),uname,commission_type,
convert(int,datepart(day,date_paid)) as Day,
convert(int,datepart(mm,date_paid) ) as Month,
convert(int,datepart(yyyy,date_paid)) as Year
from commissions_dish
WHERE commission_type in ('DVR','DVRBONUS','HD_DVR')
AND payment_amount >= 0
AND uname is not null
AND datepart(month,date_paid) =@month
AND datepart(YEAR,DATE_PAID) = @year
group by uname,commission_type,date_paid
end
end
code for display the colors on headers&footers
Using Reporting Services Embedded Code
Let's start with the fastest way to apply styles to a Reporting Services report, using embedded code. For this example we will only set colour styles, in order to make the example simpler.
The objective is to map the following styles to the following colours
Style Name Colour
Header Blue
Footer Green
BodyText Black
Subtitle Dark Blue
This is how you do it:
Defining Styles
Open an existing report, or create a new report
Access the embedded code of a report by clicking Report/Report Properties in the BIDS menu (you need to have selected either the Data or Layout tabs for this menu option to be available). You can then select the Code tab from the Report Properties dialog – and paste or enter the following code.
Function StyleColor(ByVal Style As String) As String
Select Case UCase(Style)
Case "HEADER"
Return "LightBlue"
Case "FOOTER"
Return "SkyBlue"
Case "MAINTITLE"
Return "Purple"
Case "SUBTITLE"
Return "DarkBlue"
Case Else
Return "White"
End Select
End Function
You should have the following:
Click OK to close the dialog.
Let's start with the fastest way to apply styles to a Reporting Services report, using embedded code. For this example we will only set colour styles, in order to make the example simpler.
The objective is to map the following styles to the following colours
Style Name Colour
Header Blue
Footer Green
BodyText Black
Subtitle Dark Blue
This is how you do it:
Defining Styles
Open an existing report, or create a new report
Access the embedded code of a report by clicking Report/Report Properties in the BIDS menu (you need to have selected either the Data or Layout tabs for this menu option to be available). You can then select the Code tab from the Report Properties dialog – and paste or enter the following code.
Function StyleColor(ByVal Style As String) As String
Select Case UCase(Style)
Case "HEADER"
Return "LightBlue"
Case "FOOTER"
Return "SkyBlue"
Case "MAINTITLE"
Return "Purple"
Case "SUBTITLE"
Return "DarkBlue"
Case Else
Return "White"
End Select
End Function
You should have the following:
Click OK to close the dialog.
OLAP Types
MOLAP
This is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats.
Advantages:
• Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations.
• Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly.
Disadvantages:
• Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible. But in this case, only summary-level information will be included in the cube itself.
• Requires additional investment: Cube technology are often proprietary and do not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.
ROLAP
This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement.
Advantages:
• Can handle large amounts of data: The data size limitation of ROLAP technology is the limitation on data size of the underlying relational database. In other words, ROLAP itself places no limitation on data amount.
• Can leverage functionalities inherent in the relational database: Often, relational database already comes with a host of functionalities. ROLAP technologies, since they sit on top of the relational database, can therefore leverage these functionalities.
Disadvantages:
• Performance can be slow: Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
• Limited by SQL functionalities: Because ROLAP technology mainly relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs (for example, it is difficult to perform complex calculations using SQL), ROLAP technologies are therefore traditionally limited by what SQL can do. ROLAP vendors have mitigated this risk by building into the tool out-of-the-box complex functions as well as the ability to allow users to define their own functions.
HOLAP
HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data.
This is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats.
Advantages:
• Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations.
• Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly.
Disadvantages:
• Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible. But in this case, only summary-level information will be included in the cube itself.
• Requires additional investment: Cube technology are often proprietary and do not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.
ROLAP
This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement.
Advantages:
• Can handle large amounts of data: The data size limitation of ROLAP technology is the limitation on data size of the underlying relational database. In other words, ROLAP itself places no limitation on data amount.
• Can leverage functionalities inherent in the relational database: Often, relational database already comes with a host of functionalities. ROLAP technologies, since they sit on top of the relational database, can therefore leverage these functionalities.
Disadvantages:
• Performance can be slow: Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
• Limited by SQL functionalities: Because ROLAP technology mainly relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs (for example, it is difficult to perform complex calculations using SQL), ROLAP technologies are therefore traditionally limited by what SQL can do. ROLAP vendors have mitigated this risk by building into the tool out-of-the-box complex functions as well as the ability to allow users to define their own functions.
HOLAP
HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data.
dates
• "Get Week Start" returns the beginning (00:00:00) of the Monday of the week passed to the function. "Get Week End" returns the end (23:59:59.997) of the Friday of the week passed to the function.
CREATE FUNCTION get_week_start (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
CREATE FUNCTION get_week_end (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
• "Get Month Start" and "Get Month End" return the start and end of the current month.
CREATE FUNCTION get_month_start (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END
CREATE FUNCTION get_month_end (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END
• "Get Yesterday Start" and "Get Yesterday End" return the start and end of the day prior to the parameter.
CREATE FUNCTION get_yesterday_start (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
CREATE FUNCTION get_yesterday_end (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END
• "Get Today Start" and "Get Today End" represent the start and end of the date passed.
CREATE FUNCTION get_today_start (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END
CREATE FUNCTION get_today_end (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
• "Get Weekday Start" and "Get Weekday End" return the start and end of the weekday specified within the week passed as date. For example, to get the start and end of Tuesday of the current week, pass the parameters 2 and getdate().
CREATE FUNCTION get_weekday_start (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END
CREATE FUNCTION get_weekday_end (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END
• In a similar fashion, the following functions generate dates as indicated in the function name.
CREATE FUNCTION get_year_start (@date datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
CREATE FUNCTION get_tomorrow_noon(@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
CREATE FUNCTION get_today_noon(@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
Microsoft.com Operations Virtualizes MSDN and TechNet on Hyper-V
Microsoft migrated MSDN to Hyper-V on March 31, 2008, and then followed up with TechNet on April 18, 2008. This article provides further details about testing methods and the results from Hyper-V Beta to RC0 that generated the confidence to fully roll out MSDN and TechNet on Hyper-V in production. »
________________________________________
Virtualization Case Study: Copa Airlines
Always looking to improve server availability and efficiency. Copa Airlines joined a Microsoft Rapid Deployment Program to test Hyper-V to provide a dynamic and reliable virtualization environment and System Center Virtual Machine Manager for the physical to virtual conversions of the chosen servers. The RDP program proved that a Microsoft virtualization solution could be a cost-effective way for Copa to increase business application availability, reduce data center costs, and optimize data center management. »
________________________________________
Virtualization from the Data Center to the Desktop
Integrated virtualization solutions from Microsoft can help you meet evolving demands more effectively as you transform your IT infrastructure from a cost center to a strategic business asset. »
________________________________________
Interoperability Advantages with Windows Server 2008
Learn about the advantages of using Windows Server 2008 with Windows Vista, and find information on how Windows Server 2008 interoperates with other applications and platforms. »
________________________________________
Windows Server 2008 News & Reviews
Keep up to date on the latest Windows Server 2008 news from Microsoft and independent sources worldwide. Here you'll find a compilation of technical reviews, news reports, and press releases about Windows Server 2008 and related products, services, and technologies. »
•An Android in Every Pocket? That's Google's Plan October 24, 2008
•PDC is Not Just the Windows 7 Show October 24, 2008
•How Much Is a Linux Distro Worth? October 22, 2008
•G1 Floats into Market with Fewer Apps October 22, 2008
Webcast: Palm Developer Network Technical Series. The Webcast Series will cover technical topics designed to help you build, debug and market your applications.
Dynamic Default Date Parameters in SQL Server 2000 Reporting Services
By Paul Whitaker
Go to page: Prev 1 2
Selecting Common Dates
These functions are great general purpose date calculators. To better integrate with Reporting Services, I've created a function and stored procedure to populate an RS dataset.
The following is a table-valued function that returns all of the calculated dates. It allows you to SELECT from the list to get only the parameters you need.
CREATE FUNCTION udfCommonDates (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
tomorrow_noon datetime,
today_noon datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,RETURN
END
The following stored procedure gets the pertinent dates based on the current date.
CREATE PROCEDURE uspCommonDates AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
Integrating with Reporting Services
Pulling these dates into Reporting Services via the Report Designer is a breeze. In the Data tab, simply add the uspCommonDates stored procedure as a new Dataset.
Now that you have a Dataset of commonly used dates, they are now available to you to set as Default values for the parameters. Simply select the new dataset and choose whichever "Value field" matches your desired date.
Conclusion
Business intelligence requirements are often bound to relative dates such as the previous day, previous week, or previous month. Generating a list of commonly used dates and setting the appropriate dates as default report parameters can facilitate the easy generation of time-based reports.
CREATE FUNCTION get_week_start (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,1-datepart(weekday, @date),@date))-1,0)
END
CREATE FUNCTION get_week_end (@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,7-datepart(weekday, @date),@date)),0) )
END
• "Get Month Start" and "Get Month End" return the start and end of the current month.
CREATE FUNCTION get_month_start (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(m,datediff(m,0, @date),0)
END
CREATE FUNCTION get_month_end (@date datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(ms, -3, dateadd (m,datediff(m,0,
dateadd(m,1,@date)),0))
END
• "Get Yesterday Start" and "Get Yesterday End" return the start and end of the day prior to the parameter.
CREATE FUNCTION get_yesterday_start (@today datetime)
RETURNS datetime AS
BEGIN
RETURN dateadd(day, -1, datediff(d,0,@today))
END
CREATE FUNCTION get_yesterday_end (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,@today))
END
• "Get Today Start" and "Get Today End" represent the start and end of the date passed.
CREATE FUNCTION get_today_start (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(day, 0, datediff(d,0,@today))
END
CREATE FUNCTION get_today_end (@today datetime)
RETURNS datetime AS
BEGIN
return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today)))
END
• "Get Weekday Start" and "Get Weekday End" return the start and end of the weekday specified within the week passed as date. For example, to get the start and end of Tuesday of the current week, pass the parameters 2 and getdate().
CREATE FUNCTION get_weekday_start (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date))-1,0)
END
CREATE FUNCTION get_weekday_end (@weekday tinyint,
@date datetime)
RETURNS datetime AS
BEGIN
return dateadd(yyyy, datepart(yyyy,
dateadd(weekday,@weekday-
datepart(weekday, @date),@date))-1900, 0)
+ dateadd(ms, -3,
dateadd(dy, datepart(dy,
dateadd(weekday,@weekday-datepart(weekday, @date),
@date)),0) )
END
• In a similar fashion, the following functions generate dates as indicated in the function name.
CREATE FUNCTION get_year_start (@date datetime)
RETURNS datetime AS
BEGIN
RETURN DATEADD(year,DATEDIFF(year,0, @date),0)
END
CREATE FUNCTION get_tomorrow_noon(@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0))
END
CREATE FUNCTION get_today_noon(@date datetime)
RETURNS datetime
BEGIN
RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0))
END
Microsoft.com Operations Virtualizes MSDN and TechNet on Hyper-V
Microsoft migrated MSDN to Hyper-V on March 31, 2008, and then followed up with TechNet on April 18, 2008. This article provides further details about testing methods and the results from Hyper-V Beta to RC0 that generated the confidence to fully roll out MSDN and TechNet on Hyper-V in production. »
________________________________________
Virtualization Case Study: Copa Airlines
Always looking to improve server availability and efficiency. Copa Airlines joined a Microsoft Rapid Deployment Program to test Hyper-V to provide a dynamic and reliable virtualization environment and System Center Virtual Machine Manager for the physical to virtual conversions of the chosen servers. The RDP program proved that a Microsoft virtualization solution could be a cost-effective way for Copa to increase business application availability, reduce data center costs, and optimize data center management. »
________________________________________
Virtualization from the Data Center to the Desktop
Integrated virtualization solutions from Microsoft can help you meet evolving demands more effectively as you transform your IT infrastructure from a cost center to a strategic business asset. »
________________________________________
Interoperability Advantages with Windows Server 2008
Learn about the advantages of using Windows Server 2008 with Windows Vista, and find information on how Windows Server 2008 interoperates with other applications and platforms. »
________________________________________
Windows Server 2008 News & Reviews
Keep up to date on the latest Windows Server 2008 news from Microsoft and independent sources worldwide. Here you'll find a compilation of technical reviews, news reports, and press releases about Windows Server 2008 and related products, services, and technologies. »
•An Android in Every Pocket? That's Google's Plan October 24, 2008
•PDC is Not Just the Windows 7 Show October 24, 2008
•How Much Is a Linux Distro Worth? October 22, 2008
•G1 Floats into Market with Fewer Apps October 22, 2008
Webcast: Palm Developer Network Technical Series. The Webcast Series will cover technical topics designed to help you build, debug and market your applications.
Dynamic Default Date Parameters in SQL Server 2000 Reporting Services
By Paul Whitaker
Go to page: Prev 1 2
Selecting Common Dates
These functions are great general purpose date calculators. To better integrate with Reporting Services, I've created a function and stored procedure to populate an RS dataset.
The following is a table-valued function that returns all of the calculated dates. It allows you to SELECT from the list to get only the parameters you need.
CREATE FUNCTION udfCommonDates (@date datetime)
RETURNS @t table (week_start datetime,
week_end datetime,
lastweek_start datetime,
lastweek_end datetime,
month_start datetime,
month_end datetime,
lastmonth_start datetime,
lastmonth_end datetime,
yesterday_start datetime,
yesterday_end datetime,
today_start datetime,
today_end datetime,
thisweek_monday_start datetime,
thisweek_monday_end datetime,
year_start datetime,
tomorrow_noon datetime,
today_noon datetime)
BEGIN
INSERT @t
SELECT
dbo.get_week_start ( @date ) AS week_start,
dbo.get_week_end ( @date ) AS week_end,
dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start,
dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end,
dbo.get_month_start( @date ) AS month_start,
dbo.get_month_end ( @date ) AS month_end,
dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start,
dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end,
dbo.get_yesterday_start ( @date ) AS yesterday_start,
dbo.get_yesterday_end ( @date ) AS yesterday_end,
dbo.get_today_start (@date) AS today_start,
dbo.get_today_end ( @date ) AS today_end,
dbo.get_weekday_start(1,@date) AS thisweek_monday_start,
dbo.get_weekday_end(1,@date) AS thisweek_monday_end,
dbo.get_year_start(@date) AS year_start,
dbo.get_tomorrow_noon(@date) AS TomorrowNoon,
dbo.get_today_noon(@date) AS TodayNoon,RETURN
END
The following stored procedure gets the pertinent dates based on the current date.
CREATE PROCEDURE uspCommonDates AS
begin
set datefirst 1
declare @date datetime
set @date = getdate()
select * from dbo.udfCommonDates(@date)
end
Integrating with Reporting Services
Pulling these dates into Reporting Services via the Report Designer is a breeze. In the Data tab, simply add the uspCommonDates stored procedure as a new Dataset.
Now that you have a Dataset of commonly used dates, they are now available to you to set as Default values for the parameters. Simply select the new dataset and choose whichever "Value field" matches your desired date.
Conclusion
Business intelligence requirements are often bound to relative dates such as the previous day, previous week, or previous month. Generating a list of commonly used dates and setting the appropriate dates as default report parameters can facilitate the easy generation of time-based reports.
Useful Sites
Download the original attachment
Hi Friends,
Here u can get some interesting sites on DWH, Data mining. You an also see links for readings, resources & white papers for the same. I hope this will be helpful for us.
And Ofcourse one of the good place is our Yahoo Group.
Thanks and Regards,
Moderator Team,
Informatica_World
…………………………………………….
Enjoy The Links ….
Data Warehouse White Papers:
Cost of Software Customization (Industry Articles)
Super Data Warehousing Solutions (Industry Articles)
The job forecast: partly cloudy (Industry Articles)
A Case Study in Metadata Harvesting: the NSDL (Academic Articles)
The Role of Network Storage for Data Warehousing (Industry Articles)
CRM Technique: For small industry (Peer Publishing)
Products of the Year 2002 (Industry Articles)
XML meets the data warehouse (Industry Articles)
2003 Survivor's Guide to Business Applications (Industry Articles)
Uncovering Information Hidden in Web Archives (Industry Articles) DW.ITtoolbox.com
http://www.sserve.com/dwintro.asp
An Introduction to Data Warehousing - This white paper introduces data warehousing concepts.
http://www.kenorrinst.com/dwpaper.html
Data Warehousing Technology
http://www.cis.upenn.edu/~sahuguet/OLAP/
An introduction to Data warehousing & OLAP (slides)
http://www.gita.org/chapters/ontario/Data_Ware_ATT.ppt
An Introduction to Data Warehousing (slides)
http://www.cs.man.ac.uk/~franconi/teaching/2001/CS636/CS636-dw-intro.ppt
Introduction to Data Warehousing (slides)
http://www-courses.cs.uiuc.edu/~cs497jh/ppt/lecture_slides.html
Slides for the book "Data Mining: Concepts and Techniques"
http://www.essi-hsv.com/publications/datawarehouse.pdf
Introduction: What is a Data Warehouse?
http://www.pcc.qub.ac.uk/tec/courses/datamining/stu_notes/dm_book_1.html
Data Mining An Introduction, Student Notes
http://www.dwinfocenter.org/
The Data Warehousing Information Center
http://www.dwinfocenter.org/whitepap.html
White Papers on Data Warehousing (Links)
http://otn.oracle.com/idevelop/online/courses/oln/how_to04.html#11.5
Free Data Warehouse Training from Oracle (registration required):
http://www.kdnuggets.com/
Knowledge Discovery Mine
http://www.cs.bham.ac.uk/~anp/TheDataMine.html
The Data Mine
http://www.research.microsoft.com/research/datamine/
Data Mining and Knowledge Discovery (an international journal)
http://research.microsoft.com/datamine/kdd99/
KDD-99: The Fourth International Conference on Knowledge Discovery and Data Mining
http://www.cs.helsinki.fi/research/fdk/
Data mining at the University of Helsinki
http://www3.shore.net/~kht/index.htm#wps
Data Mining White Papers Links
http://www3.shore.net/~kht/dmintro/dmintro.htm
Tutorial - An Introduction to Data Mining and Advanced DSS Technology
http://olap.winf.ruhr-uni-bochum.de/articles.phtml
German OLAP and Data Warehouse Forum - Articles (many are in English)
http://www.dci.com/news/datawarehouse/articles/1998/05/links.htm
Data Warehouse Online Resources: Sites Worth Knowing (Links)
http://www.microsoft.com/technet/SQL/Technote/datawhst.asp
The MS Data Warehousing Strategy
http://hsb.baylor.edu/ramsower/ais.ac.96/papers/gray.htm
DATA WAREHOUSES, OLAP, DATA MINING, AND THE NEW DSS: A Tutorial Presented to AIS'96
http://www.mylab.co.kr/main/html/relevance/Chemo/chemometrics/application_data/Data%20Mining.html
Introduction to Data Mining
http://idm.internet.com/features/datawarehousing.html
A Definition of Data Warehousing - A good 2 page primer on the important aspects of Data Warehousing
http://www.sgroves.demon.co.uk/olaplnks.htm
OLAP Resources
http://www.pilotsw.com/news/olap_white.htm
An Introduction to OLAP: Multidimensional Terminology and Technology
http://www.dw-institute.com/onsite2000/man.htm
Data Warehousing Institute
http://www.datawarehousing.org/
DataWarehousing.org
http://members.aol.com/lpang10473/dms.htm
Data Management Strategies and Technologies
Links from the above site:
Readings:
Corbin, Lisa. Data Warehouses Hit the Web:
http://www.govexec.com/tech/articles/0297info.htm
Data Warehousing Institute. Ten Mistakes to Avoid:
http://www.dw-institute.com/papers/10mistks.htm
Environmental Protection Agency. Envirofacts Warehouse/EnviroMapper:
http://www.epa.gov/enviro/index_java.html
Resources:
Data Warehousing Institute:
Ten Mistakes to Avoid: http://www.dw-institute.com/papers/10mistks.htm
Best Practices: http://www.dw-institute.com/papers/10mistks.htm
Case Studies: http://www.dw-institute.com/cases_a.htm
Datawarehouse.com (DM Review):
http://www.datawarehouse.com/
CIO Magazine Data Warehousing Research Center:
http://www.cio.com/forums/data/
ATG's Technology Guides on Data Warehousing:
http://www.techguide.com/dw/guides.shtml
Data Warehousing Knowledge Center:
http://www.datawarehousing.org/
http://webopedia.internet.com/TERM/d/data_warehouse.html
Data Warehouse Links
Hi Friends,
Here u can get some interesting sites on DWH, Data mining. You an also see links for readings, resources & white papers for the same. I hope this will be helpful for us.
And Ofcourse one of the good place is our Yahoo Group.
Thanks and Regards,
Moderator Team,
Informatica_World
…………………………………………….
Enjoy The Links ….
Data Warehouse White Papers:
Cost of Software Customization (Industry Articles)
Super Data Warehousing Solutions (Industry Articles)
The job forecast: partly cloudy (Industry Articles)
A Case Study in Metadata Harvesting: the NSDL (Academic Articles)
The Role of Network Storage for Data Warehousing (Industry Articles)
CRM Technique: For small industry (Peer Publishing)
Products of the Year 2002 (Industry Articles)
XML meets the data warehouse (Industry Articles)
2003 Survivor's Guide to Business Applications (Industry Articles)
Uncovering Information Hidden in Web Archives (Industry Articles) DW.ITtoolbox.com
http://www.sserve.com/dwintro.asp
An Introduction to Data Warehousing - This white paper introduces data warehousing concepts.
http://www.kenorrinst.com/dwpaper.html
Data Warehousing Technology
http://www.cis.upenn.edu/~sahuguet/OLAP/
An introduction to Data warehousing & OLAP (slides)
http://www.gita.org/chapters/ontario/Data_Ware_ATT.ppt
An Introduction to Data Warehousing (slides)
http://www.cs.man.ac.uk/~franconi/teaching/2001/CS636/CS636-dw-intro.ppt
Introduction to Data Warehousing (slides)
http://www-courses.cs.uiuc.edu/~cs497jh/ppt/lecture_slides.html
Slides for the book "Data Mining: Concepts and Techniques"
http://www.essi-hsv.com/publications/datawarehouse.pdf
Introduction: What is a Data Warehouse?
http://www.pcc.qub.ac.uk/tec/courses/datamining/stu_notes/dm_book_1.html
Data Mining An Introduction, Student Notes
http://www.dwinfocenter.org/
The Data Warehousing Information Center
http://www.dwinfocenter.org/whitepap.html
White Papers on Data Warehousing (Links)
http://otn.oracle.com/idevelop/online/courses/oln/how_to04.html#11.5
Free Data Warehouse Training from Oracle (registration required):
http://www.kdnuggets.com/
Knowledge Discovery Mine
http://www.cs.bham.ac.uk/~anp/TheDataMine.html
The Data Mine
http://www.research.microsoft.com/research/datamine/
Data Mining and Knowledge Discovery (an international journal)
http://research.microsoft.com/datamine/kdd99/
KDD-99: The Fourth International Conference on Knowledge Discovery and Data Mining
http://www.cs.helsinki.fi/research/fdk/
Data mining at the University of Helsinki
http://www3.shore.net/~kht/index.htm#wps
Data Mining White Papers Links
http://www3.shore.net/~kht/dmintro/dmintro.htm
Tutorial - An Introduction to Data Mining and Advanced DSS Technology
http://olap.winf.ruhr-uni-bochum.de/articles.phtml
German OLAP and Data Warehouse Forum - Articles (many are in English)
http://www.dci.com/news/datawarehouse/articles/1998/05/links.htm
Data Warehouse Online Resources: Sites Worth Knowing (Links)
http://www.microsoft.com/technet/SQL/Technote/datawhst.asp
The MS Data Warehousing Strategy
http://hsb.baylor.edu/ramsower/ais.ac.96/papers/gray.htm
DATA WAREHOUSES, OLAP, DATA MINING, AND THE NEW DSS: A Tutorial Presented to AIS'96
http://www.mylab.co.kr/main/html/relevance/Chemo/chemometrics/application_data/Data%20Mining.html
Introduction to Data Mining
http://idm.internet.com/features/datawarehousing.html
A Definition of Data Warehousing - A good 2 page primer on the important aspects of Data Warehousing
http://www.sgroves.demon.co.uk/olaplnks.htm
OLAP Resources
http://www.pilotsw.com/news/olap_white.htm
An Introduction to OLAP: Multidimensional Terminology and Technology
http://www.dw-institute.com/onsite2000/man.htm
Data Warehousing Institute
http://www.datawarehousing.org/
DataWarehousing.org
http://members.aol.com/lpang10473/dms.htm
Data Management Strategies and Technologies
Links from the above site:
Readings:
Corbin, Lisa. Data Warehouses Hit the Web:
http://www.govexec.com/tech/articles/0297info.htm
Data Warehousing Institute. Ten Mistakes to Avoid:
http://www.dw-institute.com/papers/10mistks.htm
Environmental Protection Agency. Envirofacts Warehouse/EnviroMapper:
http://www.epa.gov/enviro/index_java.html
Resources:
Data Warehousing Institute:
Ten Mistakes to Avoid: http://www.dw-institute.com/papers/10mistks.htm
Best Practices: http://www.dw-institute.com/papers/10mistks.htm
Case Studies: http://www.dw-institute.com/cases_a.htm
Datawarehouse.com (DM Review):
http://www.datawarehouse.com/
CIO Magazine Data Warehousing Research Center:
http://www.cio.com/forums/data/
ATG's Technology Guides on Data Warehousing:
http://www.techguide.com/dw/guides.shtml
Data Warehousing Knowledge Center:
http://www.datawarehousing.org/
http://webopedia.internet.com/TERM/d/data_warehouse.html
Data Warehouse Links
get business days
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessDays]
(
@StartDate datetime,
@EndDate datetime
)
returns int
/* Release: ?
Created On: ?
Created By: ?
Purpose: Returns the the number of business days in hours format between two dates.
Does not account for holidays.
NOTES:
*/
as
begin
declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @EvalDate datetime
select @DaysBetween = 0
select @BusinessDays = 0
select @Cnt=0
select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1
while @Cnt < @DaysBetween
begin
select @EvalDate = @StartDate + @Cnt
if ((datepart(dw,@EvalDate) = 1) or (datepart(dw,@EvalDate) = 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
select @Cnt = @Cnt + 1
end
return DateDiff(hh,@StartDate,@EndDate)-@BusinessDays*24
end
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessDays]
(
@StartDate datetime,
@EndDate datetime
)
returns int
/* Release: ?
Created On: ?
Created By: ?
Purpose: Returns the the number of business days in hours format between two dates.
Does not account for holidays.
NOTES:
*/
as
begin
declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @EvalDate datetime
select @DaysBetween = 0
select @BusinessDays = 0
select @Cnt=0
select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1
while @Cnt < @DaysBetween
begin
select @EvalDate = @StartDate + @Cnt
if ((datepart(dw,@EvalDate) = 1) or (datepart(dw,@EvalDate) = 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
select @Cnt = @Cnt + 1
end
return DateDiff(hh,@StartDate,@EndDate)-@BusinessDays*24
end
get business hours
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessMinutes] (
@startDate datetime,
@endDate datetime
)
returns int
as
/*
Purpose: Returns the number minutes over business days between @StartDate and @EndDate
Note: This function does not account for holidays, actual open hours (9am-5pm ET), or internationalization.
Modification History
Date By Notes
----------- --------------- ---------------------------------------
10/06/2008 B.Huntley created
10/08/2008 B.Huntley Fixed function to exclude weekends
12/22/2008 C.Gaden Fixed problem when start date was on a weekend
Fixed problem when end date was on a weekend
Fixed problem for daylight savings overlap
*/
BEGIN
DECLARE @daysBetween INT
DECLARE @weekendDays INT
DECLARE @count INT
DECLARE @evalDate DATETIME
declare @returnValue int
SELECT @daysBetween = 0
SELECT @weekendDays = 0
SELECT @count = 0
if (datepart(dw, @startDate) = 1 or datepart(dw, @startDate) = 7) and
(datepart(dw, @endDate) = 1 or datepart(dw, @endDate) = 7) and
(datediff(d, @startDate, @endDate) <= 2)
-- start and end date are on the same weekend; open days is zero
select @returnValue = 0
else
begin
-- account for @startDate occurring on a Saturday or Sunday (do not count these days)
-- if start date is on a weekend make it set to 1 minute after midnight on the following Monday
select @startDate =
case when (DATEPART(dw, @startDate) = 1) then convert(datetime, convert(varchar, month(dateadd(day, 1, @startDate))) + '/' + convert(varchar, day(dateadd(day, 1, @startDate))) + '/' + convert(varchar, year(dateadd(day, 1, @startDate))) + ' 12:01:00 am')
when (DATEPART(dw, @startDate) = 7) then convert(datetime, convert(varchar, month(dateadd(day, 2, @startDate))) + '/' + convert(varchar, day(dateadd(day, 2, @startDate))) + '/' + convert(varchar, year(dateadd(day, 2, @startDate))) + ' 12:01:00 am')
else @startDate
end
-- account for @endDate occurring on a Saturday or Sunday (do not count these days)
-- if end date is on a weekend make it set to 1 minute before midnight on the previous Friday
select @endDate =
case when (DATEPART(dw, @endDate) = 1) then convert(datetime, convert(varchar, month(dateadd(day, -2, @endDate))) + '/' + convert(varchar, day(dateadd(day, -2, @endDate))) + '/' + convert(varchar, year(dateadd(day, -2, @endDate))) + ' 11:59:00 pm')
when (DATEPART(dw, @endDate) = 7) then convert(datetime, convert(varchar, month(dateadd(day, -1, @endDate))) + '/' + convert(varchar, day(dateadd(day, -1, @endDate))) + '/' + convert(varchar, year(dateadd(day, -1, @endDate))) + ' 11:59:00 pm')
else @endDate
end
-- return days between start & end, inclusive of start date
SELECT @daysBetween = DATEDIFF(DAY, @startDate, @endDate) + 1
-- check each day to see if it is a weekend
WHILE @count < @daysBetween
BEGIN
SELECT @evalDate = dateadd(day, @count, @startDate)
IF ((DATEPART(dw, @evalDate) = 1) OR (DATEPART(dw, @evalDate) = 7))
SELECT @weekendDays = @weekendDays + 1
SELECT @count = @count + 1
END
-- calculate number of open minutes (less weekends)
select @returnValue = DATEDIFF(mi, @startDate, @endDate) - @weekendDays * 1440
-- check if negative value occurred; this may happen during daylight savings time-shift
if @returnValue < 0
select @returnValue = 0
end
return @returnValue
END
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessMinutes] (
@startDate datetime,
@endDate datetime
)
returns int
as
/*
Purpose: Returns the number minutes over business days between @StartDate and @EndDate
Note: This function does not account for holidays, actual open hours (9am-5pm ET), or internationalization.
Modification History
Date By Notes
----------- --------------- ---------------------------------------
10/06/2008 B.Huntley created
10/08/2008 B.Huntley Fixed function to exclude weekends
12/22/2008 C.Gaden Fixed problem when start date was on a weekend
Fixed problem when end date was on a weekend
Fixed problem for daylight savings overlap
*/
BEGIN
DECLARE @daysBetween INT
DECLARE @weekendDays INT
DECLARE @count INT
DECLARE @evalDate DATETIME
declare @returnValue int
SELECT @daysBetween = 0
SELECT @weekendDays = 0
SELECT @count = 0
if (datepart(dw, @startDate) = 1 or datepart(dw, @startDate) = 7) and
(datepart(dw, @endDate) = 1 or datepart(dw, @endDate) = 7) and
(datediff(d, @startDate, @endDate) <= 2)
-- start and end date are on the same weekend; open days is zero
select @returnValue = 0
else
begin
-- account for @startDate occurring on a Saturday or Sunday (do not count these days)
-- if start date is on a weekend make it set to 1 minute after midnight on the following Monday
select @startDate =
case when (DATEPART(dw, @startDate) = 1) then convert(datetime, convert(varchar, month(dateadd(day, 1, @startDate))) + '/' + convert(varchar, day(dateadd(day, 1, @startDate))) + '/' + convert(varchar, year(dateadd(day, 1, @startDate))) + ' 12:01:00 am')
when (DATEPART(dw, @startDate) = 7) then convert(datetime, convert(varchar, month(dateadd(day, 2, @startDate))) + '/' + convert(varchar, day(dateadd(day, 2, @startDate))) + '/' + convert(varchar, year(dateadd(day, 2, @startDate))) + ' 12:01:00 am')
else @startDate
end
-- account for @endDate occurring on a Saturday or Sunday (do not count these days)
-- if end date is on a weekend make it set to 1 minute before midnight on the previous Friday
select @endDate =
case when (DATEPART(dw, @endDate) = 1) then convert(datetime, convert(varchar, month(dateadd(day, -2, @endDate))) + '/' + convert(varchar, day(dateadd(day, -2, @endDate))) + '/' + convert(varchar, year(dateadd(day, -2, @endDate))) + ' 11:59:00 pm')
when (DATEPART(dw, @endDate) = 7) then convert(datetime, convert(varchar, month(dateadd(day, -1, @endDate))) + '/' + convert(varchar, day(dateadd(day, -1, @endDate))) + '/' + convert(varchar, year(dateadd(day, -1, @endDate))) + ' 11:59:00 pm')
else @endDate
end
-- return days between start & end, inclusive of start date
SELECT @daysBetween = DATEDIFF(DAY, @startDate, @endDate) + 1
-- check each day to see if it is a weekend
WHILE @count < @daysBetween
BEGIN
SELECT @evalDate = dateadd(day, @count, @startDate)
IF ((DATEPART(dw, @evalDate) = 1) OR (DATEPART(dw, @evalDate) = 7))
SELECT @weekendDays = @weekendDays + 1
SELECT @count = @count + 1
END
-- calculate number of open minutes (less weekends)
select @returnValue = DATEDIFF(mi, @startDate, @endDate) - @weekendDays * 1440
-- check if negative value occurred; this may happen during daylight savings time-shift
if @returnValue < 0
select @returnValue = 0
end
return @returnValue
END
Function for getting comma seperator
SET
QUOTED_IDENTIFIER ON
GO
/*
Name: CommaSeperator
Description: This function will seperate the string parameters.
Modification History
Date By Description
---------- --------------------- -----------------------------------------------
04/03/2009 Paradigm Infotech Inc. Initial Version created
Copyright [2009] New England BioLabs, Inc. All rights reserved.
*/
create
function [dbo].[CommaSeperator]
(
@stringToBreak varchar
(4000),
@limiter
char(1)
)
returns @list
table (item varchar(4000))
as
begin
if (charindex(@limiter,@stringToBreak) = 0)
begin
insert into @list values (@stringToBreak)
return
end
declare @list1 varchar(4000)
declare @charIndex int
declare @TempItems table (item varchar(4000))
set @charIndex = charindex(@limiter,@stringToBreak)
while len(@stringToBreak) > 0 and @charIndex <> 0
begin
select @list1 = ltrim(left(@stringToBreak,@charIndex - 1))
insert @tempItems select @list1
select @stringToBreak = ltrim(right(@stringToBreak, len(@stringToBreak) - @charIndex))
set @charIndex = charindex(@limiter,@stringToBreak)
end
insert @tempItems select @stringToBreak
insert @list select Item from @tempItems
return
end
QUOTED_IDENTIFIER ON
GO
/*
Name: CommaSeperator
Description: This function will seperate the string parameters.
Modification History
Date By Description
---------- --------------------- -----------------------------------------------
04/03/2009 Paradigm Infotech Inc. Initial Version created
Copyright [2009] New England BioLabs, Inc. All rights reserved.
*/
create
function [dbo].[CommaSeperator]
(
@stringToBreak varchar
(4000),
@limiter
char(1)
)
returns @list
table (item varchar(4000))
as
begin
if (charindex(@limiter,@stringToBreak) = 0)
begin
insert into @list values (@stringToBreak)
return
end
declare @list1 varchar(4000)
declare @charIndex int
declare @TempItems table (item varchar(4000))
set @charIndex = charindex(@limiter,@stringToBreak)
while len(@stringToBreak) > 0 and @charIndex <> 0
begin
select @list1 = ltrim(left(@stringToBreak,@charIndex - 1))
insert @tempItems select @list1
select @stringToBreak = ltrim(right(@stringToBreak, len(@stringToBreak) - @charIndex))
set @charIndex = charindex(@limiter,@stringToBreak)
end
insert @tempItems select @stringToBreak
insert @list select Item from @tempItems
return
end
Displaying a Pop-up message in SSRS 2005
SSRS 2005 has various limitations but it does have some work-around to perform certain tasks some may think is not possible. Here I am sharing one such experience which I hope will be useful to all SSRS developers out there.
Case In Point:
Requirement was to display a pop-up message box if one of the user entered parameter was not between 1 & 25. As soon as user clicked the ‘View Report’ button, before the report is displayed, this pop-up Confirmation Dialogue box with 'Yes' & 'No' options should be displayed.
FYI: It is also possible to display an error message box, or just plain message box or Confirmation box with 'Yes', 'No' & 'Cancel' and such as you want.
That being said, let’s assume we have a report which accepts a Parameter - an Integer from the user. If user enters a number less than 1 or more than 25, a pop-up message dialogue will appear asking user for confirmation. Something like - "You selected '30'. Are you sure you want to Continue?”
If user still decides to run the report and clicks "Yes", we should run the report. If user clicks "No", the report will not be displayed, instead a message box will be displayed asking user to correct their parameter supplied. In any case user enters a number between 1 and 25, it is valid and therefore no pop-up is displayed but just the report. You can of course change any of this functionality to suite your need.
Solution:
Let's get started by creating a parameter, say a parameter name ‘Number’, of Integer type. Under its Default values check Non-queried and set a value of 1, you can modify according to your requirement.
Now let’s create another parameter ‘IsValidNum’ of type Boolean. This will determine if the user entered number is between 1 and 25. Make this second parameter a Hidden parameter. Based on the value of this Hidden parameter IsValidNum, we will control what user gets to see.
We want to make sure our report is inside a single control. Say for example, if you have various Textboxes and may be couple of Tables, you would want to include all these inside one control – may be a Rectangle or a Listbox, or simply a single Table. We would then want to set the visibility of this control to Hidden - only if our IsValidNum parameter returns false.
Let’s name our main control “RprtCtrl”.
Go to its properties > Visibility > Hidden and change the expression to:
= Not(Parameter!IsValidNum.Value)
The whole idea of building the report as a single control is to control what gets displayed based on our parameter - IsValidNum. Of course, you do not have to make it a single control, but in that case you will have to change the “Hidden” property of each and every control based on this parameter. Now let us put a Textbox outside our main control. This Textbox will display the message to the user saying something like “Please enter the number between 1 and 25”. The Hidden property of this Textbox will be just opposite to that of our main control. So if the parameter ‘IsValidNum’ returns TRUE, this Textbox’s Hidden property will be set to FALSE and that of the main control to TRUE. Thus showing the Textbox and hiding the report.
Let’s name our textbox “MsgTxtBx”.
Go to its properties > Visibility > Hidden and change the expression to:
= Parameter!IsValidNum.Value
This should assure that you see your Report only when the user wants to or only if user selects the value between 1 and 25. In any other case, user gets a message asking to enter the number between 1 and 25. Of course based on our parameter – ‘IsValidNum’.
Okay, so how do we decide the Value for ‘IsValidNum’ parameter?
It’s simple right? If the user enter the value between 1 and 25 for the parameter ‘Number’ it is TRUE and for all others value it is FALSE.
Not exactly! Remember value of the number decides if a Message Box should be popped-up or not and then based on user’s decision i.e. whether they click ‘Yes’ or ‘No’, ‘IsValidNum’ gets its value set. So let us now write a method to handle this functionality.
Our goal of this function would be to check the value of the parameter Number and if it is not between 1 and 25, display a pop-up message asking user if they want to continue running the report. If user clicks ‘Yes’ - we display the report, if ‘No’ - a message will be displayed asking user to simply enter the correct value.
FYI: We are displaying the message to enter the correct value in a Textbox here, but you could also display the same message in a pop-up instead.
To handle this, let us write a VB function.
Go to Report Properties and under the Code tab write the following VB function:
Function CheckNum(NumEntered as Integer) as Boolean
Dim prompt as String
Dim usrResponse As MsgBoxResult
prompt = ""
usrResponse = MsgBoxResult.No
If (NumEntered > 25 OR NumEntered < 1) Then prompt = "You entered " & Str(NumEntered) _ & ". Are you sure you want to continue?" End If If prompt <> "" Then
usrResponse = MsgBox(prompt,4,"Message Box Title" )
If usrResponse = MsgBoxResult.Yes Then
Return TRUE
Else
Return FALSE
End If
Else
Return TRUE
End If
End Function
This function accepts an Integer as a parameter and returns a Boolean value. Every time the value of the parameter is not between 1 & 25, it creates a String prompt which is just asking user for confirmation and the confirmation prompt is displayed on a pop-up message box. Based on whether user clicks ‘Yes’ or ‘No’, the function returns the Boolean value. The function returns ‘TRUE’ if its parameter value is between 1 & 25.
FYI: The MsgBox Function is explained here http://msdn.microsoft.com/en-us/library/139z2azd(VS.80).aspx for more information.
Now how do we trigger this function call?
This is the trickiest part. We want the pop-up before report is displayed. So this should be triggered with the parameter.
Let’s go back to our hidden report parameter ‘IsValidNum’. Under Default values, check Non-queried and edit the expression as:
=Code.CheckNum(Parameters!Number.Value)
This will assign the boolean value returned from our function to this hidden parameter ‘IsValidNum’.
Now run the report if you enter value less than 1 or greater than 25 you should now get a pop-up.
This concept can be used to validate your report parameters and displaying user with friendly pop-up message. Enjoy!!
Case In Point:
Requirement was to display a pop-up message box if one of the user entered parameter was not between 1 & 25. As soon as user clicked the ‘View Report’ button, before the report is displayed, this pop-up Confirmation Dialogue box with 'Yes' & 'No' options should be displayed.
FYI: It is also possible to display an error message box, or just plain message box or Confirmation box with 'Yes', 'No' & 'Cancel' and such as you want.
That being said, let’s assume we have a report which accepts a Parameter - an Integer from the user. If user enters a number less than 1 or more than 25, a pop-up message dialogue will appear asking user for confirmation. Something like - "You selected '30'. Are you sure you want to Continue?”
If user still decides to run the report and clicks "Yes", we should run the report. If user clicks "No", the report will not be displayed, instead a message box will be displayed asking user to correct their parameter supplied. In any case user enters a number between 1 and 25, it is valid and therefore no pop-up is displayed but just the report. You can of course change any of this functionality to suite your need.
Solution:
Let's get started by creating a parameter, say a parameter name ‘Number’, of Integer type. Under its Default values check Non-queried and set a value of 1, you can modify according to your requirement.
Now let’s create another parameter ‘IsValidNum’ of type Boolean. This will determine if the user entered number is between 1 and 25. Make this second parameter a Hidden parameter. Based on the value of this Hidden parameter IsValidNum, we will control what user gets to see.
We want to make sure our report is inside a single control. Say for example, if you have various Textboxes and may be couple of Tables, you would want to include all these inside one control – may be a Rectangle or a Listbox, or simply a single Table. We would then want to set the visibility of this control to Hidden - only if our IsValidNum parameter returns false.
Let’s name our main control “RprtCtrl”.
Go to its properties > Visibility > Hidden and change the expression to:
= Not(Parameter!IsValidNum.Value)
The whole idea of building the report as a single control is to control what gets displayed based on our parameter - IsValidNum. Of course, you do not have to make it a single control, but in that case you will have to change the “Hidden” property of each and every control based on this parameter. Now let us put a Textbox outside our main control. This Textbox will display the message to the user saying something like “Please enter the number between 1 and 25”. The Hidden property of this Textbox will be just opposite to that of our main control. So if the parameter ‘IsValidNum’ returns TRUE, this Textbox’s Hidden property will be set to FALSE and that of the main control to TRUE. Thus showing the Textbox and hiding the report.
Let’s name our textbox “MsgTxtBx”.
Go to its properties > Visibility > Hidden and change the expression to:
= Parameter!IsValidNum.Value
This should assure that you see your Report only when the user wants to or only if user selects the value between 1 and 25. In any other case, user gets a message asking to enter the number between 1 and 25. Of course based on our parameter – ‘IsValidNum’.
Okay, so how do we decide the Value for ‘IsValidNum’ parameter?
It’s simple right? If the user enter the value between 1 and 25 for the parameter ‘Number’ it is TRUE and for all others value it is FALSE.
Not exactly! Remember value of the number decides if a Message Box should be popped-up or not and then based on user’s decision i.e. whether they click ‘Yes’ or ‘No’, ‘IsValidNum’ gets its value set. So let us now write a method to handle this functionality.
Our goal of this function would be to check the value of the parameter Number and if it is not between 1 and 25, display a pop-up message asking user if they want to continue running the report. If user clicks ‘Yes’ - we display the report, if ‘No’ - a message will be displayed asking user to simply enter the correct value.
FYI: We are displaying the message to enter the correct value in a Textbox here, but you could also display the same message in a pop-up instead.
To handle this, let us write a VB function.
Go to Report Properties and under the Code tab write the following VB function:
Function CheckNum(NumEntered as Integer) as Boolean
Dim prompt as String
Dim usrResponse As MsgBoxResult
prompt = ""
usrResponse = MsgBoxResult.No
If (NumEntered > 25 OR NumEntered < 1) Then prompt = "You entered " & Str(NumEntered) _ & ". Are you sure you want to continue?" End If If prompt <> "" Then
usrResponse = MsgBox(prompt,4,"Message Box Title" )
If usrResponse = MsgBoxResult.Yes Then
Return TRUE
Else
Return FALSE
End If
Else
Return TRUE
End If
End Function
This function accepts an Integer as a parameter and returns a Boolean value. Every time the value of the parameter is not between 1 & 25, it creates a String prompt which is just asking user for confirmation and the confirmation prompt is displayed on a pop-up message box. Based on whether user clicks ‘Yes’ or ‘No’, the function returns the Boolean value. The function returns ‘TRUE’ if its parameter value is between 1 & 25.
FYI: The MsgBox Function is explained here http://msdn.microsoft.com/en-us/library/139z2azd(VS.80).aspx for more information.
Now how do we trigger this function call?
This is the trickiest part. We want the pop-up before report is displayed. So this should be triggered with the parameter.
Let’s go back to our hidden report parameter ‘IsValidNum’. Under Default values, check Non-queried and edit the expression as:
=Code.CheckNum(Parameters!Number.Value)
This will assign the boolean value returned from our function to this hidden parameter ‘IsValidNum’.
Now run the report if you enter value less than 1 or greater than 25 you should now get a pop-up.
This concept can be used to validate your report parameters and displaying user with friendly pop-up message. Enjoy!!
To find out the quarter start date and end date
USE [reddy]
GO
/****** Object: StoredProcedure [dbo].[usp_GetQuaterStartAndEndDates] Script Date: 02/26/2011 23:41:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetQuaterStartAndEndDates]
AS
BEGIN
Declare @Q_StartDate Datetime
Declare @Q_EndDate Datetime
Declare @Qno Int
SELECT @Qno = DATEPART(QQ,GETDATE())
IF @Qno = 1
BEGIN
SET @Q_StartDate = '01-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '03-31-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
ELSE IF @Qno = 2
BEGIN
SET @Q_StartDate = '04-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '06-30-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
ELSE IF @Qno = 3
BEGIN
SET @Q_StartDate = '07-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '09-30-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
ELSE IF @Qno = 4
BEGIN
SET @Q_StartDate = '10-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '12-31-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
SELECT @Q_StartDate AS BEGINNINGDATE, @Q_EndDate as ENDDATE
END
GO
/****** Object: StoredProcedure [dbo].[usp_GetQuaterStartAndEndDates] Script Date: 02/26/2011 23:41:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetQuaterStartAndEndDates]
AS
BEGIN
Declare @Q_StartDate Datetime
Declare @Q_EndDate Datetime
Declare @Qno Int
SELECT @Qno = DATEPART(QQ,GETDATE())
IF @Qno = 1
BEGIN
SET @Q_StartDate = '01-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '03-31-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
ELSE IF @Qno = 2
BEGIN
SET @Q_StartDate = '04-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '06-30-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
ELSE IF @Qno = 3
BEGIN
SET @Q_StartDate = '07-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '09-30-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
ELSE IF @Qno = 4
BEGIN
SET @Q_StartDate = '10-01-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
SET @Q_EndDate = '12-31-' + Convert(Varchar(10),(DATEPART(YY,GETDATE())))
END
SELECT @Q_StartDate AS BEGINNINGDATE, @Q_EndDate as ENDDATE
END
recussive stored procedure
create
procedure proc_all(@eno int,@add int output)
as
begin
select
@add=@eno+5
return
end
create
procedure proc_1(@eno int,@mul int output)
as
begin
select
@mul=@eno*5
return
end
alter
procedure proc_2(@eno int)
as
begin
declare
@x int
declare
@mul int
declare
@add int
--declare @add int
exec
proc_all @eno,@add output
exec
proc_1 @eno,@mul output
select
@x=@add+@mul
select
@x as result
return
end
exec
proc_2 8
procedure proc_all(@eno int,@add int output)
as
begin
select
@add=@eno+5
return
end
create
procedure proc_1(@eno int,@mul int output)
as
begin
select
@mul=@eno*5
return
end
alter
procedure proc_2(@eno int)
as
begin
declare
@x int
declare
@mul int
declare
@add int
--declare @add int
exec
proc_all @eno,@add output
exec
proc_1 @eno,@mul output
select
@x=@add+@mul
select
@x as result
return
end
exec
proc_2 8
Reset Page Number on Group Break
SQL Server Reporting Services provides 2 Global variables: Globals.PageNumber and Globals.TotalPages. You may use these to display page numbers in your "Page Footer." However, suppose you need to "reset" the page number whenever a "group" in your RDL breaks. For example, say you are displaying customer invoices and you want the page number to reset on each new customer. This is such an obvious requirement, but unfortunately there is no native support for this in the reporting services.
Possible Solution
Initially, we were very happy to find a similar solution at Chris Hay's Weblog. In fact, there are some other interesting articles too at his Weblog that are worth reading. His solution really lies in using the "shared" variables. Precisely, add a custom function to set the shared variables and retrieve the group page number.
Collapse
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) _
as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
Use the function in the page header or footer:
Collapse
=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)
Possible Solution
Initially, we were very happy to find a similar solution at Chris Hay's Weblog. In fact, there are some other interesting articles too at his Weblog that are worth reading. His solution really lies in using the "shared" variables. Precisely, add a custom function to set the shared variables and retrieve the group page number.
Collapse
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) _
as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
Use the function in the page header or footer:
Collapse
=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)
get word count of the input string
USE
[Company]
GO
/****** Object: UserDefinedFunction [dbo].[WordCount] Script Date: 08/25/2009 11:28:42 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
FUNCTION [dbo].[WordCount] ( @InputString VARCHAR(4000) )
RETURNS
INT
AS
BEGIN
DECLARE
@Index INT
DECLARE
@Char CHAR(1)
DECLARE
@PrevChar CHAR(1)
DECLARE
@WordCount INT
SET
@Index = 1
SET
@WordCount = 0
WHILE
@Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar = ' ' AND @Char != ' '
SET @WordCount = @WordCount + 1
SET @Index = @Index + 1
END
RETURN
@WordCount
END
select
dbo.wordcount ('r e d d e p p a')
[Company]
GO
/****** Object: UserDefinedFunction [dbo].[WordCount] Script Date: 08/25/2009 11:28:42 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
FUNCTION [dbo].[WordCount] ( @InputString VARCHAR(4000) )
RETURNS
INT
AS
BEGIN
DECLARE
@Index INT
DECLARE
@Char CHAR(1)
DECLARE
@PrevChar CHAR(1)
DECLARE
@WordCount INT
SET
@Index = 1
SET
@WordCount = 0
WHILE
@Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END
IF @PrevChar = ' ' AND @Char != ' '
SET @WordCount = @WordCount + 1
SET @Index = @Index + 1
END
RETURN
@WordCount
END
select
dbo.wordcount ('r e d d e p p a')
SQL Reporting Services FAQ
¡P What is SQL Reporting Services?
o SQL Server Reporting Services is a comprehensive, server-based solution
that enables the creation, management, and delivery of both traditional,
paper-oriented reports and interactive, Web-based reports. An integrated
part of the Microsoft business intelligence framework, Reporting Services
combines the data management capabilities of SQL Server and Microsoft
Windows Server. with familiar and powerful Microsoft Office System
applications to deliver real-time information to support daily operations
and drive decisions. (text from
http://www.microsoft.com/sql/reporting/productinfo/overview.asp)
¡P How do I create a report?
o There are 2 common ways of creating a report for use by SQL Reporting
Services. Reports in SQL Reporting Services use the Report Description
Language (RDL) which is an XML based standard for defining reports.
Since it is simply XML, a report can, theoretically, be written using any
text editor. This method, however, is not an approach favored by most
since implementing RDL by hand can be laborious in addition to having a
steep learning curve.
By far, the most common way of creating a report is by using the SQL
Report Designer which is available to those owning licenses for both SQL
Server and Visual Studio.NET 2003. The Report Designer is and Visual
Studio.NET 2003 add-in packaged with SQL Reporting Services and,
when installed, makes available a Report project type in Visual
Studio.NET 2003.
Also on the horizon are numerous third party tools that are able to
generate RDL. At the time of this writing, many of the available tools are
still in beta testing but there are a handful that offer full products for
consumer purchase and/or evaluation.
¡P I want to get the Report Designer. Can you make it available for download?
o The Report Designer add-in for SQL Reporting Services is available only
with a license for SQL Server. Because SQL Reporting Services licensing
is covered by licensing for SQL Server itself, you must own a license for a
version of SQL Server in order to make use of client tools such as the
Report Designer, SQL Enterprise Manager, and SQL Query Analyzer.
However, the developer edition of SQL Server currently sells for a lower
price than Standard or Enterprise of SQL Server. Since SQL Reporting
Services is licensed with SQL Server, you would be entitled to a
Developer Edition of SQL Reporting Services if you were to purchase
SQL Server Developer Edition. More information can be found at
http://www.microsoft.com/sql/howtobuy/development.asp
¡P Can I use my MaximumASP database for reporting?
o Yes you can. Reports can use your MaximumASP database as a
datasource. Simply use the correct connection string in your report data
source file (.rds).
¡P Can I use a database outside of MaximumASP for reporting and if so what kinds?
o Any Internet connected database server can be used as a report data source
for SQL Reporting Services as long as you have the proper connection
settings and we have the necessary drivers installed. Currently, we only
offer ODBC drivers for Microsoft data sources such as SQL Server or
Access as well as common ¡§text¡¨ formats like XML, CSV or tab delimited
files.
¡P How do I reference SQL Reporting Services in my own web application?
o The SQL Reporting Services product available with your MaximumASP
account offers a fully functional web service that allows you to create, run,
and view reports programmatically. To consume the web service, simply
reference the service at
https://www.myreportserver.com/ReportServer/ReportService.asmx. For
more information about consuming and implementing the SQL Reporting
Services web service, consult the SQL Reporting Services Books Online.
¡P What is the difference between the ¡§Report Manager¡¨ and the ¡§Report Server¡¨
links on the home page of http://www.myreportserver.com?
o Report Manager is a convenient web user interface to the Report Server
that enables you to view and administer your reports, data sources, and
report resources.
Report Server is the web service application that functions as your
programmatic gateway to the SQL Reporting Services engine. When
browsing to the Report Server, you will be shown the contents of your
individual Reports directory.
¡P Why do I have to connect using https when browsing to the Report Server or
Report Manager applications?
o Technically, you don¡¦t HAVE to connect with https (using SSL).
However since you are required to provide authentication information for
each, SSL will ensure that your authentication information is not sent over
the wire in clear text.
¡P Why can¡¦t I manage subscriptions or create subscriptions with SQL Reporting
Services?
o SQL Reporting Services requires certain domain roles and permissions to
take full advantage of the power of subscriptions. At this time, we do not
offer Active Directory management tools to our customers to manage their
own group of domain users. Also, there is the real possibility that SQL
Reporting Services can be used for purposes of high volume email
deployment which is in violation of the MaximumASP Terms of Service.
¡P Where can I learn more about SQL Reporting Services?
o There are several resources available for SQL Reporting Services. The
best resource is the SQL Reporting Services Books Online that comes
with all versions of SQL Reporting Services. Also available with all
versions of SQL Reporting Services are report samples and an
accompanying database that functions as the datasource for those samples.
There is also help available inside the Report Manager application via the
¡§help¡¨ link. The information in this area applies mainly to the manager
application, but can offer some very useful tips and knowledge about
reports in general.
For those who like to look elsewhere for their information, there is a
newsgroup set up for Reporting Services within msnews.microsoft.com
called microsoft.public.sqlserver.reportingsvcs
¡P How can I get other people to view my reports without having to give them my
SQL Reporting Services password?
o By consuming the SQL Reporting Services web service, customers can
pull reports directly from the report server and render them inside any web
application as well as custom windows, console, or other types of
applications that may run within your business. To consume the web
service, simply reference the service at
https://www.myreportserver.com/ReportServer. For more information
about consuming and implementing the SQL Reporting Services web
service, consult the SQL Reporting Services Books Online.
¡P Why is the navigation toolbar not showing up when I render an HTML report
using the web service?
o The navigation toolbar that appears when using the Report Manager
application or the Report Server application interface is available only as
conveniences in those applications. It is not part of the consumable part of
the web service. However, the methods and properties needed to construct
the functionalities of that toolbar are available in the SQL Reporting
Services Web Service. To consume the web service, simply reference the
service at http://www.myreportserver.com/ReportServer. For more
information about consuming and implementing the SQL Reporting
Services web service, consult the SQL Reporting Services Books Online.
¡P What do I need to do to sign up for SQL Reporting Services?
o The only requirement to ¡§activate¡¨ SQL Reporting Services is that you
have an active user account with MaximumASP. Armed with your
authentication information, you need only to point your browser to
http://www.myreportserver.com and logon to either the Report Server or
Report Manager Applications. We would suggest you start in the Report
Manager Application as this will offer a more intuitive user interface than
the Report Server application.
¡P What are the licensing terms for SQL Reporting Services?
o SQL Reporting Services is part of SQL Server. The licensing of SQL
Reporting Services is covered by the terms of the version of SQL Server.
More information can be found by going to the licensing FAQ at
http://www.microsoft.com/sql/reporting/howtobuy/faq.asp or the article
http://www.microsoft.com/sql/reporting/howtobuy/howtolicensers.asp or
by calling your Microsoft licensing representative.
¡P Is there an added fee for using the MaximumASP SQL Reporting Services
product?
o There is no additional fee for using the SQL Reporting Services product.
It is open to all current customers of MaximumASP.
¡P I have an assembly that will enhance or customize the Report Server for my
application(s). Will you install this onto the server?
o Because the Reporting Services product is shared by multiple customers,
any changes made to the configuration or architecture of the report
server(s) would potentially affect all customers using that server. For this
reason, we will not entertain installing additional assemblies or make
configuration changes unless we deem the changes are necessary and are
for the benefit of all customers using MaximumASP¡¦s SQL Reporting
Services product.
¡P I have written an assembly that is an extension to SQL Reporting Services that
will deliver a report in a different format. Will you install this onto the server?
o Adding a delivery extension to MaximumASP¡¦s SQL Reporting Services
product is considered a global change. Because the report server product
is shared by multiple customers, any changes made to the configuration or
architecture of the report server(s) would potentially affect all customers
using that server. For this reason, we will not entertain installing
additional assemblies or make configuration changes unless we deem the
changes are necessary and are for the benefit of all customers using
MaximumASP¡¦s SQL Reporting Services product.
You may want to consider pulling the RDL file directly from SQL
Reporting Services Server and then implementing your rendering
extension within your own web or desktop application.
¡P How do I publish or deploy my report to my MaximumASP SQL Reporting
Services folder?
o There are a handful of ways to push a report up to your MaximumASP
SQL Reporting Services folder.
Within the Report Manager application, you can upload your RDL file
into your reports folder. Then you set the datasource for your report and
upload your datasource file (.rds) if needed.
Using Report Designer in VisualStudio.NET 2003, you will first need to
set the project properties for the report server so that it will point to
https://www.myreportserver.com/ReportServer. Then, you simply rightclick
on your report project and select ¡§Deploy¡¨ from the menu. After the
report project is compiled, you will then be asked to provide
authentication information for the report server. After entering this
information, VisualStudio.NET 2003 will attempt to copy up your report
as well as any data source files and resources. Upon successful posting of
your report, your report will be available in your folder and you will be
able to browse to the Report Server or Report Manager to view and
manage your report.
¡P I am a dedicated customer and I want to install reporting services on my server.
How do I make this happen?
o To install this on your server, you will need to meet the licensing
requirements for SQL Server. We will run a basic default setup of
Reporting Services on your server. If there are any previous versions of
SQL Reporting Services on your server, they will be removed along with
the SQL Reporting Services Databases, Web Applications, and Windows
Service. You will need to provide the following for the installer:
„X Administrator login to the SQL Server
„X SQL Admin (sa) user and password used for installation of SQL
RS databases (only if Windows Administrator does not have admin
privileges in SQL Server)
„X SQL user for the Reporting Services engine (Default will be sa or
the Windows Administrator)
„X Windows user for the Reporting Services Windows service
(Default will be NetworkService)
„X Single site to host Report Server and Report Manager applications
o This installation does NOT qualify as a free upgrade for existing dedicated
server customers or new dedicated server customers. Hourly support
charges will be incurred by the requesting customer.
¡P I am a dedicated customer and I want to use SQL Reporting Services, but I do not
want to install SQL Server on my dedicated server.
o As long as you have a current MaximumASP Account login ID, you can
use the SQL Reporting Services product from MaximumASP.
¡P Something isn¡¦t working with my report. What can I do to troubleshoot this as a
user?
o Check and make sure your report is using the correct datasource (.rds) file
or that the datasource is compiled into the report. Also, browse to the
Report Manager application and make sure you are not viewing an older
report that has been cached..
¡P
Add a custom function to set the shared variables and retrieve the group page
number
Public Function GetGroupPageNumber(group as Object, pagenumber as
Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
o SQL Server Reporting Services is a comprehensive, server-based solution
that enables the creation, management, and delivery of both traditional,
paper-oriented reports and interactive, Web-based reports. An integrated
part of the Microsoft business intelligence framework, Reporting Services
combines the data management capabilities of SQL Server and Microsoft
Windows Server. with familiar and powerful Microsoft Office System
applications to deliver real-time information to support daily operations
and drive decisions. (text from
http://www.microsoft.com/sql/reporting/productinfo/overview.asp)
¡P How do I create a report?
o There are 2 common ways of creating a report for use by SQL Reporting
Services. Reports in SQL Reporting Services use the Report Description
Language (RDL) which is an XML based standard for defining reports.
Since it is simply XML, a report can, theoretically, be written using any
text editor. This method, however, is not an approach favored by most
since implementing RDL by hand can be laborious in addition to having a
steep learning curve.
By far, the most common way of creating a report is by using the SQL
Report Designer which is available to those owning licenses for both SQL
Server and Visual Studio.NET 2003. The Report Designer is and Visual
Studio.NET 2003 add-in packaged with SQL Reporting Services and,
when installed, makes available a Report project type in Visual
Studio.NET 2003.
Also on the horizon are numerous third party tools that are able to
generate RDL. At the time of this writing, many of the available tools are
still in beta testing but there are a handful that offer full products for
consumer purchase and/or evaluation.
¡P I want to get the Report Designer. Can you make it available for download?
o The Report Designer add-in for SQL Reporting Services is available only
with a license for SQL Server. Because SQL Reporting Services licensing
is covered by licensing for SQL Server itself, you must own a license for a
version of SQL Server in order to make use of client tools such as the
Report Designer, SQL Enterprise Manager, and SQL Query Analyzer.
However, the developer edition of SQL Server currently sells for a lower
price than Standard or Enterprise of SQL Server. Since SQL Reporting
Services is licensed with SQL Server, you would be entitled to a
Developer Edition of SQL Reporting Services if you were to purchase
SQL Server Developer Edition. More information can be found at
http://www.microsoft.com/sql/howtobuy/development.asp
¡P Can I use my MaximumASP database for reporting?
o Yes you can. Reports can use your MaximumASP database as a
datasource. Simply use the correct connection string in your report data
source file (.rds).
¡P Can I use a database outside of MaximumASP for reporting and if so what kinds?
o Any Internet connected database server can be used as a report data source
for SQL Reporting Services as long as you have the proper connection
settings and we have the necessary drivers installed. Currently, we only
offer ODBC drivers for Microsoft data sources such as SQL Server or
Access as well as common ¡§text¡¨ formats like XML, CSV or tab delimited
files.
¡P How do I reference SQL Reporting Services in my own web application?
o The SQL Reporting Services product available with your MaximumASP
account offers a fully functional web service that allows you to create, run,
and view reports programmatically. To consume the web service, simply
reference the service at
https://www.myreportserver.com/ReportServer/ReportService.asmx. For
more information about consuming and implementing the SQL Reporting
Services web service, consult the SQL Reporting Services Books Online.
¡P What is the difference between the ¡§Report Manager¡¨ and the ¡§Report Server¡¨
links on the home page of http://www.myreportserver.com?
o Report Manager is a convenient web user interface to the Report Server
that enables you to view and administer your reports, data sources, and
report resources.
Report Server is the web service application that functions as your
programmatic gateway to the SQL Reporting Services engine. When
browsing to the Report Server, you will be shown the contents of your
individual Reports directory.
¡P Why do I have to connect using https when browsing to the Report Server or
Report Manager applications?
o Technically, you don¡¦t HAVE to connect with https (using SSL).
However since you are required to provide authentication information for
each, SSL will ensure that your authentication information is not sent over
the wire in clear text.
¡P Why can¡¦t I manage subscriptions or create subscriptions with SQL Reporting
Services?
o SQL Reporting Services requires certain domain roles and permissions to
take full advantage of the power of subscriptions. At this time, we do not
offer Active Directory management tools to our customers to manage their
own group of domain users. Also, there is the real possibility that SQL
Reporting Services can be used for purposes of high volume email
deployment which is in violation of the MaximumASP Terms of Service.
¡P Where can I learn more about SQL Reporting Services?
o There are several resources available for SQL Reporting Services. The
best resource is the SQL Reporting Services Books Online that comes
with all versions of SQL Reporting Services. Also available with all
versions of SQL Reporting Services are report samples and an
accompanying database that functions as the datasource for those samples.
There is also help available inside the Report Manager application via the
¡§help¡¨ link. The information in this area applies mainly to the manager
application, but can offer some very useful tips and knowledge about
reports in general.
For those who like to look elsewhere for their information, there is a
newsgroup set up for Reporting Services within msnews.microsoft.com
called microsoft.public.sqlserver.reportingsvcs
¡P How can I get other people to view my reports without having to give them my
SQL Reporting Services password?
o By consuming the SQL Reporting Services web service, customers can
pull reports directly from the report server and render them inside any web
application as well as custom windows, console, or other types of
applications that may run within your business. To consume the web
service, simply reference the service at
https://www.myreportserver.com/ReportServer. For more information
about consuming and implementing the SQL Reporting Services web
service, consult the SQL Reporting Services Books Online.
¡P Why is the navigation toolbar not showing up when I render an HTML report
using the web service?
o The navigation toolbar that appears when using the Report Manager
application or the Report Server application interface is available only as
conveniences in those applications. It is not part of the consumable part of
the web service. However, the methods and properties needed to construct
the functionalities of that toolbar are available in the SQL Reporting
Services Web Service. To consume the web service, simply reference the
service at http://www.myreportserver.com/ReportServer. For more
information about consuming and implementing the SQL Reporting
Services web service, consult the SQL Reporting Services Books Online.
¡P What do I need to do to sign up for SQL Reporting Services?
o The only requirement to ¡§activate¡¨ SQL Reporting Services is that you
have an active user account with MaximumASP. Armed with your
authentication information, you need only to point your browser to
http://www.myreportserver.com and logon to either the Report Server or
Report Manager Applications. We would suggest you start in the Report
Manager Application as this will offer a more intuitive user interface than
the Report Server application.
¡P What are the licensing terms for SQL Reporting Services?
o SQL Reporting Services is part of SQL Server. The licensing of SQL
Reporting Services is covered by the terms of the version of SQL Server.
More information can be found by going to the licensing FAQ at
http://www.microsoft.com/sql/reporting/howtobuy/faq.asp or the article
http://www.microsoft.com/sql/reporting/howtobuy/howtolicensers.asp or
by calling your Microsoft licensing representative.
¡P Is there an added fee for using the MaximumASP SQL Reporting Services
product?
o There is no additional fee for using the SQL Reporting Services product.
It is open to all current customers of MaximumASP.
¡P I have an assembly that will enhance or customize the Report Server for my
application(s). Will you install this onto the server?
o Because the Reporting Services product is shared by multiple customers,
any changes made to the configuration or architecture of the report
server(s) would potentially affect all customers using that server. For this
reason, we will not entertain installing additional assemblies or make
configuration changes unless we deem the changes are necessary and are
for the benefit of all customers using MaximumASP¡¦s SQL Reporting
Services product.
¡P I have written an assembly that is an extension to SQL Reporting Services that
will deliver a report in a different format. Will you install this onto the server?
o Adding a delivery extension to MaximumASP¡¦s SQL Reporting Services
product is considered a global change. Because the report server product
is shared by multiple customers, any changes made to the configuration or
architecture of the report server(s) would potentially affect all customers
using that server. For this reason, we will not entertain installing
additional assemblies or make configuration changes unless we deem the
changes are necessary and are for the benefit of all customers using
MaximumASP¡¦s SQL Reporting Services product.
You may want to consider pulling the RDL file directly from SQL
Reporting Services Server and then implementing your rendering
extension within your own web or desktop application.
¡P How do I publish or deploy my report to my MaximumASP SQL Reporting
Services folder?
o There are a handful of ways to push a report up to your MaximumASP
SQL Reporting Services folder.
Within the Report Manager application, you can upload your RDL file
into your reports folder. Then you set the datasource for your report and
upload your datasource file (.rds) if needed.
Using Report Designer in VisualStudio.NET 2003, you will first need to
set the project properties for the report server so that it will point to
https://www.myreportserver.com/ReportServer. Then, you simply rightclick
on your report project and select ¡§Deploy¡¨ from the menu. After the
report project is compiled, you will then be asked to provide
authentication information for the report server. After entering this
information, VisualStudio.NET 2003 will attempt to copy up your report
as well as any data source files and resources. Upon successful posting of
your report, your report will be available in your folder and you will be
able to browse to the Report Server or Report Manager to view and
manage your report.
¡P I am a dedicated customer and I want to install reporting services on my server.
How do I make this happen?
o To install this on your server, you will need to meet the licensing
requirements for SQL Server. We will run a basic default setup of
Reporting Services on your server. If there are any previous versions of
SQL Reporting Services on your server, they will be removed along with
the SQL Reporting Services Databases, Web Applications, and Windows
Service. You will need to provide the following for the installer:
„X Administrator login to the SQL Server
„X SQL Admin (sa) user and password used for installation of SQL
RS databases (only if Windows Administrator does not have admin
privileges in SQL Server)
„X SQL user for the Reporting Services engine (Default will be sa or
the Windows Administrator)
„X Windows user for the Reporting Services Windows service
(Default will be NetworkService)
„X Single site to host Report Server and Report Manager applications
o This installation does NOT qualify as a free upgrade for existing dedicated
server customers or new dedicated server customers. Hourly support
charges will be incurred by the requesting customer.
¡P I am a dedicated customer and I want to use SQL Reporting Services, but I do not
want to install SQL Server on my dedicated server.
o As long as you have a current MaximumASP Account login ID, you can
use the SQL Reporting Services product from MaximumASP.
¡P Something isn¡¦t working with my report. What can I do to troubleshoot this as a
user?
o Check and make sure your report is using the correct datasource (.rds) file
or that the datasource is compiled into the report. Also, browse to the
Report Manager application and make sure you are not viewing an older
report that has been cached..
¡P
Add a custom function to set the shared variables and retrieve the group page
number
Public Function GetGroupPageNumber(group as Object, pagenumber as
Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
Automatically generate report in a specific format using SSRS
If you would like to generate a report in a specific format(html,pdf,xls, csv, xml) without having the user to select the format, use this technique:
PDF : http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=PDF
EXCEL: http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=Excel
With Parameters: Passing State and City as parameters
http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=PDF&State=CA&City=ALI
PDF : http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=PDF
EXCEL: http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=Excel
With Parameters: Passing State and City as parameters
http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=PDF&State=CA&City=ALI
Fixing Number Of rows while rendering the report into excel Report
Def:- After rendering the report into excel you want to show 100 records in each and every sheet
Create the dataset
SELECT ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate, rowguid, ModifiedDate
FROM Production.Product
Create the group
on group exression =ceiling(rownumber(nothing)/100)
Create the dataset
SELECT ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate, rowguid, ModifiedDate
FROM Production.Product
Create the group
on group exression =ceiling(rownumber(nothing)/100)
Multi-Statement Table-Valued Functions
----------Multi-Statement Table-Valued Functions
--select * from employee1
CREATE FUNCTION myProc (@ID Int)
RETURNS @EmployeeList Table
( ID Int
, Name nVarChar(50)
, Sal Money
)
AS
BEGIN
IF @ID IS NULL
BEGIN
INSERT INTO @EmployeeList (ID, Name, Sal)
SELECT ID, Name, Sal
FROM Emp5
END
ELSE
BEGIN
INSERT INTO @EmployeeList (ID, Name, Sal)
SELECT ID, Name, Sal
FROM Emp5
WHERE ID = @ID
END
return
END
GO
select * from myProc(10)
select * from emp5
--select * from employee1
CREATE FUNCTION myProc (@ID Int)
RETURNS @EmployeeList Table
( ID Int
, Name nVarChar(50)
, Sal Money
)
AS
BEGIN
IF @ID IS NULL
BEGIN
INSERT INTO @EmployeeList (ID, Name, Sal)
SELECT ID, Name, Sal
FROM Emp5
END
ELSE
BEGIN
INSERT INTO @EmployeeList (ID, Name, Sal)
SELECT ID, Name, Sal
FROM Emp5
WHERE ID = @ID
END
return
END
GO
select * from myProc(10)
select * from emp5
Stored Procedure using cursor
CREATE TABLE Orders (
OrderID int NOT NULL ,
CustomerID nchar (5) NULL ,
EmployeeID int NULL ,
OrderDate datetime NULL ,
RequiredDate datetime NULL ,
ShippedDate datetime NULL ,
ShipVia int NULL ,
Freight money NULL DEFAULT (0),
ShipName nvarchar (40) NULL ,
ShipAddress nvarchar (60) NULL ,
ShipCity nvarchar (15) NULL ,
ShipRegion nvarchar (15) NULL ,
ShipPostalCode nvarchar (10) NULL ,
ShipCountry nvarchar (15) NULL
)
--------------------------------------------
GO
INSERT INTO Orders VALUES (10248,'1',5,'7/4/1996','8/1/2001','7/16/2001',3,32.38,'V','A','R', NULL,N'51100','France')
go
-----------------------------------
alter PROCEDURE spCursorScope
AS
DECLARE @Counter int,
@OrderID int,
@CustomerID varchar(5)
DECLARE CursorTest cursor -----Declaring the cursor
LOCAL
FOR
SELECT OrderID, CustomerID
FROM Orders
SELECT @Counter = 1
OPEN CursorTest
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
PRINT 'Row ' + CONVERT(varchar,@Counter) + ' has an OrderID of ' +
CONVERT(varchar,@OrderID) + ' and a CustomerID of ' + @CustomerID
WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
BEGIN
SELECT @Counter = @Counter + 1
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
PRINT 'Row ' + CONVERT(varchar,@Counter) + ' has an OrderID of ' +
CONVERT(varchar,@OrderID) + ' and a CustomerID of ' + @CustomerID
END
GO
exec spCursorScope
go
OrderID int NOT NULL ,
CustomerID nchar (5) NULL ,
EmployeeID int NULL ,
OrderDate datetime NULL ,
RequiredDate datetime NULL ,
ShippedDate datetime NULL ,
ShipVia int NULL ,
Freight money NULL DEFAULT (0),
ShipName nvarchar (40) NULL ,
ShipAddress nvarchar (60) NULL ,
ShipCity nvarchar (15) NULL ,
ShipRegion nvarchar (15) NULL ,
ShipPostalCode nvarchar (10) NULL ,
ShipCountry nvarchar (15) NULL
)
--------------------------------------------
GO
INSERT INTO Orders VALUES (10248,'1',5,'7/4/1996','8/1/2001','7/16/2001',3,32.38,'V','A','R', NULL,N'51100','France')
go
-----------------------------------
alter PROCEDURE spCursorScope
AS
DECLARE @Counter int,
@OrderID int,
@CustomerID varchar(5)
DECLARE CursorTest cursor -----Declaring the cursor
LOCAL
FOR
SELECT OrderID, CustomerID
FROM Orders
SELECT @Counter = 1
OPEN CursorTest
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
PRINT 'Row ' + CONVERT(varchar,@Counter) + ' has an OrderID of ' +
CONVERT(varchar,@OrderID) + ' and a CustomerID of ' + @CustomerID
WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
BEGIN
SELECT @Counter = @Counter + 1
FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
PRINT 'Row ' + CONVERT(varchar,@Counter) + ' has an OrderID of ' +
CONVERT(varchar,@OrderID) + ' and a CustomerID of ' + @CustomerID
END
GO
exec spCursorScope
go
Subscribe to:
Posts (Atom)