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 RepliesThis 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?
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????
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 RelatedHi,
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
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
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
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!
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?
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?
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])
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!
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.
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.
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.
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 RelatedHi 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 ~
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).
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 RelatedI'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.
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.
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
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!
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
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?
In VS, you can automatically can create roles/members etc and vs creates tables in SQL Express.
Are there SQL scripts zo I can create the tables in SQL instead of SQL Express.
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
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
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...
I set up a table called relationships that 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
The following code works great that I got with the help on another form:
SELECT Relationship.RELID1
, Relationship.RELID2
, ReMembers.FirstName
, ReMembers.LastName
FROM Relationship
INNER
JOIN ReMembers
ON ReMembers.AccountID = Relationship.RELID1
WHERE Relationship.RELID2 = 70
UNION ALL
SELECT Relationship.RELID1
, Relationship.RELID2
, ReMembers.FirstName
, ReMembers.LastName
FROM Relationship
INNER
JOIN ReMembers
ON ReMembers.AccountID = Relationship.RELID2
WHERE Relationship.RELID2 = 70
My question is: What is the best way to set this table up? I'm not committed to any any design as of right now, but want to be sure I set it up in the most efficient manner.
Any feedback / opinions are welcome! :)
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
I'm using a SQL 2008 database on a SQL 2012 hosted server. I have an autoincrement ID for locations that my members create. These go up by 1.... eg. 9400, 9401, 9402.
Tonight a member attempted to make a new location and the ID went from 9407, 9408 and JUMPED to 10408. Any subsequent creations only continue from 10408 onwards.
What happened here? 9409 to 10407 just aren't available.
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
Enterprise 2014 SQL Server - SSAS
I have a need to insert 50 - 100 new members each week.Â
Is there an SSIS procedure that will do this?Â