SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Maxer
Maxer
Say Hey Kid
Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)

Group: General Forum Members
Points: 672 Visits: 1603
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!
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4742 Visits: 3218
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.



Maxer
Maxer
Say Hey Kid
Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)Say Hey Kid (672 reputation)

Group: General Forum Members
Points: 672 Visits: 1603
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search