Creating Custom Reports

[mathe]

I would like to create custom reports from ACCPAC Advantage series and service manager by editing the existing ones or by starting from scratch. My problem is how to create ODBC connection to the database so that i have access to the tables necessary to come up with such reports.
Somehow i can't seem to find the tables used in the already existing reports in our database using a database editor . Where do these tables reside?how do i connect to that location?

 

[jayconverse]

First set the database location to your Accpac ODBC DSN.

Jay Converse
IT Director
Systemlink, Inc.

 

[mathe]

That is what i have done but the tables used in the already existing reports are no there in the data source.

 

[jayconverse]

Then you used the wrong data source!

Name some of the tables you can't find.

Jay Converse
IT Director
Systemlink, Inc.

 

[mathe]

some of the tables are,
POAGED
VMDH
VMDTIT
POHISTS

 

[jayconverse]

POAGED and POHISTS are not tables, they're called datapipes. They are in fact programs that collect data and feed it to Crystal reports. You can't join them to regular tables.

However, VMDH and VMDTIT are indeed regular tables, I use them all the time. Therefore, you're using the wrong ODBC DSN to connect. Use the same one as Accpac uses in database setup (it might be servername32).

Jay Converse
IT Director
Systemlink, Inc.

 

[mathe]

I only have the following tables in the data source:
master.dbo.spt_datatype_info
master.dbo.spt_datatype_info_ext
master.dbo.spt_fallback_db
master.dbo.spt_fallback_dev
master.dbo.spt_fallback_usg
master.dbo.spt_monitor
master.dbo.spt_provider_types
master.dbo.spt_server_info
master.dbo.spt_values
master.dbo.sysconstraints
master.dbo.syslogin
master.dbo.sysopentapes
master.dbo.sysremotelogins
master.dbo.syssegments
master.INFORMATION_SCHEMA.CHECK_CONSTRAINTS
master.INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
master.INFORMATION_SCHEMA.COLUMN_PRIVILEGES
master.INFORMATION_SCHEMA.COLUMNS
master.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
master.INFORMATION_SCHEMA.CONSTAINT_TABLE_USAGE
master.INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
master.INFORMATION_SCHEMA.DOMAINS
master.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
master.INFORMATION_SCHEMA.PARAMETERS
master.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
master.INFORMATION_SCHEMA.ROUTINE_COLUMNS
master.INFORMATION_SCHEMA.ROUTINES
master.INFORMATION_SCHEMA.SCHEMATA
master.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
master.INFORMATION_SCHEMA.TABLE_PRIVILIGES
master.INFORMATION_SCHEMA.TABLES
master.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
master.INFORMATION_SCHEMA.VIEW_TABLE_USAGE
master.INFORMATION_SCHEMA.VIEWS

I realy don't know where i'm getting this wrong. I did check that VMDTIT for example is a valid table using a database editor but somehow it doesnt appear in the above list.

 

[jayconverse]

Looks like you're using Windows authentication to the database. Either use SQL authentication, or check your permissions.

Jay Converse
IT Director
Systemlink, Inc.

 

[mathe]

Ok I managed to adjust the security rights of the user(assigned user as "db_datareader") in the database and using crystal reports 9 i now have access to the tables but with crystal reports 8.5 i still don't have access to the tables!

 

[jayconverse]

Use SQL authentication, like Accpac does.

Jay Converse
IT Director
Systemlink, Inc.

 

[Storyteller]

Hello mathe,
Are you using both Crystal Reports 9 and Crystal Reports 8.5 in the same organization?
Please be aware that if a report is created or saved in Crystal Reports 9 format the Crystal Reports 8.5 users will not be able to open/use the report.
I would suggest you create a backup copy of all your reports in Crystal Reports 8.5 format before you let the CR 9 users access the reports.

Regards,
Michael

 

[mathe]

I was forced to use vers 8.5 to edit reports since when i tried to use vers 9 I always had an error when i ran the report using ACCPAC.
I understand what you mean and im well aware that reports edited using vers 9 won't open with vers 8.5
My problem though is that i still cannot access the tables using my ODBC(uses SQL authentication) when im on version 8.5 but i can with vers 9, you can understand that its a big a problem since i use vers 8.5 to edit my reports.

 

[jayconverse]

Again, I don't know what to say. I use version 8.5 all the time (and 9, and 10, and even 7), and I use ODBC with all of them. The fact that you had to mess with SQL security (db_datareader) to get things working tells me you're still using Windows authentication.

Jay Converse
IT Director
Systemlink, Inc.

 

[mathe]

Ok I created a new FILE DATA SOURCE and used the same login credentials I use for my already existing ACCPAC Data source, somewhere along the setup there is an option of changing the default db(default is master) so i changed it to be my ACCPAC db and guess what, the tables now appear.
I now have an ODBC called "MyODBC.dsn" and its working!!
Thanx again for all your troubles.

 

[Michael57]

I have created a report in crystal reports that I would like to access through a menu item in accpac advantage series but I'm not sure how to do this while at th same time setting up a selection process for the report. I would like to print a receiving slip for pos but I would like to generate this report based on pos selected in a range prior to printing the report.

 

[jayconverse]

You need a VBA front-end for the drop-downs, then you need to add your report to xxRPT.INI to pass in the parameters from your drop-downs.

Jay Converse
IT Director
Systemlink, Inc.

 

[ettienne]

or create a shortcut to the report on the Accpac desktop, you will be prompted for the Crystal parameters when you run the report.