Manual Cross Tab Dynamic Crystal Report

Lately I’m been involve in a lot of crystal reports and one of them is a cross-tab dynamic report which will grow vertically and horizontally. There are a lot of ways to do it. And I think the most easiest way is to use the cross-tab function in the Crystal report which directly pump in all your data and let the crystal report do the rest. Unfortunately, it is too late for me to realized there is such function to use, and I used the way of coding which will faced some challenges in the midst of preparing the format of data before putting it into crystal report and display it.

OK. Here is just something to share for those who wanna use such method to display your data. This is what I’ve done, and may not be the most efficient way to do. Btw, I’m using MVC c# and nHibernate for this case.

Below is the View in my database, which you can see where the report will be display according to do order ValueDate, BrokerName, and TradePLCcyCode.

data in database
data in database

And Below is the Crystal report format where data will be populated into and be display. I have created a sub report for this to make ease if next time there is a need of adding new extra report to this report. And the below diagram shows the section where data will be displayed. I have fix it to 10 rows for Currency, 6 Columns for Broker and Total. And yeah, it is group by ValueDate. These fields are also define in my dataset file(.xsd).

crystal report
crystal report
subreport
subreport

Now, we go to the coding part. Before I pump in my data from view to the report, I have to convert all the data from view into a datatable before assigning them row by row into an object. Below is the datatable generated from the view where all data are populated into. As you can see there are different ValueDate which we have to group them later.

datatable
datatable

Now is the challenging part where I have to split this ‘huge’ datatable into sections of smaller datatable to be displayed. So What I’ve done is, firstly I split it by ValueDate. It depends on how many ValueDate in my datatable.

split by value date
split by value date

Above code split the ‘huge’ datatable into smaller lists of datatables with different ValueDate. And from here, I will loop through the number of distinct ValueDate. However, the rows and columns may exceed what I’ve set in the Crystal report, so I have to split these smaller lists of datatables into the sections that fit in the report.

How to know how many section for my rows and columns? As above, I have set 6 rows and 10 columns. So a loop function is use to loop through the ‘huge’ datatable to do the following process:

1. splitting it into smaller datatables. It will split by rows then by columns.

E.g. table is 12 X 20, it will split into 4 smaller tables.

table is 12 X 10, it will split into 2 smaller tables.

2. assigning each smaller datatables in an object.

3. assigning all objects into a list of object.

After that, will pass this list of object into the crystal report to be print.

Below is a sample of the report generated.

 Capture3

TF400018: The local version table for the local workspace could not be opened

solution:
This error usually due to workspace getting corrupted especially when local workspace are configured. This error doesn’t allow you to view the source control explorer. To solve this and to re-use the same workspace, you have to rename the $tf hidden folder which will be created at the root of your local workspace mapped folder.

* Pls don’t use this method if your code is merge from the branch to the trunk but not yet check-in to the trunk.

Configure quickfix\n and stunnel to connect to currenex

I have use quickfix 4.4 and sTunnel for below example to connect to a Currenex demo account. Download the UIDemo project from github. I’m using quickfix\n for c#.

About FIX

FIX is an electronic messaging protocol for financial transactions. The Financial Information eXchange (FIX) is an open, widely used industry standard. More information is available on the FIX protocol website.

Quick fix is an open source application that provide such service as above. So below is my config file setting.

Configuration

1. In quickfix.cfg

[DEFAULT]
ConnectionType=initiator
ReconnectInterval=2
FileStorePath=store
FileLogPath=c:\fixlog
StartTime=00:00:00
EndTime=23:00:00
UseDataDictionary=Y
DataDictionary=FIX44.xml
SocketConnectHost= my external ip address
SocketConnectPort= port to connect to sTunnel in your local pc
ResetOnLogon=Y
ResetOnLogout=Y
ResetOnDisconnect=Y
CheckLatency=N

# standard config elements

[SESSION]
BeginString=FIX.4.4
SenderCompID= Hub FIX Taker ID
Username= Hub FIX Taker ID
Password= Hub FIX Taker Password
TargetCompID=CNX (Name I define in sTunnel config file)
HeartBtInt=20
SocketConnectPort=444
SocketConnectHost= my external ip address
DataDictionary=FIX44.xml

2. In stunnel.conf

[CNX]
client = yes
accept = your local pc external ip : port   eg.(10.160.103.65:444)
connect = Currenex server ip address  eg.(dret-xxx-xxx-xxx.com:444)

3. Build a self-signed pem cert in sTunnel.

createcert1createcert2Click ‘enter’ and your cert is done!!

4. Add 2 lines of code in QFApp.cs as below, which is your hub username and password.

fix2

5. Start your sTunnel service.

6. Run the UIDemo application. And it works!!

fix

Safra Run Marathon 10km

As Singapore is well known for its marathon, everyone craze for it. I’m not sure what’s the reason behind that but it is quite interesting to try once. So I registered for 10km category with a few bros. Personally, I’m not  the one who like to run, honestly, I would rather prefer other sports than running. Any way, I’ve registered and participated.

This is the first time I participated in a marathon. What worried me the most before the event is that I may fain during the half way. But it turn out better than during the training session. I dont have a good stamina, and may run out of breath. I have to improve on this aspect in order to go for another challenge.

