Changing grouping at runtime

[marabou]

I was wondering if anyone could help me with this problem.

I have a table which is full of licences bought, which includes the customer id where the licence is held and if the organisation who bought it, bought it through an agent (another customer), the customer id of the agent.

How can I get my report to group on agents and detail the licences they have sold and if there is no agent (customer, to group on customer and detail the licences they have bought. At the moment I have two seperate reports, one which gives me the agents and their customers and one which gives me the direct customers. I have to amalgamate the two.

Is there a way of doing multiple-page reports in CR8, or does the answer lie within the group selection formula editor.

Either way, I am very stuck and would appreciate any help whatsoever any one could give me.

Thanks in advance

 

[IdoMillet]

Join:
License - Customer
- Customer (Aliased as Agent)]
by adding the Customer table twice and ensuring you have an Outer join between License and Agent.

Group on Customer as Agent and within that on Customer.

You could create a formula field that returns "No Agent"
if Customer as Agent is Null (survived due to the Outer Join) and "Agent Name" otherwise.
Then Group on that at the top level (instead of on Agent Name) to ensure you get a meaningful Group Header for
cases without an agent.

hth,
- Ido

 

[marabou]

Thanks for the help.

I had already solved the problem, by using the same joins as you suggested, but grouping on Customer ID and adding an extra section within the group which holds the agents name and details, which only appears if the customer is an agent.

It is good to know I was on the right lines.