2

I have just started a new job where I am responsible for building and improving reports.

There is one report I am trying to move from Access to Crystal. The data is stored in an application called Acctivate. The previous person who held my job built the report with a series of queries that built multiple tables, and then a final query which aggregated the information from the tables built by the first five queries.

The PTB want the report in Crystal so the management for each client can call up their info and run the report, rather than have to call me and have me do it.

What is the best approach to this multi-level query (currently make-table) that will let me move the report out of Access?

  • 1
    What data store will you be using going forward? – Mr.Brownstone Dec 10 '18 at 20:14
  • Reporting tools by definition cannot create or load table structures. Access is not a reporting tool by definition, it is an RDBMS. You will need some external tools or scripts to handle that part or you can still use Access to handle the data loading. Is the data source still going to be an access database or are you migrating the data out as well? – Shooter McGavin Dec 10 '18 at 20:20
  • @ShooterMcGavin not sure i agree with your statement. A report can issue an embedded query or execute a stored proc which could create or populate tables. (Temporary or permanent) – Sir Swears-a-lot Dec 11 '18 at 8:18
  • @Sir Swears-a-lot A stored proc would require an RDBMS. The question implies using Crystal to directly manipulate data to load tables similar to Access which isn't possible. Crystal, at least the versions I've used will not allow a sprok if it issues update or insert statements. Regardless, that would require an RDBMS which we still don't know. – Shooter McGavin Dec 11 '18 at 14:23
  • @ShooterMcGavin yes you are right. I was getting a bit ahead of myself. Im assuming there is an rdbms behind the scenes. – Sir Swears-a-lot Dec 11 '18 at 17:49
1

Given that you have a SQL Server backend you have a few options depending how much freedom you have to add objects to the application db or on the server.

If you are not permitted to add objects to the native schema i would request a small report db on the same server where you can create views or stored procs that reference the source database schema using 3 part names.

If you can re-write the query and deploy it as a view that might be a good option. If the query is too complex to be rewritten as a single query then you could reproduce the behavior of "make tables" with temorary #tables and deploy it as a stored procedure.

If you arent permitted to create objects on the server at all i would rewrite the query using #tables and embed the whole thing in the report query.

Views and SP's get better performance as the number of users/hits increase compared to embedded queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.