Custom Reports

[mikebrewer]

Hey all,

I have a custom program that I've developed using Access 2007. I currently have about 30 reports that I use from the program and I use SQL as the back-end database. I'd like to allow my users to create custom reports and be able to run them.

Basically, I'd really like it if the users could use a platform like Crystal Reports or anything similar (I don't know of anything else) so that users can create reports and then choose them as their preferred reports. Is there a way to allow users to create reports in access without having the full version? Can I give them a way to create access reports in access without having full access to everything?

Is there a program available that does such a thing? Is it easy to work with crystal reports from Access or does anyone have a good suggestion?

thanks for your help!

 

[Pat Hartman]

Giving them each a copy of Crystal Reports will probably cost more than giving them each a copy of Access. Since your BE is SQL Server, the reporting tools there might solve your problem.
The runtime version of Access does not allow you to create objects so it couldn't create reports. You could build a simplistic report writer but I wouldn't even attempt it because nothing I built with Access would be as feature rich as Access' own report writer.

 

[mikebrewer]

well, there becomes my problem. My program is sold to shops and for their use, I'm not going to be "giving" them anything, they will decide whether they want to buy it or not. I just want to allow them to create their own reports (should they want to).

 

[Pat Hartman]

If the client wants to use Crystal Reports against SQL Server, he can do so. If you are asking how to integrate those reports into your application, that is a different problem. I think the viewer for Crystal is free so you would need to package that with your app if your app will include code to open Crystal reports. As to how you do it, I don't remember it has been so many years since I used it. I'm going to guess that if the viewer is installed and registered, the FollowHyperlink method will open a report so you might want to create a SQL server table where the users can log their reports that they want to share. Of course, the reports would need to be in a shared directory.

I also have an app that is sold to the public. I've created several exports to Excel for the clients. There is some filtering in the Access app but after the export is created, they can do whatever they want.

 

[mikebrewer]

I'm glad you mentioned that. I was just thinking about how easy it may be to package something or allow them to create Word templates or Excel templates that they could then use as their reports. The whole key to it is allowing them to be able to edit/create their own files. Main reasoning is for receipts for repair work, I want them to be able to create their own branding, logos, language, etc. on their proposals/receipts. This way, they have a little more control over it. I have some canned things they can use but I like the ability to allow them to have control over it. Have you had success or is it simple enough to create reports based on those excel sheets?

 

[Pat Hartman]

The application I mentioned also works extensively with Word templates. The users create their own Word documents with bookmarks. Then they define the document to the application by creating a cross reference of the available application fields to a bookmark in a document. If you are NEVER going to have to use the same data item more than once in a document, you can avoid the xref step by simply insisting that the bookmark names equal the application field names but the xref gives you more flexibility and once the users get the hang of it, they don't have trouble. I also gave them a copy feature so they can copy the xref from one document to another and then just delete and add the differences.
They customize the logo and anything else they want. The client uses this app to conduct benefits audits for their clients so each "audit" has a different set of documents with different customization.

You could allow them to customize the logo in your access reports by making a bound OLE control if that's the only problem you have.