Column Wise Report

[alisaif]

Hi,

Please refer to my thread thread184-1723072.

Two codes were suggested:

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,

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

 

[Olaf Doschke]

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:

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.

 

[Tore Bleken]

http://www.tomorrowssolutionsllc.com/Articles/Create Cross-tabs.PDF

 

[Olaf Doschke]

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.

 

[alisaif]

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

 

[omr1119]

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

 

[Olaf Doschke]

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.

 

[alisaif]

Yes,

It solves my query by creating new table.

Thanks

Saif

 

[Koen Piller]

Hi,

If you do

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

 

[Olaf Doschke]

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.

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.

 

[Koen Piller]

Olaf,

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

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

 

[Olaf Doschke]

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 [tt]Order By Theyear, ThenMonth[/tt], 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.

 

[alisaif]

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

 

[alisaif]

Hi,

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

Please suggest me how to avoid this?

Thanks

Saif

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

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.

 

[alisaif]

Thanks I got it.

Saif