difficult report design

[springle]

Greetings to all,

I am having a very tough time designing a report involving 3 tables from out CRM system, Goldmine, and I think I just need a push in the right direction.

What I am trying to accomplish is to show all activities both schedule (CAL table) and completed (HIST table) by a user in a given time period. The report would be somthing like select a date range, select a user, and then display details and count of all activities, pending and completed. I would like to have one line per acitivity.

The problem is that scheduled and completed activities are in separate tables and each linked to the main contact talbe via a one (contact) to many (activity) link by an account number.

At first I was thinking that a sub report would be the answer but I have not been able to make that work.

Then I though what if I merged the CAL and HIST tables into one. Then the report would be simple. If that is feasable then it might be able to succeed since design of the CAL and HIST tables are VERY similar and both share the same linking field, accountno.

Thanks in advance for you help,
Scott

 

[lbass]

You could use the subreport method. You would group on the account number and place the subreport in a group header section_b, and then link it to the main report by account number and date parameter. Then format the GH_b to "underlay following sections".

Otherwise, you could try a union all statement to combine the tables. How you approach this depends on what version of CR you are using.

-LB

 

[springle]

Sorry, for got to mention I am using CR 9.2 Pro. How does my version affect your recommendations?

Scott

 

[lbass]

I asked because the version affects how you would do a union all. In 9.0, you would use "Add Command" as your data source and perform the union all there. It would be easier to recommend an approach if I could visualize the final result you are looking for. You say that it would be easy if you could merge the tables into one, but I'm not sure how that would solve the problem, again because I don't know what the final result should look like.

-LB

 

[aaronburro]

This might be a stupid question, but are the two tables mutually exclusive? As in, would a scheduled activity also be in the completed activity table, or a completed activity in the scheduled table?

Or, even better, is there a table which holds all activities, scheduled, completed, and any other types?

If the answer is no to both questions, then I can only think that a subreport would be the best option. I'd do two subreports, one for each table and just put them into two separate sections w/ no groups.

I'm assuming that you want the report to look like this:


Activity Status Details
1 C
2 S
3 C
4 C
5 C
6 S
7 C
8 S
...

granted, if the completed and scheduled activities are in separate tables, then the different activity types won't be interspersed among each other... it'd have to be a block of each type of activities.

 

[synapsevampire]

Springle: Why do you want to know why posting your software version matters? If you're new to IT, I can understand that you may not understand that software evolves, and with it comes functionality. The 1st question asked by tech support will be your software version, and given that you're using Goldmine, it's version and the underlying database.

In general try posting technical; information when requesting technical assistance:

Crystal version
Database/connectivity (not Goldmine)
Example data (show tables.fields used)
Expected output

This is basically a mini-software requirement, and will minimize the discovery process.

LB: The Goldmine version and it's underlying databsae are good questions as well as Goldmine used to be xbase, and a SQL Expression probably wouldn't work.

-k