MEASURE PROPERTIES

I have a report that has multiple measures coming from an OLAP data source.
Some of these measures are counts, some are % and some are $. How can I set
the format property for each. Thanks.
--
Michael Hardy
ETL Developer
Visit our web pages:
www.atlantis.com
www.oceanclub.com
www.oneandonlyresorts.com
www.kerzner.comIn RS 2000, you have to set format codes on the textboxes where you show the
field values.
E.g. P0 for percentages without decimals, P2 for percentages with two
decimals, C for currency values, etc. More information on format codes is
available on MSDN:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
NOTE: format codes only have an effect if the contents of the textbox is not
a string, but a numeric data type. You might need to use conversion
functions to make sure the value is a numeric value. Common conversion
functions are listed here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Opuspocus" <Michael.Hardy@.Kerzner.Com> wrote in message
news:427B1A97-96CB-48F7-B7F0-3A88F79B50E5@.microsoft.com...
> I have a report that has multiple measures coming from an OLAP data
source.
> Some of these measures are counts, some are % and some are $. How can I
set
> the format property for each. Thanks.
> --
> Michael Hardy
> ETL Developer
> Visit our web pages:
> www.atlantis.com
> www.oceanclub.com
> www.oneandonlyresorts.com
> www.kerzner.com
>

MDX to locate data on a higher aggregation from a lower aggregation

I think I have a strange problem with a cube. The problem does come from the source data provider, but since there are no means to change that, I have to live with it and make the best of it.

Let me try to describe my situation: I gather data on two levels, two different grades of aggregation (for me: Groups and individual lines). On the higher level, I see an exact amount of time (<-my target), on the finer one I only have a sum of my target value and another time span. I also have counts of members on each level.

What I need to do is "split up" the sum value I have on the fine grain level according to distribution on the higher granularity level.

Does MDX provide a way to "look up" one aggregation level from a cell and use that value as a base for calculation? what would be the typical commands to step up along one aggregation line and request a value from there?

This is a more detailed explanation of the situation in case you are interested:

I have two measures on a group level, like this:

Group 1 50 (a) 1000 (b)

Group 2 some numbers as well

The 50 (a) means 50 seconds average worktime that happened by 1000 (b) jobs that were handled in the group.

Each group consists of 2 to 5 lines. For each line I also have a counting of jobs and a time, but this time is a total time, consisting of a waiting period and the worktime, like this:

Group 1

Line 1 80 750

Line 2 120 250

So, what that means is Group 1 handled 1000 jobs with 50 seconds average worktime, and that resulted out of 750 jobs handled in line 1 with an average (waiting + working time) of 80 seconds and 250 jobs that had 120 seconds work and wait time.

What I need to do is find out how long the work time was in each line, how the 80 seconds distributes between work and wait time. The only way to do that is to estimate the the waiting time in both lines is equal (in my real project it really is, or I do estimate it to be equal as there is not reason not to think so)

If you resolve this little riddle, you find that Line 1 has a waiting of 40 and work of 40, and Line 2 is waiting 40 as well but working is 120.

Hi Ralf_From_Europe:

Perhaps I'm simplifying this too much. If I am simplifying too much please correct me and I can look at the problem again. I think you can get the value you want by using the .PARENT function. You can reference the <dim>.<hierarchy>.CURRENTMEMBER.PARENT and get the value from the group level for your calculation. To illustrate I included an MDX query in this reply which references the CurrentMember.Parent sales value, shows the value, and uses it in a calculation. The sample works against the AdventureWorks DW sample from Microsoft.

Hope this helps.

PaulG

WITH MEMBER Measures.ProductParentSales AS
'
([Product].[Product Categories].CurrentMember.Parent, [Measures].[Internet Sales Amount])
'

MEMBER Measures.MyNewCalc AS
'
Measures.ProductParentSales/[Measures].[Internet Sales Amount]
', FORMAT_STRING = "#,0.00"

SELECT
{[Measures].[Internet Sales Amount], Measures.ProductParentSales, Measures.MyNewCalc} ON COLUMNS,
{[Product].[Product Categories].[Subcategory].[Bottles and Cages]
,[Product].[Product Categories].[Subcategory].[Bottles and Cages].Children
,[Product].[Product Categories].[Subcategory].[Helmets]
,[Product].[Product Categories].[Subcategory].[Helmets].Children} ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003])

|||

"..you find that Line 1 has a waiting of 40 and work of 40, and Line 2 is waiting 40 as well but working is 120". Shouldn't it be 80, not 120?

Anyway, if you look at the Adventure Works MDX Calculation Script section for [Measures].[Sales Amount Quota], you'll find allocations like:

/*-- Allocate equally to months in FY 2002 --*/

Scope

(

[Date].[Fiscal Year].&[2002],

[Date].[Fiscal].[Month].Members

);

This = [Date].[Fiscal].CurrentMember.Parent / 3;

End Scope;

The Sales Targets Measure Group relates to Date at the Calendar Quarter granularity, so monthly [Measures].[Sales Amount Quota] aren't directly loaded from fact data. Instead, they are allocated - is this along the lines of what you want to do?

