Basic question about Data Item properties and Joins

[Anke]

Question about Data Items and Joins in a report. 

Example:
First I create a query and add a data item, then change the Data Item property:  Aggregate Function to Maximum.  Then the Query1 is joined to Query2 and all of the Data Items moved over to the new Query3.  If you look at the Data Item created in Query1 with the Aggregate Function of Maximum...the Aggregate Function now say Automatic.  All items now have a 'lightning bolt' in the icon...and in the Data Item properties say Automatic for Aggregate Function/ Rollup as they did before, but the icon looks different.

My question: As you create new Data Items and queries/ joins, should I be continuously updating the Data Item's properties to what I initially set it as back in the original query??  Why do the Data Items change back to the default settings when you create a Join and move items?

I am wondering this because a consultant pointed out that I should be changing these properties back to what I set them as or changing to 'none' ...he said they could give me bad data if I didn't. He said when the icon has the little lightning bolt, it should be changed back to none (But weren't these set to auto in the first place?—they are on mine). 

Personally, I cannot think of anytime I have gotten bad data from not changing and I was thinking the original change was saved to the Data Item as it moved through all of my joins...

Is this true??  :-\

[melee]

I am wondering this because a consultant pointed out that I should be changing these properties back to what I set them as or changing to 'none' ...he said they could give me bad data if I didn't. He said when the icon has the little lightning bolt, it should be changed back to none (But weren't these set to auto in the first place?—they are on mine). 

Is this true??  :-\

I have had this issue many, many times with JOINS. My suggestion is to change it to none, as the aggregate function is performed at the query level prior to the join, making another aggregate (or even an automatic aggregate) redundant and open to errors.

[cognostechie]

Something to think about is if you are joining two queries to form a third query, then you would probably be displying the third query in the report and the 1st two queries are to be used to provide data to the third one.
So can't  you just remove any aggrgrated/summarised Data Items fron the 1st two queries and create those aggregrated items only in the third query?

That's how I do it and works fine without these addiotional headaches?

The Maxiumum/None/Total/Automatic can change the data and are meant to
be used to calculate the aggregation differently in case you need different calculations in different columns.
In some cases, we don't need to Total everything, in some cases we need to summarise not from the detail level but from the summary level itself , percetages , averages etc.

[PRIT AMRIT]

So can't  you just remove any aggrgrated/summarised Data Items fron the 1st two queries and create those aggregrated items only in the third query?
That's how I do it and works fine without these addiotional headaches?

I do agree with cognostechie, this is how I have been dealing with UNION & JOIN queries. Especially when you have a lot queries and you are performing an UNION, it's always a best practice to do the Aggregation/Calculation in the Final Query which points your report.

Thanks
Prit

[Anke]

Okay...yeah, sounds like everyone changes the data item properties at some point.  Yes, I think I will go with changing my process to make all property changes in the final 'main' query.  For me, it would be a PITA to make property changes in every join query - as many of my report have 12+ joins.

Thank you for all of your comments, I know I can always come here and get great answers  :)

[jive]

Hi Anke,

Look at that document : Cognos 8 Report Studio Creating Multi Query Reports in the hint section at the end of the doc everything is there.


Regards Jacques

[nsaxena]

Hi all ,

I am struggling with same issue.

Did if fixed by above steps?