en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Amar Ravi  
#1 Posted : Tuesday, July 19, 2011 1:31:52 AM(UTC)
Amar Ravi
Rank: Newbie

Groups: extranet\Forum
Joined: 4/27/2011(UTC)
Posts: 3

Hi

I would like to create a bar graph with year as the multiple series. For example - 2010 and 2011. Month will be the common attribute. The Year and Month are from the same date dimension table. I have a fact table with one fact "count".

Dimension: dim_date
Attributes: Year, Month, Date

Measures: fact_summary
Facts: Count

When I create a cross-tab/pivot table I get two same months; one for 2010 and another for 2011. I am looking for 12 rows but I get 24.

2010 2011
======================
Jan 10
Feb 2
Mar 9
Apr 1
May 3
Jun 11
Jul 1
Aug 3
Sep 4
Oct 6
Nov 1
Dec 1
Jan 9
Feb 7
Mar 0
Apr 1
May 5
Jun 12
Jul 2



I want to have the table show like this:

2010 2011
======================
Jan 10 9
Feb 2 7
Mar 9 0
Apr 1 1
May 3 5
Jun 11 12
Jul 1 2
Aug 3
Sep 4
Oct 6
Nov 1
Dec 1

We use SSAS 2008 Cube. Can you please help me?

Thank you,
Amar
Ole  
#2 Posted : Tuesday, July 19, 2011 9:52:26 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 325

You will need to look at the definition of your Month attribute in the SSAS project.
You will probably see that the key for your current Month attribute is a combined key, including year information.
What you need to do is to create a Month attribute where the key is independent of years. I.e. the key should most likely just be month numbers 1-12.
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Amar Ravi  
#3 Posted : Thursday, July 28, 2011 12:52:14 AM(UTC)
Amar Ravi
Rank: Newbie

Groups: extranet\Forum
Joined: 4/27/2011(UTC)
Posts: 3

It worked. Thank you,
Amar
Dan Cowan  
#4 Posted : Thursday, July 28, 2011 7:02:19 PM(UTC)
Dan Cowan
Rank: Advanced Member

Groups: extranet\Forum
Joined: 3/22/2011(UTC)
Posts: 4

Hi Amar,

In your cube under the date dimension, there should be an individual 'Month' member (not part of a date hierarchy).

Use this month member on the x-axis and use a Calendar or Fiscal Year date hierarchy on the y-axis.
Add the same date-year hierarchy to the criteria bar and select the desired years.

Add your measure and you should be in business. If you notice the months appear Jan,...Dec, Jan,...Dec then the individual month member is still tied to year and this needs to be adjusted in the SSAS project.

Thanks,
Dan
UserPostedImage
TARGIT USA Reseller of the Year 2011!
DAN COWAN
VP OPERATIONS & DIRECTOR OF SALES
dcowan@BusinessImpactInc.com
Proud supporter of YouTARGIT.com and TargitUsers.net

You have to understand business to build a business intelligence solution…
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error