RE: Using SQLREQUEST in Excel to get AS/400 data -- MIDRANGE-L

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