Page Index Toggle Pages: [1] 2  Send Topic Send Topic Print Print
Hot Topic (More than 10 Replies) Columns in Reports (Read 3827 times)
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Columns in Reports
Apr 9th, 2005 at 3:27am
Print Post Print Post  
Hi -

I need to design a report that contains a person's full name followed by a table number.

This is easy enough, except that I want it to contain two columns per page ... in alphabetical order by last name, sorted from top left to bottom left and top right to bottom right such as:

    James A .... 28                Michael K .... 21
    Mary B .....  34                Spencer L ....  4
    Harvey C ...   2                Joan P ........ 13

Is Sesame currently capable of handling the 2-columns per page printing?

The ellipsis (series of dots) is not necessary.  I put them here primarily to align the columns; however, since I brought them "into the picture", is there a way to include them, too, should I so desire?

Thanks!
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Columns in Reports
Reply #1 - Apr 9th, 2005 at 5:57am
Print Post Print Post  
Spencer, hace you considered exporting as a Word Merge document that may be easier to make with columns?  That is not an uncommon task in Word.



Re the ellipses, you can use
@Text( (Width of Column - Length of name string -Length of table name), ".")   Typical of what is done in phone directories.
  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Columns in Reports
Reply #2 - Apr 9th, 2005 at 3:21pm
Print Post Print Post  
Bob -

In my mind - and past experience - one of the beatutiful functions of databases is that you can take the information you collected and print it out in nearly any format you want, with very little effort.  So, I suppose that I will just continue "biting my nails" until the new report generator/writer is released.  lol

To have to go to an export process for something so basic seems a bit 'trying.'

Nevertheless, even if I did want to do that, could you tell me how it's done?  I did find an option to export to MS Excel; however, to put it in to MS Word, I can only do it with copy and paste.

As for the ellipses, I suppose I was hoping there was a built-in "option"; however, your suggestion sounds viable; thanks.
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Columns in Reports
Reply #3 - Apr 9th, 2005 at 3:54pm
Print Post Print Post  
Make a Word Merge document that just has the elements you want:

<Name><ellipses><TableNumber>

Or only one field if you combine them in Sesame first:
<Name...............TableNumber>

Now, in Word, select Format, Columns.  Select the nunber of columns widths, etc. and Save as your Merge Document.

Now just treat as any other Word Merge Document.
--------------------------------------
Or you can use Word to make the ellipses:

1.  Create the Merge fields
<Name> <TableNumber>

2.  Set up your columns and widths.

3.  Move between the fields, use the ruler at the top, and position slightly to the right of the right margin for the column, and insert Tab, Right Alignment, chose leader #2 for dots.  (Easier to do vs. explaining, experiment in word with any two words, don't need to use merge fields.
  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Columns in Reports
Reply #4 - Apr 9th, 2005 at 4:00pm
Print Post Print Post  
Hi Bob -

OK, I thought you were saying to export the report itself into Word.  Now I understand (hopefully, correctly) that instead of generating a report, I will just export the data from my current retrieve spec and then import that into a Word merge document.

Thanks!
« Last Edit: Apr 11th, 2005 at 2:04pm by SpencerWulwick »  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Columns in Reports
Reply #5 - Apr 9th, 2005 at 7:27pm
Print Post Print Post  
That is correct Spencer. 

I am sure you will have to play with page length,etc.  From what you are describing, maybe treat it like a label, and ID how many labels on the page?

Sorting by last name will have to happen in Sesame with Last Name as Invisible element, or just not exported.  Need to define and sort the record set in Sesame, then do the Merging export.

  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Columns in Reports
Reply #6 - Apr 11th, 2005 at 12:52pm
Print Post Print Post  
Quote:
In my mind - and past experience - one of the beatutiful functions of databases is that you can take the information you collected and print it out in nearly any format you want, with very little effort.  So, I suppose that I will just continue "biting my nails" until the new report generator/writer is released.  lol

To have to go to an export process for something so basic seems a bit 'trying.'


Spencer,

I don't know of many dedicated database products that can produce the report you describe without jumping through hoops. Columns of data that run down, then across, are very difficult to generate, especially when they have to do it by page. Q&A doesn't have an easy option to do it. I don't believe that Access has an easy option do it. Access does have a down-then-across option for subreports, but it's known to be broken. In most cases, while it may be doable, it requires some doing. This is not a "basic" capability, as far as a report rendering engine is concerned.

