Analysis :: Getting Available Cell Properties Via XML/A

Jun 1, 2015

In the relevant documentation on Cell Properties I read:

" Only the CELL_ORDINAL, FORMATTED_VALUE, and VALUE cell properties are required. All cell properties, intrinsic or provider-specific, are defined in the PROPERTIES schema rowset, including their data types and provider support. For more information about the PROPERTIES schema rowset, see MDSCHEMA_PROPERTIES Rowset."

So it seems that for each cube, the MDSCHEMA_PROPERTIES rowset would at least need to return CELL_ORDINAL, FORMATTED_VALUE and VALUE would need to be in the MDSCHEMA_PROPERTIES rowset.

However, when I retrieve the MDSCHEMA_PROPERTIES Rowset via XML/A, with a request like this:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="...I can't post links yet..." SOAP-ENV:encodingStyle="...I can't post links yet...">
  <Discover xmlns="urn:schemas-microsoft-com:xml-analysis" SOAP-ENV:encodingStyle="...I can't post links yet...">

[Code] ...

I only get a list of properties that apply to the levels. CELL_ORDINAL, FORMATTED_VALUE and VALUE are not there.

The rowset columns for PROPERTY_TYPE is always either 1 or 5 (which according to MDSCHEMA_PROPERTIES documentation indicates these are all member properties, not cell properties)

DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_UNIQUE_NAME are always filled, also indicating that these are member properties, not cell properties.

So the question is: Why don't I see any cell properties in the rowset?Is the documentation on Cell properties I cited wrong? Is there another way to retrieve a list or rowset of available cell properties via XML/A?

View 3 Replies


Analysis :: Hide Member Properties By Default?

Oct 28, 2015

Is there any option in SSAS cube to hide the member properties from the user by default in the cube itself. We know we have a option in the excel pivot 'Show properties in tooltips check box where we can hide or display the member properties. But I need an option in the cube  to hide the member properties by default and display in the excel depends on demand.

View 2 Replies View Related

Copying An Ntext Cell From One Cell Into Another (destination Row Already Exists)

May 6, 2004


What I'd like to do is:

UPDATE table1
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @precomputed_id_1)
WHERE table1_ID = @precomputed_id_2

Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.

I'd like to do something like (PSEUDOcode):

WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@textptr_initialised_to_point_at_target_c ell))

But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...

Any hints or pointers HUGELY appreciated... THANX

View 1 Replies View Related

Is There A Way To Do Mass Formatting Of Cells In A Table Instead Of Cell By Cell?

Oct 5, 2007

I'm working with MRS and I've got a table with a lot of entries. For each value in the table I'm trying to get the text colour to be set to 'red' when the value of the cell is less than 0. Otherwise remain black.

I can do this by setting the colour property cell by cell. But I have a lot of cells in the table. Is there a way to set the statement to apply to ALL cells in the table?

Basically I'm asking if there is a way to set the property in bulk instead of going through tediously cell by cell.

Any help would be much appreciated. Thanks!

View 4 Replies View Related


Jul 20, 2005


View 4 Replies View Related

SQL 2012 :: Connection Properties Versus SSMS Server Properties

Mar 16, 2015

I have an ODBC connection string that is working fine with the following properties:

Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.

I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.

Under Registered server name I've tried:


When I browse the server for the database instance list, I receive "network path was not found".

I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.

What am I doing wrong ?

View 1 Replies View Related

Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error

Mar 5, 2015

I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.

We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.

View 10 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS.  In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task.  The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube.  This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.

I would expect that once development is done, and it is processed and deployed, that is it.  My thinking is that the SSIS task should just update the already deployed cube,

View 2 Replies View Related

Analysis :: How To Right Choose Key Column In Mining Structure For Microsoft Analysis Services

Jun 12, 2015

How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:

"Incoming goods"

