07.21.08

SQL Server Reporting Services Calendar Control for Date Parameter (SSAS OLAP)

Posted in Programming at by chenty

When building a report in SQL Server Reporting Services, we often use date as parameters. When you create dataset from SQL Server Analysis Services source, by default the date will be treated as string and you will have a drop down list of date. In SQL Server 2005, Reporting Services introduced “calendar control” to allow user select date from calendar (similar to calendar control in ASP.NET). To activate this feature, you simply set the parameter data type to “datetime” instead of “string”, and a nice calendar control will show when you run the report.

So is it the end of story? Definitely NO!

By doing that, a lot of report designers find the report would not be rendered according to the date you select, or most time, it throws an error. For example:


An error occured during local processing.
An error has occured during report processiong.
Query execution failed for data set 'TestCalendarDataSet'.
Query(47,14) the restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated.


So what went wrong? In short, the data returned by the calendar control is not ready to be used in SQL or MDX query. For example, the MDX code with your date parameter looks like this:

STRTOMEMBER(@Date, CONSTRAINED)

and the @Date value returned by the control (Parameters!Date.Value) is something like “2008-07-01″. So the piece of MDX will be

STRTOMEMBER("2008-07-01", CONSTRAINED)

As you can see, “2008-07-01″ is not a valid member for MDX. What we really expect should be something like this:

STRTOMEMBER([Date].[Date].&["2008-07-01T00:00:00"], CONSTRAINED)

Therefore, in order to make the date parameter work in the MDX code of your dataset, we need to manually convert the parameter value. Here are the steps how to convert the parameter value:
1. Go to “Data” tab in Report Designer
2. Choose the dataset that populates the report, and click “…” button beside it
3. Now, the “Dataset” dialog window shows, and you click “Parameters” tab in the dialog and you should see a list of parameters you choose.
4. Assume the calendar control parameter is @Date, in “Parameters” tab, you will have “Date” in the “Name” column, and “=Parameters!FromDateDate.Value” in “Value” column
5. Now, change the value column to: =”[Date].[Date].&[” + Format(CDate(Parameters!FromDateDate.Value), “s”) + “]”
6. Click “Ok” and close all windows
7. Click “Preview” to test the result

The main job is done in Step 5. What I did is to convert the parameter from a simple date string to a MDX member. CDate(Parameters!FromDateDate.Value), “s”) will convert the date string to the format like “2008-07-01T00:00:00″ (this format is standard “sortable” date format). If you named date dimension other than “Date”, you need to change “[Date].[Date]” part accordingly.

Permalink Comments

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.

Permalink Comments

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.

Permalink Comments

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

REVERSE(SUBSTRING(REVERSE(REVERSE(SUBSTRING(REVERSE(HTTPRequestURIStem),0,
CHARINDEX('/',REVERSE(HTTPRequestURIStem))))),0,
CHARINDEX('.',REVERSE(REVERSE(SUBSTRING(REVERSE(HTTPRequestURIStem),0,
CHARINDEX('/',REVERSE(HTTPRequestURIStem))))))))

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

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

Permalink Comments

01.12.08

Windows Live Messenger won’t open? Here is the solution! Sorta..

Posted in Programming at by chenty

One of my friend called me two months ago because he could not open his MSN Messenger. When he double click the MSN icon, it was simply no response. However, MSN process was shown in Task Manager. I tried to reinstall the program but the problem persisted. A couple of days later my friend called me and told me his MSN suddenly worked again!

Yesterday, I got hit by the exact same problem. No matter what I tried, my Windows Live Messenger would not open. After googling around, I found this post:
http://forums.microsoft.com/msdn/showpost.aspx?postid=1553517&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

It seems that Windows Live Messenger has some conflicts with D-Link Router, so the router blocked the Messenger, and the Messenger will stall and try to connect the server forever. If you disconnect the Internet, the program will open immediately.

Solution: Reset your router! Well, you do NOT have to reset the factory default and lose all your settings. A simple reboot or unplug the power for 15 seconds will do the trick. People report that the problem does come back once in a while, alas! Fortunately, “Once in a while” means “once in a few months” in this case.

