Monthly Archives: August 2012

Crystal report formula field Tutorial

You can use formula field in Crystal report for a custom field, for example

  • You want to find total sales based Qauntity and units sold
  • You want to multiply, add two numbers
  • You want to add number of days to the current date

Note that Crystal Report formula fields are not function but they act like one. They do not declare any input variables or parameters and you do not declare any output variable or return a value. All is taken care of in the editor without declaring anything.

Tutorial

Right click on Formula fields and select New. Choose a name for your field. Start editing.

Drag any field from the available fields and use it in the formula. These act as input fields.

No need to return  a value. What ever is the product of the calculation will  be return when you use Formula field

Example : Add 7 days to current date

Right click on Formula field, just below Database Fields. Choose the formula field name

In the popup dialog and editor, in the middle column (Function field), select Date Time and expand that selection by clicking + in front of it. Double click two functions that we we will need, DateAdd and Current Date. Give it proper parameters. consult documentation for proper syntax. When done clic on X-2 syntax check mark to make sure all is ok. If no error is found, save the document and close.

The screenshot and formula in this case happens to be the following

DateAdd("d",15,CurrentDate )

Note that there is no return type or any return value.

Equivalent font sizes of H1 H2 H3 H4 H5

Wondered what is the equivalent size of html header tags H1, H2, H3, H4 and H5? I rendered an XSLT containing these 5 heading into a word document, the following was the result

 

Header Font Size (pt) Font Weight
H1 24 Bold
H2 18 Bold
H3 13.5 Bold
H4 12 Bold
H5 10 Bold

For reference, P tag is equivalent to

Tag Font Size (pt) Font Weight
P 12 Regular

Database Vendor code: 229 – Crystal Report

If you get the below error in Crystal Report

Failed to retrieve data from the database. Details: [Database Vendor Code: 229]

This mean you do not have access to the data. This possibly occurred because you created your report on one machine and then deployed it on another. On deployment, you noticed that your report does not pull that data instead you get this error. The most likely cause is the permission. You do not have permission to query that data or run stored procedures or function.

Possible Causes

  • Your report uses functions calls or store procedure, your login is not allowed to run function and stored procedures
  • Your login does not have select permission for the database/table
  • Other permission related problem

Solution

By allowing read or execute permission will fix the problem. If it is just one one function call /store procedure that is causing the problem, the following command will be helpful

GRANT EXECUTE ON dbo.my_function TO [user/role]

SQL function says command completed successfully

I got the above error when I was trying to return a value from my sql function (scalar) and it would instead say,
Command(s) completed successfully. without actually returning the value. So I investigated

Scalar SQL Functions

Scalar SQL function are like regular functions which returns a single value rather than a table. It is usually used for calculations rather than selection a row(s) of data. You cannot run select command in Scalar SQL Function. Well you can use it but you cannot return the result of that select command. A typical Scalar SQL function looks like this

ALTER FUNCTION SquareTheNumber -- 1. Function Name
(
@score float -- 2. parameter to the function
)
RETURNS float -- 3. Function must return a datatype
AS
BEGIN

DECLARE @square float -- 4. Calculations, this and the next line
set @square = @score * @score
RETURN @square -- 5. Return this value

END

Calling SQl Function in SQL Server 2008

This is the tricky part. The function above might be perfectly fine but if you call it, it will not give you unexpected result such as “Command(s) completed successfully.”

Note that SQL Functions are not the same as Stored Procedures. They have different declrations, they have different purposes, they have different ways of calling, they have different ways passing the parameter. So first mistake to not call the above function as you would do if it was a stored procedures. Namely the following are wrong syntax

exec SquareTheNumber 2.8 — wrong

exec SquareTheNumber(2.8) — wrong

exec SquareTheNumber @score = 2.8 — wrong

Correct way of Calling SQL Function

In SQL Server 2008, the correct way of calling the SQL function is

  1. you must not use the exec command when calling the SQL function
  2. pass parameter to the function in brackets only
  3. use database schema along with the function name
  4. you must capture the return value

All of the above must be true to call a scalar function. Therefore the correct way is

select dbo.SquareTheNumber(2.8)

dbo = database schema
bracket = for parameter passing
select = captures the return value
exec = execute was dropped from calling the function

Other possible calling syntax that may give you error or not error is as follows

dbo.SquareTheNumber(2.8) // Incorrect syntax near ‘2.8’.
exec SquareTheNumber 2.8 // Command(s) completed successfully.
exec SquareTheNumber @score =2.8 // Command(s) completed successfully.
select dbo.SquareTheNumber 2.8 // Incorrect syntax near ‘2.8’.

