×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Column Wise Report

## Column Wise Report

(OP)
Hi,

Two codes were suggested:

#### CODE -->

dt1 = {01/01/2017}
dt2 = {31/12/2017}
Sele Year(cdate) As theyear, ;
month(cdate) As themonth, ;
sum(debit) As debit, ;
sum(credit) As credit, ;
SUM(debit-credit) as net ;
from actran where BETWEEN(cdate,dt1,dt2) and ccode = 'CC020C' ;
group By theyear, themonth ;
BROWSE LAST 

Output:

And,

#### CODE -->

dt1 = {01/01/2017}
dt2 = {31/12/2017}
Sele Year(cdate) As theyear, ;
UPPER(LEFT(CMONTH(cdate),3)) AS TheMonth,;
sum(debit) As debit, ;
sum(credit) As credit, ;
SUM(debit-credit) as net ;
from actran where BETWEEN(cdate,dt1,dt2) and ccode = 'CC020C' ;
group By theyear, themonth ;
BROWSE LAST 

Output:

Please note that while change month no. in to alpha, it gives alphabetical order.

Also I want to show this report in column-wise like

Thanks

Saif

### RE: Column Wise Report

When you populate excel, simply take recno() + offset as column of the cell adress. There's no need to have data in this structure to output it to excel that way.

So, for example, start with the necessary subset of the first query and do:

#### CODE -->

dt1 = {01/01/2017}
dt2 = {31/12/2017}
Select Year(cdate) as nYear ,;
Month(cdate) as nMonth ,;
UPPER(LEFT(CMONTH(cdate),3)) AS cMonth,;
SUM(debit-credit) as net ;
from actran where BETWEEN(cdate,dt1,dt2) and ccode = 'CC020C' ;
Group By 1,2;
Order By 1,2;
into Curs tempSi nofilter

oExcel = CreateObject("Excel.Application")
WITH oExcel.ActiveSheet
SCAN
.Cells(1,RECNO()).Value = tempSi.cmonth
.Cells(2,RECNO()).Value = tempSi.net
ENDSCAN
ENDWITH
oExcel.Visible = .T. 

Bye, Olaf.

### RE: Column Wise Report

The crosstab wizard tbleken points to is an option, but what is so mesmerizing in having data 1:1 in the form you want to present it?

Interpret a cursor as what it is: A table of data. Everything is organized in rows of same columns, but that doesn't hinder you to display it otherwise.

Every time you force the task on you to transform the data in 1:1 the form to display already within the BROWSE of a DBF, you just burden yourself with a useless task, as that mirroring of rows to columns simply is a coordinate transformation and you just need an output medium like Excel sheets enabling you to address the cells in any way you want.

Bye, Olaf.

### RE: Column Wise Report

(OP)

Dear Mr. Olaf

I don't want any output in excel. I applied what tbleken suggested and go this result.

I just need month name in sequence.

Saif

### RE: Column Wise Report

Saif

another solution to ur problem.
create another table

ex: create mytable ;
(theyear n 4,;
jan n (10,2)
...to dec )

sele mytable
appe from dbf('mysource')

-omr ;)

### RE: Column Wise Report

Simpler, have both MONTH(cdate) and the CMONTH() column and group and order by the year, the numeric and the textual month column. The number of groups will not change but the order.

Bye, Olaf.

### RE: Column Wise Report

(OP)
Yes,

It solves my query by creating new table.

Thanks

Saif

### RE: Column Wise Report

Hi,

If you do

#### CODE --> quote

have both MONTH(cdate) and the CMONTH() column and group and order by the year, the numeric and the textual month column
and you have more than one record of the same month, they will not be summed with the given select statement. I suppose you will have to do two different selects.

Regards,

Jockey2

### RE: Column Wise Report

No, Jockey, I said - as you quoted - "and group and order by the year, the numeric and the textual month column", you do group the data and have one result per month, because 1 always is JAN and 2 always is FEB, etc. The number of groups does not get higher.

#### CODE

Create Cursor crsDates (dDate D)

=Rand(-1)
For nCount = 1 To 120
Insert Into crsDates Values (Date()-Rand()*365)
Endfor nCount

Select Count(*);
,   Year(dDate) As nYear;
,   Month(dDate) As nMonth;
,   Left(Cmonth(dDate),3) As cMonthShort ;
From crsDates ;
Group By nYear, nMonth, cMonthShort Into Cursor crsResult

Browse 

Is it really that hard to imagine what I said, what you read and even quoted? If you needed an illustration or prove, well here you are.

Bye, Olaf.

### RE: Column Wise Report

Olaf,

You are absolutely 100% correct, it seems I had made a writing error in my tests, the following code gives the desired result:

#### CODE --> VFP

Local ldT1 As Date, ;
ldT2 As Date

ldt1 = {^2017/01/01}
ldt2 = {^2017/12/31}
Sele Year(cdate) As Theyear, ;
upper(Left(cMonth(cdate),3)) As TheMonth,;
month(cDate) As ThenMonth, ;
sum(Debit) As Debit, ;
sum(Credit) As Credit, ;
SUM(Debit-credit) As Net ;
from table1 Where Between(cDate,m.ldT1,m.ldT2) And cCode = 'CC020C' ;
group By Theyear, ThenMonth, TheMonth ;

Thisform.Grid4.RecordSource = 'tempSi' 

Thanks for clarification,

Jockey2

### RE: Column Wise Report

Yes, and that can be the input for VFPXTAB again, still with the textual months making up the columns.

For sake of completeness, you could add in Order By Theyear, ThenMonth, but VFP seems to order in the grouped columns by default anyway, that would not necessarily be true for any databases SQL dialect.

Bye, Olaf.

### RE: Column Wise Report

(OP)
Hi

I want cumulative total for each month. How can I do so?

And, in cumulative row, it should not repeat the figure of August in september, october, november and december.

Thanks

Saif

### RE: Column Wise Report

(OP)
Hi,

I am facing this error while replacing the total with individual figures:

Please suggest me how to avoid this?

Thanks

Saif

### RE: Column Wise Report

Well, create the totmonth field nullable. As you don't show what totmonth is, how you create it, it's quite impossible to tell you how, but when you create a table using the table designer it's just clicking the column with caption NULL, if you create a table with CREATE TABLE or CURSOR you specify totmonth Y NULL, when you create the column via SQL query use the expression Cast(something as currency null) as totmonth to create the totmonth column as nullable currency column.

Bye, Olaf.

### RE: Column Wise Report

The other possible solution is find your error, as far as I assume your totmonth column should always contain a value, may it be 0.00, but never would be .NULL.

Bye, Olaf.

### RE: Column Wise Report

(OP)
Thanks I got it.

Saif

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!