Duplicate rows?

[Knight]

Hi!

(I am not sure if this should be under Framework Manager or Report Studio, if it is not at the right place, please feel free to move it...)

To make it more easy for our users to make reports, I imported the same table (in Framework Manager) multiple times giving it different names (in my business view) and adding a filter.

For example, let's say my "PEOPLE" table contains suppliers, employees, clients, etc... I used the same table I had in my database view and transferred it (by making a new query subject) in my business view and presenting it as 3 different "tables", suppliers, employees and clients.

To only get the appropriate entries in each of these I applied a (different) filter to each of them so they only return the appropriate entries (the supplier "table" only shows suppliers, the employees one employees, etc...).

This seemed to work nice until I tried to use them all together in a query...

I thought that if fields from these (in my case it is the first and last name) in my query I would only get one record containing the first and last name of the supplier, employee and clients...

It definitely did not work, I only got the info for one of them...

Now to debug this I look at the tabular data and I saw that the result of that query returned 3 records, in every way identical except for one only having the name of the supplier filled in, another only the name of the employee and another only the name of the client.

(The reason why I do not see these 3 records in the output is because of further processing...)

Why didn't what I tried to do work and how could I make it work?

Thank you,

Nick

[bdbits]

What you saw in the tabular data is exactly what I would expect, three records, since they are separate query subjects that have differing SQL for each one. I am not clear what "further processing" made it such that you only saw one. And if you know what suppressed them, what are you expecting different from what you saw?

Sorry, maybe I am being obtuse but I do not see the problem.

[Knight]

I was expecting it to behave as if I had joined separate tables together...

For one specific file number there is only one supplier, one employee, one client...

so if I asked it to show something like

file number, creation date, contract number, supplier first name, supplier last name, employee first name, employee last name, client first name, client last name...

I was expecting to get one row...

What I get is 3 records which contain


file number, creation date, contract number, supplier first name, supplier last name,  empty employee first name, empty employee last name, empty client first name, empty client last name
file number, creation date, contract number, empty supplier first name, empty supplier last name,  employee first name, employee last name, empty client first name, empty client last name
file number, creation date, contract number, empty supplier first name, empty supplier last name,  empty employee first name, empty employee last name, client first name, client last name

which is definitely useless for my needs and SOMETHING which my user definitely should not have to bother with, they should be able to drag these fields in their report without having to care about them being from the same table and because of this ending up with duplicate entries...

What should I have done for this to work as I intended?

Thank you,

Nick

[MFGF]

I was expecting it to behave as if I had joined separate tables together...

For one specific file number there is only one supplier, one employee, one client...

so if I asked it to show something like

file number, creation date, contract number, supplier first name, supplier last name, employee first name, employee last name, client first name, client last name...

I was expecting to get one row...

What I get is 3 records which contain


file number, creation date, contract number, supplier first name, supplier last name,  empty employee first name, empty employee last name, empty client first name, empty client last name
file number, creation date, contract number, empty supplier first name, empty supplier last name,  employee first name, employee last name, empty client first name, empty client last name
file number, creation date, contract number, empty supplier first name, empty supplier last name,  empty employee first name, empty employee last name, client first name, client last name

which is definitely useless for my needs and SOMETHING which my user definitely should not have to bother with, they should be able to drag these fields in their report without having to care about them being from the same table and because of this ending up with duplicate entries...

What should I have done for this to work as I intended?

Thank you,

Nick

Hi,

You probably need to set the Aggregation Type property on each name field to be "Maximum". I know this is normally something you'd do with a measure (Fact query item) but I'm thinking it just might work for you in this case?

Worth a try...

MF.

[bdbits]

Well, you have three query subjects, which means three results (one from each query subject); that is why you are getting three rows. Cognos is not going to magically consolidate multiple query subjects. Some ideas...

• You could union the queries in the report.
• You could create a query subject that does not have the filters for use when you need to see rows regardless of type.
• Or, maybe rethink your model a little (which I have been assuming all along is relational). Create just one query subject for all types, and expose the attribute that defines the type. When you need to filter by type, you include that attribute in the report query.

[Knight]

Hi,

There is probably something missing in my description because I don't quite get why I need to go through hoops like this.

(I sure will test what you suggested MFGF as soon as I get the chance but I think I need to add more info as it sounds like more info...)


The "PEOPLE" table has the file number in it...

Usually for each file number there are multiple entries in that PEOPLE table, it is the filter I put on each of these query subjects which enforce the only one (or no) entry for each file number.

The file number, creation date and contract number are from the even file...


If I was doing this in SQL I would do something like


select e.file_number, e.creation_date, e.contract_number, sup.first_name, sup.last_name, emp.first_name, emp.last_name, clt.first_name, clt.last_name

from

EVENT e, PEOPLE sup, PEOPLE emp, PEOPLE clt

where

e.file_number = sup.file_number and e.file_number = emp.file_number and e.file_number = clt.file_number

and

sup.role = 'supplier' and emp.role = 'employee' and clt.role = 'client'



(that role field is the one I used to set filters on each of those query subjects to only get the entries I want.)


I do know I could union the queries, have a query subject without filter and expose that attribute but I am supposed to make this easy for our users to make report and asking them to do any of this doesn't qualify as easy...

I am supposed to make it as simple as dragging a field from any of these (suppliers, employees, clients) for them to be able to make a report...

As soon as it requires doing too much manipulation they are no longer able to do it by themselves...

Is what I want to do and what I have to play with clearer now?

Thank you and have a nice day,

Nick