|||

Thanks both of you for your responses. I think I do understand a bit better now, I think I understand what the MDX parts in each answer is trying to do, but... it turns out I do not know how to "use" MDX in my case, I do not know where to "put it" in my SSAS project. There is measured columns, unary operators, semiadditive calculations and finally custom rollups. I think I need to do a lot of reading in the "advanced design" chapters of my books.

It seems I left out a crucial detail in my first description as well, the data on the two different levels is in different tables and therefore in different measuregroups. I hope that does not mean this makes it impossible. The book I try to learn from does work with adventure works as well and uses several measure groups at once, so I dare say it is possible.

But if I may, let me take out the first step of what I need to and see if someone can help me with it. This is not really different from the original question, it is only the very first step to calculate an intermediate result needed later to solve the "real wanted" value. Say my cube looks like this:

Group Line Counter Value

G1 L1 750 80 750x 80=60,000

G1 L2 250 120 250x120=30,000

G1Total 1000 200 90,000 / 1,000 = 90

The 200 marked in red is just summed up, which is wrong. The correct calculation is shown on the right, and the result is 90. In words you would say: For all children (L1, L2) of a Group (G1) sum up all the products of counter x value and divide by the sum of counter.

Now: Can that be put into MDX? And if it can, where does it have to be "installed" into the cube? I would go with custom rollup, which requires me to put the MDX into a cell of the table the dimension is based on, correct?

I'd really appreciate any hint or website or book recommendation! Thanks in advance.

Ralf

P.S. Deepak:You are of course absolutly right, I wrote down the result wrong. Thanks for pointing it out!

|||

Assuming that you have a dimension like [DimGroupLine], with [Line] attribute rolling upto [Group] attribute, you could add such a calculation statement to your cube MDX script:

([Measures].[Value], [DimGroupLine].[Group].Members)

= Sum(Existing [DimGroupLine].[Line].[Line].Members,

[Measures].[Counter] * [Measures].[Value]) /

[Measures].[Counter];

MDX resultset in SSIS

Hi, I'm trying to write a MDX query resultset from a cube to a database table
via an SSIS package. I create an OLE DB source in a data flow and choose OLE
DB Provider for Analysis Services 9.0 as the connection manager. Then I run
it through some node afterwards but it's the OLE DB source node that's giving
me a headache.
Once run it gives me a...
[Source - Query [1]] Error: An OLE DB error has occurred. Error code:
0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB
Provider for Analysis Services 2005" Hresult: 0x80004005 Description:
"Server: The operation has been cancelled.".
and...
[DTS.Pipeline] Error: component "Source - Query" (1) failed the pre-execute
phase and returned error code 0xC0202009.
I sould say that the query is quite heavy.
Can anyone help, thank you!
Jacob
Hi,
Is there really no one who can help me here? I would really appreciate it.
Alternatively, maybe someone could tell me where/to whom I might pose my
questions.
Thanks a lot.
/Jacob

MDX resultset in SSIS

Hi, I'm trying to write a MDX query resultset from a cube to a database tabl
e
via an SSIS package. I create an OLE DB source in a data flow and choose OLE
DB Provider for Analysis Services 9.0 as the connection manager. Then I run
it through some node afterwards but it's the OLE DB source node that's givin
g
me a headache.
Once run it gives me a...
[Source - Query [1]] Error: An OLE DB error has occurred. Error code
:
0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB
Provider for Analysis Services 2005" Hresult: 0x80004005 Description:
"Server: The operation has been cancelled.".
and...
[DTS.Pipeline] Error: component "Source - Query" (1) failed the pre-exec
ute
phase and returned error code 0xC0202009.
I sould say that the query is quite heavy.
Can anyone help, thank you!
JacobHi,
Is there really no one who can help me here? I would really appreciate it.
Alternatively, maybe someone could tell me where/to whom I might pose my
questions.
Thanks a lot.
/Jacob|||Hi Jacob!
We are trying to accomplish the same thing as you describe above (with same
error messages). Were you successful with your efforts, or should we go anot
her way?
Thank you!
Radim Hampel

MDX query to filter the cube

I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :

pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

Now

my requirement is to show a filtered cube, not the whole cube. I am

using SQL server 2000 Analysis Services to prepare and store the cube.

I

think an MDX query as commandText can do this. But I am not being able

to write the suitable MDX that can give a filtered cube which I can

use to populate pivot table?

I have used MDX query as "select

from <cube name> where <filter condition(s)>" . But it is

showing that no column found that excel can use.

What would be the currect MDX?

I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.

Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.

That should give you an idea how to control OWC.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.

There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:

Code Snippet

varArray = Array("Bikes")

pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray

MDX query to filter the cube

I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :

pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

Now my requirement is to show a filtered cube, not the whole cube. I am using SQL server 2000 Analysis Services to prepare and store the cube.

I think an MDX query as commandText can do this. But I am not being able to write the suitable MDX that can give a filtered cube which I can use to populate pivot table?

I have used MDX query as "select from <cube name> where <filter condition(s)>" . But it is showing that no column found that excel can use.

What would be the currect MDX?

I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.

Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.

That should give you an idea how to control OWC.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.

There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:

Code Snippet

varArray = Array("Bikes")

pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray

MDX query question

Here is the content of my source table.

IND_ID CALL_ID

1 1

1 2

2 3

2 4

3 3

3 4

4 7

I would like to create a measures which return the number of IND_ID with 2 element in the group for exemple

Call #2 times

2

Call #1 times

1

could u help me please !

Sorry, but I'm kinda confused about what you are trying to return. Could you describe the logic in a little more detail or possilby even write a SQL statement to show us what it is you are trying to achieve?

Thanks,
Bryan

MDX query for an Histogram

Hi All,

Trying to plot an Histogram chart in Reporting services using the data source as a cube. Currently the cube doesnt retrives the intervals from the cube for which the count is 0. I need the chart to show all the intervals everytime even when the count for that particular interval is 0.

Has anyone created any such chart? or can any one please help me with the correct MDX query for it.

Awaiting a response.

Regards,
Sumeet

What you will find is that if a count measure does not have any values it will be empty rather than 0 and in your query you probably have a "NON EMPTY" statement on the axis which is excluding the empty cells. Removing the "NON EMPTY" clause from the axis in question should display the members with empty (0) values. If this does not help, posting your query might help in seeing what is going on.|||

The Non empty thing doesnt works, already tried with that. The query used in the Data source view is as follows:-

Case when Earned_Value <> 0 or Earned_Value <> null then
Case when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) < -0.50 then
'Less than -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.50 and -0.41 then
'-41% to -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.40and -0.31 then
'-31% to -40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.30 and -0.21 then
'-21% to -30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.20 and -0.11 then
'-11% to -20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.10 and -0.01 then
'-1% to -10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) = 0 then
'0%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.01 and 0.10 then
'1% to 10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.11 and 0.20 then
'11% to 20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.21 and 0.30 then
'21% to 30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.31 and 0.40 then
'31% to 40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.41 and 0.50 then
'41% to 50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) > 0.50 then
'Above 50%'
end
end

The above query excludes the intervals itself for which their is not a value and thus no zero value is assigned to that interval as the interval itself doesnot exists. So actually the above query itself should be modified so that zero value can be assigned to the intervals for which no value is present.

Do anyone have an idea how this can be done?

Regards,
Sumeet

|||

I'm not sure if this will help or not, you have swapped from talking about an MDX query against the cube to wanting to change a named view in the DSV. But if all you want to do is to assign the records with 0 or no Earned_Value some sort of label, then adding the lines in red below should fix this.

Case when Earned_Value <> 0 or Earned_Value <> null then
Case when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) < -0.50 then
'Less than -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.50 and -0.41 then
'-41% to -50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.40and -0.31 then
'-31% to -40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.30 and -0.21 then
'-21% to -30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.20 and -0.11 then
'-11% to -20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between -0.10 and -0.01 then
'-1% to -10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) = 0 then
'0%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.01 and 0.10 then
'1% to 10%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.11 and 0.20 then
'11% to 20%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.21 and 0.30 then
'21% to 30%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.31 and 0.40 then
'31% to 40%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) between 0.41 and 0.50 then
'41% to 50%'
when CAST((Earned_Value - Actual_Cost)/Earned_Value AS decimal(8,2)) > 0.50 then
'Above 50%'
end
else
'no value'
end

MDX in MS Reporting Services

Hi

(All using 2005 release)

I've written a MSRS report based on an OLAP data source which is working fine in the report designer but once I have deployed it to the report server & attempt to browse it I get the following errror:

An error has occurred during report processing.

Query execution failed for data set 'DataSet1'.
Line 1: Incorrect syntax near '{'. Line 1: Incorrect syntax near 'Tbl Acc Codes'

It seems to be pointing to an mdx error although I've constructed the query just by using the drag & drop tool.

Are there any known problems with MSRS & MDX & if not can any of you MDX gurus spot anything wrong with the qry:

SELECT NON EMPTY { [Measures].[Ratio Val] } ON COLUMNS, NON EMPTY { ([Tbl Ac Year].[AcYear].[AcYear].ALLMEMBERS * [Tbl Comparison Group].[Tbl Comparison Group].[Tbl Comparison Group].ALLMEMBERS * [Tbl Comparison Group].[Description].[Description].ALLMEMBERS * [Tbl Group].[Tbl Group].[Tbl Group].ALLMEMBERS * [Tbl Group].[Display Order].[Display Order].ALLMEMBERS * [Tbl Clients].[Tbl Clients].[Tbl Clients].ALLMEMBERS * [Tbl Acc Codes].[Acc Desc Hierarchy].[Description].ALLMEMBERS * [Tbl Acc Codes].[Acc Code Hierarchy].[Tbl Acc Codes].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [FE Benchmark] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks :)

Wayne

Try and paste your query into MDX editor in SQL Managment Studio. It will allow you to perform a syntax check on your query.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.