Alternate sunday's display along with the summation in Cognos 10.2.2 relational

[xyz]

Hi Gurus,

I am using Congos 10.2.2 and relational model, data source is sql server. I have a requirement of a line chart, where I have to display alternate sunday's for the given date range, along with that I have to sum up the in between sunday's to previous sunday's total.

Example of Sundays in January and its totals for the current year.

Sunday's of 2016 Year                        Total
--------------------------                        ------
03/01/2016                                      100
10/01/2016                                      250   
17/01/2016                                      570
24/01/2016                                      660
31/01/2016                                      300

Expected Output of alternate Sundays:

Sunday's of 2016 Year                        Total
--------------------------                        ------
03/01/2016                                      350
17/01/2016                                    1230
31/01/2016                                      300


For the first sunday i.e. 03/01/2016, I am adding the total of 10/01/2016. So for 03/01/2016, I have a total as 100 and for 10/01/2016, I have a total of 250. Total output for first sunday is 350 (100+250). Similarly for 17/01/2016 is 570 and
24/01/2016 is 660, finally when I display 17/01/2016 then the total is 1230 (570+660) and so on.

I am able to get all the sunday's with the below expression, but unable to get the alternate sundays and with the corresponding total.

cast(CASE (_day_of_week(getdate(), 7) )
WHEN 1 THEN getdate()
WHEN 2 THEN _add_days(getdate(), -1)
WHEN 3 THEN _add_days(getdate(), -2)
WHEN 4 THEN _add_days(getdate(), -3)
WHEN 5 THEN _add_days(getdate(), -4)
WHEN 6 THEN _add_days(getdate(), -5)
WHEN 7 THEN _add_days(getdate(), -6)
END,date)

Can you please guys, suggest me, how will I get the alternate sundays display with its corresponding the totals?


Thanks & Regards,
XYZ

[Lynn]

You can try the same technique that is used to shade alternating rows in a list report to identify every other row. You can google for the technique, but the basic idea (if memory serves) is to use RowNumber() and the mod() function. Perhaps using this you can figure out how to construct a data item that would get the grouping you need. If I have time I can try to mock something up and see if this suggestion holds any water.

[BigChris]

I think you might need to build in the week number to get the right Sunday. Bear with me...this is off the top of my head, so you might need to test and tweak.