Permalink 1 Comment

10.15.07

Install PHP + MySQL on Windows 2003 (IIS + PHP + MySQL)

Posted in Programming at by chenty

1. Why Windows Server 2003 and IIS?
The optimal environment for running PHP + MySQL application is a Linux server running Apache Web Server. It requires minimal setup and assures the best performance and reliability. However, what if .NET applications are required to run on the same server? I guess the only option is to install PHP + MySQL on a windows server (Why not .NET on Linux? No, the other way has been tried, but it did not work well).

On a windows server, Apache Web Server is still the best choice to run PHP + MySQL combo, but unfortunately Apache and IIS cannot share the same port, which means only one of them will run on port 80 (even you bind 100 IP addresses on the same server). Therefore, if you want to run both PHP and .NET applications on the same server, your best bet will be IIS + PHP + MySQL.

2. Sample environment
(1) Windows Server 2003 SP2 + IIS 6.0
(2) PHP 5.2.4
(3) MySQL 5.1.22

3. Download Files
(1) PHP: http://www.php.net/downloads.php
There are several PHP packages for Windows. Make sure you download “Zip Package”. DO NOT DOWNLOAD “installer”! Too many people have problems with “installer” version and that package messes up too much.

(2) MySQL: http://dev.mysql.com/downloads/
As the time I wrote this guide, MySQL 6.0 is out as a public preview. I did not use it because there is no manual. I recommend to use a stable release for production servers.

4. Install MySQL
(1) Most tutorials online installs as the order: MySQL -> Apache -> PHP because you can test MySQL alone, and when you install PHP you can directly modify php.ini for MySQL (otherwise, you have to modify php.ini again after you install MySQL).

(2) I downloaded installer version of MySQL. Just run setup.exe and it will do all the work for you. I am lazy to write detail installation process because I have not had any problem. Everything was straight forward. If you had run in a problem, check official documentation:
http://dev.mysql.com/doc/refman/5.1/en/installing.html

(3) Test MySQL server in command-line:
a) Start -> Run -> cmd
b) mysql -u root -p
c) input your password
d) if it connects to the server successfully, you are in good shape. type “show databases;” to display all default databases. If you see the list, you are ready to move on.

5. Install PHP
(1) Unzip all the files to “C:\php” (I unzipped to “C:\Program Files\PHP” but I have seen people having problem with file path that contains “space”, so most tutorial uses “C:\php”.

(2) create a folder you want to run PHP applications, e.g. C:\inetpub\phproot. Most tutorial uses “wwwroot” which is the default IIS web directory, but I think it makes sense to use a different directory since I don’t want my application mess up with all asp files.

(3) Modify php.ini
php.ini is the most important configuration file. I will revisit this file later in this guide when I talk about setting up email and file upload. For now, we need to modify:
a) “doc_root”: set this as “c:\inetpub\phproot”;
b) “extension_dir”: set this as “c:\php\ext”;
c) uncomment lines with mysql extensions (e.g. remove “;” before “extension=php_mysql.dll”);
d) change default exection timeout, e.g. “max_execution_time = 3600″ (Note: this is very important! I have rarely seen tutorial mentioning this parameter, but the default is 30 seconds. If you did not increase this number you will get way too many 500 error and take you forever to figure out “what’s wrong with my code?” (there is nothing wrong with your code, but the server will return an error after 30 seconds).

(4) Setup IIS (using “IIS Services Manager”):
a) expand “Web Service Extensions”;
b) Action -> add a new service extension;
c) enter “PHP” as name and click “Add…”, and select “c:\php\php5isapi.dll”. (Note: many earlier tutorials recommend to use CGI mode because ISAPI is not stable for PHP. I believe things have been improved dramatically now and most new tutorials prefer ISAPI to CGI);
d) Create a new website. Set “c:\inetpub\phproot” as home directory;
e) In the same “Home Directory” tab, click “Configuration…”;
f) In the new dialog window, click “Add…”;
g) Set “c:\php\php5isapi.dll” as executable and “.php” as extension. Make sure “Script engine” option is checked.
h) Now restart your IIS Server (open “Services” in Control Panel, and restart “World Wide Web Publishing Service”)

