HOW TO MODIFY MDX TO RESTRICT MEMBERS IN TIME DIMENSION

Dec 29, 2006

There are some 55 members in the arrival year level of the time dimension (1995 - 2055).
I am trying to find a way to restrict the number of years returned by this mdx query to the current year - 5. Any help will be appreciated.

WITH MEMBER [Measures].[ParameterCaption]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS,
[TIME DIMENSION].[ARRIVAL YEAR].allmembers ON ROWS
FROM [TOURISM CUBE]

Thanks

View 4 Replies


ADVERTISEMENT

Order By Dimension Members In MDX

May 22, 2007

Hello!

I'm developing a report with Samsung products sales compared to other producers' sales.

I have the following dimensions:

Dim Period (date-time)
Dim Region (region where the products are sold)
Dim Product Group (notebooks, CRT,...)
Dim Producer (Samsung, LG, ..., Other producers)

Report should contain Samsung sales on the first row, other companies (LG, Dell,...) sales on subsequent rows and "Other producers" sales on the last row, i.e.:

Week 1 - SomeRegion - Notebooks - Samsung - 2350

Week 1 - SomeRegion - Notebooks - LG - 1100

Week 1 - SomeRegion - Notebooks - Dell - 3000

Week 1 - SomeRegion - Notebooks - Other Producers - 10000



How can I position Producer values in such way?

Please note, Dim Producers may be enalrged, so I can't just enumerate all Dim Producer members in the query.



I tried to use field ProducerType, which I set to 1 for Samsung, 2 for other companies (LG, Dell, ...) and 3 for "Other producers", after that I ordered by this field, however it didn't position producers as I expected.

Please see the query I used below:




Code Snippet

SELECT

NON EMPTY

{

[Measures].[Quantity]

} ON COLUMNS,

NON EMPTY

Order(

{

(

[Dim Period].[Period Week No].[Period Week No].ALLMEMBERS *

[Dim Region].[Region Name].[Region Name].ALLMEMBERS *

[Dim Product Group].[Product Group Name].[Product Group Name].ALLMEMBERS *

[Dim Producer].[Producer Name].[Producer Name].ALLMEMBERS

)

}, [Dim Producer].[Producer Type], ASC)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [ShelfShare]

WHERE ( [Dim Period].[Period Year].&[2007] )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS



Any help is greatly appreciated!

View 3 Replies View Related

Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension

May 19, 2008

Hi!

Need some help building a query that does the following :

I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)

In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]

Both Time Dimensions are Year -> Month -> Day and have the same Members.

I have every option available, using calculated Members and/or Measures to do this.

The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.

Appreciate all help!

Regards,
Stian Bakke

View 3 Replies View Related

Show Only Non-empty Dimension Members?

Jul 14, 2005

greetings,

if I create a dimension from fact table, then it only has members which appeared in fact table. but if I create dimension from dimension table, then it shows all members, even those with no data. how can I show only those with existing data in member list?

thank you

View 1 Replies View Related

Split Olap Dimension Members

Apr 4, 2006

Argent
Need to split olap dimension members into three different dimension members
example
account dimension member = 11111200900
account_num.member = 11111
item_num.member = 200
sub_item = 900

Please help me
Thanks

View 9 Replies View Related

Create Or Drop Dimension Members

Mar 19, 2008

Hi All,

I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.

E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].

Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"

Please look into this issue, advice me what do I do ASAP.

Thanks,
Srinivas

View 3 Replies View Related

Not Able Create Or Drop Dimension Members

Mar 19, 2008

Hi All,

I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.

E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].

Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"

Please look into this issue, advice me what do I do ASAP.

Thanks,
Srinivas

View 1 Replies View Related

Analysis :: Querying Dimension Members In MDX?

Sep 17, 2015

The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.

Is there any way i can get the Member_key in a set in rows section.

Like

With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube

I want the value as just like

Value
Employees, UniqueName

View 2 Replies View Related

Dimension Calculated Members Not Visible In Perspective

May 14, 2008



Hi,

