Cognos report - displaying multiple columns by week using prompt

[kstevens67]

Hi all,

I am new to these boards and relatively new to Cognos Reporting. I am searching for a way to allow users to enter a date range and display the results separated by weeks. Currently I am able to 'hard code' the weeks in the report for each column similar to this:

Example:
total ((case when
_week_of_year ([MY VIEW - MY SYSTEM].[SYSTEM].[date])=1
then
[MY VIEW - MY SYSTEM].[SYSTEM].[float2]
end) for report)

But hard coding all 52 weeks for the year seems a bit much and I am guessing there is an easier way. What I would like to have is the user enter a date range via a prompt page and only display the weeks in that range.

For example, if someone enters a range from Jan 1, 2019 to February 20, 2019, then the report should print something like

Name  | Week1 | Week2 | Week3 | Week4 | Week5 | Week6 | Week7 | Week8
Bill         5            10          8           12          14         0          22          11
Ted        8            3            15          4            20         5         10          12

or if the selection is June 1, 2019, to July 31, 2019, then only those weeks will be displayed and so on. The numbers are number of projects closed per week which is why we want them separated while also not displaying all 52 weeks if we don't have to as that would make for one large report if we don't need to see all 52 columns.

[Andrei I]

Use crosstab and function
_week_of_year

[kstevens67]

Thanks for the quick response.

So instead of using a List tool, use Crosstab.

Would the function be similar to what I have, except for 1 (week 1), use something like ?Date Range? to use what is entered via the prompt?

total ((case when
_week_of_year ([MY VIEW - MY SYSTEM].[SYSTEM].[date])=?Date Range?
then
[MY VIEW - MY SYSTEM].[SYSTEM].[float2]
end) for report)

Best,
Kev

[Andrei I]

In Crosstab
rows => [Name]
columns => 'Week ' + _week_of_year ([MY VIEW - MY SYSTEM].[SYSTEM].[date]) (might need to cast to string)
And Crosstab query will have a Filter:
[MY VIEW - MY SYSTEM].[SYSTEM].[date] in_range ?Date Range? (depends how you defined the prompt)