(5) Run a test script for php setting:
Create a new file in “c:\inetpub\phproot” and name it “test.php”. Open it with notepad and type:

< ?php
phpinfo();
?>

Run this script, e.g. type “http://localhost/test.php” in a web browser. If you see the script display a whole page of server configuration like this one, that means your server is supporting PHP now. Scroll down a little bit in the page, and make sure it has sections for “mysql” (In the sample I provided above, it has a section for mysql).

Note: If you did not get the expected result unfortunately, there are many good posts on troubleshooting. Troubleshooting is beyond the scope of my guide.

============================================

Now you got a good server running PHP + MySQL under IIS, and most tutorial will stop here. However, it is far from over for a production server! In my case, the application I run requires:
(1) backward compatibility of old instruction sepraration;
(2) sending email using mail();
(3) users can upload files.

If you just follow my guide above, non of these features will function properly!

(1) Proper instruction separation for PHP should use “< ?php" as open tag and "?>” as close tag. However, many legacy code has other format, e.g. “< ?" to open and "?>” to close. The best practice is to modify the code for standard format! Nonetheless, PHP can still support “< ?" opening by set "short_open_tag=1" in php.ini.

(2) PHP supports file upload from client to server, but PHP file upload feature is not enabled under IIS by default.

You need setup two folders on your server. One is the folder where you want the upload files to store, and the other one is the folder for temporary upload files (PHP handles upload in two separate steps: it first uploads the file to the temp folder, and then use "move_uploaded_file()" function to move from temp folder to final destination. For a good example, check official doc http://ca3.php.net/features.file-upload).

Here is an example on setup procedures:
a) create two folders "c:\inetpub\phproot\uploads" and "c:\inetpub\phproot\upload_temp";
b) In "IIS Service Manager", right click on the folder and click "Properties". In "Directory" tab, make sure you enable "write" permission for both folders;
c) Open php.ini, uncomment and edit this line: upload_tmp_dir = "C:\inetpub\phproot\upload_temp\";
d) restart the service and file upload feature should be enabled.

(3) If you google "php mail IIS", you find many many posts from people who have trouble to make mail() function work. I have talked about this issue in another guide I wrote.

Permalink 1 Comment

10.09.07

How to make PHP mail () function work on Windows

Posted in Programming at by chenty

mail() is a useful and powerful function in PHP, but many developers got problems to make mail() work properly on Windows. Many these problems were related to headers and different line feed formats between Windows and Linux. However, this post does not address these problems. The problem I encountered last week was how to make mail() function to send even the simplest mail on Windows.

The server setup:
Windows Server 2003 Standard Edition (SP2)
Apache 2.2
PHP 5.2.4
MySQL 5.1

run a test script with phpinfo() to confirm the server settings. There were several lines that relate to mail():
smtp = localhost
smtp_port = 25
sendmail_from = yourname@yourdomain.com
sendmail_path =

Windows and Linux handle mail() differently. Linux uses “sendmail” whereas Windows employs SMTP. You can refer to PHP manual for more information:
http://ca3.php.net/function.mail

In this post, I will only cover Windows. Windows Server 2003 comes with IIS 6.0, which supports SMTP. If you are a .NET programmer, you will find mail features working out of box. Unfortunately, this was not the case for PHP. You can try to run the following script to check if mail() works:

if(mail('test@test.com','test subject','test message'))
{
  echo('ok');
}
else
{
  echo('not ok');
}

If the script returns ‘not ok’ when you run the code, that means mail() function could not send the mail through SMTP. So what was the problem? A good way to demonstrate the problem is run an email client (e.g. Outlook Express) and setup a new account using “localhost” as SMTP. When you try to send a new message, you will receive an error similar to this one:

