Will I be able to use a Report Writer other than MS Exel
Results 1 to 5 of 5

Thread: Will I be able to use a Report Writer other than MS Exel

  1. #1
    Join Date
    Apr 2012
    Posts
    116

    Will I be able to use a Report Writer other than MS Exel

    A very long story short if I may please. I will attempt to answer further questions the best I can.

    Attempting to get an idea of how to proceed.

    Starting point - I have a CSV file. My objective is to use a report writer such as Crystal Reports for example to extract data from this file and create a report. Will I be able to convert my CSV file and or save it as another format so that I can use a report writer? If so what format would I save it in?

    Presently I only have on hand Apache "Open Office-Spreadsheet" to work with.

    We are a young company. We don't have any database servers etc. We are presently looking at accounting and warehouse management software which is "cloud" based from several companies. Overall so far I have found that we can "export" information to a CSV file.

    I am more used to working with databases in the past (many years ago) and using Crystal Report Writer to manipulate the data and create a report. I would be able to link fields of records from one data table to another. Also, using some simple SQL statements I could take data from several data tables and create a new virtual data table where I could use this information to create my report and once the report ran the data table would no longer exist so it did not take up a lot of hard disk space.

    I know I have several file formats I can select from when saving the "CSV" file in Open Office but don't know the best way to proceed. Not ruling out using MS Excel but we do not own the full version and don't want to invest in it unless it will provide us with the ability to do with what we need. If we use MS Excel I would assume that we would only be able to use the report writer within Excel itself to create the reports. If I am incorrect in this please let me know.

    Overall my/our main objective is to be able to use the CSV file data that has been exported and then later have the ability to use a report writer to create the report.

    So basically I am looking for a starting point here. Do we need to purchase something such as MS SQL (or similar) to accomplish this.

    Please forgive me if some of my terminology is incorrect or not on target. I am trying to help a friend of mine. He has a growing business and he has asked me to look at some accounting and warehouse management systems with him. Many years ago I was more into the database end (such as a distribution center). I would create Crystal Reports for the heads of the departments such as Purchasing, Inventory Control, Warehouse Production etc. I had the database tables to work with along with the definitions of the data-tables and records/fields. At the time (2003-2006) they were using MicroSoft Great Plains and Radio Beacon WMS. These of course are high end packages of which my friend can not invest in at this time. We are a bit limited as the package must be able to inter-connect with many websites such as Amazon, eBay etc that he sells his products on.

    It has been years for me but I do know that no matter what "canned" reports a software package provides it will never be exactly what you want. This is why I want to be able to extract data and create the reports that will be far more useful for him and also he won't have to use 3 of the canned reports, create an excel spreadsheet entering the information and then have the results he needs in one report.

    Any suggestions would be so most gratefully appreciated.

    Thank you

  2. #2
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    15,634
    .csv files can be used with lots of software. It might help if we had some idea of what sorts of data you will have in the .csv files, and what sorts of reports you want to get out of that data. Excel has the advantage of having VBA (Visual Basic for Applications) available, which can be used to organize or extract data from imported .cvs files.

  3. #3
    Join Date
    Apr 2012
    Posts
    116
    I will try and give you a couple of potential scenarios


    Let's say I want to create a report that I only run once a week. The report will be generated and printed out for the warehouse production team. It lists items that are to be made up (kitted) from items we have in stock. The report would tell how many kits need to be made up based on sales over the past 21 days. The kit may contain all of the same item but not necessarily the same quantity. KitA may contain Item 1 at 3 pieces where kitB may contain the same item 1 but at 7 pieces. The kits are sold at different price ranges to the customers. If they buy Kit A which has less pieces than kit B of course the overall price of the kit to the customer will be less than KitB but the piece price is less per piece if they buy KitB so it is an incentive for them to buy KitB. KitA and KitB use the same item which we keep in stock in bulk. So the objective is to make up enough of both KitA and KitB over the next week to meet or shipping requirements. Of course we would want to have them made up and sitting in the bin for picking before the order is due to go out. Most orders we receive are due to be shipped with the same day to as far out as 5 days into the future. The one item used to make both kita and kitb will need to be replenished (most of the items come in from overseas) so the lead times may be a bit long. So getting a jump of 21 days advanced notice would help in the reordering of the item. Let's say the software CSV files has the sales over the past 21 days and we will of course be able to download/export the sales information to a CSV file. But from there we would have to manipulate the data using a spreadsheet program for sorting of the items and then add in the fact that for example a kitA contains 3 piece of item 1 and then add a column in the spreadsheet representing that in order to do the cross calculation of how many of item 1 was actually used to ship x number of kita.

    So the report would generate the kits needed to be produced to be set aside for shipping to customer over the next 3 weeks and at the same time show us a "virtual" on hand figure of the item in stock remaining since the software may not deduct from the on hand inventory until a sale is made on each kit.

    Let's take a simple data table. The data table I would create for example would have kits listed in it. The data table would have the main components of the kit as a header record. A few fields; kit #, kit description, kit color etc. Then to a linked table which would show the components of the kit where each component of the kit would be a separate record. Just like a sale order. The sales order header file contains the main header information of the sale order and each sales order is a separate record within the file. Then the link would be the sale order number going to the line item information file and then each record would be the line item in the order.

    May of the kits if not all require components that are not actually sold to our customers as individual items such as the bag that we use to hold the 3 pieces of kitA now the label that is used to print the bar code on kita and some kits use different size labels. So to create this by hand would be quite time consuming so that we can not only keep track of our "virtual" inventory of item1 before it actually sells but also keep track of the inventory of the labels, the bags the shipping cartons etc needed to produce kitA.

    Some software packages make available a few fields in some data table records to use as "custom" fields so you can populate the fields with specific information to be used at a later date.

    Some software packages allow you to do kitting to some degree but many do not deduct from your on hand inventory until after the kit has been shipped. So we can have made up 300 kits in advance but our on hand inventory of the item used will not reflect any reduction in inventory until possibly over the next 21 days as the kits are sold thus burning up our lead time for reordering.

    Another easier example would be to used the sales information and be able to have the report show us specificially by a group of "states" that we select the sales of only certain items that we ship to. This report would make it easier for us to determine in advance where to have our vendor ship our next order to (meaning which of the three warehouse i the country that we use) because shipping a item that has a 2 day lead time to a customer in las vegas from california is going to be cheaper than shipping it from NewJersey. So we would have the vendor ship more of an item to the california warehouse and less to the new jersey warehouse.

    Hope I have given a good enough example/s.

  4. #4
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    15,634
    You could use Excel VBA macros for this, or you may be able to use .csv files with Crystal Reports, or with Excel and then use Crystal Reports on the Excel file.

    Possibly useful links:

    https://www.youtube.com/watch?v=lmnDvpdpgSE

    https://archive.sap.com/discussions/thread/909789

    https://www.google.com/search?hl=en&....0.y2hSTlrzlUo

  5. #5
    Join Date
    Apr 2012
    Posts
    116
    Wow!!! jdc2000 you are like "all over the place"!! I guess you are a master of all trades kind of speaking. I mean that with a high level of respect towards you; honestly!!

    Great information and links you have provided. Exactly what I needed was a starting point and you gave it to me. Fabulous!!

    I think what is going to happen is my boss will continue to use me as the warehouse manager and probably hire someone (as he finds the need) to create reports to his "specs" per report etc. He needs my time more in the internal operations of the business. I am saying this because I have been out of the programming and report writing game for so long that I would basically have to learn from scratch and just won't have the time.

    On a very good note though jdc2000 I can advise him that software providing csv download files can be used to his advantage only though it may need someone more experienced than I am and not quite as - "rusty". He will understand.

    But again jdc2000 thank you for your time and help. So very much appreciated!!!!

    P.S. I'm lov'in my Blu Vivo X you suggested :-) And of course the "classy" case too!! - thanks again for that!!
    Last edited by question2012; May 24th, 2018 at 07:24 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.