Western Union vs MoneyGram Cost Comparison

Use this calculator to Compare western union rate vs MoneyGram.

Usage Notes: Note that Western Union (WU) and MoneyGram(MG) dollar conversion rate changes on daily basis. There service fee also changes and is usually different state by state. You must provide the fees and dollar exchange rate for both WU and MG by calling them or visiting them locally. Links are given below for currency exchange rates. Scroll down to the bottom of the page to see comparison. If you have any comments, please comment below.

See comparison


Vendors: Local stores, grocery stores
Vs
Vendors: Walmart, CVS, Shaws
Estimate WU Curreny Excange Rate Estimate MG Currency Exchange Rate

Compare Western Union vs MoneyGram cost

From United States
To country
Amount($)
WU Exchange Rate (Local Currency) WU Transfer Cost
MG Exchange Rate (Local Currency) MG Transfer Cost
Comparison

Winner : MoneyGram

Benefit : $0

Cost Comparison

Western Union

MoneyGram

Money Send : $
Money Received : (local)0
Total Cost: $
Conversion Rate Gain/Lost: $0
Net $ Value: $0
Total Money Send : $
Money Received :(local) 0
Total Cost: $
Conversion Rate Gain/Lost: $0
Net $ Value: $0

How to write complex SQL Query or Report

There will be times when you will be asked to write a complex report that involves more than 3 tables, has obscure fields that do not know where they are in the database. You will posed with the problem of finding these fiedlds first and then include this field relevant to a primary field such as customer name for example. The process main include join tables, left, right join, writing subqueries and some other technique. Here will I briefly describe how can you accomplish such complex report in SQL.

Step 1: Define the Report

You are given a report. The report contains some 10 fields form differt tables in the database. All you know is you have to pull this data form the database? Where it is? You do not know.

Step 2: Hunt down the fields

I will not discuss it here. You basically have to find which table and which field stores your required information.

Step 3: Prepare a block diagram of the report

Now that you have hunted down the fields or some fields at least (you may want to start with the two most crucial fields first and include others while you write the report), your are thinking of how this column in one table can be related to this other column in another table. The relationship may not be straight forward. It may required 2 or more hopes to join the two tables. An easy tool that I found for this is Microsoft Excel. Draw each table in Excel, show only the required fields and map it to the next table that makes sense. Map the next table to another table and son on until you reach your desired table. The best way to start would be to draw it on paper first and once it make sense, transfer it to Excel using color coding as below.

Block Diagram SQL Join

Block Diagram SQL Join

Step 4: Write the query, starting from the farthest end

You will need the following steps to write the query. Do not make it perfect in the first attempt, just something that works and pulls the main fields.

  1. Write Select column_names from the farthest table. Include ID columns only. DO not use * or pull all columns
  2. Right the first join, do not include extra parameters in select statement. Continue on
  3. At this point you have joined the first hop between operation table and commission_sch_cal_relate (the two on the farthest left side)
  4. Join the Commission_Schedule table in the same manner
  5. In the third hop, you noticed that you can’t join SalesEntityRelate to commission_Schedule because you are join part of commission_schedule based on relate_class_name (you are not joining the full table), plus you are joing SalesEntityRelate which has not been reference before. Essentially you are joing two new tables at the same time which you can do in one step
  6. The solution is self join Relate_ID and Relate_class_name, write a sub query that create this table and then join it to Commission_Schedule. Now you have a this temp table that you can reference to join to SalesEntityRelate
  7. Joing this temp table to SalesEntity Relate based on Relate_ID=Sales_Entity_Relate_ID
  8. You will experience the same situation when joining SalesEntityRelate to Contrct. Again use a subquery and a self join to create temp table that you can use as reference in the next join for your ease
  9. The next join are straight forward. You do not need any more self joins because the same table is not reference in two different ways.
  10. I used RIGHT JOIN 3 times in the left most tables. This is because for some contracts, the there was no broker fee, if there is no fee, there was no commission. Since there is no commission, operation value_no is not entered for those records. Since I want to include all brokers, I have include right join because even if the value is not found, the broker name should still be preset in the report
  11. Add all the columsn that you need and your report will be ready. Debug as necessary

The Query

Finally the query that was used in the above example

ASP.NET Weird Error

The following error in thrown in my asp.net web application (folder based)

Keyword not supported: ‘id’.

Guess where could be the problem? When I look at the compilation error

It takes to an ASPX page (not C# code) to indicate where is the problem. The fact is that ASPX page is totally fine. I double click on code behind of that page as well, there were not errors. The code was simple enough.

Finally googled it and came at this stackoverflow question, that says the problem is in connection string.

The problem is actually in connection string

I had just added a new connection and copied the string from another application to supposedly everything would be fine. This is btw my connection string

Apparently something was wrong here, that throwed the error (ID should have been User ID). After I fixed this, the error went away.

My question is, how intuitive is this error? how much time I would have spend on this error if it was not for google and onine help?

I come across many such errors in ASP.NET and I want to document it for the record.

Writing Complex SQL Queries

I have had a chance to work with some complex SQL Query in different reports, I will highlight the skillset that you will need writing such a report.

First Thing

When writing a report the field names might not be intuitive or something that you would expect. For example I wanted to include commission in my report but in the database it was call value_no. More ever it was in the table called operation. Was that intuitive? Not at all.

Get some hints from the software that uses this value. In our software it was referring to as operation value. Therefore it made sent to look it up in operation table. First I verified this value matches the value in the software for this particular contract so I knew I have to read this value.

 

The problem

The problem was attaching this value to the contract. But it was not easy. There was not direct relationship between the operation and the contract table. I had to hunt.

Methodologies

In my case, the relationship between the contract and the commission was too complicated. I simplified this bu writing a bock diagram. This block digram established a connection between the two tables. I created this in Excel, you can use Excel for similar table too.

 

Writing the Actual Query

Now that you have established a connection (the weird as it is), the task is to right the query. This part may not be as simple as you would think. Where do you start from? In the middle, left or right?

  1. Start with the left table that contains the value you need in your report.
  2. Include fewer values in the select statement, do not use *. In this case,  you are going to need value_no and comm_calc_id so use SELECT value_no,comm_calc_id from OPERATION.
  3. From this point it is just a smoothe right of just connecting the two adjecentt tables based on primary keys. But there is one problem in table 3 that I will discuss below.

In table 3 Commission_Schedule, there are two fields  that relate to SalesEntityRelate table, this makes the query a little difficult. In fact you have to use subquery at this point.

Assume you are this stage where you already joined Commission_Calcaulation table

What will you do next to join SalesEntityRelate table? Can I do something like this?

Here we have a problem of how to move forward? The solution is to create sort of psedu join which basically does not nothing except give you an extra table that you can use in your join later.

This give you one extra table ‘CS2’ that you can use in your next join.

As you can see, we successfully moved on to the next table. We can continue this procedure till we read the Contract table. At that point we have contract_ID as well as value_no that we wanted to join together and we successfully did.

Fieldnames Abbreviations in Database

The following shortcut codes are used in some database to shorten field names. Underscore characters are used along these shortcut codes to separate different words.

Abbreviation meaning
NM Name
DT Date
CD Code
COMM Commission
TX Text field
CALC Calculation
ACCT Account
COMMISSION Commission where length is not too much
HU Historic Usage
QTY Quantity
INV Invoice
REQ Request

Western Union vs Moneygram

There are two popular ways of sending money within United States or across different countries.

  • Western Union
  • MoneyGram

Western Union vs MoneyGram Comparison Calculator

Western Union

Wester Union is the most popular one. They have more agents thoughout united States. Their agent can be a grocery store, supermarket which are easily accessible and usually we can find them very close to us. They also have more options for sending money such as in minutes, next day, only transfer.

MoneyGram

MoneyGram is less popular but provide the same service. They usually have less options, I am not sure if they have within minutes options but generally it is over next day. They do not have many locations around. One of their agent is Walmart and I know Walmart is not every where. Of course they will have other agents as well.

Quick Comparison

They both provide online options and agents option. There rates constantly change as well. Western Union have a bigger limit on maximum amount sent online. As of today (2012-08), it is $3000 to Pakistan. MoneyGram has $800 limit which is quite less. As I mentioned above, Wester Union gives more option. In terms of number of Agents in Pakistan, both have about the same. I have no problem with either of them.

Comparison when sending Money within United States

If you are sending money withing United States, all that matters is the transaction fee. There is no exchange rate involved. Choose the one that has less transaction fee. Of course make sure they both are available at the destination.

Comparison for overseas Money Transfer

In overseas money transfer, there are two or may be three factors involved

  1. Is MoneyGram (and Western Union) both available at the receiving station. Usually it will be.
  2. What is the transaction fee
  3. What is the dollar exchange rate you are getting

Considering that you will take care of point 1, point 2 and 3 are very important. It is hard to choose one because with one transaction fee can be less but can give better exchange rate and vice verse. In order to do a best comparison, I am going to write an application that will compare which one is better.