_add_days(current_date,_day_of_week(current_date,7)-1-mod(_week_of_year(current_date,2)*7)

The first part is a calculation that mimicks your original case statement, then takes off another 7 days every other week. Anyway, experiment and see if it works for you.

[navissar]

I'd take a different approach.
Should be easy enough to identify the first Sunday - It'll be the one that's no more than 6 days after the given start date and that is a Sunday. So I'd create a data item and call it First Sunday.
Next I'd run through all the dates, and check the number of days between them and First Sunday. If that number can be divided by 14 with no remainders then put in 1, else 0. Let's calls it the Every Other Sunday Flag.
Next is an item doing a running sum of the Every Other Sunday Flag. That would mean that for every 14 days Sunday to Sunday we will have a single number (0 for the first fortnight, 1 for the next and so on).
Next is a data item which finds the days between the date and ([First Sunday]+[Every Other Sunday Running Sum]*14)(Which should give you the nearest alternating Sunday), and reduces the date by that many days. And you group by that.

[BigChris]

Of course we're missing the best solution...

Build a calendar table in your database...one of the columns on the table should be "Reporting Sunday"

[navissar]

Of course we're missing the best solution...

Build a calendar table in your database...one of the columns on the table should be "Reporting Sunday"

+1

[xyz]

Thanks every one, for your suggestions and replies.

I like Nimrod's approach, but unable to achieve what is suggested in the email.
Nimrod, can you please help me with the expressions in data items in Cognos on how to achieve my requirement, that would be very helpful for me.


Thanks & Regards,
XYZ

[navissar]

I'd take a different approach.
Should be easy enough to identify the first Sunday - It'll be the one that's no more than 6 days after the given start date and that is a Sunday. So I'd create a data item and call it First Sunday.
Next I'd run through all the dates, and check the number of days between them and First Sunday. If that number can be divided by 14 with no remainders then put in 1, else 0. Let's calls it the Every Other Sunday Flag.
Next is an item doing a running sum of the Every Other Sunday Flag. That would mean that for every 14 days Sunday to Sunday we will have a single number (0 for the first fortnight, 1 for the next and so on).
Next is a data item which finds the days between the date and ([First Sunday]+[Every Other Sunday Running Sum]*14)(Which should give you the nearest alternating Sunday), and reduces the date by that many days. And you group by that.

Just thought of an even better approach. Here's the logic.

Data Item - First Sunday - finds the first Sunday of the range given.
if ?startdate? is a Sunday then ?startdate?
else ?startdate?+8-day of week of ?startdate? (base 7)

So, for a Monday you will go to the next Tuesday (+8) and then back 2 (-2 for the day of week of Monday) resulting in the coming Sunday. And this is true for the rest of them as well.

Data Item Alternating Sunday:
If mod(days between Date and First Sunday, 14)=0 then Date else First Sunday+(14*Div(number of days between Date and First Sunday,14)).
This will return the date of the nearest fortnightly Sunday.

And I think that's it.

[navissar]

Thanks every one, for your suggestions and replies.

I like Nimrod's approach, but unable to achieve what is suggested in the email.
Nimrod, can you please help me with the expressions in data items in Cognos on how to achieve my requirement, that would be very helpful for me.


Thanks & Regards,
XYZ

Try the new logic I gave. Putting it to code should be simple.

[xyz]

As Big_Chris and Nimrod, suggested,

I don't have a access to do the edit to sql server database, which is production database, I have to raise the request and process would take a longer time.

I tried getting the alternate sunday from sql server database with the below code.

DECLARE @StartDate DATETIME
SET @StartDate='01/01/'+DATENAME(Year,GETDATE())


;WITH CTE AS
(
       SELECT @StartDate as dt, 1 as Id

       UNION ALL

       SELECT DATEADD(dd,1,dt),CTE.Id + 1
       FROM CTE
       WHERE Id <= 365)

SELECT ROW_NUMBER()OVER(ORDER BY dt) AS ID1,
dt AS SUNDate
FROM CTE A
WHERE (DATEPART(dw,dt)=1)
and ID % 2 <> 0
and dt < = getdate()
OPTION (MAXRECURSION 0);

When I tried the above code in Cognos sql, because of the temp variables, query is not executing even though it is getting validated.

I just pasted the above code, it might help someone else.


Thanks & Regards,
XYZ

[xyz]

Just thought of an even better approach. Here's the logic.

Data Item - First Sunday - finds the first Sunday of the range given.
if ?startdate? is a Sunday then ?startdate?
else ?startdate?+8-day of week of ?startdate? (base 7)

So, for a Monday you will go to the next Tuesday (+8) and then back 2 (-2 for the day of week of Monday) resulting in the coming Sunday. And this is true for the rest of them as well.

Data Item Alternating Sunday:
If mod(days between Date and First Sunday, 14)=0 then Date else First Sunday+(14*Div(number of days between Date and First Sunday,14)).
This will return the date of the nearest fortnightly Sunday.

And I think that's it.

Hi Nimrod,

Thank you very much for the solution.

I am able to implement the second logic as suggested, but got little confusion with the below statement.

Data Item Alternating Sunday:
If mod(days between Date and First Sunday, 14)=0 then Date else First Sunday+(14*Div(number of days between Date and First Sunday,14)).
This will return the date of the nearest fortnightly Sunday.

My data item expression

if (mod(_days_between ([JiraIssue].[Q - JiraIssue].[CREATED], [First_Sndy_DtRng]), 14) = 0 )
then ([JiraIssue].[Q - JiraIssue].[CREATED])
else ([First_Sndy_DtRng]+(14*mod(_days_between ([JiraIssue].[Q - JiraIssue].[CREATED], [First_Sndy_DtRng]),14)))

I am getting the error with incompatible datatype between date and int.

I have used mod in the else statement, can you please let me know, if my data expression is correct? Where am I doing wrong?

else ([First_Sndy_DtRng]+(14*mod(_days_between ([JiraIssue].[Q - JiraIssue].[CREATED], [First_Sndy_DtRng]),14)))

Thanks & Regards,
XYZ

[navissar]

So, 2 things:
1. You replaced DIV with MOD. Div means the integer outcome of a division (So no remainders) - you can achieve this by rounding down the result of a division.
2. Instead of using + to add days use the built in _add_days function.

[xyz]

Thank you very much all and special thanks to Nimrod.