Different Behaviors in FM for similar Items - SOLVED

[grifter102]

I created a query subject several months ago using Cognos 8 and recently updated it to Cognos 10.  I had some item counts in the framework, like this:

if ([Selected] = 'Y') then (1) else (0)

which when used in a report would properly count the number of items that were selected.  I just tried to create a new item in the report for responded items:

if ([Responded] = 'Y') then (1) else (0)

but it doesn't seem to work properly at all.  The new item is set as a fact, and the Regular Aggregation property is set to "Sum", but, unlike the previous query item, the Semi Aggregate property is fixed as Unsupported.  I created them both in the exact same way (at least I think so) but if I test the new item using Auto Sum, it just returns 1 and 0 as the items.  If I test the old item using Auto Sum, it gives me a proper count of all Selected items.

The only difference in the properties between the two items is that the first (working) query item has both Regular and Semi aggregates set to "Sum", but the second (broken) item has Regular = Sum but Semi fixed/unchangeable as Unsupported.

Anyone know what is going on here?

[grifter102]

I'm testing out various things... and I can't even recreate the measures that I already have.  The query subject I have set up is based on five tables with one to many relationships.  The keys are Req, Item, Supplier, Candidate, Purchase.  I created a query item at each level using determinants with the definition of 1, set as facts to be summed.  When I bring in those query items into query studio, it properly calculates the counts, provided the report is at the proper level (i.e. if i drag a supplier level item into the report, the req and item counts will be off... but otherwise they're all right).

I tried to just recreate one of the items that I already had.  I created a "Supplier Count 2" item, set the definition as 1 and set as many of the properties as I could to be the same as the existing item. I couldn't modify the datatype, which was stuck as blank (the original item is set as Int32, and I couldn't change the precision or size). I added it to the same determinant.  I published the package, and when I try to add the Supplier Count 2 item, Query Studio sees it as having an unknown data type and will not sum it... it only displays it as 1, and that's it.

Now, I don't have a photographic memory so I don't remember the exact steps that I took to originally create the Supplier Count item that works perfectly... but I'm pretty sure I followed the same steps for the Supplier Count 2 test item... but I cannot get it to work.  Is there some step or option to change or something that I'm missing?  This is driving me a bit mental.

[wyconian]

Hi

I could be wrong on this but as I understand it the aggregations you ca use are dependant on the data type from the source database.  So you get sum, avg etc etc for numeric data items but unsupported or unknown for text data types.

Do you have access to the source database?  If you do check the data types and if possible cast them as numerics.

[grifter102]

You're right about the source database and datatypes... but I'm creating a computed item with a hardcoded definition of 1.  So there's no source field or a datatype.

[blom0344]

Usage settings of dataitems of imported query subjects may influence the ability to set the usage items in model query subjects based on them .  So check settings in the database layer..

[Lynn]

Can you look at the SQL being generated to get a clue? If there is an integer key that uniquely identifies a record you could try using that instead of the hardcoded 1 and then set the aggregation to count. This will allow users to get a count of records regardless of what other query items are included.

Semi-aggregate property applies only to SAP BW sources as far as I know.

[cognostechie]

I created a query subject several months ago using Cognos 8 and recently updated it to Cognos 10.  I had some item counts in the framework, like this:

if ([Selected] = 'Y') then (1) else (0)

which when used in a report would properly count the number of items that were selected.  I just tried to create a new item in the report for responded items:

if ([Responded] = 'Y') then (1) else (0)

but it doesn't seem to work properly at all.  The new item is set as a fact, and the Regular Aggregation property is set to "Sum", but, unlike the previous query item, the Semi Aggregate property is fixed as Unsupported.  I created them both in the exact same way (at least I think so) but if I test the new item using Auto Sum, it just returns 1 and 0 as the items.  If I test the old item using Auto Sum, it gives me a proper count of all Selected items.

The only difference in the properties between the two items is that the first (working) query item has both Regular and Semi aggregates set to "Sum", but the second (broken) item has Regular = Sum but Semi fixed/unchangeable as Unsupported.

Anyone know what is going on here?

I presume you are creating this data item in the Model Query subject in the Business Layer. In that model query subject, drag & drop a data item that is of data type integer/decimal in the database. This data item could be from any query subject  regardless of whether it has proper joins or not. Ex: Qty which is marked as integer in the databse table.  Save the model. You will see that both Regular Aggregate and Semi-aggregate are marked as Sum. Open this new query item again and replace the definition with if ([Responded] = 'Y') then (1) else (0)

[grifter102]

I did exactly what you said on my own when I was trying various workarounds and it worked perfectly.  Now whenever Cognos fails to vive an item the proper type and aggregation settings I just pull something in that already has the types that I want and change the definition.

Thanks for the help.