Can we place two command objects fields in one section ?

[Former Member]

Hi Everyone,

I have a report contains 2 command objects.As per my requirement i need to place those 2 command objects fields in one detail section.

But there is no link between those 2 command objects.Those two queries from different tables. If i placed those 2 queries  fields in one details section i am getting wrong results.Can any one please tell me what i have to do in this situation.Any solution would be greatly appreciated

[abhilash_kumar · Accepted solution]

Hi Vissu,

If you have unlinked commands/tables in your report and if you include records from both the command objects/tables, you're ought to get inflated results. This is also known as 'Cross-join'.

Is there a reason why you want fields from both the tables side-by-side?

1) Either create one single command object by joining the two tables OR

2) Add the 2nd command object in the Subreport and place this Subreport on the Report Header. Then go to the Section Expert > Report Header and check "Underlay Following sections".

-Abhilash

[Former Member · Accepted solution]

Hi Abhilsh,

Actually in my report there are 2 commands.One command object contains from time to time,some sales fields.in 2nd command there is a field called lead (Supervisor for a particular period in store).

So i need to show from time,to time,sales,lead.

from time     to time     sales       lead.

2                      3          333           bob

But i don't have any link between those 2 command objects.Those 2 are from different tables.Similarly i have number of command objects from different tables to place in one section only.

Please resolve this.

PFA

[abhilash_kumar · Accepted solution]

So, there isn't any common field to link between the two command objects right?

How many values do you have in the "Lead" field? If there are multiple records, then how do you tell which lead belongs to which from time and to time combination?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

It contains multiple values and some times nulls.(No lead)

No common field between them and that is the problem for me.Can you suggest what i have to do ?

[abhilash_kumar · Accepted solution]

If there is no common field to link on, then you can't show the "Lead" column beside the other columns from Command 1. You will always get wrong results especially when you have multiple records in the "Lead" column.

My question again is, take your data as an instance, how do you know "Bob" is associated with Sales - 333?

Even if you plan on using a Subreport, you need some column that you can link on.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Give me some time as i am checking all my queries again.

[Former Member · Accepted solution]

Hi Vissu

This works similar to SQL queries without joins we will see Cartesian product

Regards

Bose

[JWiseman · Accepted solution]

hi vissu bab,

the easiest way out of this would be to take Abhilash's suggestion of using a subreport for each command. i.e. have your main report run off of your main command and then for each section that you need the different data, use a subreport based on the appropriate, single, command.

if you want the more difficult way you can always union the data together in a single command. however, this is going to get messy fast as you'll need to union the same number of fields (or dummy values) ensuring that they're the same type.

e.g.

select datefield1, datefield2, numberval1, numberval2

from table1

union all

select {ts '1900-01-01 00:00:01'}, {ts '1900-01-01 00:00:01'}, 0, numberval3

from table2

note that in the above a dummy date is used twice and a dummy number value is used once in the 2nd section of the union. also note that a "union all" is used as this will be much faster than a "union" which looks for distinct results.

cheers,

jamie

[Former Member · Accepted solution]

Hi Abhilash and Jamie Please find the queries.These queries are already written .So i need to change according to requirement.

The 1st query contains from time and to time

2nd query contains some sales fields

3rd query contains lead

So now i need to display sales fields based on time interval and also lead based on time interval.

Can you please suggest what i have to do to achieve this.I have to use sub queries or Sub report

Actual sales data.txt.zip

Hour group.txt.zip

Lead.txt.zip

[JWiseman · Accepted solution]

that's really up to you as to which method that you want to pursue. subreports would most likely be easier than trying to get everything on the main report using one query or a combination of queries.

link each subreport on the time prompts / parameters which should be included in each query.

if you haven't built a subreport before, i would advise you to go to the online help in crystal reports and look up "subreport linking". there will be many different topics there that will help you.

the basic idea though is to base your current report on the main / largest query. each subreport will then be based on a different data set. now go to the Insert menu, choose Subreport, and then you'll choose a connection. choose the next query that you want to deal with. there is a Links tab that you will use to match up the date prompts between the main report and the subreport.