Server Response: ‘550 5.7.1 Unable to relay for yourname@yourdomain.com’, Port: 25, Secure(SSL): No, Server Error: 5500

This is the reason your PHP script cannot send the mail: by default, IIS restricts all SMTP relay! In order to solve the problems, you need to grant access to your server IP:
1. Open “IIS Manager”;
2. Right click “Default SMTP Virtual Server” and select “Properties”;
3. In “Access” Tab, click “Relay…”;
4. Check “only the list below” and click “Add” to add the server IP.

Now, if you open Outlook Express and create a new message, the mail will be sent out properly. Now, run the test script I wrote above, the script should return ‘ok’ and you should receive the mail in your mailbox amazingly!

Permalink Comments

08.31.07

Silicon Valley interview questions?

Posted in Programming at by chenty

This is not the first time I read news articles about tough questions interviewees were asked during an interview for top companies such as Google. My favorite one is “why are the sewer lids round in shape instead of square”?

I have always questioned about the effectiveness of those questions, especially from my “bad” experience. I once attended an interview from Microsoft, and the interview went very well at the beginning. Suddenly, I was asked if I were the product manager, how could I design a remote control for senior people.

My answer was: if I were the manager I would like to conduct a full survey of seniors first, and ask for what difficulties they have when they use a remote control. The manager was very disappointed with my answer, and he started lecturing me about what suggestions I should have give. He named a long list of features he think it would be “cool” for seniors. I did not want to argue during the interview, but all I had in my mind was: if I were the manager, I need to make sure the product addressing the need not just to be “cool”. The real question is: will seniors really spend hard earned dollars for a universal remote control? I believe simplicity is over functionality in this perticular case. Apparently my performance didn’t impress the manager and he cut short with my interview immediately. When I got home, I googled the interviewer’s name and surprisingly learned that he was actually the team lead for Outlook 97 and Outlook 2002. Any thoughts of that?

Nonetheless, I still like to read and solve these interesting interview questions as brain teasers, but I deeply doubt the usefulness in an interview since if you cannot press the “hot button” you will be judged unfairly. On one hand it helps to pick up smart thinkers, on the other hand it filtered out deep thinker.

The question I read today is “how much money will you make if you clean all windows in Seattle”? The author has suggested the following answer:

assume 10000 buildings in Seattle; 600 windows per building; 5 mins per window; $20 per hour wage; the income will be $10 million

I think this answer is kinda boring, so I tried some other approaches:

(1) If I were granted a contract to clean all windows in Seattle, I would become a monopoly. So I can charge whatever price and maximize the economic rent as long as consumers can afford, which means I can make way more than $10 mil! More importantly, if I could subcontracted the work, I could make money sooner.

(2) Think about time value of money! Sure, you can finish all windows and make $10 mil. However, to finish all windows it took more than 57 years if I did it non-stop, 24X7. If I worked as normal, I could not even finish the work in my life. Therefore, this income stream becomes a “perpetuity”. Assume I worked 8 hours a day, $20 hours per hour, 200 days per year, my annual income would be $36,000 and assume my expected rate of return is 5%, the present value of my total income would be $36000/0.05 = $720,000, which is way less than $10 mil.

Things can get even more complicated if you combine the idea (1) and idea (2). Happy brain teasing!

Permalink Comments

08.20.07

Text file splitter by number of lines

Posted in Programming at by chenty

I have not updated my blog for almost seven months. The main reason is that I am making some big move! I am getting into finance! I have been preparing my Charted Financial Analyst exams for months.

Anyway, I need to write about CFA in another post, so I’d better talk about text file splitter. I have a very simple need, split a big text file that is over 1GB into small pieces so I can only them with text editors. My first take is to download some freeware, but I was out of luck, and people charge $10-$30 USD for such a simple task! Jeez. I have found some free chinese file splitter, but they all crash upon opening the file. The reason they crush because they all load the whole text file for preview first, which does not make any sense imho.

I have found some source code online but most of them advertised as “untested”, so I decided not to read them. After spending hours of researching and having tried more than 20 software, I decided to write my own.

