The value of FoxPro User Groups is tremendous. I've learned so much from them, made so many contacts, improved my skills, learned about many third-party & productivity tools, made lots of friends, and even obtained a job through them.
Sadly, FoxPro User Groups are a dying breed. Our local group (GRAFUG - Grand Rapids Area FoxPro Users Group) is struggling to stay alive. Attendance has dropped and we're trying to figure out how to attract more people. The core people that attend each month don't want to let it go. We consider the meetings extremely valuable, and a resource that must be kept. We'd like to "market" the group and make sure all FoxPro developers in our area are aware of the group.
I'm asking for suggestions from everyone on places to post a blurb about our group in hopes of attracting new attendees. If you're running a successful FoxPro User Group, I'd like to hear from you too. What do you do to advertise the group. I'm looking for any and all ideas on how to boost our local user group and keep it alive!
Sunday, January 27, 2008
Saturday, January 19, 2008
They Stole My Idea!
I'm all about efficiency, and many of you that know me have heard me talk about my "invention" to help me be more efficient. It seems I can never find time to exercise, even though I have a treadmill and a home gym in my house. My invention was to build a desk that attaches to my treadmill so that I can work on the laptop at the same time I'm walking. Think about it .. I have a wireless internet connection .. my printer is hooked up through the wireless connection .. so I have everything I need. It's a great idea and it really works well. At first I thought there might be too much vibration, but it turns out that the desk I built is very stable. I just have to remember to put wrist bands on so the sweat doesn't run onto my keyboard.
So, a while back Craig Berntson sent me a link to a treadmill desk for sale on the market. There wasn't much information, and nowhere was a price. You had to send away for information, which always leaves me thinking the price is way too much.
Move forward to last night when I was reading the recent issue of Reader's Digest. They have an article called "The New Slow Burn At Work" and it shows a picture of a treadmill desk offered by Steelcase called "The Walkstation". Now for those of you that don't know, western Michigan is known for furniture manufacturers. Steelcase, Herman Miller, and Haworthe are all here. I think a spy overhead me talking about this idea and ran back to their boss at Steelcase and presented the idea as their own.
I will admit their version looks much more professional than mine and offers more workspace. However, it comes with a price tag of $4000. Mine cost me less than $500 for the treadmill and the desk combined!!
I should have patented the idea! Darn .. my financial independence has been dashed again!
So, a while back Craig Berntson sent me a link to a treadmill desk for sale on the market. There wasn't much information, and nowhere was a price. You had to send away for information, which always leaves me thinking the price is way too much.
Move forward to last night when I was reading the recent issue of Reader's Digest. They have an article called "The New Slow Burn At Work" and it shows a picture of a treadmill desk offered by Steelcase called "The Walkstation". Now for those of you that don't know, western Michigan is known for furniture manufacturers. Steelcase, Herman Miller, and Haworthe are all here. I think a spy overhead me talking about this idea and ran back to their boss at Steelcase and presented the idea as their own.
I will admit their version looks much more professional than mine and offers more workspace. However, it comes with a price tag of $4000. Mine cost me less than $500 for the treadmill and the desk combined!!
I should have patented the idea! Darn .. my financial independence has been dashed again!
Sunday, January 13, 2008
Press Release: MBS attains Microsoft Certified Partner status
Memorial Business Systems, Inc. (MBS) has attained Certified Partner status in the Microsoft Partner Program. To become a Certified Partner, MBS had to demonstrate expertise with Microsoft technologies and demonstrate its ability to meet customers’ needs with exceptional expertise. Microsoft Certified Partners receive a rich set of benefits, including premier access, special training, and the highest levels of support, giving partners a real technologically competitive advantage.
Memorial Business Systems’ software and services are designed to improve the operational efficiency and effectiveness of cemetery and combo operations. MBS software represents the leading edge of technology ensuring against customer obsolescence.
“We are obviously pleased to have attained Certified Partner status. This is confirmation that our software is advantageous for cemeteries at all levels and as flexible and easy to use as we say they are,” said Fred Miller, President of MBS. “We put out products that work from the get-go, and our customers are clearly appreciative. Our developmental talent gets the credit.”
“Microsoft recognizes MBS as a new Certified Partner for demonstrating its expertise in providing customer superior satisfaction using Microsoft products and technology.” said Allison Watson, corporate vice president of the Worldwide Partner Group at Microsoft Corp.
Memorial Business Systems’ software and services are designed to improve the operational efficiency and effectiveness of cemetery and combo operations. MBS software represents the leading edge of technology ensuring against customer obsolescence.
“We are obviously pleased to have attained Certified Partner status. This is confirmation that our software is advantageous for cemeteries at all levels and as flexible and easy to use as we say they are,” said Fred Miller, President of MBS. “We put out products that work from the get-go, and our customers are clearly appreciative. Our developmental talent gets the credit.”
“Microsoft recognizes MBS as a new Certified Partner for demonstrating its expertise in providing customer superior satisfaction using Microsoft products and technology.” said Allison Watson, corporate vice president of the Worldwide Partner Group at Microsoft Corp.
Tuesday, January 1, 2008
How Do I ... Create some complicated data groups and build a recap?
I recently had to create a very complicated report for a client that covers several different techniques. I thought it would be good to share this modified sample with everyone.
At first, the report sounded simple (don't they all!) Create a list of orders, sorted and subtotaled by customer and each customer should start on a new page. That's easy, I can handle that. Put the orders in the detail band. Create a data group on the Customer and mark it to start on a new page. Put subtotals for the customer in the Data Group Footer band.
Oh .. the client says .. the list of orders per customer can get quite long. Can we have ledger-style shading or a line every so many lines? Sure, I think I can handle this. Oh ... the client says again ... we'd also like subtotals at the bottom of each page showing the totals of just that page. That should be easy .. I can use the page footer band. Oh wait .. as I think that through some more, it's not going to work because on the last page for the customer, the data group footer band would print and then the page footer would show up at the bottom. That's not cool. Hmm ... these two requirements combined make things a little sticky .. but I'm sure I can come up with the answer.
Then comes the killer requirement .. Remember those page subtotals we want at the bottom of each page .. well .. at the end of the customer we'd also like a recap that shows each one of those subtotals like the following:
Page 1, $999.99
Page 2, $999.99
Page 3, $999.99
Yuk .. I really don't like that one especially combined with the ledger-style and page subtotals. Now I'm really going to have to put some thought into this report. I think my head is spinning!
For this example, I'm using VFP 9 SP2 (although it should work with previous versions because I'm not using a ReportListener or any of the new features.) I'll use the TasTrade data stored in C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data. I wanted to be flexible with the number of lines per ledger section and the number of lines per page so I decided to control them with a variable. That way, if the customer changes their mind down the road I can easily change it.
I also didn't want to mess around with trying to build the data for the recap ahead of time. Again, I was thinking down the road about using this technique for other situations and if I were to use it on a report with stretchable fields, I didn't want to use hard-coding to "count" each record and figure out the appropriate page it falls on. I just wanted to let it flow and let the report build the recap data for me no matter how many records appeared on a page. I also didn't want to have to hard-code all the variables to be totaled. I wanted some generic code that totals all the numeric or currency fields.
Let's start by looking at the final report as shown below in Figure 1. The first page shows the records for the first customer. The second page shows the "recap" for that customer, which only contains one page. The third and fourth pages do the same thing for the second customer. The fifth through seventh pages show the details for the third customer and the last pages shows the "recap" of each of the three previous pages which belong to the third customer.
Figure 1: The final printed report.
Next, let's look at the simple program that I use to create the data and print the report. Notice that I use two variables to control the groupings. I could have hard-coded this in the report itself, but this is more flexible.
Besides the above program, I also need a function that I'm going to call from the report itself. This function will update the cursor that is driving the report by either adding a new record for a particular page, or adding to the numeric fields for the given page. Note that I have to take great care to move the record pointer back to where it was when this function was called. Otherwise, you'll make a mess of the running report. Add the following function to the bottom of the program that runs the report so it's available to the report.
Now let's get to the definition of the report, which is the meat of this solution. Figure 2 shows how the report definition will look when we're done.
Figure 2: The report definition.
Start by creating 3 report variables:
At first thought, it might look like I made a typo above because none of the variables reset at the Customer group or Page group. But trust me .. it's not a mistake. Just bear with me and it will make sense soon.
The last variable is used to call the function we created earlier. It looks a little complex but that's just because I made the function generic so I can reuse it on other reports. You could simplify it by taking away all the parameters and hard-coding the function.
Next, create 4 different Data Groups:
Expand the #1 Data Group Header band and put the Customer field.
Put the detail fields in the Detail band and lay them out as you desire.
Expand the #4 Data Group Header band and add a horizontal line to separate the chunks of detail records. I made my light gray so it's not so distracting.
Expand the Data Group Footer bands for #3 and #4 so you can add some totals. Copy your numeric fields from the Detail band into the two different group footer bands. Next, change the Calculate option on each to SUM and reset on the applicable group.
Now go ahead and add whatever column headings you want. You can use Print When logic of RecType = '1' if you want to print something for just the regular data. Use RecType = '2' if you want to print something on just the page recaps.
At this point you might think we're done, but we're not. Remember when we defined the report variables I mentioned that we are not resetting them at the appropriate data groups. The reason is that whenever you try to alter the variable that is being used to control the groups, the VFP Report Writer gets completely confused. You end up with more breaks than you want and often times you get pages with no detail records and just some totals.
So here's the trick behind this whole report. We need to reset the report variables ourselves. Well, actually, we aren't resetting them, we're fudging them. The problem is that if we reset them to zero, we get the extra breaks I just mentioned. So instead of resetting them to zero, we fudge them to a value that makes the next page start at a clean point.
We are going to alter these variables in the On Entry and On Exit Expressions of the #2 Group Footer band as follows:
On Entry: _VFP.SetVar('rnLinesForCustomer', rnLinesForCustomer- MOD(rnLinesForCustomer, nMaxLinesPerPage))
The above "expression" resets the rnLinesForCustomer variable to a value that is equal to what the count would be if the page was completely full of lines, without resetting it for each page. So if we allowed 10 lines per page, at the end of the first page we reset it to 10, at the end of the 2nd page we reset it to 20, and so on. Regardless of how many lines actually printed, we just make it think that full pages have been printed. By doing this, we avoid the funky extra breaks that happen when you try to zero out the variable.
On Exit: _VFP.SetVar('rnLinesOnPage', rnLinesOnPage- MOD(rnLinesOnPage, nMaxLinesPerLedger))
The above "expression" does the same thing for the ledger breaking as the previous one did for the page breaking. It really doesn't matter which one is in the On Exit and which one is in the On Entry. I just needed to reset two variables and this gave me a place to do each one.
That's it. We're done. Simple, right? Well .. of course it's simple when you already know the answer. Trust me .. it wasn't that simple for me to come to this conclusion, but now you can benefit from my troubles. If you want the sample program and report I used to create this blog, just send me an email and I'll gladly send it to you.
At first, the report sounded simple (don't they all!) Create a list of orders, sorted and subtotaled by customer and each customer should start on a new page. That's easy, I can handle that. Put the orders in the detail band. Create a data group on the Customer and mark it to start on a new page. Put subtotals for the customer in the Data Group Footer band.
Oh .. the client says .. the list of orders per customer can get quite long. Can we have ledger-style shading or a line every so many lines? Sure, I think I can handle this. Oh ... the client says again ... we'd also like subtotals at the bottom of each page showing the totals of just that page. That should be easy .. I can use the page footer band. Oh wait .. as I think that through some more, it's not going to work because on the last page for the customer, the data group footer band would print and then the page footer would show up at the bottom. That's not cool. Hmm ... these two requirements combined make things a little sticky .. but I'm sure I can come up with the answer.
Then comes the killer requirement .. Remember those page subtotals we want at the bottom of each page .. well .. at the end of the customer we'd also like a recap that shows each one of those subtotals like the following:
Page 1, $999.99
Page 2, $999.99
Page 3, $999.99
Yuk .. I really don't like that one especially combined with the ledger-style and page subtotals. Now I'm really going to have to put some thought into this report. I think my head is spinning!
For this example, I'm using VFP 9 SP2 (although it should work with previous versions because I'm not using a ReportListener or any of the new features.) I'll use the TasTrade data stored in C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data. I wanted to be flexible with the number of lines per ledger section and the number of lines per page so I decided to control them with a variable. That way, if the customer changes their mind down the road I can easily change it.
I also didn't want to mess around with trying to build the data for the recap ahead of time. Again, I was thinking down the road about using this technique for other situations and if I were to use it on a report with stretchable fields, I didn't want to use hard-coding to "count" each record and figure out the appropriate page it falls on. I just wanted to let it flow and let the report build the recap data for me no matter how many records appeared on a page. I also didn't want to have to hard-code all the variables to be totaled. I wanted some generic code that totals all the numeric or currency fields.
Let's start by looking at the final report as shown below in Figure 1. The first page shows the records for the first customer. The second page shows the "recap" for that customer, which only contains one page. The third and fourth pages do the same thing for the second customer. The fifth through seventh pages show the details for the third customer and the last pages shows the "recap" of each of the three previous pages which belong to the third customer.
Figure 1: The final printed report.
Next, let's look at the simple program that I use to create the data and print the report. Notice that I use two variables to control the groupings. I could have hard-coded this in the report itself, but this is more flexible.
*-- Build the data
SELECT '1' AS RecType,
Orders.Customer_ID, ;
Customer.Company_Name, ;
Orders.Order_Number, ;
Orders.Order_Date, ;
CAST(Orders.Discount AS N(14,2)) AS Discount, ;
CAST(Orders.Freight AS N(14,2)) AS Freight, ;
CAST(NVL(SumLines.nPrice, 0) AS N(14,2)) nPrice ;
FROM 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\Orders' ;
LEFT OUTER JOIN 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\Customer' ;
ON Customer.Customer_ID = Orders.Customer_ID ;
LEFT OUTER JOIN ;
(SELECT OrdItems.Order_ID, ;
SUM(OrdItems.Unit_Price * OrdItems.Quantity) AS nPrice ;
FROM 'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Tastrade\Data\OrdItems' ;
GROUP BY OrdItems.Order_ID ;
) SumLines ;
ON SumLines.Order_ID = Orders.Order_ID ;
INTO CURSOR tmpReportData READWRITE
*-- Index the data
SELECT tmpReportData
INDEX ON Customer_ID + RecType + Order_Number TAG SortKey
GOTO TOP
*-- Set some variables to control # of lines
*-- for each section and # of lines per page.
nMaxLinesPerLedger = 2
nMaxLinesPerPage = 6
*-- Run the report
REPORT FORM ComplexGroups_Recap TO PRINTER PROMPT PREVIEW
Besides the above program, I also need a function that I'm going to call from the report itself. This function will update the cursor that is driving the report by either adding a new record for a particular page, or adding to the numeric fields for the given page. Note that I have to take great care to move the record pointer back to where it was when this function was called. Otherwise, you'll make a mess of the running report. Add the following function to the bottom of the program that runs the report so it's available to the report.
************************
FUNCTION AddToPageTotals
************************
LPARAMETERS tcField, txPage, tcSeek, tcIndex
LOCAL loRecord, lnRecNo, lcAlias, lcSeek, lx
*-- Don't do this on the total records themselves!
*-- (Can you say "infinite loop")
IF RecType = '2'
RETURN
ENDIF
*-- Remember where we are
lnRecNo = RECNO()
lcAlias = ALIAS()
SCATTER NAME m.loRecord
m.loRecord.RecType = '2'
m.loRecord.&tcField = txPage
lcSeek = EVALUATE(STRTRAN(m.tcSeek, 'XTMPX', 'm.loRecord'))
IF SEEK(m.lcSeek, m.lcAlias, m.tcIndex)
*-- add the amount fields to the existing record
lnFields = AFIELDS(laFields)
FOR ln = 1 TO lnFields
lcField = laFields[m.ln, 1]
lx = EVALUATE(m.lcField)
IF VARTYPE(lx) $ 'NY'
REPLACE (m.lcField) WITH NVL(EVALUATE(m.lcField),0) + NVL(m.loRecord.&lcField,0)
ENDIF
ENDFOR
ELSE
*-- Add the record for this page
INSERT INTO (m.lcAlias) FROM NAME m.loRecord
ENDIF
*-- Go back to the "detail" record that we were processing
GOTO m.lnRecNo
Now let's get to the definition of the report, which is the meat of this solution. Figure 2 shows how the report definition will look when we're done.
Figure 2: The report definition.
Start by creating 3 report variables:
- rnLinesOnPage
- Value to Store = 0
- Reset = Report
- Calculation Type = Count
- rnLinesForCustomer
- Value to Store = 0
- Reset = Report
- Calculation Type = Count
- Dummy
- Value to Store = AddToPageTotals('Order_Number', TRANSFORM(_PageNo, '999999'), 'XTMPX.Customer_ID + XTMPX.RecType + XTMPX.Order_Number', 'SortKey')
- Reset = Report
- Calculation Type = None
At first thought, it might look like I made a typo above because none of the variables reset at the Customer group or Page group. But trust me .. it's not a mistake. Just bear with me and it will make sense soon.
The last variable is used to call the function we created earlier. It looks a little complex but that's just because I made the function generic so I can reuse it on other reports. You could simplify it by taking away all the parameters and hard-coding the function.
Next, create 4 different Data Groups:
- 1: Customer_ID, New Page Number 1, Reprint group header on each page
- 2: RecType, New Page
- 3: INT(rnLinesForCustomer / nMaxLinesPerPage), New Page
- 4: INT(rnLinesOnPage / nMaxLinesPerLedger)
Expand the #1 Data Group Header band and put the Customer field.
Put the detail fields in the Detail band and lay them out as you desire.
Expand the #4 Data Group Header band and add a horizontal line to separate the chunks of detail records. I made my light gray so it's not so distracting.
Expand the Data Group Footer bands for #3 and #4 so you can add some totals. Copy your numeric fields from the Detail band into the two different group footer bands. Next, change the Calculate option on each to SUM and reset on the applicable group.
Now go ahead and add whatever column headings you want. You can use Print When logic of RecType = '1' if you want to print something for just the regular data. Use RecType = '2' if you want to print something on just the page recaps.
At this point you might think we're done, but we're not. Remember when we defined the report variables I mentioned that we are not resetting them at the appropriate data groups. The reason is that whenever you try to alter the variable that is being used to control the groups, the VFP Report Writer gets completely confused. You end up with more breaks than you want and often times you get pages with no detail records and just some totals.
So here's the trick behind this whole report. We need to reset the report variables ourselves. Well, actually, we aren't resetting them, we're fudging them. The problem is that if we reset them to zero, we get the extra breaks I just mentioned. So instead of resetting them to zero, we fudge them to a value that makes the next page start at a clean point.
We are going to alter these variables in the On Entry and On Exit Expressions of the #2 Group Footer band as follows:
On Entry: _VFP.SetVar('rnLinesForCustomer', rnLinesForCustomer- MOD(rnLinesForCustomer, nMaxLinesPerPage))
The above "expression" resets the rnLinesForCustomer variable to a value that is equal to what the count would be if the page was completely full of lines, without resetting it for each page. So if we allowed 10 lines per page, at the end of the first page we reset it to 10, at the end of the 2nd page we reset it to 20, and so on. Regardless of how many lines actually printed, we just make it think that full pages have been printed. By doing this, we avoid the funky extra breaks that happen when you try to zero out the variable.
On Exit: _VFP.SetVar('rnLinesOnPage', rnLinesOnPage- MOD(rnLinesOnPage, nMaxLinesPerLedger))
The above "expression" does the same thing for the ledger breaking as the previous one did for the page breaking. It really doesn't matter which one is in the On Exit and which one is in the On Entry. I just needed to reset two variables and this gave me a place to do each one.
That's it. We're done. Simple, right? Well .. of course it's simple when you already know the answer. Trust me .. it wasn't that simple for me to come to this conclusion, but now you can benefit from my troubles. If you want the sample program and report I used to create this blog, just send me an email and I'll gladly send it to you.
Subscribe to:
Posts (Atom)
