Seeking Suggestions 3.70

artping

What would be the best way to extract Items sold by zip code. I believe the only way to do it would be to create a report linking the sale header and line tables? I am trying to get the data out to excel to manipulate.

Format:

Zip code
Item No. Amount Cost

grouping would be by zip code

Any suggestions would be greatly appreciated.

Savatage

Are we also assuming here you want to group the Item No too?

ZIPCODE 11747
-Item ABC 1000 pcs
-Item ABD 1023 pcs

Instead of having a ton of lines per item no?

artping

Sorry, that would be correct.

Savatage

Unless you have zip code as a key Could Be hard to group & sum
I would use tables Item ledger Entry & Sales invoice Header you can link the ILE-Document No. to SIH-No.

If your not good at using Nav Reports - Try first using the wizard to create a report in the Item Ledger Entries. Add fields Item, Quantity & amount.
You'll want to keep totals on Quanty & Amount. You'll be asked to sort & group Choose a Key That has Item No First. Finish the wizard & now when you run the report you can see it will total by item (STEP1) Now you'll have to add the Sales Invoice Header dataitem above ILE & create the link. Add A SIN GroupFooter Underneath for the zipcode.

...anyway

Can you use Odbc to connect to Excel.?
If So try this.

open Excel.
Data->Import External Data->New Database Query
Choose your Odbc Datasource you have setup->OK->Login
you list of table should appear
Select Sales Invoice Header & item ledger Entry Tables.->Next
I manually Link The Two Tables now.
ITL-Document No to SIH-No.

Now your ledger entries have a zip code attached to it.
If your good at Excel you should be able to work your magic at this point.

- Note the report you really want can be made in under 30 seconds in Crytal Reports. & Exported right to Excel. FYI. It's really awsome.

artping

First off, thanks for all info Harry.

I think the report route is creating a lot of extra work. I think I need to fine tune the import to excel as it takes quite a bit of time.

I have ODBC and Crystal reports 11. I am trying to configure my odbc to connect with my native server database. I was able to make it work on a local database but have hit a snag trying to connect. From your description this sounds like the best route, if I can get the ODBC to work.

Savatage

are you using Native or Sql database?
c/odbc or n/odbc with native - odbc for sql

Crystal lets you group & sort by anything, even variables you make up.
then Export->Excel->Done

We do alot of Customer Ledger Entries by Zip - to help create territories for our salespeople.

artping

I am using Native with c/odbc. I am able to get it to work in a development enviroment. Local database, but when I try to get it to work by connecting to the server. I receive a log on failure in Crystal.

You are using the report for the same exact reason that I am trying to setup. :-)

Savatage

artping wrote:

I am using Native with c/odbc. I am able to get it to work in a development enviroment. Local database, but when I try to get it to work by connecting to the server. I receive a log on failure in Crystal.

Make sure in Control Panel->Administrative tools->Data Sources is set up properly. Did you specify the database server directory?

artping

I think that I have it setup properly. I have the server button checked, net type is tcp, pointing it to the database with the proper company name. I then put my id and password in. It keeps coming back with a ISAM error, cannot read file?

Savatage

You need to have a license file has Granule
1,700-C/ODBC-1 to run it on a network

then here's how I set mine up
http://savatage99.googlepages.com/OdbcSetup.pdf

artping

I have that granule. I suspect my problem is with the program folder. I thought I read that this is like running a client and that it should point to my local client folder. I noticed in your instructions that it looks like you are pointing to a network drive with a shared folder. Do I need to map to a certain folder on the server?

Savatage

I don't know if it's right or wrong but I know it works.

I have it mapped to the Database Server Folder in the navision directrory on the server.

artping

I think to get the ODBC to work with the server you need to point to the server folder and if you are working local then to the local client? I pointed it to the server folder and a querying we will go. The entry type is coming back as a numeric value instead of sale,purchase etc?

artping

Now that the ODBC is running would it be faster to create a report in Crystal or let Microsoft query and put it into Excel? It seems to take quite a bit of time to move it from the query to excel. If i create a report in Crystal can I send it out to Excel?

Savatage

File->Export->Format:MS Excel (Data Only)->Destination:Application

artping

Thank you Harry for all your help with this project.