Have you considered a computed column for this?

A quick example is:

DECLARE @TABLE TABLE

(

ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

XmlFile AS (CAST('<root><Id>' + CAST(Id AS VARCHAR(10)) + '</Id><Name>'...

It looks like the calculation is being broken down by the dimension you have on the rows in your diagram.

Without knowing that dimension's name, I can't give an exact answer,...

Don Shields-368041 (3/23/2010)

---

Another thought I had. When you say "caclulating for every date" could this be due to the granularity attibute being set to "Date" on Dimension usage for...

Out of interest, how many financial years are there in your cube? Have you gone very far into the future or the past?

If you're unfamiliar with Mosha's blog, he worked...

EDIT:

Scratch that, try this and report on what happens:

http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!500.entry

Seeing as there's no NON_EMPTY, include the base measure in the query. So, run:

select {[Last year total Sales quantity], [Measures].[QTYINV]}

on...

What level of aggregations do you have on the cube, and have you considered usage-based optimisation? You could capture the long-running query in the query log table, then add aggregations...

This is one of the many reasons I like using views on my fact and dimension tables.

Inserting a view between the tables and the DSV in SSAS allows you...

Does the INSERT require the tally table, and does the tally table need to be populated? I wonder if some sort of concurrency problem could be the cause...

I assume you're...

Hi,

Your problem is that EXEC does not change the @sql variable to be the resultset. It's still the query you put together, hence why you can't convert...

If you haven't read Sommarskog's piece on dynamic SQL and how to avoid it when you want to create a dynamic WHERE clause, then you really should do so now!

http://www.sommarskog.se/dynamic_sql.html

It's...

Hi Sandy, realistically we need to see the underlying tables (And ideally some sample data within them) to help here. Can you post table structures? In Enterprise Manager, right-click the...

Is this what you're looking for?

DECLARE @TABLE TABLE

(

[REF] INT NOT NULL,

[Id] BIGINT NOT NULL,

[Value] INT NOT NULL,

[Code] CHAR(1) NOT NULL

)

INSERT INTO @TABLE([Ref],[ID],[Value],[Code])

SELECT 169, 176810994, 0, 'X' UNION

SELECT 169, 176812280, 0,...

No problem Jeff, I figured you may have skim-read it. I've knocked up a working version of the CLR assembly, some test data is as follows:

Firstly, the working code:

using System;

using...

Nicely found. I doubt either of our solutions are ideal as far as performance and security goes, but it's good to find two solutions to a problem that intially seemed...

Hi Smerg,

If that is the requirement then I really can't see any other way from the CLR. You just can't execute dynamic SQL in a function, so using the...