Create table Income (         
ID int not null identity(1, 1)            
[Date] datetime not null,             
GoodID int not null,               
PriceDeliver decimal(18, 2) not null,               
PriceSalse decimal(18, 2) not null,               
CONSTRAINT FK_IncomeGood foreign key (GoodID)  references dbo.Goods ( ID )            

I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?

View 2 Replies View Related

Analysis :: Create Analysis Services Project In Visual Studio 2012 Data Tools?

Feb 18, 2013

It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?

View 5 Replies View Related

Analysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?

Nov 24, 2015

I am using

SSAS: version 2008
Excel: version 2013

I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:

    - Type: System.Integer
    - Value: yyyyMMdd
    - Type: System.DateTime
    - Type: System.String
    - Value: dd/MM/yyyy (note: I am not using US culture)
    - Example: 01/11/2015  
    - Type: System.String
    - Value: %d/%M/yyyy (note: I am not using US culture) 
    - Example: 1/11/2015  

Filtering on date is working fine:

Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting  DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:

I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:

But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.

For date values of my data coming from cube, excel consider them as 'General'

    2.1. But value here is same as it was in simple test.

    2.2. 'Date Filter' works just fine.

    2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell. 

    2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.

    2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.

So, I don't know, what is the puzzle piece that I am missing.

1. Date filter works, group does not work

2. Excel consider it as 'General' string.

3. SSAS does not allow to change 'NameColumn' to Date.

View 2 Replies View Related

Analysis :: Running Analysis Service In Terms Of Port Usage?

May 30, 2015

I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.

View 2 Replies View Related

Analysis :: Creating Roles In Analysis Service Without AD Account

Aug 6, 2015

I want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.

View 3 Replies View Related

Apostrophe In Cell

Jan 8, 2008

I need help with a simple query. We have 86 entries with the City of O'Fallon in our db. How do I do this with the apostrophe in O'Fallon? Below is just to give an idea of what I want. Thanks.
FROM Organization
WHERE City=O'Fallon

View 2 Replies View Related

Cell Is Ready Only!!!

Apr 5, 2008

Hello All, i am trying to create a normal ASP.NET application using VS2005. Yesterday i was adding tables.. entering data from within "View Table Data" tab, but today i am unable to do any data insertion or updating except by entering the insert or update statement by hand. When updating data of existing record or trying to add new data, it reports that "Cell is readonly!". I did not modify any settings or configuration and actually did nothing to set it to readonly! I am quite confused to be honest and writing sql statements by hand is kinda time consuming for me if i want to update a single field. What can i do to re-enable data modifications from VS2005 without reporting readonly?   Thanks,Rakan       

View 1 Replies View Related

Cell Security : Help

Feb 4, 2004


From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?

View 1 Replies View Related

Invalid Value For Cell

Apr 20, 2007


I updated my db system from access to sql 2005.
But, i can not add new recors to database.
I'm receiving this error:

"""Invalid value for cell (row 3914, column 2).
The changed value in this cell was not recognized as valid.

.Net Framework Data Type: Int32
Error Message: Input str?ng was not in a correct format.

Type a value appropriate for the data type or press ESC to cancel the change."""

It can not open new id number. DataType was Auto Number in Access. Which can I select in SQL 2005?

Thank you.

View 1 Replies View Related

Cell Changed Each Less Than 1 Sec

Feb 1, 2008

hi all
im really have a problem in my project.
i have server and client side each side contain SQL Server DB.
and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed.

now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).

my problem :

u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).

so i do this to know changed cell range:
when run the program saved all excel tabel into SQL server table.
and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.

and rise event to send this row to client by socket over internet to update the row in client side too.

but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.

this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)

and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)

thank u.

View 3 Replies View Related

Cell Is Read Only

Mar 14, 2008


In SQL 2000 I used to be able to open a table in Enterprise Manager and make changes or Update with Query Analyser. Now when I attempt this in SQL 2005 I am told that the Cell is Read Only.

How do I update this column as I cannot find a read-only or allow updates property anywhere?

Please help,

A very frustrated user

View 6 Replies View Related

Cell Is Read Only

Oct 16, 2007

HI All,

I changed the join type and did some modifications to the query in the query designer. Then I executed the query. Resutls are shown in query designer. But it says Cell is read only. Therefore when I change the tap from data to preview , I can not view the report.

This happens only when I use generic query builder.
Does anybody know how to change the read only option ?


View 6 Replies View Related

Row And Cell Security

Dec 13, 2005

I am trying to implement row-security in SQL 2005 but i make a query to make a view

View 14 Replies View Related

Read Cell Value

Jan 4, 2007

anyone can help me plz,

I have data like these in the report page:

Item_ID code_ID
xxx1 x1
xxx2 x2
xxx3 x3
xxx4 x3
xxx5 x3
xxx6 x2

I want get summary data like these:
code_ID x1 : 1 (items)
code_ID x2 : 2 (items)
code_ID x3 : 3 (items)

what should I do to get summarized data like that?
I've tried to use "CountDistinct" but the result always 6 that indicate how many of the data.

thank you.

View 1 Replies View Related

Fetch The Value From Each Cell Into Datatable

Jul 23, 2007

I'm trying this code but nothing is being displayedSqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CON"]);
SqlDataAdapter da = new SqlDataAdapter("my_sproc", conn);DataTable dt = new DataTable();
DataRow dr;
//Adding the columns to the datatabledt.Columns.Add("CategoryIdIn");
dt.Columns.Add("CategoryNameVc");foreach (DataGridItem item in gd_freq.Items)
dr = dt.NewRow();
dr[0] = item.Cells[0].Text;
dr[1] = item.Cells[1].Text;
gd_freq.DataSource = dt;

View 2 Replies View Related

Select Certain Data From One Cell

Jun 13, 2006

Anyone knows how to select
only certain data from one data field?

Lets say i have this field which captures
Member Name and ID.
The ID is in parantheses "()".
I only want the Member name.
How do I select this from the table?

Field: Data
MName: John Doe (123)

I need to select only "John Doe".
Any help is deeply appreciated.
Thank you!!

View 1 Replies View Related

Ordering Values In A Cell

Jul 21, 2004

10 13 15 3 26
10 13 20 35 29
10 13 5 26 2
10 15 27 34 33

Declare @num1 char(5)
Set @num1 = (SELECT ltrim(Substring(ltrim(number2), 8, CHARINDEX(' ', ltrim(number2)))) FROM Num2played)
SELECT @num1 FROM Num2played

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestions or fix?

Thank you.
The data above is in one colum. I would like to rearange the
numbers in order. e.g. from "10 13 15 3 26" to "3 10 13 15 26".

View 4 Replies View Related

Masking Cell Contents

Oct 11, 2004

Is there a way to mask the contents of a cell or column? I have a table that stores passwords and I would like to mask the password much like Access does with asterisks. I know I can restrict the column based on user but that creates other problems on the frontend.

View 1 Replies View Related

Can Use A Formula From A Table Cell

Apr 17, 2012

I'm looking at a system where formulas have been added into fields in a table and I need to look at the field to see what formula to use when selecting eg: eg this + this, this / this etc.Here's a basic table I have knocked up to try different things...

CREATE TABLE #HeaderOrder(
[HeaderCode] [varchar](10) NOT NULL,
[HeaderCode2] [varchar](10) NOT NULL,
[FormulaCode] [varchar](10) NOT NULL


View 8 Replies View Related

Checking !empty() Cell

Sep 29, 2007


I have the following statement:

'select TOP 1 CATCODE From IMS.CATEGORY WHERE YEAR = 2007 and catcode <> ""'

exec @L_SQLCMD

The ms sql not accept the "" for !empty value is there a way to work around

Best Regards

View 7 Replies View Related

Cannot Edit Text Cell

Jul 23, 2005

I am trying to edit a long text cell in Enterprise Manager but when I select"<long text>" and try to delete it, I get an error saying "cannot edit thiscell".Could anyone tell me how to edit this cell?Thanks, Amanda

View 2 Replies View Related

Using Cell Value For Formatting Expression

Mar 25, 2008

I have created a report that uses a fairly complex IIF statement to sum the data. That part works just fine. Now I need to format the font color so negative values show in red. Is there a way to reference the individual cell (by its textbox name maybe?) in the Expression Editor?

For example, if tb_Orders was the name of the textbox that sums the data, I'd like to write something like:

=switch(tb_Orders < 0, "Red")

I could just copy the IIF statement but I'd rather keep things simple. This way, if I need to change the formulas, I don't have to change all of the conditional formatting as well.

Is this possible?


View 4 Replies View Related

Matrix Cell Expression

Apr 21, 2008


I'm currently working on a report which contains a number of matrix' with subtotals. Each matrix calculates a STDEVP which works just fine and the total shows the values I want.

However, I would like to change the font color of each cell based on the value compared to the total.

For example:

A 4.5
B 7.8
C 3.9
D 5.2
Total 5.7

With the values of A and C being less than the Total, I want to change their color to "Green" and the others to "Red".

I was hopeing I would be able to do something like this.
=Iif(ReportItems!matrix1_std.value < xxxxxxxxx,"Green","Red")

Where ReportItems!matrix1_std.value is the values of A-D and xxxxxxxxx would be the "Total"

Is this in some way possible??

I found many expamples for changing the color etc of the totals cell but nothing which tells me how to change the layout of a cell based on the total value.

Many thanks in advance


View 4 Replies View Related

Multicolumn Cell In Table

Feb 21, 2007


I need to realize multicolumn cell in table for grouping fields, but i don't know how to do that. Help me please.

1col. 2 col. 3 col.

long text grouping field

first second third

data data data

Thank you.

View 1 Replies View Related

Column Number Of Particular Cell

Jun 27, 2007


Can I come to know Column number of particular cell.The way we get rownumber().



View 2 Replies View Related

Copyrights 2005-15, All rights reserved