SSAS Tabular- possible to display just detail level data in Excel?

  • Imagine an HR model and one requirement is to just list all the employee "dimension" data:

    Group by Department

    Under department one row for each employee with the following columns: Employee Name, BirthDate, HomePhone, Address1, Address2, City, State, Zip

    If you try and do that in an SSAS Tabular model via Excel you just get a new "group row" under the employee for each of those.

    Do NOT want:

    Accounting

    ----John Smith

    ---------1975-01-15

    --------------1711 Smith Street

    ------------------FakeCity

    ---------------------IA

    -------------------------50702

    (hyphens just for spacing on line, not actually in report)

    Instead I would want to just see

    Accounting

    ----John Smith 1975-01-15 1711 Smith Street FakeCity, IA, 50702

    ----Jane Smith 1979-05-25 1711 Smith Street Fake City, IA, 50702

    ----Bill Johnson 1965-07-01 25825 3rd Ave Somewhere, MO, 64070

    etc...

    Is there some straight forward option for that?

    It isn't immediately apparent to me.

    Thanks!

  • You could try changing the "Display" option for the pivot table to "Classic PivotTable layout" (right click in the pivot table, select "PivotTable Options" and select the "Display" tab). Not sure whether it will give you exactly what you are after but it should get a lot closer.

  • Thank you, that helped immensely.

    I didn't even realize those options existed (never saw them in any of the training materials, blogs, documentation I have reviewed).

    Good news is that those options tipped me off to other formatting opportunities.

    (Also, I really liked how you could drag / drop right inside the classic layout, seems more intuitive than having to use the fields pane on the far right hand side of the screen).

    Thanks!

    Now:

    Is there ANYWAY to pull together different tables of data with OUT placing a metric (or I guess Microsoft calls them measures) on the pivot table?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply