locked
Matrix Formating Empty Rows/Cells RRS feed

  • Question

  • I am creating report based on the following dataset.

     

    Employee ID

    Week ID

    Client

    Hours

    1

    1

    Client 1

    40

    1

    2

    Client 2

    40

    1

    3

    Client 3

    40

    1

    4

    Client 4

    40

    2

    1

    Client 5

    20

    2

    1

    Client 6

    20

    2

    2

    Client 7

    40

    2

    3

    Client 8

    40

    2

    4

    Client 9

    40

     

    The requirement is to create crosstab report with Employee ID on Y axis and Week ID on X axis along with details of client name & total hours for each intersection.

    I started with matrix (because week_ids are dynamic), and nested table in column to make sure each client & hours is listed. Below is the report output. The report output is correct but I am struggling with formatting of this data. For example, Employee ID 2 has 2 assignments in Week 1 so total of 2 rows are displayed however in week 2, 3 there is only 1 assignment. The report output doesn’t show the empty row/cell in this case.

    Can someone point me in right direction for this? I have tried using IsNothing or VAL functions to detect missing row/value but that didn’t solve the problem. Here is link to the report screenshot

    Free Image Hosting by FreeImageHosting.net

     

     


    • Edited by HKM44139 Tuesday, January 11, 2011 4:09 AM Fixed Link
    Tuesday, January 11, 2011 4:03 AM

Answers

  • Hi,

    I think we can set border style to get the result, please see:

    Besides, I recommend that you add the Client as a group, for detail steps you can refer to the following steps:

    1. Right -click the EmployeeID Text Box, choose Insert Column, and then choose Insert Group Right,
    2. Click this Text Box, select the Client parameter,
    3. Set expression Hours such as follows:
    =IIF(IsNothing(Sum(Fields!Hours.Value)),0,Sum(Fields!Hours.Value))

    The result is:

    For more information, please see:
    Lesson 2: Adding a Matrix Data Region with Row and Column Groups
    http://msdn.microsoft.com/en-us/library/cc627571.aspx

    Thanks,
    Ai-Hua Qiu


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 13, 2011 6:27 AM

All replies

  • bump....

     

    Can someone help?

     

    Wednesday, January 12, 2011 7:06 PM
  • Hi,

    I think we can set border style to get the result, please see:

    Besides, I recommend that you add the Client as a group, for detail steps you can refer to the following steps:

    1. Right -click the EmployeeID Text Box, choose Insert Column, and then choose Insert Group Right,
    2. Click this Text Box, select the Client parameter,
    3. Set expression Hours such as follows:
    =IIF(IsNothing(Sum(Fields!Hours.Value)),0,Sum(Fields!Hours.Value))

    The result is:

    For more information, please see:
    Lesson 2: Adding a Matrix Data Region with Row and Column Groups
    http://msdn.microsoft.com/en-us/library/cc627571.aspx

    Thanks,
    Ai-Hua Qiu


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 13, 2011 6:27 AM
  • I have a similar type report (column grouping matrix report)

    For the data fields my expression is this

    =IIF(Sum(Fields!Count.Value) Is Nothing, 0, Sum(Fields!Count.Value))

    With this expression, rows that do not have data for some of the columns, write out 0 in the cell.

    Thursday, January 13, 2011 6:30 PM