Issues with grouping in list in ReportNet

[stacybost]

I have a list report grouped by Week, Date. However, I am still getting mulitple rows per date rather than a sum. Any thoughts?

Thanks,
Stacy

 

[flex13]

I would need a better example of what you are trying to do, but this could be the way that the data types have been defined in the package. For instance:

Week Amount
1 10
12

Would not sum up if Amount has been set as an identifier or attribute. IF it was a fact, you would get

Week Amount
1 22

Please provide a better example if this doesn't help.

flex

 

[CognosProfessional]

Total(Amount)for Week after your Week is a grouped item.

CP

 

[flex13]

If Amount is a fact, then the value should sum automatically. IF it doesn't, there is something different with the dates, date-time perhaps?

 

[stacybost]

Thanks for the replies. Haven't had a chance to get back to this report but will definitely use your feedback when I do!

Stacy.

 

[stacybost]

Here's more detail...

I have a Parent tabular query and a Child tabular query. In each query, I select week_id, dt, store_id, total(fact1), total(fact2). Then, in the Parent query I have a filter where I set Parent.dt = Child.dt and Parent.store_id = Child.store_id, and I pull the Child facts into the Parent query. However, in my report list I only want to group the data by week_id and dt. In the Query, I have only week_id and dt as dimensions. However, I still get a row of data for each store_id under each date in the list report, like so:

Week_id Dt fact1 fact2 fact3
fact1 fact2 fact3
fact1 fact2 fact3
Dt fact1 fact2 fact3
fact1 fact2 fact3
etc.

I can't really set the store_id to be a fact, because I think it will mess up my filter, where I am joining the Parent Query to the Child Query. I WAS just selecting week_id, dt, fact1, fact2, etc. and joining on dt, but I encountered a performance problem (see my posting titled "ReportNet Query Performance&quot.

 

[stacybost]

CP,

That would probably work, but I'm afraid it will impact performance. I have a lot of facts, and a lot of calculations on the facts, so I'd have to do a lot of, for example:
total(amount) for report / total (count_of_stores) for report

Do you think the report will take a perfomance hit if I do it that way?

 

[stacybost]

CP,

I did try your suggestion, but kept getting a syntax error. I tried 'total(amount) for Day' and 'total(amount for Day)'. Neither worked. Any thoughts?

 

[stacybost]

Okay, I discovered the Level Span property for a Column List, which seems to fix the formatting. But now my data is not correct - instead of summing the data for the date, it appears to be displaying just the first row of facts that happens to appear. Am I missing something?

 

[sharatkb]

Hi Stacy,

Whatever Flex has suggested is correct....so I would request you to verify the same in FrameWork Manager and also the 'Aggregate' option set for it.

Though for a fact it by default sets the aggregate option to Sum...

Regards,

 

[stacybost]

Thanks, think I finally have it working. In the tabular query, I set the Aggregate Function for store_id to 'Aggregate' in my parent tabular query (though not in the child tabular queries). For week_id and dt, I left the Aggregate Function set to 'None'.

Although logically this seems a bit odd to me (aggregating on store_id when I'm setting query1.store_id = query2.store_id), the data looks good (so far).