Creating Calculated Members Programatically

Sep 27, 2004

Hi

I've read the MSDN documentation about creating calculated members and states that we can only create session scope or query scope members.

I need to create a static calculated member, one that stays there until it's deletion... That is possibel using Analysis Services, but what about with MDX or DSO?

Thanks in advance

View 1 Replies


ADVERTISEMENT

Calculated Members

May 9, 2005

This is correct
store.currentmember.properties("Store_Manager")

I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR

Do you can help me?

View 1 Replies View Related

Calculated Members

Jun 21, 2006

Hi...
I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????

View 1 Replies View Related

Calculated Members

Oct 20, 2005

I have a reporting services report that I'm creating from an Analysis Services cube.  I created two calculated memebers.  When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command.   I have 21 GB of hard drive space. 

View 3 Replies View Related

Can't Display Calculated Members

Mar 8, 2004

I have created a few calculated members under one dimension (meaning the parent dimension is not Measures, but other dimensions). It can be showed in the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Office 2k, xp, even 2003). Is there any solution to display the calculated members (as with the dimension) in Excel PivotTable? vba code needed? service pack needed?

View 4 Replies View Related

Can't Access Calculated Members With Excel

Nov 29, 2005

Hi,
I created a cube with 4 calculated members. Data is ok within cube browser.
When building report in Excel with Pivot Table or with Analysis Services Excel AddIn, I can't have access to these 4 calculated members. I'm using a olap connexion.
What did I do wrong ?
Thanks a lot for ur suggestions.
Vincent

View 1 Replies View Related

MDX Calculated Members With Reporting Services

Oct 3, 2006

Hi all,

I have an analysis services 2005 cube with a lot of Calculated Members (for example I have a "Sales Volume in kg" and a "Sales Volume in squaremeters (m²)" and a calculated member "Sales Volume in g/m²" with (kg * 1000 / m²), and so on. This Measures should be shown with a hierarchical Product Dimension in which I can drilldown from a level1 to a level 4.

Sales Volume (g/m²)
--------------------------------------------------------------------------------
Level1
Level2
Level3
Level4
Level4
Level2
Level3
Level2

I can select them all with a MDX Query in the Data-part of reporting services. In the layout-part, I have a matrix-control which should show the values in the above form. The value-part of the matrix has the form "sum(field)". I know, it's not correct, I believe I should use "AVG(field)".

When I use non-calculated fields, all works ok. With calculated fields, I get the error

[rsAggregateOfMixedDataTypes] The Value expression for the textbox €˜textbox3€™ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

It's the same with sum and avg (as the error message tells me). But how can I use such fields in reporting services? My users wants a report with the product hierarchy vertical and the salews volume g/m² horizontal so that the values are correct on every level the drill in. In Excel, OWC or other analysis clients, it's no problem to do this, but I can't find a way, doing this in reporting services.

Thanks for any help
Hans

View 3 Replies View Related

Group Calculated Members Into A Named Set

Feb 6, 2008

Hi,
It's a long story i'll try to make short, I am running a MDX query through Integration Services, this query is very very memory intensive, so intensive I get a System.outofmemory error. The workaround for this was getting the data in smaller ranges. this works fine in Management Studio.

select { many measures and calculated members} on columns,
[Cuenta].[Cuenta].&[1]: [Cuenta].[Cuenta].&[10000] on rows
from DB
WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00] : [Tiempo].[Mes].&[2007-11-01T00:00:00]

Now, in Integration services the optimal way of running a MDX query should be through an OLEDB source, but as it's stated in many sites around the web there is an error that hasn't been fixed. So I moved to a Datareder which is much slower, but works... kind of... to get the whole data out of the cube I am getting it in ranges which I should change with parameters, but MDX doesn't support parameters so I have to change the whole query using an expression.

Finally the problem with this is that the query I am running is longer than 4000 characters which is the limit for an expression on integration services. Here is when someone told me to shrink my query by using named sets, and this is what i don't know how to do, is there any way of grouping all the calculated members I have created in one named set or something alike.

thanks!

View 4 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 :: SSAS Calculated Members For Each Week Of Month

Jul 6, 2015

I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count

This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week

Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan) SalesVolume
1            1                        2                  0                         0               1000

Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.

If calculated member for each week, how do I do this, any example code?

View 2 Replies View Related

