Converting Datetime values to various formats - SQL

Using the CONVERT function in SQL Server 2005 the following datetime formats can be produced:


SELECT
GETDATE() AS UnconvertedDateTimeUsingGetDate,
CURRENT_TIMESTAMP AS UnconvertedDateTimeUsingCurrentTimestamp,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 100) AS [Default mon dd yyyy hh:miAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 101) AS [USA mm/dd/yy],
CONVERT(nvarchar(30), GETDATE(), 102) AS [ANSI yy.mm.dd],
CONVERT(nvarchar(30), GETDATE(), 103) AS [British/French dd/mm/yy],
CONVERT(nvarchar(30), GETDATE(), 104) AS [German dd.mm.yy],
CONVERT(nvarchar(30), GETDATE(), 105) AS [Italian dd-mm-yy],
CONVERT(nvarchar(30), GETDATE(), 106) AS [dd mon yy],
CONVERT(nvarchar(30), GETDATE(), 107) AS [Mon dd, yy],
CONVERT(nvarchar(30), GETDATE(), 108) AS [hh:mm:ss],
CONVERT(nvarchar(30), GETDATE(), 109) AS [Default+milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 110) AS [USA mm-dd-yy],
CONVERT(nvarchar(30), GETDATE(), 111) AS [Japan yy/mm/dd],
CONVERT(nvarchar(30), GETDATE(), 112) AS [ISO yymmdd],
CONVERT(nvarchar(30), GETDATE(), 113) AS [Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 114) AS [hh:mi:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 120) AS [ODBC canonical yyyy-mm-dd hh:mi:ss(24h)],
CONVERT(nvarchar(30), GETDATE(), 121) AS [ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 126) AS [ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)],
CONVERT(nvarchar(30), GETDATE(), 130) AS [Hijri**** dd mon yyyy hh:mi:ss:mmmAM],
CONVERT(nvarchar(30), GETDATE(), 131) AS [ Hijri**** dd/mm/yy hh:mi:ss:mmmAM]

Create a CTE list of dates on the fly - SQL

CTE list of dates on the fly:

WITH D AS
(
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
)
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM D;


2000/pre CTE solution here

Create a list of dates on the fly - SQL

I've recently had the requirement to produce a list of the last 30 days on the fly (I didn't have the option to refer to a 'Numbers' table). This was to be used as a 'Command' in Crystal Reports and joined to a database table to produce 'missing dates'.

Using the system table, spt_values, the first stage was to produce a list of numbers:

SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29

This is then extended to produce a list of the last 30 dates.

SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
) AS D

This was with help from both Chris Morris & Kevin McKelvey at http://www.sqlservercentral.com/

CTE solution here



 

DateName function - SQL

SELECT DATENAME(month, GETDATE()) AS 'MonthName';

Column aliases and derived tables in TSQL 2005 - SQL

SELECT YearMonth, COUNT(JobLogID) AS JobCount
FROM
(SELECT JobLogID, CONVERT(VARCHAR(7), JobLogSignOffTimeStamp, 121) AS YearMonth
FROM JobLog WHERE JobLogSignOff ='1') AS J
GROUP BY YearMonth
ORDER BY YearMonth;

Row numbering in TSQL 2005 - SQL

New to SQL Server 2005 is ROW_NUMBER function.

