Great Plains reports in Access

[barbola]

I use Access to create alot of reports using Great Plains data. I have played around with Crystal Reports a little bit but haven't been able to come up with anything useful.

Does anyone have any comments to share on using Access? I have created views in SQL and used them in Access, and have also just linked to GP tables and then created Access queries and reports.

The company I work for doesn't want to spend any more money on GP than they have to (it's a big fight every time we get an invoice from our VAR). I would love to get VBA modifier.



Thanks!
Barb E.

 

[winthropdc]

Have you tried to create the reports you want as Custom Reports using the report Writer?

David Musgrave
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

mailto:dmusgrav@nospam-microsoft.com

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.

 

[VictoriaYudin]

Are you looking to link the reports to GP for users, or are you simply looking to run them in something like, say, Excel?

 

[jazgeek]

Access is too dangerous to the untrained user (modification to data is too easy)

You bought Crystal reports, you should use it. Crystal reports is by far a superior reporting product to MS Access.

Until Microsoft comes up with their own "crystal reports", Crystal is the industry standard reporting application.

I suggest you buy "crystal reports for dummies", I found for a beginning user the book was very useful. Plus play around with it, it's a very intuitive interface and if you know sql then you've got more than half the battle won.

jaz

-----------
and they wonder why they call it Great Pains!

jaz

 

[rgittins]

Have you ever considered creating a data warehouse and create reports that would be made avialble on line?

 

[barbola]

Wow that's alot of great ideas. Thanks everyone! I don't think Crystal is the answer for me because it seems like it is just to create reports - you can't create new queries or design fancy forms for user interface. I set up security on all my access databases (with or without links to GP data). Alot of times I will run a query just to give someone a list of data they asked for - I could do this in query analyzer, but then I have to re-enter all the joins.

I tried custom reports in Great Plains but have some difficulty getting table relationships set up properly, and it is very VERY slow which is why I don't even use alot of the reports already in GP. I don't want to offend you Dave, but alot of your posts make you sound just like our vendor who I get very frustrated with alot.

I also have alot of Excel sheets that pull data from Great Plains (using ms query).

I don't know what data warehousing is - I guess I should find out.

Thanks everyone. I just wanted to find out if I'm alone in trying all this as I have minimal support, internally and from our vendor.



Thanks!
Barb E.

 

[jazgeek]

Try Business Portal, its "free" now and it has freeform query tools similar to Smartlist.

Haven't played with it much myself, but from what I saw it was pretty nifty.

As an old Crystal report hound, I find I can create my crystal report a lot quicker than I can format a SQL statement to give me the results.

To each his own, so to speak. I use access for quick and dirty queries... mostly for duplicate and unmatched queries.

-----------
and they wonder why they call it Great Pains!

jaz

 

[MentatDragon]

I use Access a lot. It is much faster than Report Writer and I feel like I have more power over the data. I have a template database with a report form that prompts for date ranges and drop-down selection of ranges of values and I can mock-up alost anything in 1-3 hours. I used Crystal years ago, but at the time, it was ugly in the way that it prompted end-users for ranges. I'm sure it is much better now, but like Access better because I am used to it.

We also would publish the database/reports using Citrix, which worked well for us.

We are going to look at Business Portal and the latest Crystal, though.

 

[barbola]

I haven't had time to look at Business Portal yet. Maybe tomorrow...or the next day

Thanks!
Barb E.

 

[winthropdc]

Barb

Report writer with VBA can actually be alot more powerful than you know. You can use VBA to open an ADO connection to SQL server and retrieve information from tables that you have been unable to create relationships to.

Also, Dexterity can be used (if you have access to a Dexterity developer) to create reports using temporary tables and various other techniques, this makes it possible to create reports normally not possible.

The advantage of staying within the Report writer is that it can be seamlessly executed from with the one application.

PS: Sorry for sounding like your vendor, it must be those 5 years as a partner and 2 years as an ISV.

