none
SQL 2012 Power View report based PowerPivot Model

    Question

  • Hi,

    I've created PowerPivot Model having 2 tables coming from SQL 2012 database. One of this table is a dimension and the other one is a fact having measures.

    I exported this workbook having PowerPovit model into Share Point 2010. When I go to create Power View report I am missing the option "Add to table as Sum" for numeric columns (measures). Not sure why is this? When I create a linked table based on data sitting in the worksheet and this option shows for numeric columns in PowerView report.

    Anyhelp would be much appreciated.

    Thanks


    soori

    Thursday, January 03, 2013 9:22 PM

Answers

  • I dont think it is an iissue with Power View. Can u see dragging the columns in excel (or querying the model from Management studio) and see if you are seeing the same behaviour. If yes, there is something wrong with your relationship. 

    And is it actual data that you have pasted here? I have a feeling that the relationship is inversed, which is why it comes right at the detail level but not at the summarized level. But based on the data that you have given, it would give an error if the relationship direction is reversed. That is why I asked if this is the actual data.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Soori Monday, January 07, 2013 12:26 AM
    Sunday, January 06, 2013 1:39 PM

All replies

  • Friday, January 04, 2013 9:51 AM
  • Can you ensure that the data type in the powerpivot model is set to number and not text?

    Once you make the column's data type as number, you shouldnt have this issue


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Friday, January 04, 2013 11:49 AM
  • Here's my real issue.

    I've 2 tables as following which I imported into a tabular model.

    Here's the data. Both these tables are joined using column "Main_No"

    Table 1

    Main_No            Name       

    1                        TRAN001

    2                        TRAN002

    3                        TRAN003

    Table 2

    Detail_No         Detail_Name        Main_No        Amount

    1                       D0001                   1                     200.00

    2                       D0002                   2                     100.00

    3                       D0003                   1                       50.00

    4                       D0004                   3                     100.00

    When I go to Power View and dragged the following columns into the report view.

    Name, Amount

    The output came as following which is incorrect. I've selected the option "Sum" under the column Amount in Power View.

    Name              Amount

    TRAN001          450.00

    TRAN002          450.00

    TRAN003          450.00

    The output came as following (which is incorrect as well ). I've selected the option "Do not Summarize" under the column Amount in Power View.

    Name              Amount

    TRAN001          200.00

    TRAN002          100.00

    TRAN001            50.00

    TRAN003          100.00

    What should I do to get the proper aggregation as following.

    Name              Amount

    TRAN001          250.00

    TRAN002          100.00

    TRAN003          100.00

    Any help would be much appreciated.

    Thanks


    soori

    Saturday, January 05, 2013 4:01 PM
  • I dont think it is an iissue with Power View. Can u see dragging the columns in excel (or querying the model from Management studio) and see if you are seeing the same behaviour. If yes, there is something wrong with your relationship. 

    And is it actual data that you have pasted here? I have a feeling that the relationship is inversed, which is why it comes right at the detail level but not at the summarized level. But based on the data that you have given, it would give an error if the relationship direction is reversed. That is why I asked if this is the actual data.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Soori Monday, January 07, 2013 12:26 AM
    Sunday, January 06, 2013 1:39 PM
  • Hi Jason,

    You are perfectly right. I had the relation reversed before and changed now. It's working as expected.

    Thanks a lot.


    soori

    Monday, January 07, 2013 12:25 AM
  • Dear all,

    I've add a table to this model as following.

    Table

    Detail_No         Detail_Name        Main_No        Amount1      Amount2

    1                       D0001                   1                     200.00          100.00

    2                       D0002                   2                     100.00            50.00

    3                       D0003                   1                       50.00            24.00

    4                       D0004                   3                     100.00            48.00

    I want to add a calculated column as Amount2/Amount1 and the output should be dynamic percentage.

    If I do this Power View is adding up the percentage which is incorrect. It should SUM up Amount1 and Amount2 based on the dimensional columns I select.

    How to do this?

    Thanks for your help in advance.


    soori

    Monday, January 07, 2013 7:33 PM
  • You should make a measure and not a calculated column for that.

    http://www.powerpivotblog.nl/where-to-create-powerpivot-calculations-and-why


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Monday, January 07, 2013 7:41 PM
  • Thanks again Jason. Appreciate your help.


    soori

    Monday, January 07, 2013 8:34 PM