GasFillUp

There are many website and apps for managing and handling gas receipts, I wanted to try out something of my own. I have been working on a responsive web application called GasFillUp to track gas receipts and provide a simple report to display the gas usage. Its in beta phase http://gasfillup.azurewebsites.net, all through my way I was cherry picking neat and interesting stuff what others created. Its been a great end to end learning experience. Things to do, implement error handling, email support, more charts, other performance improvements.

This project revolves around one simple use case, one user can have more than one car. The app has three distinct features vehicle info, data entry and dashboard with infographic style report to display the historical gas receipts information. Upon registering, users have to create a profile for the vehicle.

Data entry begins by selecting the car.

Initially the dashboard is empty, when user enter the information into the system the dashboard populates with nice infographics.

The app is built using ASP.NET webforms, plain old ADO.NET, MySQL, Bootstrap for responsive interaction. Currently, the site is hosted with Microsoft Azure and the database is with ClearDB. This hosting platform is free hence you get lot of limitations, its quick way to port a website. Microsoft Azure looks really promising. The source code is hosted with GitHub, clone/fork it here GitHub

Apple WWDC 2014 feast for developers!

Phew..look at the list of technologies announced, enhanced messages, Handoff, SpriteKit, CloudKit, HomeKit, MetalKit,Swift (cross between Javascript, Python) and many more. This is the perfect time to jump and be a part of Apple family.

If you observe the trend in computing machines, hand held devices (x64 bit processor in palm) and servers (throw in 32 GB of RAM) are beefing up like crazy. The in betweeners MBP/Air are slowly decaying. How are the big players coping with this paradigm shift in hardware. Microsoft's answer ditch your PC, adopt Surface Pro. Apple's answer continuity. Its apps which are being shared across devices and the technology HandOff  if it really works that seamlessly it would be a game changer. 

 

Last year apple released the 64 bit processor for the iPad and iPhone this year iOS 8 is loaded with stuff which can truly bring value to the new Eco system. What really surprised was the new programming language Swift, Objective-C was defacto to Apple for three decades. I believe Swift has the potential to be what Java & .NET was for the enterprise. Overall, we barley touched the surface of mobile platform/computing, definitely headed in the ring direction. 

How to remove extra page with tablix SSRS 2012

Recently I had to create a transaction summary report for my accounts receivable project. To display the transactions I choose a tablix as shown below

The grouping for the tablix was on a sequenceid (assign a rank to all the transactions with one clientid), I applied page break at the end of the group

I thought the report was done, however in the print preview the report was printing a blank page in between the group the fix for that is

Body Width <= Page Width - (Left Margin + Right Margin)

report width= 8.5, left margin = 0.164, right margin = 0.164

(report width – (left margin + right margin)) = 8.172

Click on any white space on the report and hit F4 to see body properties

The Width in the body was 8.2in this threw me off and I was getting blank pages, so reduced it to 8.15 that fixed the issue.

Extended Properties in SQL Server

Extended properties (EP) are SQL Server objects which can be used for documenting other database objects such as tables, views, functions etc. Extended properties offer a common standard place for information about various database objects

Extended properties have three level categories such as Schema -> Table -> Column.  Adding, altering of extended properties are handled through system stored procedures  

sp_addextendedproperty  -> adds a new extended property to a database object

sp_dropextendedproperty -> removes an extended property from a database object

sp_updateextendedproperty -> updates the value of an existing extended property

The above stored procedures have the following parameters

@name = Name of the extended property  

@value = Comments/Description

@level0type = Schema, @level0name = name of schema (dbo)

@level1type = Table,  @level1name = table name

@level2type = Column, @level2name = column name

The Level0/1/2Type’s are not limited to Schema/Table/Column, refer to http://blogs.lessthandot.com/index.php/datamgmt/datadesign/document-your-sql-server-databases/, for a detailed explanation

Consider a sample schema,

create table dbo.Consumers

(

            SeqID int identity(1,1) not null,

            Name varchar(100) not null,

)

To add a description for the SeqID column we can use ‘sp_addextendedproperty’ system stored procedure

-- creating

exec sp_addextendedproperty

