Iniciar sesión
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Trouble Generating Pivot / Skills Matrix

Respondida Trouble Generating Pivot / Skills Matrix

  • martes, 22 de mayo de 2012 20:21
     
     
    Inicie sesión para votar
    0

    Hello,

    I have of course the default users entity. I also have a custom entity for training skills. On this custom entity I have several fields but the two I care about for this matrix is the skill category and the experience level. I have related and the user and the skills as N:N and exposed for advanced searching.

    What I am trying to do is create a matrix with the username on the X axis and the skill on the Y axis and groupings on the Y axis. Then where the data points intersect I wanted to display the experience level code like this.

                                          Bob       Joe

    Biology Classes--------------------------------------------------

    Nose Picking 101               E          C

    Toe Nail Clipping               L           L

    Math Classes--------------------------------------------------

    Adding                             B           I

    You get the idea. It seemed to me a pivot table would be a good option for this AND it would allow the user to manipulate the data versus the static matrix they currently use. Well as far as I understand you need to create a custom view that has all the data in the "view grid" so that would then be exported. If I create a custom view for skills then I can get every column to show except User>FullName. It's in the query and the results look good I just can't get the column to show so that when it's exported to Excel it's an option for pivot.

    So my question is if there is a better way please enlighten me. If I am on the right track how can I get the UserName column and values exported so I can put the username in the X axis of the pivot table.

    Ultimately in the end after I have the Excel sheet all prettied up and color coded etc. I was going to import it back into crm 2011.

    Thank You

    JB

     

Todas las respuestas

  • martes, 22 de mayo de 2012 17:31
     
     
    Inicie sesión para votar
    0

    Hello,

    I have developed a custom report in CRM2011 and the data displays and is grouped as expected. So the trick is that it is a boring tabular report. This report takes the place of a matrix type Excel report where we had employees across the top and skills down the left. Then in the intersection of say an employee and skill we had a char for proficiency and a color code. Is this type of thing doable via the ssrs report wizard or will I have to develop it in Excel and Import it into cmr2011 as a report?

    TIA

    JB

     
  • martes, 05 de junio de 2012 14:47
     
     Respondida
    Inicie sesión para votar
    0

    Finally got back to this one and here is what I did...might not be the best but it worked. Generated an Advanced find view with almost everything I wanted in it. Then exported the report as a dynamic pivot table report. Then opened it in Excel. Under the data tab I took the sql query and copied it into SSMS. From there I edited the query join to include what I wanted from the other tables then took the complete query back to Excel saved and refreshed. The report now has the data available for pivot that I wanted and imported it back in crm2011 for release.

    FWIW if it helps someone.

    JB

    • Marcado como respuesta zzpluralza martes, 05 de junio de 2012 14:47
    •