I created some calculated members for a dimension. They are set to be visible and when i connect to the cube itself I can see them with both Excel and ProClarity as clients. I added them to a couple perspectives (by checking their boxes down at the bottom of the Calculations list) and deployed. There were no errors or issues. However from both Excel and ProClarity I do not see the calculated members of the dimension.

Any ideas?

View 9 Replies View Related

Analysis :: Sorting Dimension Members From Fact

Dec 2, 2015

I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:

The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.

View 5 Replies View Related

Analysis :: Limit Date Dimension Members Dynamically?

Jul 7, 2015

We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.

View 4 Replies View Related

Slowly Changing Dimension:inferred Members Update

Jul 12, 2006



Hi,

Anybody who knows how inferred members update function in slowly changing dimension? I came across this when I started using some of the functions of the Slowly changing dimension object.

Thanks!

cherrie

View 4 Replies View Related

Analysis :: Setup Multiple Default Members For A Dimension Attribute In SSAS?

Jun 2, 2015

I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.

I want to set more than one default members for dimension report.

I've read all about how dimensions can have only one default member, but I need to set more than 1.

View 2 Replies View Related

Simple MDX Query: Multiple Non-Contiguous Members From Same Dimension In Calculated Member

May 28, 2008

Hi

A very basic MDX question. Using the following from AdventureWorks as an example:




Code Snippet
WITH MEMBER [Measures].[Big Cities]
AS
(
[Geography].[City].&[New York]&[NY]
, [Measures].[Internet Order Count]
)
SELECT
[Measures].[Big Cities] ON COLUMNS
, [Customer].[Total Children].Children ON ROWS
FROM [Adventure Works]


How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.

Thanks,

Julia.

View 9 Replies View Related

Reporting Services - Analysis Services - Displaying Dimension Members As Columns

Dec 3, 2006

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]



Any ideas?

Thanks,
Arjun

View 8 Replies View Related

Modify Report Elements At Run Time

Apr 18, 2007

I have a report that is embedded in a .net 2.0 web form. It works. I would like to be able to modify some of the elements in the report as the web form loads. I have tried the following code that did NOT work:



Dim myImage1 As New Image

myImage1 = Me.ReportViewer1.FindControl("Image1")

myImage1.ImageUrl = "Images/small_info_logo.jpg"



Does anyone know of a site that has some godo examples and object models on how to do this?

Thanks

Chuck

View 4 Replies View Related

How To Modify Schema At Run-time - My Code Or SQL Server?

Mar 27, 2007

Hi Smart Folks,
My SQL Server-based app will require generating a few tables at run-time, and, possibly, a new small database. Again, this is at run-time. Any advice on this? Should I build the functionality from the app, having my code generate all of the commands on the db to create what I need, or should I rely on SQL Server functionality (Scripts? SPs?) to do so. I am sure there is trade-off in one appraoch versus the other - I could use a tour of the trade-offs.
Thanks all!
DF 
 

View 1 Replies View Related

How To Modify Trigger To Show Time Stamp.

Jun 24, 2004

Down below is my tables.
If I want to add time stamp in log_old_val table.
what value in log_old_val and cone in trigger have to be modified?

thanks


************************************************** *****

--main table
> create table test (manufacturer varchar(500), score int)
> insert into test values('Toyota', 1 )
> insert into test values('Toyota', 2)
>
> --logging table
>
> create table log_old_val(manufacturer varchar(500), score int, operation
> varchar(10))
>
> --trigger to log old value into log_old_val table.
> create trigger tr_man on test
> for update,delete
> as
> if @@rowcount = 0
> return
> if exists (select * from inserted)
> if exists (select * from deleted)
> insert into log_old_val
> select manufacturer,score, 'update' from deleted
>
> if exists (select * from deleted)
> if not exists (select * from inserted)
> insert into log_old_val
> select manufacturer,score, 'delete' from deleted
> go

View 1 Replies View Related

Time Dimension

Sep 4, 2007

Hello,

I am trying to make a time dimension in analysis services. Is it possible to include the hours of a day in the dimension ? Is necessarily the day level the lowest of the hierarchy?

Thank you
ST

View 3 Replies View Related

Time Dimension

Aug 14, 2007

(This is the Twilight Zone......not so much)

