INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

Column Wise Report

Column Wise Report

(OP)
Hi,

Please refer to my thread thread184-1723072: How to break sales into months.

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 ;
into Curs tempSi readwrite
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 ;
into Curs tempSi readwrite
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



Please suggest..

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")
oExcel.Workbooks.Add()
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)
Thanks for the reply!

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.

Please suggest

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 ;
	into Curs tempSi Readwrite

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

Please refer to the image.
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.

Please advised.

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close