SELECT
JobLog.JobLogID,
Establishment.EstablishmentName,
JobSubType.JobSubTypeDescription,
JobLog.JobLogSignOffTimeStamp,
ROW_NUMBER() OVER(ORDER BY EstablishmentName ) AS rownumNonDeterministic,
ROW_NUMBER() OVER(ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumDeterministic,
ROW_NUMBER() OVER(PARTITION BY EstablishmentName ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp ) AS rownumPartitioning
FROM JobLog
INNER JOIN
Establishment ON JobLog.JobLog_EstablishmentID = Establishment.EstablishmentID  
INNER JOIN
JobSubType ON JobLog.JobLog_JobSubTypeID = JobSubType.JobSubTypeID
WHERE JobLog.JobLogSignOff = '1'
ORDER BY EstablishmentName,JobLog.JobLogSignOffTimeStamp

Useful DBCC commands - SQL

Link to Brad McGehee's Useful DBCC commands

Select All option with multi-valued parameters (Part 2) - SSRS

Following on from my previous post on the shortcomings of the 'Select All' option with multi-valued parameters, another workaround is to assess whether the user actually Selects All. If so, then deal with this in the main query code.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.

The EstabCount parameter is given the following properties:

The below code simply populates the mult-valued parameter list:

Once the two parameters are set up the next stage is to alter the main dataset query. Select the Parameters tab and create an IIF statement that returns a predetermined string , "Select All" if the count of the number of parameters selected equals or is greater than EstabCount parameter value, i.e. if the 'Select All' option is checked in the drop down box.

=iif(Parameters!EstablishmentParameter.Count>=Parameters!EstabCount.Value, "Select All", Parameters!EstablishmentParameter.Value)

Then we need to alter the WHERE clause in the main dataset query:

WHERE (('Select All' IN (@EstablishmentParameter)) OR (View_ActiveEstablishment.EstablishmentName IN (@EstablishmentParameter)))

So, if the user checks 'Select All' in the drop down the IIF statement returns the string 'Select All' and the WHERE clause evaluates to true and returns a full selection thus negating the need for a potentially massive IN clause being used in the query. Likewise, if the user selects multiple parameter values then these are treated as expected. Unfortunately this may still result in a cumbersome IN clause if the user decides to select all but a few of the parameter values!

A quick play with SQL Server Profiler shows the 'Select All' database query before and after the alteration:

Useful date formulas - SQL

This was posted on http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

declare @ThisDate datetime;

set @ThisDate = getdate();

select dateadd(dd, datediff(dd, 0, @ThisDate), 0) -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0) -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0) -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year

Table metadata - SQL

Useful bit of code to get a tables metadata:


SELECT
COLUMN_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME AS Expr1, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME = 'mytable')
ORDER BY ORDINAL_POSITION

Auxiliary Calendar table - SQL

This code is derived from a useful article by Todd Fifield at SQLServerCentral.com and has been modified to include columns based on the ISO8601 date and times standard (here and here).

IF OBJECT_ID('dbo.CalendarDay') IS NOT NULL
DROP TABLE dbo.CalendarDay;
GO

CREATE TABLE CalendarDay
( DayID INT IDENTITY(1, 1)
, DayDate SMALLDATETIME
, WeekNumber INT
, OrdinalDate VARCHAR(8)
, DateWeekNumber VARCHAR(10) 
, DayNumber INT
, NameOfDay VARCHAR(10) 
)
GO
SET NOCOUNT ON;
DECLARE
@Date SMALLDATETIME;
SET DATEFIRST 1; --assuming start of week is monday
SET @Date = '2010-01-01';
WHILE @Date < '2011-01-01'
BEGIN
INSERT INTO CalendarDay
( DayDate, WeekNumber, OrdinalDate, DateWeekNumber, DayNumber, NameOfDay )
SELECT @Date
, (DATEPART(WK, @Date))
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-' + CAST(DATEPART(DY, @Date) as VARCHAR)
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-'
+ 'W' + CAST(DATEPART(WK, @Date) as VARCHAR) + '-'
+ CAST(DATEPART(WEEKDAY, @Date) as VARCHAR)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Sunday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO

SQL Server 2005 System Views

Here's a very useful schema showing SQL Server 2005 System Views:

 

http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en

https://docs.google.com/fileview?id=0B2hXx-EkjlNsMzdhNTc0MWQtMmE1Ny00MWM1LTgxOWItNDA4ZWI2ZTk2Nzlm&hl=en

Select All option with multi-valued parameters - SSRS

A major short coming with multi-valued parameters in SQL Server Reporting Services is that there is no way to disable the 'Select All' option. This may have massive performance issues if 'Select All' is checked and large numbers of records are pulled from the DB. A work-around is to create another parameter and by using the IIF function limit the number of parameters available for selection.
















The first stage is to create a new parameter, in this case, called LimitEstablishmentParameter.















I've decided to limit the number of parameters available for selection using the following code in Default Value for the LimitEstablishmentParameter.



=iif(Parameters!EstablishmentParameter.count<=20,Parameters!EstablishmentParameter.value,"xxx")












Where xxx is a value that will return zero records from the dataset.

Change your dataset to refer to your new 'limiting' multi-valued parameter (LimitEstablishmentParameter in this case).






It's also worth putting a textbox near the top of the report with an expression similar to the following, informing the user of the selection limit:

=iif(Parameters!LimitEstablishmentParameter.Value(0)="xxx","The selection is limited to 20 establishments.",(Join(Parameters!EstablishmentParameter.Value, ", ")))

Display a list of all multivalued parameter values - SSRS

To display a list of all the selected multivalued parameters in a report use the Join function:

=Join(Parameters!Field.Value, ", ")

Multivalued parameter drop down width - SSRS

I was working on a SSRS project where I was using multivalued parameters. This creates a drop down list whereby the user can select the relevant values. Unfortunately in SSRS 2005 (SP3) the width of the dropdown is fixed, which seems to me as a bit of an oversight.

I believe that this may be corrected by altering the HtmlViewer.CSS file or by programmatically fixing the width of the available parameter values.

Neither is ideal.

















I will look into whether this has been rectified in SSRS 2008.

Crystal Reports rebranded

SAP have recently announced that Crystal Reports is to be rebranded as SAP Crystal Reports, hopefully ensuring their long term commitment to the product. Various new products are also in the pipeline. Further details here

Display dataset query in the report - SSRS

To display a dataset query in the report, simply place the following code in a textbox expression:

=datasets!datasetname.commandtext

Group Header continued... - CR

To display 'continued' or similar when a group continues onto a new page, firstly ensure Repeat Group Header is checked in Group Expert then add the following formula to the Group Header:

IF InRepeatedGroupHeader THEN {?School} & " continued"
ELSE {?School}

Show Database name - SQL

To show the database name:


SELECT DB_NAME() AS DataBaseName


This also proves useful in Crystal Report development where 'test' and 'live' databases in use. Simply add the above code to a Command in Database Expert and show the DatabaseName field in the report header.

Auxiliary CTE of Months - SQL

WITH CTE_Months
AS
(
SELECT Mths ='January ' UNION ALL
SELECT Mths ='February ' UNION ALL
SELECT Mths ='March ' UNION ALL
SELECT Mths ='April ' UNION ALL
SELECT Mths ='May ' UNION ALL
SELECT Mths ='June ' UNION ALL
SELECT Mths ='July ' UNION ALL
SELECT Mths ='August ' UNION ALL
SELECT Mths ='September ' UNION ALL
SELECT Mths ='October ' UNION ALL
SELECT Mths ='November ' UNION ALL
SELECT Mths ='December '
)
SELECT Mths FROM CTE_Months

Auxiliary Months of Year Table - SQL

IF OBJECT_ID('dbo.CalendarMonth') IS NOT NULL
DROP TABLE dbo.CalendarMonth;
GO

CREATE TABLE dbo.CalendarMonth
(
mon nvarchar(10) NOT NULL
);

INSERT INTO dbo.CalendarMonth(mon)
SELECT 'January ' UNION ALL
SELECT 'February ' UNION ALL
SELECT 'March ' UNION ALL
SELECT 'April ' UNION ALL
SELECT 'May ' UNION ALL
SELECT 'June ' UNION ALL
SELECT 'July ' UNION ALL
SELECT 'August ' UNION ALL
SELECT 'September ' UNION ALL
SELECT 'October ' UNION ALL
SELECT 'November ' UNION ALL
SELECT 'December '

If, for example you are trying to provide an output of sales data for every month and the sale table doesn't have records for all 12 months, by creating an OUTER JOIN to the Calendar table, each month is returned.

IF OBJECT_ID('dbo.yearsales') IS NOT NULL
DROP TABLE dbo.yearsales;
GO
CREATE TABLE dbo.yearsales
(
sales int NOT NULL,saledate smalldatetime NOT NULL
)


INSERT INTO dbo.yearsales (sales, saledate)
SELECT '4','Jan 1 2009 12:00AM' UNION ALL
SELECT '5','Feb 1 2009 12:00AM' UNION ALL
SELECT '1','Apr 3 2009 12:00AM' UNION ALL
SELECT '11','May 5 2009 12:00AM' UNION ALL
SELECT '1','Jun 15 2009 12:00AM' UNION ALL
SELECT '4','Aug 5 2009 12:00AM' UNION ALL
SELECT '3','Oct 12 2009 12:00AM' UNION ALL
SELECT '17','Nov 2 2009 12:00AM' UNION ALL
SELECT '19','Dec 14 2009 12:00AM'

SELECT yearsales.sales, yearsales.saledate, DATENAME(month, yearsales.saledate) AS salesmonthname, CalendarMonth.mon
FROM yearsales RIGHT OUTER JOINCalendarMonth ON DATENAME(month, yearsales.saledate) = CalendarMonth.mon

Datetime types in SQL - SQL

You cannot store only the date or time portion.

If only the date is specified, 00:00:00.000 is stored in the time portion.

