|
Marshall, check out the OLAP cube option when you you are in MS Query. I selected a file that has a status field, so it will count that field. Then I dropped two different date fields (order date, approve date). They are date data types. The cube tool expands them out from year to day. Pretty cool. I saved it, then ran it. In Data|Get External Data I selected run saved query, and selected the cube thing I just saved. In excel it opened up a pivot table. I put the field I'm counting in the Drop Data Items here box, and the date fields in the Drop Row Fields Here box. I summarized all the data by year. Then by clicking the down arrows I was able to select which years, months, and days I wanted. And it updated instantly. (I'm connected via a cable modem) Pretty cool. PHil > -----Original Message----- > From: midrange-l-admin@midrange.com > [mailto:midrange-l-admin@midrange.com]On Behalf Of Phil > Sent: Friday, August 24, 2001 5:30 PM > To: midrange-l@midrange.com > Subject: RE: Using SQLREQUEST in Excel to get AS/400 data > > > I don't know about keying like this but you can do it this way: > > Data|Get External Data > > You'll be asked for a data source. If you have client access > installed then > you probably have an odbc driver for client access installed also (not > neccessarily though) > > Select it, and you'll get a list of files. The library may not > be what you > want, though. > > Once you get into MS Query (which used to be a separate product but it now > seems to be an optionally installable part of excel) you can use it just > like (you can key the sql directly without needing to prompt) ISQL except > whatever you return you can return to the spreadsheet. > > Test your security - see if you can query a file you don't have access to. > > Phil > > > -----Original Message----- > > From: midrange-l-admin@midrange.com > > [mailto:midrange-l-admin@midrange.com]On Behalf Of Marshall Akins > > Sent: Friday, August 24, 2001 4:32 PM > > To: MIDRANGE-L@midrange.com > > Subject: Using SQLREQUEST in Excel to get AS/400 data > > > > > > Anybody out there use the SQLREQUEST function in Excel? I ran > > across this > > function and thought it would be a great way to get data from > the AS/400 > > into an Excel cell, kinda like a lookup into the AS/400 file. > Sqlrequest > > lets you execute an sql statement and returns the data to the > cell. You > > have to use the ODBC add-in functions to use it. The function > within the > > cell looks like this > > > > =SQLRequest("DSN=QDSN_Hanna;UID=M_AKINS","select whname from > > hscflib.whpmas > > where whsno = 1",,). > > > > I found some documentation at > > http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9127. This > would be a > > really great function except that I cannot get it to work. All > I get back > > is #N/A, which seems to indicate that something is not working. I know > > that I am talking to the AS/400 because I can see that I am > connected to a > > QZDASOINIT and QZLSFILE job. Nothing in the job logs though. Any > > thoughts? > > > > > > > > > > _______________________________________________ > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) > > mailing list > > To post a message email: MIDRANGE-L@midrange.com > > To subscribe, unsubscribe, or change list options, > > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l > > or email: MIDRANGE-L-request@midrange.com > > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > _______________________________________________ > This is the Midrange Systems Technical Discussion (MIDRANGE-L) > mailing list > To post a message email: MIDRANGE-L@midrange.com > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l > or email: MIDRANGE-L-request@midrange.com > _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.