06.19.08

SQL Server Reporting Services Chart Label Interval

Posted in Programming at by chenty

When you generate a chart using date dimension for x-axis, by default, it will try to show every date as label, which make the label unreadable if you have hundreds of date. This problem can be resolved by setting “interval” in “Major gridlines”. For example, if you put “5″ in interval, it will only show one label every 5 days.

However, the problem may persist if the date range are flexible. If you have a report period varies from 1 days to 10 years, a set number in interval may not fit all circumstances. A solution is to set a fix number of labels shown (flexible interval) instead of fix interval.

Put an expression like this one as interval: =Floor(Count(Fields!Date.Value)/5)

With this expression as interval, the chart will show only 6 labels in x-axis regardless of the date range.

Show the Date with null value in reports (SQL Server Reporting Services)

Posted in Programming at by chenty

When designing dataset in Report Designer, null value is ignored by default because the corresponding MDX query uses “NON EMPTY” keyword. This raises a problem in reporting when Date is a dimension because we want to show every date in the range even there is no item or value for the date.

This can be easily done by removing “NON EMPTY” keyword in MDX queries. However, the value for these dates will be (null) which may not be the desirable output. For example, we have a field “Order Quantity”, and we want to report daily order quantities. If we have quantity of 10,0,20 in three consecutive days, because it is null instead of 0, when you chart the quantity there will be a straight line from 10 to 20 directly, instead of two segments (10 to 0 and then 0 to 20). Therefore, we need to assign some default value (e.g. “0″) to . This can be done by using “IIf” and “IsNothing” functions:

By removing “NON EMPTY” keyword in MDX, we may have some days with (null) in “Fields!OrderQuantity.Value”, now we need to assign default value to 0. This is the expression:

=IIf(IsNothing(Fields!OrderQuantity.Value),0,Fields!OrderQuantity.Value)

Now the field will have value 0 if there is nothing for the date.

06.13.08

Extract file name extension from file path using T-SQL

Posted in Programming at by chenty

HTTPRequestURIStem is the column contains file path, e.g. /webpage/default.aspx

SQL [Show Plain Code]:
  1. REVERSE(SUBSTRING(REVERSE(REVERSE(SUBSTRING(REVERSE(HTTPRequestURIStem),0,
  2. CHARINDEX(‘/’,REVERSE(HTTPRequestURIStem))))),0,
  3. CHARINDEX(‘.’,REVERSE(REVERSE(SUBSTRING(REVERSE(HTTPRequestURIStem),0,
  4. CHARINDEX(‘/’,REVERSE(HTTPRequestURIStem))))))))

This function will return “aspx” for “/webpage/default.aspx”.

NOTICE: I put the code in seperate lines for readablity.