After spending a lot of time testing and fixing defects on the Transactions View and Tag page, I finally decided to move to Reports. Theoretically, its the last piece. You register, create accounts, upload statements, categorize transactions and then, finally you are supposed to see how you did over time.
This reports page, a basic one posted good challenges of moderate complexity. I prefer moderate complexity problems. They pick your neurons, and solving them gives you a sense of satisfaction.
Here is how I define the basic problem.
- The Report will always be 2 dimensional. The Columns represent time in months. As this is individuals we are dealing with, no quarterly or six-monthly. Each column represents a month.
- The Rows can be any of these
- Categories
- Accounts
- Labels
- Categories and Subcategories together
Personally I think my database design could be better. With reporting, I now need to convert rows to columns and columns to rows. For example, Months are columns (month1, month2 etc.). Another one, I have Labels in a row In query results I get transaction date in a single column. Now to accomplish this using sql query is cumbersome, to say the least. With passage of time and increasing number of records, such queries degrade fast.
To avoid this, I decided to do the entire transformation on the client side. Here is my philosophy. When I crank up my CPU, I pay for the cycles. I can decide to take up as much work as I want and then deliver only the final results to the customer. However, this costs me a lot. Why not leverage the browser power to do some work. Its distributed and its free. The laptop CPUs are fast and capable. js engines have really become quite efficient and its economical even from a bandwidth pov, to move minimum data and do much of the work at the client side.
So, I now send the query result over to the browser as json. From there on the JQgrid and javascript takes over. I render all the views with that data entirely on the client side, without making any round trip. The good thing is that I do not bring all the transaction data, I just get grouped data. The grouping is fine-grained. That allows me to create all 4 views on the client side.
Played a lot with JQgrid. Loved its features. It took me 4 days to reach where I am today. All these views are done. I have a big js function of 250 lines that does the work. Its neat. I like it. BTW javascript array.sort is one expensive feature. But then I need it...
This reports page, a basic one posted good challenges of moderate complexity. I prefer moderate complexity problems. They pick your neurons, and solving them gives you a sense of satisfaction.
Here is how I define the basic problem.
- The Report will always be 2 dimensional. The Columns represent time in months. As this is individuals we are dealing with, no quarterly or six-monthly. Each column represents a month.
- The Rows can be any of these
- Categories
- Accounts
- Labels
- Categories and Subcategories together
Personally I think my database design could be better. With reporting, I now need to convert rows to columns and columns to rows. For example, Months are columns (month1, month2 etc.). Another one, I have Labels in a row In query results I get transaction date in a single column. Now to accomplish this using sql query is cumbersome, to say the least. With passage of time and increasing number of records, such queries degrade fast.
To avoid this, I decided to do the entire transformation on the client side. Here is my philosophy. When I crank up my CPU, I pay for the cycles. I can decide to take up as much work as I want and then deliver only the final results to the customer. However, this costs me a lot. Why not leverage the browser power to do some work. Its distributed and its free. The laptop CPUs are fast and capable. js engines have really become quite efficient and its economical even from a bandwidth pov, to move minimum data and do much of the work at the client side.
So, I now send the query result over to the browser as json. From there on the JQgrid and javascript takes over. I render all the views with that data entirely on the client side, without making any round trip. The good thing is that I do not bring all the transaction data, I just get grouped data. The grouping is fine-grained. That allows me to create all 4 views on the client side.
Played a lot with JQgrid. Loved its features. It took me 4 days to reach where I am today. All these views are done. I have a big js function of 250 lines that does the work. Its neat. I like it. BTW javascript array.sort is one expensive feature. But then I need it...
No comments:
Post a Comment