What are the pros and cons to using SSAS to create time dimensions based on a date field in the fact table as opposed to a stand alone time dimension table.

I can see many problems with loading a time dimension table. The date is from the same table as most of my fact data. I have a column in my OLTP that sets last change date so I can tell if my fact data is an insert or update but it wouldn't tell me if the date column had been changed, just some column in the table. I'm going to have several thousand sales on any given day so I'll be reading a lot of rows just to put one row into the dimension.

From a SSIS point of view I'd think leaving the date in the fact table would be better.

View 5 Replies View Related

Need Help To Create My Time Dimension

Mar 30, 2004

Hi,

I have a fact table which contains the transaction date, ProductID, QTySold, TotalSaleAmount, etc...

Since I am new to OLAP therefore I need help to now create the table for TIME on which I will be basing my time dimension... I have read a few articles and have gathered that at the end of the exercise my fact table should have a 'timeid' column which will be linked to the same column in the table being used for the time dimension...

I have gone through the tutorial of MS-Analysis Service and FoodMart example have some idea about what he structure of this table will be.

My questions are:

1. I need guidance on how to create the table for time. One option is to just copy the table used in the Foodmart example but thought that might work but my concept will not be clear

2. The structure of the table to be used for time dimension is quite clear (i think this part is easy). What I want to understand is that how do i POPULATE this table which data? Can some one provide me with scripts, SPs, or whatever to do this.... This is the area where I am lost...

3. How will I enter the "TheDate" column in my fact table and link it with my table for time dimension...

Looking forward to someone's help.

BTW, I would like to share a very good article which i recently found in one of the newsgroups. Some of you might like appreciate it too:
http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk

View 3 Replies View Related

Creating A 4-4-5 Time Dimension

Aug 29, 2007

Hello:
Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:
Jan - 1 - 27
Feb - 28 - 24
Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension. Is there any script out there that will help me build a Fiscal calendar such as the one described above?

I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.

Thank you!

View 4 Replies View Related

Time Dimension According To Different Granularity

May 29, 2008

Hi,
I got inventory fact table. For the past two weeks, I got on a daily level; beyond that, weekly level, and beyond that monthly. I need to tie it all in to the time dimension of course €“ and the problem is, how do I do it on different granularity?
As far as time dimension, tn the datamart, I got tables dim_date with key column date_id (int) , and correspondingly dim_week with week_id(int) and dim_month with month_id(int).

What I€™ve done so far, is created a time dimension from dim_date table (meaning granularity=daily) and simply tied in all the inventory €“ daily, weekly, monthly, on the day level (it all has date_id field in it, even the weekly and the monthly. Its simply the day of the end of week or end of month) I didn€™t tie anything to dim_week and dim_month.
Does that makes sense? The result is kind of strange. I cant upload an image here, but€¦ well it seems ok, I got year, week (€˜GL Week€™) and then €¦ this is the annoying thing: why am I getting a €˜date€™ column, when I only want it by week or by month? I can€™t make that column disappear (e.g when in time hierarchy I only group by month, still a €˜day€™ column will be there, and will show 4 days€¦ the 4 €˜end of week€™ that comes from €˜date_id€™.)
How do I make it go away??

Thanks for reading : ) I know it€™s a bit long

View 1 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

Time Dimension Design Question

Dec 20, 2007

Im trying to design my time dimension and need to add a field tohandle null dates in the fact. So if at the time of ETL the date isntknown, referential integrity will be preserved. Kimball suggestsinsterting a record in the time dimension to handle this with adescription of 'Date not available' or something like that. Howeverif users are doing inner joins on the dimension they will obvisouslybe pulling the datetime field..what should be in the datetime fieldfor this particular record?

View 4 Replies View Related

Sorting The Time Dimension In Browsers

May 20, 2008



Hi All,
I've looked around but can't seem to find an answer for this.
I have a cube that has a fairly large time dimension (going back to 1949) as the data demands this.
When a user is browsing the cube and applies a filter or adds the a time heirarchy as a value it's always sorted from oldest to newest. Whilst the need is there to look at data from 1949 most people want to look at the last few years. The problem is they have to scroll right down the list to get to this. Is there a way of having the most recent years of the time dimension appear first in these lists to make them more accessable?

