Weekly/Monthly/Annual Report 1

[dexterdg]

Regarding to my practice, i was thinking about putting a report generator. a monthly, weekly and annual reports. i know how to use data grouping on reports but i dont have an idea on grouping it with these selections. is there any previous post related to this question?

I've been researching and i found something like this:[for weekly report]

 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59'

i dont quite get it, it uses timestamp, i only got dates. and i cant apply it also.

Thanks
Dexter

 

[Olaf Doschke]

Are you talking about SQL Server? Because what you post is TSQL and you also specified it. Datediff should work equally for dates and datetimes in SQL Server.
I would use a more canonical way to put the datetimes in the where clause. Independant on any locale specific settings SQL Server understands datetimes in strings you format as 'YYYYMMDD HH:MM:SS' and so you'd use
WHERE mydatefield >= '20110530' and mydatefield < '20110606'. That also makes use of the fact T-SQL automatically adds 0:00:00 AM, if you don't specify a time portion. And use < instead of <= and you also catch all records, which may be in the last second of 2011/06/05 between 23:59:59 and 0:00:00 AM of the next day. The < instead of <= makes sure you don't fetch records of 2011/06/06, not even at exactly 0:00:00. Usually it's just a theoretical case anyway, even the records in the last second of a day, if it's an app used during usual working times.

Bye, Olaf.

 

[Olaf Doschke]

I don't even get, what you want to achieve with these nested DATEADD expressions. Finally you get a shifted date. In the first expression the inner DATEDIFF computes the weeks between 01/01/1900 and a date of your data, and then adds this number of weeks to 01/01/1900, which would arrive at the same date again, if the week difference was exact eg 2434.345 weeks or so. Without testing I assume datediff on weeks will round this, so the final date will be the monday of the same week as the date in mydatefield. that's because 01/01/1900 is a monday. You could also use any other reference date, that was a monday, but it's neat.

Next expression get's the same mydatefield value and computes a date from it that's the tuesday of the same week, the expression differs in adding one more week, so it get's the Monday of the next week in the first place, and finally -1 is subtracted. I don't know if this would work at all in T-SQL, because you need to use the DATEDIFF function to compute with dates or datetimes, you can't subtract a number from a datetime, like you can in VFP. Maybe it'll work and subtract one second or one day. If, it does, then either you get to the sunday of the same week at midnight or at the end of sunday at 23:59:59.

So how does this help in annual, monthly, weekly reports?
In foxpro, if you want to group by the WEEK, group by WEEK(datefield), if you want to group by Month, group by MONTH(datefield) and if you want to group by year... Well, you get the idea. WEEK has two further parameters, take a look at them, as VFP is an US product, the default is it puts weekstarts at Sunday instead of Monday, It wouldn't matter much, how you choose what's the first week of a year, a week is still a week and the same week number will group data of a week into the same group.

Bye, Olaf.

 

[Mike Lewis]

Hi Dexter,

I'm also confused about what you are trying to achieve. Before we go any further, could you clarify:

1. What database are you using? VFP? SQL Server? Other?

2. If SQL Server, which version (the reason I ask is that recent versions have a distinct date data type, so you don't need to jump through the datetime hoop)?

3. What exactly are you trying to group on? It looks like you are concerned with week numbers, but I can't see exactly how this works. Can you explain how you want the grouping to work.

If we can clear up those points, I'm sure we can help you to the answer.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[dexterdg]

I cant really express my self that effectively in writings but I'am trying my best . Its vfp free table/s and i included that codes thinking to base the logic of getting the reports. This is for the user to be able to select in particular the scope of the report he wanted, weekly, monthly, or annually. Yes the script computes the number of weeks there is bet. the two given example date because its how i would want the report to be grouped and so as by month and by year.

But i got what sir Olaf is trying to say. I'm going to try it out, maybe adding a new column to handle that and do the conventional way of grouping in the report. More ideas will be much appreciated.

Thanks
Dexter

 

[Olaf Doschke]

No, you haven't understood what I said. You also haven't understood the T-SQL you found somewhere, obviously. Because the "script" computes new dates, it doesn't group data. A grouping would be done by either defining report groups with these new fields or by GROUP BY, if you're about to computes sums (SUM) or average values (AVG) or minimum/maximum (MIN/MAX) of the values of a group. What was most probably in the original T-SQL script and query was a GROUP BY EveryMonday. The mere computation of EveryMonday does nothing for grouping, it just computes a further column with a date, that's equal to the monday of the same week as mydatefield. To be able to group by the same week of the same year, the expression DATEDIFF(WEEK, '19000101', mydatefield) As WeeksSince19000101 would be sufficient.

What you would do in VFP is what I already said, and it looks much more natural than that DateDiff "orgy", not even that shorter expression. If you have a field dDate in your DBF you can do this SQL:

Select Count(*),Week(dDate) From Table Group By 2

Caution: That will group together data of the same week number but of other years into one group. But it demonstrates, how easy the computation of a week is. It's sufficient, if you filter the data to report to one year, eg by REPORT FORM ... FOR Year(dDate)=2012

There is another thing you could do, and that even needs no computed additional field in your DBF, nor in a query, you simply define a group band in your report and set it up to group by WEEK(dDate), you cannot only group by a field of the DBF, you can group by any expression.

Then there is another thing you might mean with a weekly report, which shouldn't group by weeks or aggregate data of a week, but simply output a list of data for a certain, this is simply done by filtering dDate for dates between yeasterday and last thursday, in general between DATE()-1 and DATE()-7, so eg REPORT FORM ... FOR dDate<=DATE()-1 and dDate>=DATE()-7 or you do a query with WHERE dDate Between DATE()-7 AND DATE()-1.

That depends on what you want to report weekly, monthly, annually. In an annual report it's more likely you want a aggregated sum, in a weekly report, you might simply want to list data of the last week for overview, without aggregating it, without summing it, for example. But you don't need to change your DBF, in no case, the source data is the source data, don't add columns you only need temporarily to a DBF, only in queries and their results.

Bye, Olaf.

 

[Mike Lewis]

Dexter,

You say you are using VFP free tables.

In that case, forget DATEADD() and DATEDIFF(). Those are part of the T-SQL language, which is the language of Microsoft SQL Sesrver, which is different from Visual FoxPro. Those functions won't work in VFP. Nor do you need them. In VFP, we can simply use the plus and minus operators to add or subtract a number of days from a date (but not a datetime).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[dexterdg]

I got it now for sure, thanks. achieved the output i wanted in the three selection[weekly,monthly,annnually].

In the item group, -the list of items per week-. i would like compute the totals of every item category in the group. how can i reference them to compute for the totals? e.g. in week # 1: reimbursed are the ff: 3-medicine(the names of the items), 4-food(...), 1-funiture(...), 2-applinace(...). i would like to get the total amount reimbursed for ea. category.

Figure
Week # 1
food # 1 ......... 50$
medicine # 1 ..... 5$
food # 2 ......... 10$
appliance # 1 .... 100$
furniture ........ 200$
food # 3 ......... 9$
(.......)
TOTALS: Food: [Amt.] Appliance: [Amt.] Medicine: [Amt.] (...)
thanks
Dexter

 

[dexterdg]

Is it advisable to create a new table for the sum totals of the category per transaction and just call it out on the ReportWriter? or there is a work around to that?

thanks
Dexter

 

[JRB-Bldr]

i wanted in the three selection[weekly,monthly,annually].

Are you saying that you want ALL 3 totals [weekly,monthly,annually] shown in the same single report?

Or do you want the user to be able to choose any ONE of the 3 choices
* weekly,
* Or monthly,
* Or annually
And have that single total value shown in the single report?

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Sorry, I even didn't follow your description of data and how you want to group it. Can you give some more detailed list of the souce data, not just the result you want?

Bye, Olaf.

 

[dexterdg]

sir Mike,

I have three different reports, one for each category(weekly, monthly, annually).

sir Olaf,

I do queries like:
SELECT * FROM tbl_customer JOIN tbl_item ON tbl_item.pk = tbl_customer.pk WHERE tbl_customer.dayt >= dateFrom AND tbl_cutomer.dayt <= dateTo ORDER BY tbl_customer.dayt INTO CURSOR forprinting

REPORT FORM rptWeekly
or
REPORT FORM rptMonthly
or
REPORT FORM rptAnnualy

And VFP ReportWriter will be the one to group it accordingly, i selected all the needed records and it will arrange it neatly. With corresponds to the output i wanted, i made it with sir Olafs' idea about grouping. What i dont know is how to SUM UP the items in the same category (tbl_items) because i am making a summary of purchased item per category per customer - weekly/monthly/annually.

Thanks
Dexter

 

[JRB-Bldr]

Since you are doing 3 separate reports you could easily collect your ForPrinting data cursor 3 different times - each with the type of separate data you need.

Or you could just use a SUMMARY Band in your 3 separate VFP Report Form's and display a calculated SUM(s) from the associated ForPrinting data cursor field data.

On the on-line VFP Tutorial page:

http://www.garfieldhudson.com/FreeVideos.aspx

there are 2 videos you might want to look at.
Basic Reporting - Pt. 1
Basic Reporting - Pt. 2

Good Luck,
JRB-Bldr

 

[dexterdg]

Oh.. i so happend to make it.
I used:
IFF(category = "[category]", itm_amount,0.00)
SET Calculation type to SUM
RESET Based On: Group2(Items) && Group1 is for customers
this happens to add up all the amount when .T.

I added up a "OVER ALL TOTAL" on the summary band to sum up all categories per category.

But another problem emerges:
How can SUM UP all those categories per category? "THE SUMMATION OF THE SUMMATION". I cant apply my previous solution to this thou its also a summation.

Thanks
Dexter

 

[dexterdg]

I tried using variables and modifying
IFF(category = "[category]", itm_amount,0.00)
to
IFF(category = "[category]", furniture = itm_amount,0.00)
where furniture is the variable.
variable(furniture)'s :
Value to store : itm_amount
Initial Value : 0
Reset Value Based On: Group2
Calculation type : SUM

pass the variable and VIOLA! it doesn't work! it also SOMETIMES outputs TRUE on the total "furniture"(or what ever category it is) per customer

ALSO:
correction to what i previously stated
Group1: is the group per week/month/year on the report
Group2: is the group per customer to output the corresponding items for ea. customer

1st Summation
-SUM of categories per customer [DONE]
2nd Summation
-SUM of ALL CATEGORIES PER CATEGORY [OBJECTIVE]

Where did it go wrong? or do i have the wrong Value to Store? if so, what will i put there?

Thanks
Dexter

 

[dexterdg]

oh silly me.. i just found out that i dont need variables. just have to use IFF(category = "[category]", itm_amount,0.00) again and set its Reset Value Based on: Report! haha!

Thanks
Dexter

 

[Olaf Doschke]

Yes, you figured out there are levels you can use to make a group total or report total.
But it's much easier to nest groups and add a group band for category instead of using IIF(category...), you can then simply sum based on that sub group.

Bye, Olaf.

 

[dexterdg]

sir Olaf,

You mean like adding a new detail band and customize it?
I'll go check that out, my hunch is its about multiple detail band topic?

Just for reference:
disregarding the previous parameters, with a basic report form. If i would be having a table and one of its column is prices. how can i add it all up? cant use IIF() for it doesn't need a condition?

thanks
Dexter

 

[Olaf Doschke]

The SUM all prices of the whole report, of the whole part of the table, which is printed? Why would you need IIF() for that? IIF is just needed, when you want to do something conditionally, eg only sum all category X and no other category prices.

But you can sum on the level of a report, on the level of a grou, or on sub levels. I think you didn't get what nested means. In the report properties where you already added your group for annual/monthly/weekly grouping, you can add another group with the Add button and specify category as the "Group Records by Expression".

Now in the variables tab you can Reset value based on Groupp:category. Calculation type Sum. And of course the Value to Store will then be itm_amount.

And of course you need to sort your report cursor by time and category as a second order.

Bye, Olaf.

 

[JRB-Bldr]

my hunch is its about multiple detail band topic

Based on your saying that the Weekly, Monthly and Annually reports are all separate individual reports, I don't see why you would need Multiple Detail Bands.

Your Group band will separate out the appropriately desired Groups - the Weeks, the Months, or the Years.

The Group Bands surround the Detail Bands and can have their own Before-Detail and After-Detail area on the Report Form.

You would put your Summation results into the After-Detail area so that each Group's SUM values could be separately shown before going on to the next Group's data.

Did you spend any time looking over the Tutorial Videos I recommended you look at?
It might help your understanding about how to create and use your VFP Report Form's.

Good Luck,
JRB-Bldr