Analysis :: Statistical Function Results In Calculated Members

Jul 8, 2015

I'm working with the statistical functions Stdev and Median with calculated members.  The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself).  Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :

stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])  returns answers in the thousands when it should be more like 2.5

When used with a query, there would only be a single date member and a specific parameter member.  The total number of fact records is between 200 and 500 with values that range between 0 and 150.  This is the version that gives me answers that resemble the total sum of the [Measures].[Value].

If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....

stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])

View 4 Replies View Related

Caching (storing) Calculated Members In Analysis Services 2005

Mar 2, 2006

Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?

There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.

I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .

Thanks!

View 1 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

Creating A Table Programatically!

Nov 25, 2003

Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.

How can I solve this problem.

Any help is appreciated!
Krish Chandra

View 1 Replies View Related

Creating An MS SQL Data Table Programatically

Aug 27, 2005

I use Visual Studio and VB.

If I want to create an SQL data table on the server, I go to Server
Explorer and make a small table with the corerct columns. Then I add
rows programatically as needed from the Application I'm working with.

It would suit me, for a current job, to be able to create the table itself programatically.

I don't know how to do this. Can it be done ? If so, could someone give
me a starter, please ? Four cols with a key in the first.

David Morley

View 1 Replies View Related

Problem In Creating A Database Programatically....

Jun 27, 2001

I am crateing the database on the fly using db directory in VC++. I am able to create database. I am also able to create login. These can be easily done by logging as master using the username and password sa and "" resp. Now I want to add a user to the database that i created. but it is being added to the master database. I know that I have to change my login from sa ,"" to the username and password that i gave during login. but i dont know how to change the login. Please help. If you have any code for that then that will be of great help.
Hoping to get a positive response as soon as possible..
Thanx
Nitin Gulati

View 1 Replies View Related

Creating Script Task Programatically.

Nov 5, 2007

Hi All,
I am trying to create Script task programmatically. i am struck in one place where we will add the script code. I have given my code.
My Code:

TaskHost taskHost = package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") as TaskHost;

taskHost.Name = "Create SO";

taskHost.Properties["ReadWriteVariables"].SetValue(taskHost, "User:alesId");

Equvivalent XML code generated by the above code.



Code Block
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
<DTS:Property DTS:Name="ExecutionLocation">0</< FONT>DTS:Property>

<DTS:Property DTS:Name="ObjectName">Create SO</< FONT>DTS:Property>
<DTS:Property DTS:Name="DTSID">{4498C2BE-58B6-433C-8F3E-0268BD267BAE}</< FONT>DTS:Property>
<DTS:Property DTS:Name="Description"></< FONT>DTS:Property>
<DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</< FONT>DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property>


<DTS:ObjectData>
<ScriptProject Name="ScriptTask_681442c33ab344b7bedb4492b01245a8" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId"/>
</< STRONG>DTS:ObjectData>



Generated XML when done thru SSIS Editor. i have given only the DTSObject..thats where i am struck.



Code Block
<DTS:ObjectData>
<ScriptProject Name="ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId">
<ProjectItem Name="dts://Scripts/ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math





In the DTSObject, i dont know how to add details thru C# code. Please help me.
Thanks.

View 4 Replies View Related

SQL Sever 2005 - Creating Alias Names Programatically

Aug 14, 2006

Is there a way we can programatically create a SQL server Alias name (SQL 2005 - SQL Native client configuration) using either T-SQL, C# etc. - Manmeet

View 4 Replies View Related

Programatically Creating Dataflow Tasks Require Assembly Reference

Jan 17, 2007

Hi,

I am creating dataflow tasj using following

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

These refer to Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll. While these assemblies were already there in my dev machine I don't find these files in production enviornment for SQL server 2005. I am refering these assemblies from following path in my local machine : C:Program FilesMicrosoft SQL Server90SDKAssemblies.

How to install these assemblies in prod env, offcource one option is to copy it and then put it in GAC thru script . Why does not it gets installed while installation of SQL server 2005. Are these assemly dependent on SP1 ?

Thanks

Mohit

View 4 Replies View Related

Simple Question - Creating Calculated Member For %

Dec 20, 2004

Hi,

As the users drill down one of the dimensions, I want to show the breakup of each sub member in terms of %age e.g.

Suppose total Sales are 300 and breakup is as follows:
Category A = $ 120
Category B = $ 65
Category C = $ 115


