I set up the Total in many difference way, but it didn't work right.
Any helps? Thanks a lot
EFF_DT = minimum ([EFF_DT] for [CAN_DT],[ID])
CAN_DT = when [CAN_DT] to_date('0001-01-01','YYYY-MM-DD') then 2020-01-01
Enroll = _days_between (CAN_DT,EFF_DT), and any days before 2018, will not count
Total (Enroll for [ID])
1. NOT WORK - Tried a new sub query, add new data item Total (Enroll for [ID]),
Detail aggregation: default/calcutated
Summary aggregation: default
3. NOT WORK Tried a new sub query, add new data item Total (Enroll for [ID]),
Detail aggregation: calculated
Summary aggregation: calculated
4. NOT WORK Tried a new sub query, add new data item Total (Enroll for [ID]),
Detail aggregation: sumary
Summary aggregation: default
5. NOT WORK Tried add new data item Total (Enroll for [ID]),
Detail aggregation: default
Summary aggregation: default
ID Effective Date Cancel Date Enroll Days Total Enroll Days
AD Jan 1, 2017 Jan 1, 2018 0 617
AD Jan 1, 2018 Jan 1, 2019 0 617
AD Jan 1, 2019 Jul 1, 2019 181 617
AD May 28, 2019 Jan 1, 2020 218 617
BE Jan 7, 2018 Sep 1, 2018 0 735
BE Sep 1, 2018 Jan 6, 2019 5 735
BE Oct 25, 2018 Jan 1, 2020 365 735
show us what the correct data should look like
correct data should be:
AD: 181+218 = 399, right now showed 617
BE: 395+5 = 400, showed 735
Thank you
Have you looked at the SQL that Cognos is producing to see what it is really asking the database server?
Quote from: dougp on 28 Oct 2019 12:02:45 PM
Have you looked at the SQL that Cognos is producing to see what it is really asking the database server?
How can i do that? thanks a lot
If you are using a relatively new (i.e. within the last half decade) report studio you edit the report, click on the cogwheel on the top right and choose show generated sql/mdx
If you are using a really old report studio you need to invoke a menu to get at that but I don't remember it.
What is the values of the aggregation and usage properties of your query items?
I wonder if you have wondered why the values you got came about. The values below suggests a pattern.
181 1 181
218 2 436
-----
617
5 1 5
365 2 730
------
735
If you have an ID where there's 3 or more records with values, does it result in a value for total enroll days similar to this pattern?
v1 1 1*v1
v2 2 2*v2
v3 3 3*v3
-------
I think one thing to investigate is that something is aggregating because it has a usage of measure rather than one which would be appropriate for the object given its role, either identifier or attribute. No matter what, you will probably find that it is doing exactly what you've told it to do.
Another thing to consider is the possibility that your enroll expression, which you don't provide in full, might be wrong.
good observation. as doug suggested, better examine the sql generated. most probably some issue with the joins