report monthly columns

[MikeC]

Hi,

I have seen, when searching, similar report structures described in threads but nothing that tells me or directs me to how it is done. In the attached screenshot of a report done in a current software I am using, you will see under the main title of the report month headings for columns of data for various products. These come from "From---To" date fields and there can be as many as 12 months total. When there are not 12 you only see the months that fall within the From---To dates and the From date field is always left justified where the month column begin.

How is this done ?? This is apparently a 2 part question in that first how does one get the months from the From--To date fields....getting the first and getting the last is easy--what about the ones between. This is what confuses me.

The second and maybe the harder of the 2 questions is how to set the months obtained from the From--To date fields as the column headings with the filtered data from these, of course, falling under each month?

I think a nudge in the right direction may be enough...a point to a sample of something similar would be even better as I do learn so much faster from example....any help any would have would be apprecated!

[MikeC]

Re: report monthly columns

Well,
From a suggestion I recieved via private message I solved the first part of this problem using Calc fields...although not as I expected.

I have 12 calc fields (cMon1 thru cMon12) running horizontally each giving a month - year (Apr-07,May-07, etc) starting from the From_Date field's month and year after it is set to a global variable called rvFrom_Date from a previous form that sets the report's filters/parameters.

cMon1  =  left(cmonth(Var->rvFrom_Date),3) +"-" + right(cyear(Var->rvFrom_Date),2)

cMon2 = left(cmonth(addmonths(Var->rvFrom_Date,[COLOR="Red"]1[/COLOR])),3) +"-" + right(cyear(addmonths(Var->rvFrom_Date,[COLOR="red"]1[/COLOR])),2)

cMon3 = left(cmonth(addmonths(Var->rvFrom_Date,[COLOR="red"]2[/COLOR])),3) +"-" + right(cyear(addmonths(Var->rvFrom_Date,[COLOR="red"]2[/COLOR])),2)

etc....the red numbers are the months added--has to be done for both the cmonth and cyear functions. The number following the cMon does not correspond to a given month---just the number months that the report will show starting from the From_Date month....like first month, second month third month, etc..

I then tried to simply set each calc field's properties to hide it when a certain condition was met----did NOT work and have no idea why. I tried setting the calc object's visibility value (.T. or .F.) and also tried using object.show() and object.hide(). Neither worked.

So I ended up setting the font to white in order to hide what is to be the month column title when the following is True.

I first created a global numeric variable called No_Months that is set from the filter/parameter form---it tells me how many months are between the From_Date and the To_Date. It is not completely correct yet however--still have to tweak it a bit.

No_Months = abs(month(Var->rvTo_Date) - month(Var->rvFrom_Date) + 1)

then in each calc field's properties I used the following for the font expressions where the number in red corresponds to the number suffix of each cMon:

So the font equation for say cMon4 would be

case(Var->NO_Months<[COLOR="red"]4[/COLOR],"White on White". .T. , "Black on White"}

or simply use the genie--enter in white font on white background with Var->NO_Months<4 entered in the expression box.



NOW---on to the next part...ANY direction for this would be much appreciated too. I am going to try to use subReports to have the filtered records fall under each column month heading--have no clue if that is the way to go or not!

[MikeC]

Re: report monthly columns

The Variable No_Months should have the following script instead of what was originally posted.

if month(Var->rvFrom_Date) >= month(Var->rvTo_Date).and. year(Var->rvFrom_Date)<year(Var->rvTo_Date) then

    No_Months = convert_type(a5_eval_expression("=month(Var->rvTo_Date)+12-month(Var->rvFrom_Date)+1"),"N")
 else
    No_Months = convert_type(a5_eval_expression("=abs(month(Var->rvTo_Date)-month(Var->rvFrom_Date)+1)"),"N")
 
end if

The "convert_type" script came from AS and probably is not necessary.

[MoGrace]

Re: report monthly columns

Mike you are doing a fine job on figuring this one out! Wish I could be more help... ;)

[MikeC]

Re: report monthly columns

Hi,

Here's hoping someone can help me out...have been stuck on this since I first posted. I have made some progress in that I have been able to create the monthly columns and also can filter now the records associated with these months through the use of SubReports....I created a 1:N set with Prod (product) as parent and Allocation_tbl as child for this report to run off.

BUT the SubReports are causing a problem. When more than one product is chosen that has the same product type (which is the group break) the group does not break consistently. The numbers are correct but sometimes the same product will be listed separately when there is more than one record (instead of having just a single total) and sometimes another product of the same product type will not show up by name but will have its amounts as part of another same product types figures. If the SubReports are taken out then the group break works correctly.

I did find a thread with the same problem...but no resolution given and the person to contact is Gabriel (who is no longer available of course!!).

For the ambitious find attached a sample DB. Use the Product button to select and deselect products. Use the "Report" button on the upper right side of the Report_Filter form that I have opening as the startup form to access the report...small problem here as the first time using this button to call the report print preview it will not show all the filtered records--it will on the second time using it...may be a timing issue and an "xbasic_wait_for_idle()" may fix it although would be nice to not have to use that if another remedy can be found.

Here are the numbers associated with each product for easier diagnostics:

Cheetos - Puffs.......Qty 22...Amt.$11.00....4/1/07
..................................100...........33.00....4/2/07
..................................500.........111.00....2/16/07
.................................1000........1000.00....2/22/07

Pepsi - Diet................144...........44.00....3/14/07
................ ................100..........100.00....5/14/07

Dew...........................10............25.00....1/15/07

Del -Sour Cream&Onion..72............36.00....5/10/07