If only the time is specified, 1900-01-01 is stored in the date portion.

For example,

SELECT CAST ('20100526' as datetime)

SELECT CAST ('12:47:59:009' as datetime)

SOS! - Please help! - Report Manager times out - ***CULPRIT - AVG antivirus LinkScanner***

Any SQL Server installation/admin experts out there?


I have a development environment on my home PC running:

Windows 7 Ultimate 64-bit

(Windows Ultimate was installed as a 'clean copy' via an upgrade. The previous OS, Vista had SQL Server and all components including Reporting Services running successfully)

IIS spec

Prior to installing SQL Server I ensured the following IIS windows features were enabled:

-Internet Information Services
- -Web Management Tools
- - -IIS 6 Management Compatibility
- - - -IIS 6 Scripting Tools
- - - -IIS 6 WMI Compatibility
- - - -IIS Metabase and IIS 6 configuration compatibility
- - -IIS Management Console
- -World Wide Web Services
- - -Application Development Features
- - - -.NET Extensibility
- - - -ASP.NET
- - - -ISAPI Extensions
- - - -ISAPI Filters
- - -Common Http Features
- - - -Default Document
- - - -Directory Browsing
- - - -HTTP Errors
- - - -HTTP Redirection
- - - -Static Content
- - -Health and Diagnostics
- - - -HTTP Logging
- - - -Request Monitor
- - -Security
- - - -Request Filtering
- - - -Windows Authentication




















Navigating to http://mycomputername successfully displays the IIS 7.0 welcome screen

SQL Server spec
All components of SQL Server 2005 Developer Edition 64-bit were then installed successfully, followed by SP3.
I also installed SP1 for Visual Studio 2005 followed by SP1 update.

A default configuration of SSRS was also implemented.

Each service is running under the following:
SQL Server - LocalSystem
SQL Server Agent - LocalSystem
SQL Server Analysis Services - LocalSystem
SQL Server Browser - LocalSystem
SQL Server FullText Search - LocalSystem
SQL Server Integration Services - NT Authority\NetworkService
SQL Server Reporting Services - NT Authority\NetworkService

Shared Memory & TCP/IP enabled







Reporting Services Configuration

All relevant parts are correctly configured including the following:

Windows Service Identity:
Sevice Account - NT Authority\NetworkService
Built-in Account - Network Service

Web Service Identity:
ASP .NET Service Account - NT Authority\NetworkService
Report Service & Report Manager run under ReportServer application Pool

IIS Manager

ReportServer Application Pool runs under the Classic Managed Pipeline mode and NetworkService Identity.

Internet Explorer settings
Running IE8

Local intranet settings:
Include all local (intranet) sites....
Include all sites that bypass....
Include all network paths....

Set my login which has admin rights Content Manager in Report Manager

PROBLEM

I can view and render reports via reportserver
I can view reports but CANNOT render reports in report manager. I get the 'Report is being generated' message and it eventually times out with the message 'The underlying connection was closed: An unexpected error occurred on a receive.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host'

I've tried with various reports, some that pull data from my local db, some that have parameters and some that simply display text. NONE render in report manager, although the report paramaters are successfully retrieved from the local db.
I tested this using Mozilla firefox and although the layout is poor the reports render successfully.

I'm at a complete loss!
I've tried reinstalling the OS and SQL Server.
Tried setting up a new application pool in IIS using my login account and Classic managed pipeline mode.
In Reporting Services config I've tried using my login account for Windows Service Identity and ASP .NET Service Account.
All to no avail.


NIC & Wireless settings
This may/may not have any bearing on matters, I'm not sure!
When in the process of upgrading from Vista to Windows 7, it mentioned that the DELL Wireless WLAN Card Utility was not compatible with Windows 7 and I removed it.

Also, I was directed to this post (many thanks Jerry Nee) http://blogs.msdn.com/sql_protocols/archive/2008/04/08/understanding-connection-forcibly-closed-by-remote-host-errors-caused-by-toe-chimney.aspx regarding network adapters. Not sure if this is relevant seeing as I'm running a development system all on one PC. But, Im not sure! Please advise!


I hope someone can help me rectify this issue as I've been at it now for a good ten days!

I'm willing to flatten the whole lot and start again if recommended, if someone can state the best setup required for a 'stand-alone' development system such as mine.

Links to my forum posts are at:
http://www.sqlservercentral.com/Forums/Topic917776-162-1.aspx
http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/e976a8c9-a8a0-4223-8aec-36a3899a8e77
http://forums.iis.net/t/1167401.aspx



