Questions

Convert Access reports to Crystal Reports

Tags:
+
0 Votes
Locked

Convert Access reports to Crystal Reports

02Monte
Just started at a new company and looking for tips/hints on converting Access reports (pulling from Oracle DB) over to Crystal reports. Any tips/hints to get me off to a good start? Of course they have requests stacked up and no time to start from scratch. The Access reports are so dogging.
  • +
    0 Votes
    Dr Dij

    but I still use access to query data to see if it is in the system for the crystal reports. Just setup links to the tables you want in access. If you're using citrix, you can put an icon that starts a copy of crystal on your citrix server, to avoid installing crystal on every end user's PC.

    For some accounting reports I put them in a folder, the network guys set permissions so only finance / accounting can see them, and we copied shortcut to the folder to user's desktop. that way they click on the title of the report they want, which starts crystal with that report, rather than have them be software roadkill by starting crystal then asking what they do next. (easier than having them search for reports)

    and for the plant floor we have a dift protected folder, with production reports in crystal, showing employee jobs worked on and machine productivity.

    the wording is a bit dift. left outer join is crystal equiv of 'all files from 1st file and all recs where match from 2nd'

    Oh, and to do more complicated formulas, change box up top to vb syntax. this way you can use variables, and assign to an output variable (return var), which is hard to do in one statement in crystal syntax.

    query the data in access and limit your crystal report to one data item (job, invoice, or whatever else you're reporting on) during testing, to make sure you don't have runaway SQL. IF it hangs, get winSQL free version, attach to your database and see if there is an index with the foreign key you are linking the tables with.

    You can also embed reports in VB, and you might need to do some data sorting to a temp table before calling crystal. This if your database doesn't have data exactly the way you want for reporting. These embeded reports can be added to menus of some ERP systems.

    Subqueries are also handy.
    They also have a low cost crystal server for web sharing and pushing reports. User groups meet in many cities.

    For some sales reports, you can actually get grand totals and print each sales person as percent of grand total, pretty nifty. Crystal makes two passes on data if it needs to.

    Lots of other tips, too many to mention. not(isnull( is handy to avoid dividing by zero. you can do substringing to find parts of GL#s or depts.

    while developing your reports, make lots of copies. backup the production reports by date in a sept directory.

    I **** up the visual linking screen and re-arrange files to fit on one page, alt-prtscreen then paste in word to print. if formulas are sufficiently complicated, document them.

    IF you have a huge# of data fields and no nice front end for data fields like peoplesoft or sap, read the data dictionary into an access database and you can sort both field names and field desc by name, and sort table names and descriptions to find data items. And do simple file queries in access to check the data you want is there before doing the crystal query.

    when you give them a report, save a copy in a binder as sample. I put a ## in front. e.g. 51-Sales by Customer
    51A- sales by customer export
    because sometimes you have to dink with the columns and put in blank text boxes to get the spreadsheet export to line up in right columns.

    The ## in front of title is really necessary so that end users can ask you about specific report. without it you dont' know which report or variation they are talking about.

    Verify data in your ERP system screens and via access queries before you give to end users. Have end users give you printed copies of individual invoices, production tickets, etc if you are doing a report on these. makes it easy to match up figures.

    put in lots of groups in logical places, and give them subtotals of useful fields; also give counts, e.g. 23 invoice in that group, using total function just change to count. you can do averages too..

    If what you end up wanting on the report is simply the total line at the end of a group, copy the group name or the equiv data field from file to the group totals, and hid the detail section or any subgroups you don't want to see. If a user has questions, why is this total what it is.. you can unhide the group so they can see the columns of supporting detail. or show them to do this.

    you can even experiment with graphs and embed them in reports. quite easy. I also always put (in addition to the report title, not as text box but fill in the report title field and print it here) the page count, and more important, the data 'as of' date. That way if you run the report and users later change the data / add invoices for the month, etc. you can know why data wrong, because it was run before that data put in. e.g. before the end of month for a monthly sales report, etc.

    you can put in defaults for parameters, and even read in a lookup table, such as employee names / #s to show for selection. and you can do 'in' function to select based on a group of companies typed by users with commas, so your report shows only these.

    you sometimes have to do defined fields as running totals to correctly subtotal complicated sorts. put 'zero when field changes'. you can do selective totals with formulas, e.g. columns with each being one gl# or range of gl#s. the people in accounting really 'get their spreadsheets off' with this one :)

    Have fun!

    +
    0 Votes
    02Monte

    however I can't see that you can save a report as a csv file or such that can be used in excel. It looks like they are using Access for a frontend. Their erp is Visual enterprise (VMFG, VCRM,VFIN) which has its own built in (but very limited) report writer that will generate csv files, and it allows report overrides and suggesting Crystal for that. You have given me alot of starters and ideas I can't wait to try out. I would just love to avoid access if possible. I can work with SQL and tables, fields etc. I'm coming off 16 years of IBM and Friedman's HFA/Frontier system and need to get up to speed quickly with all new tools. Why doesn't Crystal allow save as csv? or is it just my set up? Thanks for you tips. They are greatly appreciated.

    +
    0 Votes
    Dr Dij

    we do this all the time for users:
    print | export
    then select excel file type under format pull down

    in fact some users primarily use crystal to export data to spreadsheet.

    also be sure to instll the help files. my IT dept didn't do this on one of my PCs.

    Glad I gave you some ideas. it can do lots of stuff.

    I orignally signed up and ran thru about 16 3 hour courses on mindleaders.com. you can buy them separately or in a group with other software for a hundred or two a year. (take them online). This got me started and I learned the rest when I had to do a report using a feature.

    +
    0 Votes
    jamess

    Edited by moderator

    Message was edited by: beth.blakely@...

    +
    0 Votes
    achang01

    AccessToCR from microtools.us can help you instantly convert your Access reports to Crystal Reports . Their url is http://www.microtools.us/accesstocr.aspx.

  • +
    0 Votes
    Dr Dij

    but I still use access to query data to see if it is in the system for the crystal reports. Just setup links to the tables you want in access. If you're using citrix, you can put an icon that starts a copy of crystal on your citrix server, to avoid installing crystal on every end user's PC.

    For some accounting reports I put them in a folder, the network guys set permissions so only finance / accounting can see them, and we copied shortcut to the folder to user's desktop. that way they click on the title of the report they want, which starts crystal with that report, rather than have them be software roadkill by starting crystal then asking what they do next. (easier than having them search for reports)

    and for the plant floor we have a dift protected folder, with production reports in crystal, showing employee jobs worked on and machine productivity.

    the wording is a bit dift. left outer join is crystal equiv of 'all files from 1st file and all recs where match from 2nd'

    Oh, and to do more complicated formulas, change box up top to vb syntax. this way you can use variables, and assign to an output variable (return var), which is hard to do in one statement in crystal syntax.

    query the data in access and limit your crystal report to one data item (job, invoice, or whatever else you're reporting on) during testing, to make sure you don't have runaway SQL. IF it hangs, get winSQL free version, attach to your database and see if there is an index with the foreign key you are linking the tables with.

    You can also embed reports in VB, and you might need to do some data sorting to a temp table before calling crystal. This if your database doesn't have data exactly the way you want for reporting. These embeded reports can be added to menus of some ERP systems.

    Subqueries are also handy.
    They also have a low cost crystal server for web sharing and pushing reports. User groups meet in many cities.

    For some sales reports, you can actually get grand totals and print each sales person as percent of grand total, pretty nifty. Crystal makes two passes on data if it needs to.

    Lots of other tips, too many to mention. not(isnull( is handy to avoid dividing by zero. you can do substringing to find parts of GL#s or depts.

    while developing your reports, make lots of copies. backup the production reports by date in a sept directory.

    I **** up the visual linking screen and re-arrange files to fit on one page, alt-prtscreen then paste in word to print. if formulas are sufficiently complicated, document them.

    IF you have a huge# of data fields and no nice front end for data fields like peoplesoft or sap, read the data dictionary into an access database and you can sort both field names and field desc by name, and sort table names and descriptions to find data items. And do simple file queries in access to check the data you want is there before doing the crystal query.

    when you give them a report, save a copy in a binder as sample. I put a ## in front. e.g. 51-Sales by Customer
    51A- sales by customer export
    because sometimes you have to dink with the columns and put in blank text boxes to get the spreadsheet export to line up in right columns.

    The ## in front of title is really necessary so that end users can ask you about specific report. without it you dont' know which report or variation they are talking about.

    Verify data in your ERP system screens and via access queries before you give to end users. Have end users give you printed copies of individual invoices, production tickets, etc if you are doing a report on these. makes it easy to match up figures.

    put in lots of groups in logical places, and give them subtotals of useful fields; also give counts, e.g. 23 invoice in that group, using total function just change to count. you can do averages too..

    If what you end up wanting on the report is simply the total line at the end of a group, copy the group name or the equiv data field from file to the group totals, and hid the detail section or any subgroups you don't want to see. If a user has questions, why is this total what it is.. you can unhide the group so they can see the columns of supporting detail. or show them to do this.

    you can even experiment with graphs and embed them in reports. quite easy. I also always put (in addition to the report title, not as text box but fill in the report title field and print it here) the page count, and more important, the data 'as of' date. That way if you run the report and users later change the data / add invoices for the month, etc. you can know why data wrong, because it was run before that data put in. e.g. before the end of month for a monthly sales report, etc.

    you can put in defaults for parameters, and even read in a lookup table, such as employee names / #s to show for selection. and you can do 'in' function to select based on a group of companies typed by users with commas, so your report shows only these.

    you sometimes have to do defined fields as running totals to correctly subtotal complicated sorts. put 'zero when field changes'. you can do selective totals with formulas, e.g. columns with each being one gl# or range of gl#s. the people in accounting really 'get their spreadsheets off' with this one :)

    Have fun!

    +
    0 Votes
    02Monte

    however I can't see that you can save a report as a csv file or such that can be used in excel. It looks like they are using Access for a frontend. Their erp is Visual enterprise (VMFG, VCRM,VFIN) which has its own built in (but very limited) report writer that will generate csv files, and it allows report overrides and suggesting Crystal for that. You have given me alot of starters and ideas I can't wait to try out. I would just love to avoid access if possible. I can work with SQL and tables, fields etc. I'm coming off 16 years of IBM and Friedman's HFA/Frontier system and need to get up to speed quickly with all new tools. Why doesn't Crystal allow save as csv? or is it just my set up? Thanks for you tips. They are greatly appreciated.

    +
    0 Votes
    Dr Dij

    we do this all the time for users:
    print | export
    then select excel file type under format pull down

    in fact some users primarily use crystal to export data to spreadsheet.

    also be sure to instll the help files. my IT dept didn't do this on one of my PCs.

    Glad I gave you some ideas. it can do lots of stuff.

    I orignally signed up and ran thru about 16 3 hour courses on mindleaders.com. you can buy them separately or in a group with other software for a hundred or two a year. (take them online). This got me started and I learned the rest when I had to do a report using a feature.

    +
    0 Votes
    jamess

    Edited by moderator

    Message was edited by: beth.blakely@...

    +
    0 Votes
    achang01

    AccessToCR from microtools.us can help you instantly convert your Access reports to Crystal Reports . Their url is http://www.microtools.us/accesstocr.aspx.