locked
SSRS/CRM Pivot table of hours RRS feed

  • Question

  • I want to create a report in SSRS which shows a table with the employees and the number of hours for each day they worked. The table shows each day of the month as a column.

    This was easy in SQL. Select all the hours and Pivot the results. Create a table with 32 columns and assign the dataset with the result to the table. Nice and easy.

    But FetchXML is not SQL. No pivoting. So I have to pivot in SSRS using a Matrix. But then it only shows the columns for the days in the resultset.

    I then created a dummy date table with all dates untill 2020 so I could outer join that table to fake the other columns as wel. Unfortunatally no luck on join those to tables. The hours table has a datetime field (with minutes) and the date table only has a date. The Link-Entity doesn't let you transform the datetime field.

    Next option I thought of was to do a Union so I could add a dummy employee with all days of the month. Unfortunatally no Union for FetchXML as well.

    I am really running out of options.

    Any thoughts/ideas?

    note: It is CRM Online 2015. So I have to use FetchXML (for now)


    • Edited by Jeroen van Gent Monday, June 15, 2015 11:59 AM addition and clearification
    Monday, June 15, 2015 9:43 AM

All replies

  • Hi,

    You can make an Query, witch is querying an:

    Employees Day NumberOfHouors

    from a CRM

    And than add a matrix to your report witch will contain Employe - in columns, Day - in rows Summ(NumberOfHouors) in a data. Like this:

    So, your matrix will grow in two directions:

    Down: By Employees column

    Right: By Day column

    Monday, June 15, 2015 10:33 AM
  • Thanks for the quick reply. Like I already said, it only adds the columns for which there is data. I need all days of the month. That is the problem.
    Monday, June 15, 2015 10:49 AM
  • So, make a query, witch querying all the days.

    it will be like this:

    declare @month datetime
    set @month = cast('15.05.2015' as datetime);
    
    WITH MonthDays(nDay) AS
    (
    SELECT 1
    UNION ALL
    SELECT nDay+1 FROM MonthDays WHERE nDay < Day(EOMONTH(@month))
    )
    
    select Tst.Emp, MonthDays.nDay, nHours.nHouors from Tst
    left join MonthDays on 1 = 1
    left join Tst nHours on Day(nHours.dDay) = MonthDays.nDay and BaTst.Emp = nHours.Emp


    Tst table contains Day - when employee worked, Emp - employee name (or it can be an Id), nHours - count of worked hours.

    First join - is making all employees with all days in a month (which is placing in variable @month)

    Second join - is joining working data to this table.

    • Edited by xjomanx Monday, June 15, 2015 11:52 AM
    Monday, June 15, 2015 11:49 AM
  • If you are used to SQL, FetchXML can be a bit of a pain for sure. :)

    Two suggestions for you.

    1. Check out this web site.  You can put in your SQL and it will convert it to FetchXML. 

    2. This Stackoverflow article talks about left outer joins, which I think is what you need to get all the correct dates, including the ones where the employees didn't log any hours. 

    Good luck!

    Tuesday, June 16, 2015 12:25 PM