You can download the app here (no need to install, just run it as long as your computer has .NET 2.0 installed):
Text File Splitter By Number of Lines (binary)

You can download my C# source code (VS2005 sln) at:
Text File Splitter By Number of Lines (source code)
(I was in a rush, so I mistakenly named my app as “text splitters”. Please forgive my grammatic mistake.)

I have not implemented a good checking mechanism such as using “try…catch…” because I only have one file to split. The speed is not bad: I have splitted a large text file (250MB) into 302 files (each has 10,000 lines) in just one minute.

My code is absolutely free (well, it only takes me less than an hour to create this, so I don’t think I deserve $10).

If this app works for you, please link to my app on your blog so more people can use it without paying (saddly, those who charge for this have very good google ranking) .

Permalink Comments

01.23.07

Installing slmodem driver on Fedora Core 6

Posted in Programming at by chenty

A legacy program we have requires to use a modem to dial-up. The modem is Diamond SupraMax LE internal PCI modem, and the oprating system is Fedora Core 6. Because the modem is using a SmartLink chip, my initial try was to build slmodem downloaded from:

http://linmodems.technion.ac.il/packages/smartlink/slmodem-2.9.11-20061021.tar.gz

Note: For newest Smartlink linmodem driver, you can go to: http://linmodems.technion.ac.il/packages/smartlink/

Unfortuantely, when I was trying to build the package (”make”), it returns an error about unknown “config.h” file in the kernel. After searching around, I found a slmodem-alsa RPM (search slmodem from rpmfind.net). There is a rpm package for Fedora Core 6 and I have no problem installing the package. However, for some reason the package does not work. When I try to start the daemon, it gives me an error. After a whole afternoon’s trial and error, I almost gave up and was ready to try another modem. Then I find this article:

http://www20.brinkster.com/olivares/slmodemd-setup-1.html

This article addresses the problem with missing config.h, and it guides you step by step to modify Makefile and build the slmodem-2.9.11 source code.

The brief steps are:
1. download 2 files from here. You will need slmodem-2.9.11-XXXX.tar.gz (XXXX is the latest date) and ungrab-winmodem.tar.gz
2. extract both tarballs
3. comment out the lines with config.h in /slmodem-2.9.11-20061021/drivers/amrmo_init.c and /ungrab-winmodem/ungrab-winmodem.c
4. add as a single line to /etc/modprobe.conf:
install slamr modprobe –ignore-install ungrab-winmodem ; modprobe –ignore-install slamr; test -e /dev/slamr0 || (/bin/mknod -m 660 /dev/slamr0 c 242 0 2>/dev/null && chgrp uucp /dev/slamr0)
5. copy /scripts/slmodemd from source code folder to /etc/rc.d/init.d/, and “chmod +x slmodemd”
6. modprobe ungrab-winmodem and slamr in slmodemd file
trick: search for “start()” and add these two lines below right after “start() {”
modprobe ungrab-winmodem
modprobe slamr
7. do chkconfig (seriously, I am not a Linux person, and I am not sure what’s this for, but it makes my build successful)
[root@localhost init.d]# chkconfig slmodemd –add
[root@localhost init.d]# chkconfig slmodemd on
[root@localhost init.d]# cd ..
[root@localhost rc.d]# for i in 0 1 2 3 4 5 6
> do
> ls rc$i.d/*slmodemd*
> done
rc0.d/K10slmodemd
rc1.d/K10slmodemd
rc2.d/S90slmodemd
rc3.d/S90slmodemd
rc4.d/S90slmodemd
rc5.d/S90slmodemd
rc6.d/K10slmodemd
[root@localhost rc.d]#

[root@localhost rc.d]# chkconfig slmodemd –list
slmodemd 0:off 1:off 2:on 3:on 4:on
5:on 6:off
[root@localhost rc.d]#
8. now you can “make” and “make install” for both packages.

The instruction I listed is just a brief ones, and I really recommend you check the original post and follow their instrustions. I take no credit for their good work at all! I need to thank them for make my modem works.

Permalink 2 Comments