Filemaker has a "directory" layout that can do this nicely, but they seem to be an exception. For the most part, you need a word processor to manage this kind of layout.

Alternately, you can use PrintString and create this output with a Mass Update. Let us know if you would like us to put together an example for you.

 
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Columns in Reports
Reply #7 - Apr 11th, 2005 at 1:59pm
Print Post Print Post  
Hi Erika -

I would very much appreciate your help if you feel that a PrintString report would accommodate my needs.

Just so you know, MS Access can produce this report very easily (and does not require use of a subreport).  All you do in your Page Setup is tell it the number of columns you want, the row and column spacing, the width of the column and whether you want the column layout Down, then accross or Accross, then down. Since it has been available - and I've been using it - for years, now, I guess I just started taking it for granted and assumed it was a somewhat "standard" feature. Certainly, it is a desirable one and I would love to see it in Sesame.  

Here's a link where you can see a picture of the report I have been using. http://www.middlevillagelove.com/isapi.dll?c=Content&htx=View&siteid=1C3CAM&cont...

Thanks!
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Columns in Reports
Reply #8 - Apr 11th, 2005 at 2:07pm
Print Post Print Post  
Quote:
I would very much appreciate your help if you feel that a PrintString report would accommodate my needs.


I'll ask Ray to work this up for you.

Quote:
Just so you know, MS Access can produce this report very easily.  All you do in your Page Setup is tell it the number of columns you want, the row and column spacing, the width of the column and whether you want the column layout Down, then accross or Accross, then down.  Since it has been available for years, now, I guess I just started taking it for granted and assumed it was a somewhat "standard" feature. Certainly, it is a desirable one and I would love to see it in Sesame.


Ah, yes, now I remember. I recall fighting the down-then-across subreport bug. I went looking for how to do this in Access (as I seemed to remember being able to do so) but couldn't find the right setting. I forgot that there were some additional options available from the Page Setup screens. 
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Columns in Reports
Reply #9 - Apr 11th, 2005 at 2:35pm
Print Post Print Post  
As I mentioned in an earlier message, that can also be done in Word. 

If you ar comfortable with doing it in Access, then you have at least two choices:

1. You can export an ASCII file and use Access to make a new table from the ASCII file.  Use same data seqquence as existing Access table.

2. You can make an ODBC connection and link directly from Access to Sesame. 

After either link is made, then you can make queries as normal in Access and use the queries to feed your reports.   If you have an existing report then all you need to do is temporarily rename the existing Access table.then rename the imported table to the same name as your existing Access table, and run the report.

  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Columns in Reports
Reply #10 - Apr 11th, 2005 at 2:47pm
Print Post Print Post  
Hey Bob -

I appreciate the additional input and am assuming it was prompted by my reply to Erika.  I was simply responding to her comments to give her the additional feedback .... and to take her up on her offer to help with the PrintString approach.

I had seen your earlier comments and said that I felt the merge to Word was probably a viable option.

I do not like the idea of exporting to Access because my ultimate objective would be to stop using Access completely.  I already transferred my "critical" projects to Sesame and am happier with what I am able to do in the database. 

Thanks, again.
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2482
Joined: Aug 20th, 2003
Re: Columns in Reports
Reply #11 - Apr 11th, 2005 at 2:53pm
Print Post Print Post  
Hello Spencer,

The below code was written to be put in the on element entry event of a command button on the main form in the sample Customers.db file. You will need to replace "Main Form" with the name of your form, "First" with the name of your name element, and "Key" with your table number element.

If you would rather have this in a Mass Update, just let me know.

-Ray

Code
Select All
Var vRecCnt as Int
Var vOrigRec as Int
Var vLoop as Int
Var vRecsPerCol as Int
Var vCol as Int
Var vNumInCol as Int
Var vcnt as Int
Var vX as Int
Var vY as Int

//Number of records per column
vRecsPerCol = 40

vOrigRec = @FormResultSetCurrentPosition("Main Form")
vRecCnt = @FormResultSetTotal("Main Form")
vCol = 1
vLoop = 0

NewPage(850, 1100)

//Loop through all the records.
While vLoop < vRecCnt
{
	vLoop = vLoop + 1
	vCnt = vCnt + 1
	FormResultSetCurrentPosition("Main Form", vLoop)

	//Set the X and Y cooridinates
	vX = 100 + ((vCol-1) * 325)
	VY = 50 + (vCnt * 25)

	//Print the information
	PrintString(First , vX, vY, 0, "Arial", 16, 0)
	PrintString(@Text((20 - @Len(Key)) * 2, " ") + Key , vx + 100, vY, 0, "Arial", 16, 0)

	//Check and see if it is time to switch to another column
	If vCnt = vRecsPerCol Then
	{
		If vCol = 1 then
		{
			//Switch to the second column
			vCol = 2
		}
		Else If vCol = 2 then
		{
			//Done with this page. New page and first column again
			vCol = 1
			FinishPage()
			NewPage(850, 1100)
		}
		vCnt = 0
	}
}

If (vCnt > 0) Or (vCol > 1) Then
{
	//A page has been started but not printed... so lets send it to the printer
	FinishPage()
}

//Return to the record I was looking at.
FormResultSetCurrentPosition("Main Form", vOrigRec) 

  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Columns in Reports
Reply #12 - Apr 11th, 2005 at 5:23pm
Print Post Print Post  
Ray -

I would love to play with this right now but, unfortunately, I have to work on other priorities (like setting up a mail merge for printing hundreds of envelopes).

But, it looks and sounds like this would be a great solution, assuming it will give me the end result that I want. 

Just as soon as I can test it out, I'll let you know how it works out.

Thanks, much!
  

- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Columns in Reports
Reply #13 - Apr 11th, 2005 at 7:56pm
Print Post Print Post  
Hello Ray...Works great.

I tried to insert ..... by modifying the second print information line.  I changed from " " to ".", and moved the vX position to the end of First,  as follows:
Quote:
PrintString(@Text((37.5 - @Len(Key) - @Len(First)) * 2, ".") + Key , vx + @Len(First), vY, 0, "Arial", 16, 0)
This almost works OK, but the section for the qty of "." eludes me.  It looks like the columns are about 325 "units" wide, but using 325 is much too big. 

My curent formula: (37.5 - Len(Key) - Len(First)) * 2 ) is a result of trial and error but not good enough. 
Can you please explain the logic behind the original (20 - Len(Key)*2)?
How to calculate the column width?
Any clues on how to calculate that qty number for "."? 
--------------------------------------

I guess the next progression would be to modify this so that the number of columns can be varied to 2 or more.
  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2482
Joined: Aug 20th, 2003
Re: Columns in Reports
Reply #14 - Apr 11th, 2005 at 8:39pm
Print Post Print Post  
Hello Bob,

Quote:
Code
Select All
vx + @Len(First) 


This part of your code is not going to work. The @Len is going to return the number of characters in "first" not the amount of space it took "first" to print. @Len on the word "John Jacob" will return 10 but the actual space it takes to print out is more like 63 (Arial font size 12 on NewPage(850, 1100)). A value of -1 will cause the number to print right after the name.

Quote:
Can you please explain the logic behind the original (20 - Len(Key)*2)?

The 20 is just a number that I picked to give me the spacing that I wanted. One way to right align values in printstring is to pad them over to the right with spaces. In the arial font a Number takes up the same amount of space as two space characters. That is why there is a times 2 at the end.  You could use a 10 instead of a 20 if you wanted the numbers closer to the name. You should always use a number that is bigger than the number of characters in your biggest number including commas, periods and other symbols.

Quote:
How to calculate the column width?

You can use @PageExtentX() after you print your column to see the width of it.

Quote:
Any clues on how to calculate that qty number for "."

You would have to use a fixed width font for the name instead of using arial. The code would be something like this
Code
Select All
PrintString(First , vX, vY, 0, "Courier New", 16, 0)
PrintString(@Text((30 - @Len(Key) - @Len(First)), ".") + Key , -1, vY, 0, "Courier New", 16, 0) 



-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
Page Index Toggle Pages: [1] 2 
Send Topic Send Topic Print Print