I want that when the user drills down to the 2nd level in the Products dimension (let's say this Level is called Category) then I want the %age of Sales for each category - all adding up to 100

So this is what I should get (for the desired % breakup)

Category A = 40
Category B = 22
Category C = 38


I'll appreciate your help in making such a Calculated field.

Many TIA.

View 6 Replies View Related

Creating A Calculated Field From Within VWD? (Visual Web Developer Express)

Feb 27, 2008

I'm trying to create a calculated field using SQL Server Express, from within the Visual Web Developer Express 2008 IED. The field I am trying to create is called "total" and is supposed to be the sum of the subtotal, salestax, and shipping fields.

Is this possible from within VWD 2008? How?


Thank you!!!

View 3 Replies View Related

Analysis :: Calculate Members At Certain Level In One Hierarchy As Sum Of Corresponding Members At A Certain Level

Jun 4, 2015

Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008. 

Ex.: 
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9

The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state. 

So far what I've got is:

CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;

This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.

View 5 Replies View Related

SQL 2012 :: Calculated Columns Conditional On Calculated Columns Multiple Tables

Apr 20, 2014

I have 4 tables involved here. The priority table is TABLE1:

NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500

TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15

[code]...

Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.

View 0 Replies View Related

Using Members And Roles With ONE Db

Jan 8, 2007

i'm not sure to put this in data or security, so i'll put it in both and put on my flame suit.....I'd like to setup the security to use the one single DB that i've setup to use for my inventory, instead of the ASPNETDB.MDF that accompanies the normal setup.If i need to include more info, please ask. 

View 1 Replies View Related

ARG! - Relationship Between Two Members

Dec 9, 2006

Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain...

The table (relationships) looks like this:
ID
type: int

RELID1
type: int, is the user id that initiated the relationship request.

RELID2
type: int, is the user id of the second person in the relationship.

Story
type: varchar(255), quick blerb on how they are related.

Type
type: int, a number 1-20 based on the relationship they have

Status
type: int, 1 = confirmed by second person, 0 = not confirmed

What i'd like to have pulled is a list of the people that person (for example: 70) is related to. I am having the two following problems:
1. unqid of "70" could be in RELID1 or RELID2 as they could have initiated the request or been the second person.
2. I don't want to display them selves in their own relationship listing

Example Data:
ID
1
2
3
4

RELID1
25
15
70
12

RELID2
54
70
13
8

Story
Met on the east coast
Met at walmart
Met walking
Met outside

Type
14
11
3
8

Status
1
1
1
1

Example Output:
Again assuming the current user is "70", the sql should pull: 15, 13.. but i'd like to pull their names from another table called "Users" where "15" and "13" are the UnqID's in a column called "ID".
So:
15 = Bob Smith
13 = Jane DOe

What I've Tried:

Code:


SELECT RELID1, RELID2, FirstName, LastName
FROM Relationship
INNER JOIN ReMembers
ON Relationship.RELID1 = ReMembers.AccountID
WHERE RELID1 = 70 OR RELID2 = 70



This works, however, this only works for RELID1 and not RELID2 column. Moreover, this repeats the data for some reason.


If you have any more questions, or something isn't clear please let me know!

Thanks!

~ PhIve ~

View 12 Replies View Related

TextBoxImpl Members

May 4, 2008

I am trying to use embedded code to determine if one of the textboxes on a report is visible. When I try to call the method that I have created I receive the following error:

The Value expression for the textbox €˜textbox1€™ contains an error: Public member 'visible' on type 'TextBoxImpl' not found.


What members are available to this type? I have not been able to locate any information on the web thus far. If someone can answer this question then this will also resolve an issue in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3274580&SiteID=1).

View 8 Replies View Related

What Kind Of DB Is Members ASPNETDB.MDF?

Aug 3, 2007

Hello,   I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in  ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database?  . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access                   Unlimited     Microsoft SQL Server 2000     1 x    MySQL                                 1 x    PostgreSQL                           1 xDSN/ODBC                           Unlimited SQL Database Users              Unlimited         Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you,   Beezgetz       

View 11 Replies View Related

Adding Members To DatabaseMailUserRole

Nov 15, 2007

I'm new at SQL. But I am trying to add a user to DatabaseMailUserRole. I found a sample script, but don't know how to edit it:

sp_addrolemember @rolename = 'DatabaseMailUserRole',
@membername = '<database user>'

I'm trying to add "Student" to the DatabaseMailUserRole. Please show me the SQL statement to do this. Thanks in advance.

View 1 Replies View Related

Group Login Members

Feb 20, 2008

Hi All,

'SACCAPRiskGroup' is my group login which has few users.
Now i want to know all users in this group. For this i am using
EXEC sp_helprolemember 'SACCAPRiskGroup'.

But no luck.

Can any one help me in this.

Thanks.
Malathi.

View 2 Replies View Related

Aggregated Members And Attributes

May 29, 2008

Hi,

the query below (from Adventure Works) displays the sales amount for three products and a custom member "aggregation" which is the aggregate of these three products, and it cross joins with the attribute "colour".




Code Snippet

with member [Product].[Product Categories].[Subcategory].&[31].[aggregation] as 'AGGREGATE({ [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220] })'



SELECT { [Date].[Calendar].[All Periods] } ON COLUMNS ,



NON EMPTY { { { [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220],[Product].[Product Categories].[Subcategory].&[31].[aggregation] } * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS



FROM [Adventure Works]



WHERE ( [Measures].[Reseller Sales Amount] )


Can someone please explain me why I'm getting this result:








All Periods

Sport-100 Helmet, Red
Red
39328.1586

Sport-100 Helmet, Black
Black
12098.0788

Sport-100 Helmet, Blue
Blue
13331.5816

aggregation
Black
64757.819

aggregation
Blue
64757.819

aggregation
Red
64757.819 (note that 64757.819 is the total of the three products)


instead of something like this:









All Periods

Sport-100 Helmet, Red
Red
39328.1586

Sport-100 Helmet, Black
Black
12098.0788

Sport-100 Helmet, Blue
Blue
13331.5816

aggregation
Black
12098.0788

aggregation
Blue
13331.5816

aggregation
Red
39328.1586

and also if anyone knows of a possible way of getting the second type of result?

please note that if I create a custom member that aggregates members of any other level of the Product Category hierarchy, the problem doesn't exist (see code and results below)




Code Snippet
WITH MEMBER [Product].[Product Categories].[Category].&[4].[Aggregation] as
'AGGREGATE({ [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32] })'
SELECT { [Date].[Calendar].DEFAULTMEMBER } ON COLUMNS ,
NON EMPTY { { { [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32],
[Product].[Product Categories].[Category].&[4].[Aggregation]} * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )














All Periods

Helmets
Black
87915.3689

Helmets
Blue
91052.8681

Helmets
Red
79744.6953

Hydration Packs
Silver
65518.7485

aggregation
Black
87915.3689

aggregation
Blue
91052.8681

aggregation
Red
79744.6953

aggregation
Silver
65518.7485

View 1 Replies View Related

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

Inferred Members Not Updated

Dec 15, 2007

Hi

We have modelled snowflake schema where DegreeDimension(Childdimension) flakes to ProviderDimension(ParentDimension).
We are using SCD transformation with inferred member support enabled and radiobutton :use a boolean column to indicate wheather current record is an inferredmember.



DegreeDimension inserts some inferred records in to ProviderDimension with Providerid(BusinessKey) as 001 and inferredrowflag as 1. Now this particular record comes from ProviderDimension and instead of updating the inferred record, the SCD wizard in ProviderDimension is inserting the inferred member as a new record.
The business key inserted by degreeDimension is equally same as the business key coming from provider dimension.

Kindly suggest as to why SCD transformation is not updating inferred member but it is inserting as a .new row.



Thanks in advance
Mahesh

View 2 Replies View Related

Problems With Paralel Members

Mar 21, 2008

The Enrolled column displays data but the sma12 shows #error

WITH MEMBER [Measures].[SMA12] AS
Sum
(
ParallelPeriod([Dim Times].[Calandar].[Month], 11):[Dim Times].[Calandar], [Measures].[Enrolled]
)
/
Count(ParallelPeriod([Dim Times].[Calandar].[Month], 11):[Dim Times].[Calandar])
SELECT {[Measures].[Enrolled], [Measures].[SMA12]} ON 0
,[Dim Product Lines].[Product Line].Members ON 1
FROM [BusinessIntelligence]


What am I doing wrong?

View 5 Replies View Related







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