locked
How to create complex table ( not pivot ) in PowerPivot or PowerView ? RRS feed

  • Question

  • hi to all , 

    is it possible to create table like the example Below ?  in my organisation most of the user need report that are strait tables but with Complexity in terms of rows and columns , see this :

    thanks.


    yaniv012 from M.S.

    Thursday, March 12, 2015 11:57 AM

Answers

  • Hi yaniv012,

    If you're interested in how cubeformulas would work in your case - have a look at this sample file:

    https://onedrive.live.com/edit.aspx?cid=DE165DDF5D02DAFF&resid=DE165DDF5D02DAFF%216682&app=Excel

    There are 2 different reports:

    1) StaticRows: Shows a report formatted like your report. There's a slicer where you can choose which year to be shown. This is dead easy - just create a pivot table that resembles your desired report as much as possible ("HideStaticPivot"), copy and convert to cubeformula. Just insert your blank lines and apply formatting.

    You can use all the slicers that you have put into your "source pivot" - just remember, that only the values will adjust to it: The rows and columns to be shown will not adjust with the slicer selections!

    This will fullfill many report requirements, but not all.

    2) DynamicRows: If your rows will change with the desired slicer selections - this is one way to go. (This can of course be adopted to changing columns as well).

    I'm considering this as worth the efford, but this clearly depends on how "technical" you want go.


    Imke

    • Proposed as answer by Michael Amadi Friday, March 20, 2015 9:54 AM
    • Marked as answer by Charlie Liao Tuesday, March 24, 2015 1:37 AM
    Wednesday, March 18, 2015 7:17 PM
    Answerer

All replies

  • Yes. You might use cube functions in this case:

    http://www.powerpivotpro.com/2010/06/using-excel-cube-functions-with-powerpivot/

    But if you can live with slight format/layout compromises, standard pivot reports on your data model would also do.


    Imke

    Friday, March 13, 2015 4:45 AM
    Answerer
  • Hi Yaniv,

    According to your description, you need to design complex table on Power Pivot or PowerView, rgith?

    Power View report, is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. They can easily create and interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances.

    We can create complex table based on your requirement, here is a document about design a PowerView report for you reference.
    https://technet.microsoft.com/en-us/library/hh231518%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, March 17, 2015 2:40 PM
  • hi Charlie , i know all this and also made many reports with powerview , but when it comes to complex design of a table , it begin to be Impossible , thanks.

    yaniv012 from M.S.

    Wednesday, March 18, 2015 6:34 AM
  • Hi yaniv012,

    If you're interested in how cubeformulas would work in your case - have a look at this sample file:

    https://onedrive.live.com/edit.aspx?cid=DE165DDF5D02DAFF&resid=DE165DDF5D02DAFF%216682&app=Excel

    There are 2 different reports:

    1) StaticRows: Shows a report formatted like your report. There's a slicer where you can choose which year to be shown. This is dead easy - just create a pivot table that resembles your desired report as much as possible ("HideStaticPivot"), copy and convert to cubeformula. Just insert your blank lines and apply formatting.

    You can use all the slicers that you have put into your "source pivot" - just remember, that only the values will adjust to it: The rows and columns to be shown will not adjust with the slicer selections!

    This will fullfill many report requirements, but not all.

    2) DynamicRows: If your rows will change with the desired slicer selections - this is one way to go. (This can of course be adopted to changing columns as well).

    I'm considering this as worth the efford, but this clearly depends on how "technical" you want go.


    Imke

    • Proposed as answer by Michael Amadi Friday, March 20, 2015 9:54 AM
    • Marked as answer by Charlie Liao Tuesday, March 24, 2015 1:37 AM
    Wednesday, March 18, 2015 7:17 PM
    Answerer