Many thanks,
Dom Horton

==============================
UPDATE
==============================


I've managed to solve this problem after many frustrating hours!
I'm running the free edition of AVG 9.0 and by disabling the 'LinkScanner' component I've got things to work. Funny thing was that I was sure I'd tried disabling the anti-virus software as part of my process of elimination!


==============================
Further info
==============================


http://forums.avg.com/gb-en/avg-forums?sec=thread&act=show&id=151271

Nth largest value - SSRS

I recently had a query regarding replicating Crystal Reports nth largest functionality in Reporting Services (hope this helps, Andy!).

For example, In Crystal Reports, its simply a case of using Insert>>Summary

I believe the most efficient way to go about is to use a subquery in T-SQL and incorporate into a dataset.

Using the AdventureWorks db, the first query uses the ROW_NUMBER function to sequentially order rows, purely for this examples sake. (ROW_NUMBER is only available in SQL Server 2005 onwards)

SELECT SalesPersonID, SalesYTD, ROW_NUMBER() OVER(ORDER BY SalesYTD desc) as rownum
FROM AdventureWorks.Sales.SalesPerson

The second query, using a subquery, firstly selects the top 3 SalesYTD values and then selects the smallest of these 3, the third largest of all values.

SELECT TOP 1 SalesPersonID, SalesYTD FROM
(SELECT TOP 3 SalesPersonID, SalesYTD FROM AdventureWorks.Sales.SalesPerson ORDER BY SalesYTD desc) as Sales
ORDER BY SalesYTD asc


By simply replacing the 3 in the subquery by n, the nth largest value can be obtained.

Select All option - SSRS

It's often necessary to have a 'select all' option when using parameters in Reporting Services.
Here's a way to do it:

For my report I've created three datasets.

1) The first dataset, named 'Package' creates the parameter values, using UNION command to adjoin a '** SELECT ALL **' value to those obtained from a database.

SELECT '** SELECT ALL **' as PACKAGE

UNION

SELECT PACKAGE.PACKAGE as PACKAGE

FROM PACKAGE

order by PACKAGE.PACKAGE

2) The second dataset, named 'SelectAll' is used to populate the Default Value for the parameter.

SELECT '** SELECT ALL **' as PACKAGE

These two datasets are then used to populate the Report Parameters window as below:

3) The third parameter, in this case named 'ChangedRequest', provides the select logic.

SELECT .....

FROM .....

WHERE PACKAGE.PACKAGE = @Package or '** SELECT ALL **' in (@Package)

order by PACKAGE.PACKAGE

Which results in something like this:

Green bar matrix report - SSRS

I was recently asked by an anonymous blog poster (if reading please identify yourself!) how to go about creating a 'green-bar' style cross-tab/matrix report in both Crystal and SSRS.

Here's how to do it in SSRS (Cystal to follow at a later date):


1) Create an inner row grouping, in this case named 'Count' and given a grouping expression of =1















2) for the cell 'Count', set the property Value =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")

3) for the cell 'Count', set the property BackgroundColor =value















4) for the matrix data cell, named 'STATUS', set the property BackgroundColor =ReportItems!count.Value














5) for the outer row grouping, named 'TITLE, set the property BackgroundColor =iif(RunningValue(Fields!REFERENCE.Value,CountDistinct,Nothing) Mod 2, "PowderBlue", "White")















6) To hide the inner row grouping cell, 'Count', set
the following properties:
-set width to a minimum
-set BorderStyle Left =None
-set Color =value
-set CanGrow =False
set FontSize =1pt

for the outer row grouping cell:
-set BorderStyle Right =None

Useful Character code chart

Here's a link to a useful character code chart:


http://tlt.its.psu.edu/suggestions/international/bylanguage/mathchart.html


and to get the Windows Character Map Utility:

Start>>Programs>>Accessories>>System Tools

and simply copy and paste the relevant character or obtain the unicode number from the keystoke value, where applicable, or convert the hex to decimal.

Hexadecimal to decimal converter:

http://easycalculation.com/hex-converter.php

Array example - Crystal

Local Numbervar counter := 1;
Local Stringvar Array taskdates;
Local Datetimevar dates := CurrentDate;
Local Datetimevar finishdate := DateAdd("yyyy",5,CurrentDate);
Local Stringvar returnstring;

Do
(
//set array size to counter, initally 1, preserving previous size and values
Redim Preserve taskdates[counter];
//assign dates value, initially CurrentDate to array element numbered counter, initially 1
taskdates[counter] := ToText(dates, "dd-MMM-yyyy");
//increment date variable by 1 year
dates := DateAdd("yyyy",1,dates);
//increment counter by 1
counter := counter+1;
)
//continue do loop while date variable is less than or equal to finishdate
While dates <= finishdate ;

returnstring := Join(taskdates, Chr(13))

Remove Carriage Return, Line Feed and Tab - SQL

Remove Carriage Return, Line Feed and Tab characters in T-SQL.

REPLACE(REPLACE(REPLACE(Field, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

Check whether a string was all in uppercase - CR

I needed a quick way to check whether a string was all uppercase.
Using the InStr function:

InStr ({fieldname},(UpperCase ({fieldname})) ,0 )

The syntax being:
InStr([start,]string1,string2[,compare])
start- an optional starting position
string1,2 - strings to compare
compare - either 0 or 1, 0 being a binary comparison, 1 being textual.

http://msdn.microsoft.com/en-us/library/wybb344c(VS.85).aspx

Create Button Style - SSRS

To create a reasonable 'button style' for a text box:

BackgroundColor = WhiteSmoke

BorderColor = WhiteSmoke

BorderStyle = Outset

BorderWidth = 2pt

Color = Black

SSRS URL Parameters - SSRS

To pass a report parameter through to another report via a new browser:

On the Navigation tab for the report objects properties add the following javascript command to the Jump to URL expression box:

="javascript:void(window.open('http://SERVER NAME/ReportServer/Pages/ReportViewer.aspx?%FOLDER NAME%REPORT NAME%&rs:Command=Render&PARAMETER NAME="+Fields!Pass value.Value+"'))"

For example:

="javascript:void(window.open('http://dom-pc/ReportServer/Pages/ReportViewer.aspx?%2fCAD+GIS+DB+reports%2fJobs+By+Establishment&rs:Command=Render&EstablishmentParameter="+Fields!SITE_NAME.Value+"'))"

Colour graduation function - SSRS

I needed to highlight date information dependant on it's proximity to todays date. Using a colour graduation, greater emphasis is placed on dates near todays and those in the future, while less emphasis is placed on historical dates.

I created a custom function:

Public Function MonthColourGraduation(ByVal ActualValue As integer, ByVal NeutralColour As String) As String

Select ActualValue

Case >-1

Return "#FFD2D2"

Case >-2

Return "#FFD5D5"

Case >-3

Return "#FFD9D9"

Case >-6

Return "#FFDDDD"

Case >-9

Return "#FFE1E1"

Case >-12

Return "#FFE4E4"

Case >-15

Return "#FFE8E8"

Case >-18

Return "#FFECEC"

Case >-21

Return "#FFF0F0"

Case >-24

Return "#FFF3F3"

Case >-30

Return "#FFF7F7"

Case >- 36

Return "#FFFBFB"

Case Else

Return NeutralColour

End Select

End Function

and for the table rows background colour:

=Code.MonthColourGraduation((DateDiff("m", now(),Fields!LastEndTime.Value)), "White")

To get the hex colour codes I used:

http://www.herethere.net/~samson/php/color_gradient/

CTE maximum recursion error- SQL

I was running a CTE an got the following Error message:

'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.'

The statement would undertake over 100 recursions hence the message. To rectify, I added the following query hint:


OPTION (MAXRECURSION 500);

Overriding the default of 100 for this particular statement.

Syntax for checking objects existence - SQL

SET NOCOUNT ON;
USE database;
GO
IF OBJECT_ID('dbo.table) IS NOT NULL
DROP TABLE dbo.table
GO
CREATE TABLE .....

Example Select Case to get time period bandings - CR

Example Select Case to get time period bandings:

select ({fldName}-currentdate)
case upfrom_ 365 : "12 months"
case 271 to_ 365 : "9 months"
case 181 to_ 271 : "6 months"
case 91 to_ 181 : "3 months"
case 31 to_ 91 : "1 month"
case 0 to_ 31 : "IMMINENT"
case -90 to_ 0 : "OVERDUE - by up to 3 Months"
case -180 to_ -90 : "OVERDUE - by up to 6 Months"
case -365 to_ -180 : "OVERDUE - by up to 12 Months"
case -545 to_ -365 : "OVERDUE - by up to 18 Months"
case -730 to_ -545 : "OVERDUE - by up to 24 Months"
default: "CHECK STATUS"