I hope that makes sense!

Any help would be appreciated.
Thanks
yayomayn

View 10 Replies View Related

Please Guide - Especially About Time Dimension And Approach In General

Mar 30, 2004

Hi,

I have a table which contains all the transaction details for which I am trying to create a CUBE... The explanation below in brackets is only for clarity about each field. Kindly note that I am using the following table as my fact table. Let's call it tblFact

This table contains fields like transaction date, Product (which is sold), Product Family (to which the Product Belongs), Brand (of the product), Qty sold, Unit Price (at which each unit was sold), and some other fields.

I have created a Product dimension based on tblFact. I don't know if this is a good idea or not :confused: Is it okay and am I on the right track or should I base my Product Dimension on some other table (say tblProducts and then in the Cube editor link tblProducts with tblFact based on the ProductID field? Please guide.

Now coming to my last question:
Currently I am also creating my Time Dimension based on tblFact. Is this also a wrong approach?
1. Should I instead create the Time Dimension based on a different table (say tblTime) and again link up tblTime and tblFact in the Cube editor?

2. if yes, then how do I create tblTime from tblFact in a manner that it only contains all the transaction dates.

3. Assuming that I should take the tblTime approach, then should this table (tblTime) also contain the ProductID - representing the Product which was sold for each date in tblTime?

I realize that this is a lenghty post but reply and more importantly guidance from the experienced users will be greatly appreciated becuase I have recently started learning/playing around on the OLAP side of things and I know that this is the time that I get my foundations correct otherwise I'll end up getting used to some bad practice and will find it difficult to change my approach to cube designing later down the road.

So many thanks in advance and I eagerly look forward to reply from someone.

View 10 Replies View Related

The Time Dimension Is Growing Much After Every Process Of Cube

Apr 11, 2008

Hi guys,

I made a cube with time dimension with hieracly year/month/date/hour
the problem is that dimension is growin to fast. In older version of MSSQL (2000) the same dimension doesn't grew so much.
Any ideas? The table is big (may be around 1 500 000 rows per month) now it contains around 4 500 000 rows.

View 19 Replies View Related

Reporting On A Cube -- Time Dimension Issue

Feb 28, 2007

Hello,

I have an Analysis Services Cube that I would like to report on. However, the Time Dimension currently only has four columns, Day of Month, Month(name) , Year, and DateKey (DateTime representation at midnight for every day). Thus when I drag the month attribute onto the report, it is sorted April - August - December - etc. instead of Jan - Feb - Mar. How do I fix this? I remember reading something in the MSDN Library about it but I can't find it again now.

Thomas

View 5 Replies View Related

Data Mining Against A Cube With Time Dimension

Nov 21, 2006

We have a set of cubes and dimensions, and we're experimenting with data mining against the cubes (primarily for forecasting applications). We have a custom time dimension (which we call calendar), not generated by the BIStudio wizard. The dimension has year/month/day/hour/... attributes. But when I try to add this Calendar dimension to the mining structure as a nested table using BI studio, it only shows the Year attribute, not the others. Other dimensions seem to show all the attributes.

Is there something we've done wrong in defining our time dimension? What determines which attributes show up as available for selection in BI studio?

View 5 Replies View Related

MDX To Compare The Currentmember Of The Time Dimension To Today

May 21, 2008

This is a cross-post from the Office PPS Planning site: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3379691&SiteID=17 I was hoping there may be some additional MDX resources here.

I'm trying to determine if the currentmember of the time dimension is > or < the value of today. I want to use this to change the the value of a member that I display on columns from actual (for prior to today) to forecast (after today). I have this MDX:


with

member [measures].[PYCY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())-1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)))

member [measures].[CYNY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)))

member [measures].[NYNY2] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+2)))


member [measures].[test2] as Format(Now(), "yyyyMM")

member [measures].[test4] as [Time].[Day View].CurrentMember.Member_Key

member [measures].[diff] as DateDiff('m', Format(Now(), "yyyyMM"),[Time].[Day View].CurrentMember.Member_Key)