David Musgrave
Senior Development Consultant
MBS Services - Asia Pacific

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

mailto:dmusgrav@nospam-microsoft.com

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.

 

[barbola]

We don't have access to Dexterity developer and we do not have Report Writer with VBA. I use VBA with Access/Excel but before my company will spend big bucks on any additional GP modules, I would need to convince them that it would be beneficial to the company (I have a hard time getting them to spend $$$ because of our past experience with our VAR and their obscene support charges). I'm a bit of a beginner with VBA although I've impressed them so far (and myself for that matter!)

I plan to take some courses in the next couple of years when I have less personal time commitments. This way they would be more convinced that I know what I'm doing.



Thanks!
Barb E.

 

[MentatDragon]

http://www.accountable.com/

Print forms customized in Crystal Reports® directly from SOP, POP, RM, PM, Payroll and Project.

We are using it to print some complex invoices from GP.

 

[MDA]

I would highly recommend you switch to a true Report Writer. We are using XL Reporter on Great Plains accounting 7.5 (

http://www.xlreporter.com/)

and love it. XL Reporter has integrations directly on top of most of the Great Plains ERP systems as well as multiple other ERP’s such as SAP’s Business One. This is an awesome tool that all of our users love. With the ability to create reports using “drag-and-drop”, drill down to detail, create dashboards and distribute scheduled reports, it has answered all of our needs. We have thrown out Crystal and FRX and switched to this tool. Despite what you have read, Crystal is definitely not a true financial reporting tool and of course requires extensive training to develop complex reports. We use another product from MBS called Enterprise Reporting for our Consolidating reports, currency exchange and complex inter-company eliminations.

Microsoft has recently released a report writer called Reporting Services (

http://www.microsoft.com/sql/reporting/default.asp)

This tool like Crystal requires an in depth understanding of the tables within your ERP and requires the developer to have vs.NET installed but is quite powerful. However, we feel this is great for reporting on our non financial systems but definitely not our financial data. I think it is free with SQL Server but not sure.

We went through this company for our solution (

http://www.solverusa.com/)

Good luck..
MDA

 

[Dukester0122]

You can also use OLAP Cubes which you can use in Crystal Reports or probably Access(not sure). If you have SQL Server, install the Analysis Services then create Cubes for use in different report writing apps.

I've created several Cubes then used it in Crystal Reports. You can also use this in other 3rd party modules like Webhouse.

 

[MDA]

That’s a great point Dukester. Webhouse has managed to alleviate the pain of building the cubes for Great Plains. We had custom cubes built and use Proclarity as the front end but this is not cheap. The learning curve is quite steep for understanding OLAP, especially when you get into MDX(MultiDimensional eXpressions). However, once you have the cubes built, you can present the data using many applications in addition to what you said, Excel, Proclarity and Panorama, etc. However, OLAP is usually used for analysis; it is not normally used for formatted financial reporting.

Look forward to the NEW SQL server coming soon called Yukon. This new version will integrate more BI (Business Intelligence) features.

Regards,
M

 

[Dukester0122]

I have the beta for Yukon sent by Microsoft. Installed it in our testserver but never had time to play with it.

You're right OLAP is used for analysis and not for financial reporting, FRx is actually the financial tool offered by MBS.

 

[barbola]

Update! I did a search on this forum for XL Reporter, and my own thread was the only one that appeared! I re-read all the posts, and alot of it makes more sense now.

We attended a webex demo on XL reporter yesterday, and it was very impressive. We would still need to use Access as we link GP data to other db's, but maybe there is a way to do this in XLR as well.

Another poster mentioned Enterprise Reporter to do consolidations/eliminations. This is really what we're looking for but it would cost us over $100,000 and not worth it I'm sorry we can find a better way even if it's manual!

I looked for the server analysis tool, and it doesn't appear to be installed, so I will have to ask the Tech guy for it. I'm going to look into cubes!



Thanks!
Barb E.