@name = N'MS_Description',

@value = 'Sequence ID to identify consumers, auto incrementing number ',

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table',  @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID;

-- altering

exec sp_updateextendedproperty

@name = N'MS_Description',

@value = N' Sequence ID to identify consumers, auto incrementing number seed value 1’,

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table',  @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID;

-- dropping

EXEC sp_dropextendedproperty

@name = N'MS_Description',

@level0type = N'Schema', @level0name = dbo,

@level1type = N'Table',  @level1name = Consumers,

@level2type = N'Column', @level2name = SeqID

The best use of extended properties is getting the column definitions of a table. I always create the following sql query as a stored procedure and filter it by table name

-- viewing

select col.name as [Column], ep.value as  [Description]

from sys.tables tab inner join sys.columns col

on tab.object_id = col.object_id left join sys.extended_properties ep

on tab.object_id = ep.major_id

and col.column_id = ep.minor_id

and ep.name = 'MS_Description'

where tab.name = @TabName

Note on precision & scale in SQL Server

Generally for storing a variable like 123.45 we can use a decimal or numeric data type. The usual notation for these data types are decimal [ (p[ ,s] )] & numeric[ (p[ ,s] )], p stands for precision & s stands for scale.

Precision is number digits in variable

Scale is of digits to the right of the decimal point

For ex: 12.345 p = 5 & s = 3

The sample query explains the behavior of the decimal, float & int

declare @t table(

     c1 int,

     c2 float,

     c3 decimal(18,2)

)

insert into @t (c1,c2,c3) select 12.345, 12.345, 12.345

insert into @t (c1,c2,c3) select 10/3, 10/3, 10/3

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, 10/3.0

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, round(10/3.0, 2)

select * from @t

c1        c2                    c3

12        12.345                12.35

Note: The value in C3 is truncated to two decimal places, C1 completely ignored the fraction part.

3           3            3.00

Note: The fraction part is completely ignored because the denominator is an integer

3           3.333333           3.33

Note: The fraction part is retained correctly

3           3.333333           3.33

Note: The fraction part can also be achieved by rounding the value using round function

TSQL Rambling

Wanted to share my thoughts on three interesting things stumbled across in TSQL

1. SQL Unary Operator

Lets create a simple table as follows

declare @TestPlus table

(

Type varchar(2),

TypeName varchar(10),

code int

)

insert into @TestPlus values ('P', 'Procedure', 1)

insert into @TestPlus values ('T', 'Table', 2)

insert into @TestPlus values ('V', 'View', 3)

insert into @TestPlus values ('F', 'Function', 4)

What is the output for the following select query?

select Type from @TestPlus where Code in ( +1, +++3, +4)

select +++Type from @TestPlus where Type in (+'P', +++'T')

When I saw this select query for a second I thought its trick question on how to use ++ operator, however it turns out that the result has nothing to do with the “+++” operator. Quickly browsed through MSDN and found that it has no effect on the evaluated expression

“Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression.”

http://technet.microsoft.com/en-us/library/ms174362.aspx

SQL Row Constructor

I was surprised to see a query like this

;with cte as

(

select dddid, payorid

from (values  ('100100', 1), ('100101', 2), ('100102',3 ) ) f(ClientID, SeqNo)

)

select * from cte where SeqNo = 3

turns out that this type of query is called row constructor a new feature introduced in SQL 2008.

3.  SQL Strict Names

Check out the following queries, spot the odd man out

select top 10  * from [dbo].[TestUpSurdTable]

select top 10  * from [dbo].[TestUpSurdTable]_Final

I was expecting two different result sets however the same result set was repeating twice, turns out that the “[ ]” ignored what ever is after the underscore

Best Data Visualization Libraries

Best Data Visualization Libraries

I just observed a  trend for inforgraphic style of reporting in business. Infographic reports very tangible, click, drag, drop, add elements which makes the report more like a stand alone app.

I complied a short list visualization libraries based on javascript which I personally used. I will be updating this list as I find something interesting.

1. Google Charts

https://developers.google.com/chart/

2. Data Driven Documents

http://d3js.org/

3. Protovis

http://mbostock.github.io/protovis/