select

StrToSet('{' + Generate(Descendants(

{strtomember('[Time].[Day View].[year].&[' + [measures].[PYCY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[CYNY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[NYNY2] + ']') }

, [Time].[Day View].[Month]), [Time].[Day View].CurrentMember.UNIQUE_NAME, ", ") + '}')


* {[measures].[test2],[Measures].[test4],[measures].[diff]}



on 0,

[Scenario].[All Members].members

on 1

from [SalesForecasting]


Here is the result for 2 months:










October FY2007
October FY2007
October FY2007
November FY2007
November FY2007
November FY2007


test2
test4
diff
test2
test4
diff

All
200805
200610
#Error
200805
200611
#Error

None
200805
200610
#Error
200805
200611
#Error

Actual
200805
200610
#Error
200805
200611
#Error

Forecast
200805
200610
#Error
200805
200611
#Error

Override
200805
200610
#Error
200805
200611
#Error

Plan
200805
200610
#Error
200805
200611
#Error



The error is: #Error Execution of the managed stored procedure DateDiff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..

Why can't DateDiff calculate the difference between 200805 & 200610?

Thanks

View 5 Replies View Related

Slow Moving Dimension And History Time Stamp

Nov 14, 2007

The following question might sound a bit stupid but I'm not a database expert so hopefully nobody minds me asking it.

Here's what I did:

1. I created an SSIS package that is supposed to import new data into my data warehouse as it becomes available.

2. Since I need to maintain some of the history I use the Slow Moving Dimensions part (set the history flag on input fields) but run into an error condition while running the package. The message basically says that I'm about to create a duplicate record which is not allowed.



Original Table1:

PK1 field1
PK2 field2
PK3 field3
field4
field5

-----------

Now I enhanced it like this:

Extended Table1:

PK1 field1
PK2 field2
PK3 field3
field4
field5

CreateDate (new)
NewDate (new)
ActiveFlag (new)
_____________________

Now on some records the package is supposed to archive history by populating the (new) fields. In order to keep the record unique (primary key constraint) thought, do I need to make the (new) fields primary keys as well?

So I guess I'm struggling with a more basic concept;)

I would appreciate if somebody could shed some light on this.

Thanks in advance.
Dirk

View 3 Replies View Related

Query Builder Shows Time Dimension As Two Dimensions?

Oct 17, 2007

Good afternoon all,

I have created a cube in Analysis Services with a time dimension named Time. The data is only needed at the month level, so the fields in the table that the dimension is based on are [DTE MM] and [DTE YR]. In Visual Studio 2005 or in SQL Server Mgmt Studio I can browse the dimension and I see that Time has the following attributes: Year and Month both Regular attributes and Time (Key attribute). There is also a hierarchy named [Year - Month] and if I browse that it looks good dril down from All to Year to Month.

However, when I point my Reporting Services Datasource to the cube, and start Query Builder, I see two dimensions, [DTE MM] and [DTE YR] and no Time dimension.

[DTE MM] has attributes [DTE MM].[Month], [DTE MM].[Time], [DTE MM].[Year], [DTE MM].[Year - Month].

[DTE YR] has attributes [DTE YR].[Month], [DTE YR].[Time], [DTE YR].[Year], [DTE YR].[Year - Month]

This is causing me huge problems in my report. I need to use a date range in the query. To do this, I created Query Parameters and refer to Report Parameters that will be passed in. For example, I use ="[DTE YR].[Year - Month].[Year].&[" + Parameters!StartYear.Value + "].&[" + Parameters!StartMonth.Value +"]" (thanks to Simon Philips) as the query parameter for the Start Year Month, but if I use the [DTE YR].[Year - Month] in my SELECT, the data is not sliced in my query results. That is to say that the Year and Month show correctly, according to the date range, but the Measure is equal for each month and is equal to the total for the cube. To slice the data, I have to use [DTE YR].[Year].[Year] and [DTE MM].[Month].[Month] in my select, but then the data is shown for every month, i.e. the date range is ignored.

What am I doing wrong, or is this a quirk of RS that I can work around?

Thanks,
Kathryn

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved