Re: [U2] Reporting Tools
I created a program (LIST.EXPORT) that works similar to the LIST command. The report has tab delimited columns and is output to the HOLD file. The command line command LIST.EXPORT allows conv and col.hdg column modifiers. The command requires a saved list name to know which records to select. I suspect the only limitation is the number of characters that can be entered on a command line. LIST.EXPORT reads the dictionary record for each column to determine if it is a D or I type. Value marks and sub value marks are converted to spaces. I have not yet added normalisation to the routine. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Reporting-Tools-tp42725p42781.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
How option an (Option to include or not include column headings ? -Original Message- From: Riverman [via U2 (UniVerse UniData)] ml-node+s1073795n4278...@n5.nabble.com To: Will Johnson wjhon...@aol.com Sent: Tue, Mar 4, 2014 10:53 am Subject: Re: Reporting Tools I created a program (LIST.EXPORT) that works similar to the LIST command. The report has tab delimited columns and is output to the HOLD file. The command line command LIST.EXPORT allows conv and col.hdg column modifiers. The command requires a saved list name to know which records to select. I suspect the only limitation is the number of characters that can be entered on a command line. LIST.EXPORT reads the dictionary record for each column to determine if it is a D or I type. Value marks and sub value marks are converted to spaces. I have not yet added normalisation to the routine. If you reply to this email, your message will be added to the discussion below: http://u2-universe-unidata.1073795.n5.nabble.com/Reporting-Tools-tp42725p42781.html To start a new topic under U2 - Users, email ml-node+s1073795n3...@n5.nabble.com To unsubscribe from U2 (UniVerse UniData), click here. NAML -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Reporting-Tools-tp42725p42782.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
The program does use the heading from DICT as a default column heading. I included 65 lines of explanations and examples in the program but left out some of the information for this post in the interest of brevity. -- View this message in context: http://u2-universe-unidata.1073795.n5.nabble.com/Reporting-Tools-tp42725p42783.html Sent from the U2 - Users mailing list archive at Nabble.com. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff Try mvQuery, that should not have any problems with those volumes. Brian PS Of course for actual reports rather than CSVs, there's mvPDF.. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: 28 February 2014 14:22 To: U2 Users List Subject: [U2] Reporting Tools I'm looking for feedback from those in any industry about your report needs with U2 and what tools you use to solve them. In short, Entrinsik has serious inroads in the academic community and Informer is a reasonably good and inexpensive tool for most reports. However, we are running into issues with some users like to write large reports. Large, by out standards, are reports with 200+ columns in the output. Are others writing reports with this many output fields? If so, are you ever running up against limits in the sentence size (U_SENTLEN)? Our limit is about 9700 chars and we have some reports hitting this limit. I'm having a hard time explaining to some VP types that the database can't handle this size report... Any feedback is welcome. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeff Schasny - Denver, Co, USA jschasny at gmail dot com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff, I would second Rodney Baakkonen's suggestion of writing out your delimited data to a Unix directory and ftp the blob to wherever the user would like to find it. This vastly expands the possible size limitations you would next hit at the Unix level. Of course if the existing reports consists of a lot of complex dictionaries then you would have a programming task you may not want. Endless alternatives spring to mind for that. Is performance an issue? Marc Rutherford Principal Programmer/Analyst Advanced Bionics LLC 661 362-1754 -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 12:34 PM To: U2 Users List; jscha...@gmail.com Subject: Re: [U2] Reporting Tools On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST As a note, limitations on number of columns isn't just an MV issue. MS SQL will prevent you from even creating tables with large amounts of columns, whereas MV just limits the active querying of so many at a time, so before deciding to move because of something like that, you should really look into how the issue is explained to management. http://stackoverflow.com/questions/14070932/work-around-sql-server-maximum-columns-limit-1024-and-8kb-record-size Regards, Dan -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 1:34 PM To: U2 Users List; jscha...@gmail.com Subject: Re: [U2] Reporting Tools On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
On 3/3/14, 4:01 PM, Daniel McGrath wrote: Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST Thanks Dan - this helps immensely. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
As a work around, would this work: * produce file1 with record key and then 100+ data fields * produce file2 with remaining data fields * execute Linux command: paste file1 file2 myrealdata.csv David Rotman, Ph.D. Associate Vice President for Technology Chief Information Officer Cedarville University 251 N. Main Street Cedarville, OH 45314 rotm...@cedarville.edu voice 937-766-7905 fax 937-766-8819 On Mon, Mar 3, 2014 at 3:34 PM, jeffrey Butera jbut...@hampshire.eduwrote: On 3/3/14, 3:21 PM, Jeff Schasny wrote: Jeff, What I think many of us are suggesting is essentially if it hurts when you do that, don't do that i.e. if the query language won't accomplish what you want to do, use something else. Jeff Believe me - I hear you (and others). But my administration isn't listening because they all think this is a reasonable request (excel can handle 200+ columns). So if I can't make this work in Unidata I'll have to move to MSSQL which I really, really, really don't want to do. At this point I'm just trying to understand the limit on the number of fields in a LIST statement. I know in my case that it's not a sentence length issue as I might've thought earlier so I'd like to know what is causing this (with the understanding that I very well may not be able to solve this). Jeff jeffrey Butera wrote: On 3/3/14, 2:58 PM, Brian Leach wrote: Jeff Try mvQuery, that should not have any problems with those volumes. Hi Brian We've isolated the problem to Unidata itself, not the reporting tool. In short, when we do a LIST with about 150 fields, it throws: too many items in LIST As soon as we erase a field (any field), the LIST statement runs properly. Unfortunately, I cannot locate any parameter that might control this. I thought we were hitting U_SENTLEN - but we're nowhere near that value. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
If you're not already, it's a really good idea to consider creating something that will make producing excel files (the number one choice of accounting groups everywhere) from your reports easily. Many years ago we used to physically print a bunch of reports each night, but since creating a tool the users can easily use, almost everything is electronic. http://www.pickwiki.com/cgi-bin/wiki.pl?DumpRecall shows one way to do it, though it's pretty specific to our needs. It wouldn't be hard to use this as inspiration for something that works in your own environment. I'd forgotten about the 150 field limit in unidata, but this comment remembers: * We have a list of field names and a select, parse them the hard way * and write a tab-seperated file. This is slower but avoids limitations * on the number of columns (of unidata) and the perl module WriteExcel -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 1:05 PM To: U2 Users List; dmcgr...@rocketsoftware.com Subject: Re: [U2] Reporting Tools On 3/3/14, 4:01 PM, Daniel McGrath wrote: Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST Thanks Dan - this helps immensely. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Ian you don't happen to have an example of what this code *does* do you? -Original Message- From: McGowan, Ian james.mcgo...@bankofthewest.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Mon, Mar 3, 2014 1:35 pm Subject: Re: [U2] Reporting Tools If you're not already, it's a really good idea to consider creating something that will make producing excel files (the number one choice of accounting groups everywhere) from your reports easily. Many years ago we used to physically print a bunch of reports each night, but since creating a tool the users can easily use, almost everything is electronic. http://www.pickwiki.com/cgi-bin/wiki.pl?DumpRecall shows one way to do it, though it's pretty specific to our needs. It wouldn't be hard to use this as inspiration for something that works in your own environment. I'd forgotten about the 150 field limit in unidata, but this comment remembers: * We have a list of field names and a select, parse them the hard way * and write a tab-seperated file. This is slower but avoids limitations * on the number of columns (of unidata) and the perl module WriteExcel -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Monday, March 03, 2014 1:05 PM To: U2 Users List; dmcgr...@rocketsoftware.com Subject: Re: [U2] Reporting Tools On 3/3/14, 4:01 PM, Daniel McGrath wrote: Hi Jeff, In case you missed it before: Does your query use the LIMIT keyword? This is an internally hardcoded limit that was increased, IIRC, in 7.3.6 to 250. On further checking, here is the issue, along with fix version. I suggest you update to the latest UDT version. UDT-4284 - Request for UniQuery hard limit of 150 attributes (in LIST or REFORMAT commands) to be increased A change was made to increase this to 250 attributes in 7.3.5. The actual limit has now been added to the report from the LIMIT verb so you can see limitation as U_ATTRLIST Thanks Dan - this helps immensely. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Jeff:
I've written a little BASIC utility that takes a file, selects what I
want, then outputs via the ITYPE function as a CSV file to the spooler.
I wonder if that gets around the 150 field limit, since it's in BASIC?
I know UniData is weird regarding this kind of thing. For instance,
they also have something like a 256 item limit in a query statement...
:SELECT MYFILE WITH CUST# = 123...256257
...will abort. This too is hardwired. However, a neat little utility
provided by U2 called LSELECT selects items whose field definition
value is in a list, so if you're on the web you'd first allow the user
to select stuff, put it into a list, then use LSELECT to select the
items from the file...kind of like:
:SELECT CUSTOMERS WITH LAST_ACTIVE 01/01/13
500 records selected to list 0.
SAVE.LIST MY.LIST
Overwriting existing saved list.
500 key(s) saved to 1 record(s).
LSELECT SALESORDERS WITH CUSTNO IN MY.LIST VERBOSE
(Parsed Command)
LSELECT SALESORDERS WITH CUSTNO IN MY.LIST TO 0
(Selected List)
List Source MY.LIST found 500 items.
(Create Temp File)
LSELECT0031 file created with MOD = 3
(Loaded List to Temp File) - 500 items.
1436 key(s) saved to 1 record(s).
(Selected Result List)
UDT.COMMAND = select SALESORDERS WITH EVAL
TRANS('LSELECT0031',CUSTNO,'@ID','X') GT TO 0
UDT.RESPONSE = ■1436 records selected to list 0.■■
Destination 0
Named List 0
(Delete Temp File)
LSELECT0031 file deleted...
Makes one wonder...h.
Bill
Untitled Page
- Original Message -
*From:* rotm...@cedarville.edu
*To:* U2 Users List u2-users@listserver.u2ug.org
*Date:* 3/3/2014 12:57 PM
*Subject:* Re: [U2] Reporting Tools
As a work around, would this work:
* produce file1 with record key and then 100+ data fields
* produce file2 with remaining data fields
* execute Linux command:
paste file1 file2 myrealdata.csv
David Rotman, Ph.D.
Associate Vice President for Technology
Chief Information Officer
Cedarville University
251 N. Main Street
Cedarville, OH 45314
rotm...@cedarville.edu
voice 937-766-7905
fax 937-766-8819
On Mon, Mar 3, 2014 at 3:34 PM, jeffrey Butera jbut...@hampshire.eduwrote:
On 3/3/14, 3:21 PM, Jeff Schasny wrote:
Jeff,
What I think many of us are suggesting is essentially if it hurts when
you do that, don't do that i.e. if the query language won't accomplish
what you want to do, use something else.
Jeff
Believe me - I hear you (and others). But my administration isn't
listening because they all think this is a reasonable request (excel can
handle 200+ columns). So if I can't make this work in Unidata I'll have
to move to MSSQL which I really, really, really don't want to do.
At this point I'm just trying to understand the limit on the number of
fields in a LIST statement. I know in my case that it's not a sentence
length issue as I might've thought earlier so I'd like to know what is
causing this (with the understanding that I very well may not be able to
solve this).
Jeff
jeffrey Butera wrote:
On 3/3/14, 2:58 PM, Brian Leach wrote:
Jeff
Try mvQuery, that should not have any problems with those volumes.
Hi Brian
We've isolated the problem to Unidata itself, not the reporting tool.
In short, when we do a LIST with about 150 fields, it throws:
too many items in LIST
As soon as we erase a field (any field), the LIST statement runs
properly. Unfortunately, I cannot locate any parameter that might control
this. I thought we were hitting U_SENTLEN - but we're nowhere near that
value.
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
If I have a recall (our terminology for a canned query) like this: RECALL.SHELL Info-Trieve Shell03 Mar 2014 BOTW_DEV217:38:03 Command Name MARKETING.REP Modified: ( bytes) Compiled: ( bytes) by root SELECT PARAMETER BY MRKT.NAME WITH @ID = 10*]~ LIST PARAMETER UATB.MRKT.NAME UATB.MRKT.OFFICE ATB Edit Copy Mail New Run Search Width Quit I get an excel spreadsheet as an attachment like this, limited only by what excel can handle. I've used a variety of formats over the years, but have come back to doing tab-delimited, but naming the file *.xls - that seems to be the most likely to not get mangled in transit and still open in a variety of spreadsheet programs and versions. NAME OFFICE 3RD PARTY SERVICER 1 AB HUSSMAN PROGRAM 4 ACCURAY, INC. PROGRAM 6 -Original Message- From: u2-users-boun...@listserver.u2ug.orgmailto:u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wjhonson Sent: Monday, March 03, 2014 1:42 PM To: u2-users@listserver.u2ug.orgmailto:u2-users@listserver.u2ug.org Subject: Re: [U2] Reporting Tools Ian you don't happen to have an example of what this code *does* do you? IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. - IMPORTANT NOTICE: This message is intended only for the addressee and may contain confidential, privileged information. If you are not the intended recipient, you may not use, copy or disclose any information contained in the message. If you have received this message in error, please notify the sender by reply e-mail and delete the message. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Let me put in a good word for Cedarville Download which I have been using for years to export stuff into all sorts of flat files. Works great and it free. http://www.cedarville.edu/Offices/Information-Technology/FTP-Site.aspx dale kelley wrote: We generate an annual GASB depreciation report that became too big for paper. We simply began putting those same print lines into an export record with the column spaces replaced by , 's and named it as .CSV. You need a programmer to do this but you can generate almost any size excel file and there are products like Nebula XL that apply excel formatting. Would save the email recipient any manipulation. Dale On 02/28/2014 02:31 PM, Jeff Lettau wrote: Without knowing exactly what you are doing, why not take a look at ODBC and linking an excel sheet to your database and use the powerpivot to create your report? If you are really doing a ETL you need a tool or a programmer. I've pulled over 300k worth of records without an issue. Although the record set was not 200 columns, but I'd bet you could get down to under 50 then do calculations in excel, then pivot the data to the report. Please note that my email address has changed. Beginning immediately my new address is jeff.let...@soundunited.com . Use this new address for all communications. Jeffrey Lettau | ERP Systems Manager | O 410.358.3600 | D 410.764.5242 | 5601 Metro Drive | Baltimore MD 21215 | www.soundunited.com Polk Audio - WWW.POLKAUDIO.COM Definitive Technologies - WWW.DEFINITIVETECH.COM BOOM Movement - WWW.BOOMMOVEMENT.COM -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, February 28, 2014 9:42 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Reporting Tools On 2/28/14, 9:29 AM, Jeff Lettau wrote: In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. To be clear - this is not MY choice of report, I'm trying to explain that they are using a report as a poor person's ETL tool to dump vast amount of data to Excel and manipulate offline. But I though I'd ask the question anyway. How many rows are in this report? Perhaps 10,000 - 30,000 -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Jeff Schasny - Denver, Co, USA jschasny at gmail dot com ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
We generate an annual GASB depreciation report that became too big for paper. We simply began putting those same print lines into an export record with the column spaces replaced by , 's and named it as .CSV. You need a programmer to do this but you can generate almost any size excel file and there are products like Nebula XL that apply excel formatting. Would save the email recipient any manipulation. Dale On 02/28/2014 02:31 PM, Jeff Lettau wrote: Without knowing exactly what you are doing, why not take a look at ODBC and linking an excel sheet to your database and use the powerpivot to create your report? If you are really doing a ETL you need a tool or a programmer. I've pulled over 300k worth of records without an issue. Although the record set was not 200 columns, but I'd bet you could get down to under 50 then do calculations in excel, then pivot the data to the report. Please note that my email address has changed. Beginning immediately my new address is jeff.let...@soundunited.com . Use this new address for all communications. Jeffrey Lettau | ERP Systems Manager | O 410.358.3600 | D 410.764.5242 | 5601 Metro Drive | Baltimore MD 21215 | www.soundunited.com Polk Audio - WWW.POLKAUDIO.COM Definitive Technologies - WWW.DEFINITIVETECH.COM BOOM Movement - WWW.BOOMMOVEMENT.COM -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, February 28, 2014 9:42 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Reporting Tools On 2/28/14, 9:29 AM, Jeff Lettau wrote: In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. To be clear - this is not MY choice of report, I'm trying to explain that they are using a report as a poor person's ETL tool to dump vast amount of data to Excel and manipulate offline. But I though I'd ask the question anyway. How many rows are in this report? Perhaps 10,000 - 30,000 -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. How many rows are in this report? Please note that my email address has changed. Beginning immediately my new address is jeff.let...@soundunited.com . Use this new address for all communications. Jeffrey Lettau | ERP Systems Manager | O 410.358.3600 | D 410.764.5242 | 5601 Metro Drive | Baltimore MD 21215 | www.soundunited.com Polk Audio - WWW.POLKAUDIO.COM Definitive Technologies - WWW.DEFINITIVETECH.COM BOOM Movement - WWW.BOOMMOVEMENT.COM -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, February 28, 2014 9:22 AM To: U2 Users List Subject: [U2] Reporting Tools I'm looking for feedback from those in any industry about your report needs with U2 and what tools you use to solve them. In short, Entrinsik has serious inroads in the academic community and Informer is a reasonably good and inexpensive tool for most reports. However, we are running into issues with some users like to write large reports. Large, by out standards, are reports with 200+ columns in the output. Are others writing reports with this many output fields? If so, are you ever running up against limits in the sentence size (U_SENTLEN)? Our limit is about 9700 chars and we have some reports hitting this limit. I'm having a hard time explaining to some VP types that the database can't handle this size report... Any feedback is welcome. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Two things. First I tend to agree with the number of columns. Perhaps the report is trying to do more than just a couple of things. Consider evaluating the various uses of this report and producing more, smaller reports that provide only what the needs are. Second, I'm a programmer so I just program all of my reports. Columns and rows are really just one big long string with a variety of delimiters. If you are producing reports at TCL then a programmer could make that very easy. Sincerely, David Laansma Hubbard Supply Co. Direct: 810-342-7143 Office: 810-234-8681 Fax: 810-234-6142 www.hubbardsupply.com Delivering Products, Services and Innovative Solutions -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Lettau Sent: Friday, February 28, 2014 9:29 AM To: U2 Users List Subject: Re: [U2] Reporting Tools In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. How many rows are in this report? Please note that my email address has changed. Beginning immediately my new address is jeff.let...@soundunited.com . Use this new address for all communications. Jeffrey Lettau | ERP Systems Manager | O 410.358.3600 | D 410.764.5242 | 5601 Metro Drive | Baltimore MD 21215 | www.soundunited.com Polk Audio - WWW.POLKAUDIO.COM Definitive Technologies - WWW.DEFINITIVETECH.COM BOOM Movement - WWW.BOOMMOVEMENT.COM -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, February 28, 2014 9:22 AM To: U2 Users List Subject: [U2] Reporting Tools I'm looking for feedback from those in any industry about your report needs with U2 and what tools you use to solve them. In short, Entrinsik has serious inroads in the academic community and Informer is a reasonably good and inexpensive tool for most reports. However, we are running into issues with some users like to write large reports. Large, by out standards, are reports with 200+ columns in the output. Are others writing reports with this many output fields? If so, are you ever running up against limits in the sentence size (U_SENTLEN)? Our limit is about 9700 chars and we have some reports hitting this limit. I'm having a hard time explaining to some VP types that the database can't handle this size report... Any feedback is welcome. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
On 2/28/14, 9:29 AM, Jeff Lettau wrote: In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. To be clear - this is not MY choice of report, I'm trying to explain that they are using a report as a poor person's ETL tool to dump vast amount of data to Excel and manipulate offline. But I though I'd ask the question anyway. How many rows are in this report? Perhaps 10,000 - 30,000 -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
We create a lot of delimited flat files at a Unix level. Then we ftp them to the windows side of the network. The ftp process generates an email to the user with a link to the file we just put on the LAN. The users can then open it up using the email link in Excel and do what they want. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Jeff Lettau Sent: Friday, February 28, 2014 8:29 AM To: U2 Users List Subject: Re: [U2] Reporting Tools In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. How many rows are in this report? Please note that my email address has changed. Beginning immediately my new address is jeff.let...@soundunited.com . Use this new address for all communications. Jeffrey Lettau | ERP Systems Manager | O 410.358.3600 | D 410.764.5242 | 5601 Metro Drive | Baltimore MD 21215 | www.soundunited.com Polk Audio - WWW.POLKAUDIO.COM Definitive Technologies - WWW.DEFINITIVETECH.COM BOOM Movement - WWW.BOOMMOVEMENT.COM -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, February 28, 2014 9:22 AM To: U2 Users List Subject: [U2] Reporting Tools I'm looking for feedback from those in any industry about your report needs with U2 and what tools you use to solve them. In short, Entrinsik has serious inroads in the academic community and Informer is a reasonably good and inexpensive tool for most reports. However, we are running into issues with some users like to write large reports. Large, by out standards, are reports with 200+ columns in the output. Are others writing reports with this many output fields? If so, are you ever running up against limits in the sentence size (U_SENTLEN)? Our limit is about 9700 chars and we have some reports hitting this limit. I'm having a hard time explaining to some VP types that the database can't handle this size report... Any feedback is welcome. -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- CONFIDENTIALITY NOTICE: If you have received this email in error, please immediately notify the sender by e-mail at the address shown. This email transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance. Copyright (c) 2014 Cigna == ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Reporting Tools
Without knowing exactly what you are doing, why not take a look at ODBC and linking an excel sheet to your database and use the powerpivot to create your report? If you are really doing a ETL you need a tool or a programmer. I've pulled over 300k worth of records without an issue. Although the record set was not 200 columns, but I'd bet you could get down to under 50 then do calculations in excel, then pivot the data to the report. Please note that my email address has changed. Beginning immediately my new address is jeff.let...@soundunited.com . Use this new address for all communications. Jeffrey Lettau | ERP Systems Manager | O 410.358.3600 | D 410.764.5242 | 5601 Metro Drive | Baltimore MD 21215 | www.soundunited.com Polk Audio - WWW.POLKAUDIO.COM Definitive Technologies - WWW.DEFINITIVETECH.COM BOOM Movement - WWW.BOOMMOVEMENT.COM -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of jeffrey Butera Sent: Friday, February 28, 2014 9:42 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Reporting Tools On 2/28/14, 9:29 AM, Jeff Lettau wrote: In the past I've made dictionary names that are very short to save characters. But my real reason for responding is I can't help but wonder what you need 200+ columns for? My first instinct when hearing this is that a report is not what you need. To be clear - this is not MY choice of report, I'm trying to explain that they are using a report as a poor person's ETL tool to dump vast amount of data to Excel and manipulate offline. But I though I'd ask the question anyway. How many rows are in this report? Perhaps 10,000 - 30,000 -- Jeffrey Butera, PhD Associate Director for Application and Web Services Information Technology Hampshire College 413-559-5556 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