On that day we were late to run. We started 20 min after the gun time. It is quite tired when running the first few km. after that, I think I’m numb for my lower part of my body. Just have to struggle to overcome it and go on.

Finishing the course is the best thing. Although is far behind from other contestants, but is still a sense of happiness, satisfaction that I can complete this thing! All tiredness have disappear, but fill with joy.
Lastly, through this event, I’m able to know my own limits and train for more advance challenges.There are lot of aspects need to be improve. But with this experience, it can encourage me continue to improve. Is a wonderful experience.

 

 

IMG_0928.JPG

IMG_0933.JPG

IMG_0934.JPG

IMG_0932.JPG

 

Below is my ranking   >.<

 run

 

my cert

certificate10km_1

certificate10km_2

 

The secret to being perfected to be a pillar

Really being encourage by this portion of word.

THE SECRET TO BEING PERFECTED
TO BE A PILLAR
Let me now tell you the secret to being solidly perfected to be a strong pillar for the Lord’s move. Certain brothers have been perfected because they have had no concepts of their own. Recently, one brother declared strongly that he only knows to follow the ministry of Brother Lee and to absorb everything of this ministry. There have been others among us who were quite opinionated. They often said, “Brother Lee says such-and-such. Is this right? Is the church right? Just a week ago, I learned about a mistake made by the church.” None of these opinionated ones has yet been perfected. But those who haven been perfected to be pillars are not like this. Even when they see certain mistakes, they forget about them, having no time to waste discussing them. They only desire to soak in all the positive things.
FEASTING ON THE POSITIVE THINGS
According to God’s principle in His creation, in order for anything to grow there is the need for a negative side. Take the example of a chicken. We all appreciate chicken eggs, breasts, and legs, but we certainly do not care for chicken dung, feathers, and bones. Nevertheless, without dung, feathers, and bones, a chicken cannot grow. In order for a chicken to be a chicken, it must have these things. But it is not our portion to eat them. We should enjoy the eggs, the breasts, and the legs, and forget the dung, the feathers, and the bones. If we concentrate on the positive aspects of the chicken, we shall receive much nourishment.
I admit that the church in Los Angeles has made certain mistakes, and I confess that I have made mistakes. The elders can testify of this. Everybody makes mistakes. No one can deny this. I have had to make mistakes in order to grow. These mistakes are my “dung.” If you eat this, you are foolish. I also admit that I have “feathers.” The church in Los Angeles also has had a certain amount of “feathers” and “bones.” However, without these “feathers,” “bones,” and “dung,” neither the church in Los Angeles nor my ministry would be able to exist. Do you intend to gather up the “feathers” and say, “Look! This is the church in Los Angeles. Look! This is what Brother Lee has done. See all these awful ‘feathers.'” If you do this, you will not damage the church in Los Angeles or my ministry, but you will surely damage yourself. To do this is not wise. These who have been perfected to be pillars, who surely are not less intelligent than you, are wise. Their eyes are much clearer than yours. But they refuse to devote their attention to the negative things. They would say, “Although Brother Lee has some ‘dung,’ he has a great many eggs. I don’t care for the ‘dung’ issuing out of his ministryI want to eat all the ‘eggs,’ ‘breasts,’ and ‘legs.’ I have no time to hear about ‘feathers’ and ‘bones.'” Let us follow the example of such brothers to forget the negative things and to feast upon the “eggs,” “breasts,” and “legs.” This is my burden in this message.
Are you in Anaheim as a spy, investigating whether or not Witness Lee has any “dung”? I cannot live without “dung.” To be sure, both “feathers” and “bones” can be found here in Anaheim. The elders have made many “bones.” But I would stand, even dance, upon all the “bones” they have made; I would not be so foolish as to eat them.
Three or four of us knew Brother Nee very intimately. He fully opened himself to us, and we knew his imperfections. But we realized that these imperfections were the “dung” that enabled him to exist. Unlike others, we would not cling to his “feathers,” nor to the “bones” of the “chicken” in Shanghai. If we had done this, we would have sacrificed ourselves. I never suffered such self-inflicted damage. Rather, I enjoyed the fresh, nourishing “eggs,” “breasts,” and “legs” of Brother Nee’s ministry. When a great turmoil was aroused against his ministry, I was not ashamed to say that I was an absolute follower of Brother Nee. I did not care what others said about his mistakes. I only knew how grateful I was to him for the perfection he had rendered to me. I knew the nourishment I had received from him. Even when we are in the New Jerusalem, I shall be able to say that the Lord used Brother Nee to perfect me. Apart from his ministry, I would never be the person I am today.
How foolish it would be for anyone in the church to devote his time to finding “dung” or stuffing his pockets with “feathers,” saying, “This is a ‘feather’ from that ‘chicken’ Witness Lee, and these are the ‘bones’ of the church in Anaheim. Don’t you know that the church in Anaheim has made mistakes?” If this is your intention, you are wasting your time. You are in the wrong place. Neither Witness Lee nor the church in Anaheim would pay you for exposing them. But do not think that we are afraid of being exposed. Whatever Witness Lee is, he is what he is. Whether the church is genuine or not, the church is what the church is. Neither the church in Anaheim nor my ministry is afraid of exposure. On the contrary, we appreciate it. But what will you gain by exposing us?

From life study of genesis msg 88.