Each SubReport has its own filter and each Qty and Amt inside the SubReports are their own Calc field. On recommendations of other messageboard posts I have no detail section in the SubReports and the Calc fields are placed on the subreport Header section---and seems to be the only way to have them work correctly.

Everything seems to work just fine except for the group break---I have tried using both variables and/or calc fields in lieu of the SubReports but could not see how they could work...am still hoping and striving for a way to get this done the way I am doing it but any suggestion(s) will be greatly appreciated.

[MoGrace]

Re: report monthly columns

Mike,
For some reason the set you have defined for the report will not group properly so that you only see one heading for Prod_Type and then the list of Product_id's for that type. It seems to be related to your sub reports and the totals you are trying to achieve. Pepsi keeps wanting to appear twice for example, while monthly totals for Cheetos appears correctly.

I defined a set using Allocations with Prod as a lookup. I think what you need is a Prod_type field in the allocation table then your grouping should appear correctly with the report based just on the one table. Do not the Prod_code and Prod_type fields represent the same thing btw? I made the Prodcode_a field into a Prod_type field.

The report group is "Prodcode_a-Prod_id" and I suppressed blanks to get the group title to only print once. Also I put the calc totals in the Subreport footers instead of the header.

Then there is always the possibility of creating a temporary report table...

I played around a bit with your tabbed form too, using just one browse - see what you think. I did have to delete the double click events and all the wait_for_idle() references.

[MikeC]

Re: report monthly columns

Hi Robin!,

Thanks for helping out with this! I do believe it does just what I want it to do.

A couple of things...Just so you and others know. Each product has a
unique identifier through the field rules as auto-increment. Each also has
a Product Code usually obtained via a supplier or manufacturer and is user
entered and changeable as they do change even though the product may not be any different. The Product Type are simply categories of products such as Soda, Candy, Chips, Cookies, etc. that make it easier to separate like products--used for filtering records for viewing and also for reports.

I also kept the SubReport calc fields in the header section as it seems
to work just fine--unless you found something wrong with this and there is a
definite need to put them into the footer section????


Question:

The group break you used, "Prodcode-Prod_id"...how does this work??? I also would like to understand a bit more as to why a reverse set had to be used (I know...because it did not work the other way, right! ).


edit: Would be a bit less maintenance with only one browse that is "Always On Top"---are there any inherent problems with this method that you know of?

[MikeC]

Re: report monthly columns

Hi again,

Just a small update. I fixed the print preview not showing all the records until the second time of pushing the button...I changed the setting of the variables that show each filtered month's starting date to the OnPrintInit event of the report.

[MoGrace]

Re: report monthly columns

Hi Mike,
The only reason for putting the totals in the footer is consistency. In a main report that is where you would put the totals, so I just followed that in the subreport. In a main report it would require preprocessing of the records to put totals in the header. I tried various ways of getting the group break to work so that each prod_id would appear with the right totals. Combining the prodcode (which should be prod_type) with the prod_id put the records in the right sort order for the break and totals to appear correctly. Somehow this seems to work better with the calc fields which were forcing their own order in the other set; splitting the records etc.

As for the tabbed form with 3 browses of the same table, this was flickering and behaving badly for me. Since the display in each browse shows the same fields & order, it seemed simpler to use just the one browse and resort it. It retains the tabbed look and function you wanted and there are less multiple copies of similar objects on the form which reduces the complexity somewhat.

I don't know about you, but going back to a form to edit it months later, I find I forget much of what I originally did! Just trying to keep it simple ;) (K.I.S.S.)

[MikeC]

Re: report monthly columns

Thanks Robin...makes a bit more sense now...almost and maybe is a bug in how subreport's/calc's sort order is determined. If this is correct I wonder if messing with the sort order of the subreports would cure the original ailments...but then it works now so may just leave as is. Unbelievable how much time I have spent on this but with your help it does give me the satisfaction that it was finally accomplished.

Edit: one time it seemed to list a product twice when the records were from the same month...but now seems to work---I think it is my records that are messed up as some field values have had to be inserted manually as I now have some fields that have been added and have not been taken into account for when adding records. If there is a problem I will, of course, report back to the thread.

[MoGrace]

Re: report monthly columns

Mike,
There is an Aging report here somewhere for Accounts Receivable reporting. You might check the Learn Alpha site. It uses a similar method of calculating subtotals horizontally.

[MikeC]

Re: report monthly columns

HI,
Thanks for the info Robin.
I just checked out Learn_Alpha with a few search words with "report" bring up 70 results--nothing so far---will continue searching later on though as am interested if a different way of doing this--had done well over 10 searches before beginning this task (translates into well over 100 threads looked at) as would hate to do something someone else has already figured out and posted somewhere.

If its there I'll find it but if someone else runs across it before I do please post the link here!

[MikeC]

Re: report monthly columns

I found the link I believe.


http://msgboard.alphasoftware.com/al...t=Aging+report


But this example given by Peter Greulich is fairly basic (surprisingly!!! ) and has static columns and calculations whereas what I needed and now have is dynamic in that the monthly columns coincide with however many months are between the two dates chosen (up to 12 months worth) and only the months chosen show up...ie;. if only 5 months are within the range of the From and To Dates then only 5 month columns will show up and only the calcs for those months will show below them.

So maybe this is not the correct example?? :)

[MoGrace]

Re: report monthly columns

Mike,
I did a search on "aging" and came up with 53 hits.

[MikeC]

Re: report monthly columns

Just a final update to this thread with an almost finished report attached.