List report with calculated 'Day of Week' is not rendering the result as expec

[xyz]

Hi All,

I have requirement to develop a report against cube datasource using DMR functions, where we need to display 'Task Count' measure for each 'Day of Week' for a given fiscal week and with 'Priority Group' and 'Department' dimensions. To develop the report, I have created seven data items each for a 'Day of Week'. Please find the expression and current output below.

The expression, we used for Saturday 'Day of Week', looks like below.

total(
[Task Count] within set
filter(
[PM].[Task Due Date].[Task Due Date.Fiscal Day of Week].[Fiscal Day of Week],[PM].[Task Due Date].[Task Due Date.Fiscal Day of Week].[Fiscal Day of Week].[Fiscal Day of Week - Key]='1')
)

Other, expressions are also similar, just by changing the [Fiscal Day of Week - Key] key, we created separate data items.

For 'Total' column, I have written expression as aggregate([Task Count]), which is also giving incorrect totals.

Current Output, which is not displaying the results as expected:

Priority Group  Department  Saturday  Sunday  Monday  Tuesday  Wednesday  Thursday  Friday  Total
XX                    ABC                          1            1            1              1                  1               1         1        1     
YY                     EFG                          1            1            1              1                  1               1         1        1
Total                                                 2            2            2              2                  2               2         2        2



As per database, result should look like below as Expected Output:

Priority Group  Department  Saturday  Sunday  Monday  Tuesday  Wednesday  Thursday  Friday  Total
XX                    ABC                                                                      1                  1                                    2     
YY                     EFG                          1                                                                                                    1
Total                                                 1                                          1                  1                                    3

I have corrected the expression for 'Total' column like below.

aggregate([Task Count] within set [Fiscal Day of Week]), which is working as expected now.

The expression, written for data items for 'day of week' causing issue, can someone help out on this.

Your help will be much appreciated.

Thanks & Regards,
XYZ

[xyz]

Hi All,

One more thing, I forgot to mention that I used list, which is not working as expected, whereas if I use crosstab to develop the report, 'Task Count' measure is displaying data as expected. But the problem is, when i suppress null's, all the days of week, won't display in the report. Whereas it has to display the rows, which have data.

Can some one please help me on this?


Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone, please guide me or give suggestion on the same.

I have to show to view to the user, one view with null values in the report and the other without null values. If I use crosstab for the development the view with null values will show the result as expected like below.

Current Output with null values view:

Priority Group  Department  Saturday  Sunday  Monday  Tuesday  Wednesday  Thursday  Friday  Total
XX                    ABC                                                                      1                  1                                    2     
YY                     EFG                          1                                                                                                    1
Total                                                 1                                          1                  1                                    3

Current Output without null values view which is suppressing Sunday, Monday, Thursday and Friday:

Priority Group  Department  Saturday  Tuesday  Wednesday  Total
XX                    ABC                                         1                  1         2     
YY                     EFG                          1                                            1
Total                                                 1             1                  1         3

How will I retain Sunday, Monday, Thursday and Friday days of week columns in crosstab, even though they have null values, basically days of weeks should show always even though they have null values, even if they show not null view(report output, should display as null view output, even not null view is selected).

Your help will be much appreciated :)

Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone, give some suggestions or share there thoughts on the same.
Am I not clear with my requirement? If not please suggest, I will explain in detail on it.


Thanks & Regards,
XYZ

[xyz]

Hi All,

Can some one help me on this.




Thanks & Regards,
XYZ

[orbair]

I don't know if I understand well your problem, but can you check your crosstab suppression property? Or if you are working with relational db, can make sure you set up outer join?

[xyz]

Hi Morfeusz,

Thanks for the reply, I developed the report using list object and I am working with cube data source. I am getting repeated data issue in the list as I have written expression for each day of week data item. Example for Saturday day of week the expression looks like below.

total(
[Task Count] within set
filter(
[PM].[Task Due Date].[Task Due Date.Fiscal Day of Week].[Fiscal Day of Week],[PM].[Task Due Date].[Task Due Date.Fiscal Day of Week].[Fiscal Day of Week].[Fiscal Day of Week - Key]='1')
)

The problem here I am facing is all the days of week starting from Saturday to Friday, data is repeating, whereas it should not. When I developed the report initially in the list, list was displaying data correctly as expected. But suddenly, data repetition started in list.

Can you help me, how will I write the expression to avoid data repetition and get the correct data as expected.


Thanks & Regards,
XYZ