Hi,
I am using the following query in reporting services
="With Member [Measures].[Time] as '[TimeDim].CurrentMember.UniqueName'
MEMBER [Measures].[DisplayTime] AS '[TimeDim].CurrentMember.Name' SELECT
{[Measures].[Time],[Measures].[DisplayTime]} on columns, {Descendants({" &
Parameters!Year.value & "},[TimeDim].[Actual Date],BEFORE)} on rows from
ICMDestinationService"
I have given even spaces between the Query.
I am getting the following error
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot set the command text for data set 'Time_Dim'.
(rsErrorSettingCommandText) Get Online Help
Error during processing of the CommandText expression of dataset â'Time_Dimâ'.
(rsQueryCommandTextProcessingError)
Any ideas or solutionVerify the MDX runs in the MDX Sample Application first.
Also, make sure you are using Generic Query Designer - 3rd or 4th button to
the right of dataset dropdown in data window
Finally, be sure there are no carriage returns in the query.
If still having problems, see advice offered in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
news:4BADB3C5-20E1-495C-B1A0-8914E30E34DE@.microsoft.com...
> Hi,
> I am using the following query in reporting services
> ="With Member [Measures].[Time] as '[TimeDim].CurrentMember.UniqueName'
> MEMBER [Measures].[DisplayTime] AS '[TimeDim].CurrentMember.Name' SELECT
> {[Measures].[Time],[Measures].[DisplayTime]} on columns, {Descendants({" &
> Parameters!Year.value & "},[TimeDim].[Actual Date],BEFORE)} on rows from
> ICMDestinationService"
> I have given even spaces between the Query.
>
> I am getting the following error
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot set the command text for data set 'Time_Dim'.
> (rsErrorSettingCommandText) Get Online Help
> Error during processing of the CommandText expression of dataset 'Time_Dim'.
> (rsQueryCommandTextProcessingError)
> Any ideas or solution
>
Showing posts with label member. Show all posts
Showing posts with label member. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Reference to a non-shared member requires an object reference
Hi,
I tried to use a custom assembly, written in C#. I received the following
error:
"[BC30469] Reference to a non-shared member requires an object reference..."
Here's the detail info for the custom assembly:
*I have 2 C# files. I did not write the code. So my C# knowledge is
limited. I removed some of the code within the methods because it gets very
long. Here is the code:
--[IConvertor.cs]--
using System;
namespace sbu.convertor
{
public interface IConvertor
{
string convert(string label, string direction, string val);
string convertByUnit(string before, string after, string val, string
direction, string group, string format);
string convertByUnit2(string before, string after, string val, string
direction);
string getUnit(string usUnit, string saUnit, string direction, string
group);
string getUnit2(string usUnit, string saUnit, string direction);
}
}
--[Convertor.cs]--
using System;
using System.IO;
using System.Xml;
using System.Globalization;
namespace sbu.convertor
{
public class Convertor:IConvertor
{
private static Convertor instance;
public static IConvertor getInstance()
{
if (instance==null)
{
instance = new Convertor();
}
return instance;
}
private XmlDocument doc;
private string docSource = null;
private NumberFormatInfo format;
public Convertor()
{
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//pdu dev box
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//webdevtest
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
doc = new XmlDocument();
doc.Load(docSource);
format = new NumberFormatInfo();
format.NumberDecimalDigits = 3;
format.NumberGroupSizes = new int[] {3};
format.NumberGroupSeparator = ",";
format.NumberDecimalSeparator = ".";
}
public string convert(string label, string direction, string val)
{
return (double.Parse(val)*getRatio(label,direction)).ToString();
//return "0000000000";
}
private double getRatio(string label, string direction)
{
XmlNode node = doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
string valStr = node.InnerText;
return double.Parse(valStr);
}
public string convertByUnit(string usUnit, string saUnit, string val,
string direction, string group, string format)
{
////
}
public string convertByUnit2(string before, string after, string val,
string direction)
{
////
}
public string getUnit(string usUnit, string saUnit, string direction,
string group)
{
///
}
public string getUnit2(string usUnit, string saUnit, string direction)
{
///
}
public string convertTemp(string val, string direction)
{
///
}
}
}
* Here is how I complied it:
csc /t:library /out:bin/sbu_convertor.dll *.cs
Thanks in advance. JLI found a solution. I simply added the class and instance names in the
Reference tab. That fixed it.
"JL" wrote:
> Hi,
> I tried to use a custom assembly, written in C#. I received the following
> error:
> "[BC30469] Reference to a non-shared member requires an object reference..."
>
> Here's the detail info for the custom assembly:
> *I have 2 C# files. I did not write the code. So my C# knowledge is
> limited. I removed some of the code within the methods because it gets very
> long. Here is the code:
> --[IConvertor.cs]--
> using System;
> namespace sbu.convertor
> {
> public interface IConvertor
> {
> string convert(string label, string direction, string val);
> string convertByUnit(string before, string after, string val, string
> direction, string group, string format);
> string convertByUnit2(string before, string after, string val, string
> direction);
> string getUnit(string usUnit, string saUnit, string direction, string
> group);
> string getUnit2(string usUnit, string saUnit, string direction);
> }
> }
>
> --[Convertor.cs]--
> using System;
> using System.IO;
> using System.Xml;
> using System.Globalization;
> namespace sbu.convertor
> {
> public class Convertor:IConvertor
> {
> private static Convertor instance;
> public static IConvertor getInstance()
> {
> if (instance==null)
> {
> instance = new Convertor();
> }
> return instance;
> }
> private XmlDocument doc;
> private string docSource = null;
> private NumberFormatInfo format;
> public Convertor()
> {
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //pdu dev box
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //webdevtest
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> doc = new XmlDocument();
> doc.Load(docSource);
> format = new NumberFormatInfo();
> format.NumberDecimalDigits = 3;
> format.NumberGroupSizes = new int[] {3};
> format.NumberGroupSeparator = ",";
> format.NumberDecimalSeparator = ".";
> }
> public string convert(string label, string direction, string val)
> {
> return (double.Parse(val)*getRatio(label,direction)).ToString();
> //return "0000000000";
> }
> private double getRatio(string label, string direction)
> {
> XmlNode node => doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
> string valStr = node.InnerText;
> return double.Parse(valStr);
> }
> public string convertByUnit(string usUnit, string saUnit, string val,
> string direction, string group, string format)
> {
> ////
> }
> public string convertByUnit2(string before, string after, string val,
> string direction)
> {
> ////
> }
> public string getUnit(string usUnit, string saUnit, string direction,
> string group)
> {
>
> ///
> }
> public string getUnit2(string usUnit, string saUnit, string direction)
> {
> ///
> }
> public string convertTemp(string val, string direction)
> {
> ///
> }
> }
> }
>
> * Here is how I complied it:
> csc /t:library /out:bin/sbu_convertor.dll *.cs
>
> Thanks in advance. JL
I tried to use a custom assembly, written in C#. I received the following
error:
"[BC30469] Reference to a non-shared member requires an object reference..."
Here's the detail info for the custom assembly:
*I have 2 C# files. I did not write the code. So my C# knowledge is
limited. I removed some of the code within the methods because it gets very
long. Here is the code:
--[IConvertor.cs]--
using System;
namespace sbu.convertor
{
public interface IConvertor
{
string convert(string label, string direction, string val);
string convertByUnit(string before, string after, string val, string
direction, string group, string format);
string convertByUnit2(string before, string after, string val, string
direction);
string getUnit(string usUnit, string saUnit, string direction, string
group);
string getUnit2(string usUnit, string saUnit, string direction);
}
}
--[Convertor.cs]--
using System;
using System.IO;
using System.Xml;
using System.Globalization;
namespace sbu.convertor
{
public class Convertor:IConvertor
{
private static Convertor instance;
public static IConvertor getInstance()
{
if (instance==null)
{
instance = new Convertor();
}
return instance;
}
private XmlDocument doc;
private string docSource = null;
private NumberFormatInfo format;
public Convertor()
{
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//pdu dev box
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
{
//webdevtest
docSource = "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
}
doc = new XmlDocument();
doc.Load(docSource);
format = new NumberFormatInfo();
format.NumberDecimalDigits = 3;
format.NumberGroupSizes = new int[] {3};
format.NumberGroupSeparator = ",";
format.NumberDecimalSeparator = ".";
}
public string convert(string label, string direction, string val)
{
return (double.Parse(val)*getRatio(label,direction)).ToString();
//return "0000000000";
}
private double getRatio(string label, string direction)
{
XmlNode node = doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
string valStr = node.InnerText;
return double.Parse(valStr);
}
public string convertByUnit(string usUnit, string saUnit, string val,
string direction, string group, string format)
{
////
}
public string convertByUnit2(string before, string after, string val,
string direction)
{
////
}
public string getUnit(string usUnit, string saUnit, string direction,
string group)
{
///
}
public string getUnit2(string usUnit, string saUnit, string direction)
{
///
}
public string convertTemp(string val, string direction)
{
///
}
}
}
* Here is how I complied it:
csc /t:library /out:bin/sbu_convertor.dll *.cs
Thanks in advance. JLI found a solution. I simply added the class and instance names in the
Reference tab. That fixed it.
"JL" wrote:
> Hi,
> I tried to use a custom assembly, written in C#. I received the following
> error:
> "[BC30469] Reference to a non-shared member requires an object reference..."
>
> Here's the detail info for the custom assembly:
> *I have 2 C# files. I did not write the code. So my C# knowledge is
> limited. I removed some of the code within the methods because it gets very
> long. Here is the code:
> --[IConvertor.cs]--
> using System;
> namespace sbu.convertor
> {
> public interface IConvertor
> {
> string convert(string label, string direction, string val);
> string convertByUnit(string before, string after, string val, string
> direction, string group, string format);
> string convertByUnit2(string before, string after, string val, string
> direction);
> string getUnit(string usUnit, string saUnit, string direction, string
> group);
> string getUnit2(string usUnit, string saUnit, string direction);
> }
> }
>
> --[Convertor.cs]--
> using System;
> using System.IO;
> using System.Xml;
> using System.Globalization;
> namespace sbu.convertor
> {
> public class Convertor:IConvertor
> {
> private static Convertor instance;
> public static IConvertor getInstance()
> {
> if (instance==null)
> {
> instance = new Convertor();
> }
> return instance;
> }
> private XmlDocument doc;
> private string docSource = null;
> private NumberFormatInfo format;
> public Convertor()
> {
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //pdu dev box
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> if(File.Exists("C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml"))
> {
> //webdevtest
> docSource => "C:\\Inetpub\\wwwroot\\rocks\\itapps\\sbu\\sbu_convertor\\conversion.xml";
> }
> doc = new XmlDocument();
> doc.Load(docSource);
> format = new NumberFormatInfo();
> format.NumberDecimalDigits = 3;
> format.NumberGroupSizes = new int[] {3};
> format.NumberGroupSeparator = ",";
> format.NumberDecimalSeparator = ".";
> }
> public string convert(string label, string direction, string val)
> {
> return (double.Parse(val)*getRatio(label,direction)).ToString();
> //return "0000000000";
> }
> private double getRatio(string label, string direction)
> {
> XmlNode node => doc.SelectSingleNode("RICETEC/LABEL/CONVERSION[@.ID='"+label+"']/"+direction);
> string valStr = node.InnerText;
> return double.Parse(valStr);
> }
> public string convertByUnit(string usUnit, string saUnit, string val,
> string direction, string group, string format)
> {
> ////
> }
> public string convertByUnit2(string before, string after, string val,
> string direction)
> {
> ////
> }
> public string getUnit(string usUnit, string saUnit, string direction,
> string group)
> {
>
> ///
> }
> public string getUnit2(string usUnit, string saUnit, string direction)
> {
> ///
> }
> public string convertTemp(string val, string direction)
> {
> ///
> }
> }
> }
>
> * Here is how I complied it:
> csc /t:library /out:bin/sbu_convertor.dll *.cs
>
> Thanks in advance. JL
reference member in user hierarchy
hi,
I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]
And I can access the member by directly call the memeber name, for example:
select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................
The results will be correct. However, if I use reference to call the member, for example:
select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................
There will be nothing in my results.
Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.
With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.
"[Date].[Calendar].[Years].&[2006].&[2]" might help.
Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.
Regards
Thomas Ivarsson
|||Hi, Thomas
Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.
I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?
|||It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.
Regards
Thomas
|||Hi, Thomas,
Thanks and would you please give me a little bit more details?
Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?
And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.
|||No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.
Regards
Thomas Ivarsson
|||hi, Thomas,
Thanks and I have tried to add a composite key (year+quarter) according to your steps.
But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.
Would you please tell me how to deal with this problem? Thanks a lot.
Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.
You can also simply use the datepart function for year and quarter and return two integer fields.
Regards
Thomas Ivarsson
|||hi, Thomas
I have checked that in the AS2005, my Year and Quarter are in the format: Integar.
And in the data source view, I see that the source columns of the key column are both: System.Int32
However, the error still exists, do you have any idea about this? Thanks.
|||BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?
|||hi, Thomas,
I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).
And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.
I do not know if this will cause any other problems. Anyway, thanks a lot for your help.
|||I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.
Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.
Regards
Thomas Ivarsson
|||Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).
Regards
Thomas Ivarsson
|||Hi, Thomas
Thanks a lot for your response! I have several questions about your answer.
First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?
Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)
|||Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.
Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.
Regards
Thomas Ivarsson
reference member in user hierarchy
hi,
I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]
And I can access the member by directly call the memeber name, for example:
select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................
The results will be correct. However, if I use reference to call the member, for example:
select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................
There will be nothing in my results.
Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.
With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.
"[Date].[Calendar].[Years].&[2006].&[2]" might help.
Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.
Regards
Thomas Ivarsson
|||Hi, Thomas
Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.
I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?
|||It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.
Regards
Thomas
|||Hi, Thomas,
Thanks and would you please give me a little bit more details?
Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?
And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.
|||No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.
Regards
Thomas Ivarsson
|||hi, Thomas,
Thanks and I have tried to add a composite key (year+quarter) according to your steps.
But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.
Would you please tell me how to deal with this problem? Thanks a lot.
Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.
You can also simply use the datepart function for year and quarter and return two integer fields.
Regards
Thomas Ivarsson
|||hi, Thomas
I have checked that in the AS2005, my Year and Quarter are in the format: Integar.
And in the data source view, I see that the source columns of the key column are both: System.Int32
However, the error still exists, do you have any idea about this? Thanks.
|||BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?
|||hi, Thomas,
I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).
And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.
I do not know if this will cause any other problems. Anyway, thanks a lot for your help.
|||I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.
Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.
Regards
Thomas Ivarsson
|||Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).
Regards
Thomas Ivarsson
|||Hi, Thomas
Thanks a lot for your response! I have several questions about your answer.
First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?
Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)
|||Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.
Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.
Regards
Thomas Ivarsson
reference member in user hierarchy
hi,
I have a user hierarchy in my Date Dimension such as [Calendar]->[Years]->[Quarters]
And I can access the member by directly call the memeber name, for example:
select [Date].[Calendar].[Years].[2006].[Q2] on columns, ...................................
The results will be correct. However, if I use reference to call the member, for example:
select [Date].[Calendar].[Years].[2006].&[2] on columns, ...................................
There will be nothing in my results.
Would you please tell me what shall I do to enable the reference call to my hierarchy member? Thanks.
With "select [Date].[Calendar].[Years].[2006].&[2] on columns, you are referencing the keys for the member.
"[Date].[Calendar].[Years].&[2006].&[2]" might help.
Another method is [Date].[Calendar].[Years].Lastchild.Lastchild. With this method you will always find the last quarter of the last year in your time dimension.
Regards
Thomas Ivarsson
|||Hi, Thomas
Thanks a lot for your answer. I have also tried "[Date].[Calendar].[Years].&[2006].&[2]" but no use. I still get nothing from that.
I was forced to use the format: "[Date].[Calendar].[Years].&[2006].&[2]" because this is a fixed format in a third-part software. I guess maybe there is sth wrong about the settings of my user hierarchy?
|||It can be a problem with key for the Quarter member. Are you using integers like 1,2,3,4 for Quarters? In that case you can make a combination by year and quarter for the quarter key. Check also your attribute relation between quarter and year in the dimension editor.
Regards
Thomas
|||Hi, Thomas,
Thanks and would you please give me a little bit more details?
Do you mean that I should use 1,2,3,4 as the quarter key for Quarters?
And do you mean that I should build the attribute relation between quarter and year? I have tried both with/without attribute relation but it did not work. Thx.
|||No. You should use a composite key with both the year and the quarter. A quarter key like 1,2,3,4 is not unique. If you klick on the key-column, for the level, in the properties(dimension editor) you can add year to quarter(in the key) and create a collection. This is in the dataitem collection editor.
Regards
Thomas Ivarsson
|||hi, Thomas,
Thanks and I have tried to add a composite key (year+quarter) according to your steps.
But during processing, there is an error message: The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute.
Would you please tell me how to deal with this problem? Thanks a lot.
Hello. Make a new column, for either year or quarter, and use the TSQL convert och cast-function to change the data type to something common.
You can also simply use the datepart function for year and quarter and return two integer fields.
Regards
Thomas Ivarsson
|||hi, Thomas
I have checked that in the AS2005, my Year and Quarter are in the format: Integar.
And in the data source view, I see that the source columns of the key column are both: System.Int32
However, the error still exists, do you have any idea about this? Thanks.
|||BTW, do I also need to change the Name Column (or the value column) to the integer column I defined in the source?
|||hi, Thomas,
I have finally get the result I want ([Date].[Calendar].&[2005].&[2] is available).
And the step follows: 1. Remove any attribute relationship in dimension date. 2. Build a new integer column (1,2,3,4) as the source of the key column for Quarter. 3. Specify the key column of Quarter to that new integer column.
I do not know if this will cause any other problems. Anyway, thanks a lot for your help.
|||I realized this now. So you have a join on one of the levels in your time hierarchy above the leaf level? In the dimension usage tab for the cube you must use the same combination as in your composite key. You do not have to change the name column.
Check the Adventure Works Demo project that is part of the installation. Have a look at the date.dim there(and the attribute month_name(properties)) and check the relation between the date dim and sales targets measure group in the Adventure Works cube.
Regards
Thomas Ivarsson
|||Nice that it works. Without attribute relations you will not get any aggregations on the time dimension. Another problem is that your calculations can be wrong. What you needed to do was to add 2006(Year) to the quarters(1,2,3,4) so that their collection will point to year and quarter, at the same time. Actually you combine each year with each of their quarter in this way. Quarter(1,2,3,4) will not point to a specific year if you do not do this(ie add the year key to each quarter key).
Regards
Thomas Ivarsson
|||Hi, Thomas
Thanks a lot for your response! I have several questions about your answer.
First, what do you mean by have a join on one of the levles above the leaf levels? And how can I see that?
Second, I have seen the AdventureWorks cube and see the attribute month_name has a combination key column of year + month. But in the dim usage table, I do not know how to modify my own project. I can see a RED line under my date dimension and I think that indicates error. However, if I add an attribute relationship between Quarter and Year, the dim usage table will automaticlly add this relationship to itself. (In other words, I can not find the place to add sth about the combination key in the dim usage table)
|||Hello again Jeremy. If you scroll out to the right, in the dimension usage tab of the cube editor(In the Adventure Works demo cube), you will find a box where the dimension date intersect with the measure group Sales Targets. All the other relations between date and the measure groups in this cube are at the lowest(leaf level) or the box empty(no relation). Click the box(date dim and Sales Targets) There you can see the granularity attribute for the relation, thats the level that is used in the join between the measure group and the dimension.
Check here that you join on the correct column to avoid the error message you talked about before: "The key columns of the Quarter measure group attribute do not match in either number or data types to the key columns of the source attribute." You will only have to this if you have changed the key column for the Quarter member in your time dimension.
Regards
Thomas Ivarsson
Friday, March 9, 2012
Reference axis(2) members in MDX
Hello everybody.
I've got a little problem: I want to reference axis(2) members in a calculated member, so I can use multiple selection.
I've tried:
with member [Measures].[Test] as 'settostr(axis(2))'
select {[Measures].[Test]} on 0,
{[Dim Sales Territory].[Sales Territory Region].members} on 1
from [Adventure Works DW]
where ([Dim Geography].[Country Region Code].&[US],[Dim Geography].[Country Region Code].&[CA])
However, I get an error.
Is there any other way?
Thanks a lot,
Santi
The Axis function cannot be used on the filter axis.The Axis function cannot be used on the filter axis.
http://msdn2.microsoft.com/en-us/library/ms145531.aspx
your shuldn't use Axis function if you whant to use multiple filter
|||
Thanks Vladimir.
Then, assuming I have several members on the filter axis, how can I find out what those members are?
Is it at all possible?
The thing is, I'm trying to enable multiple selection in a dimension, and the selected members go directly to the where clause of the query. Since I have some calculated measures, I can′t reference those members using currentmember.
Thanks again for your answer.
|||What the client do you use? Do you have a full control on it.|||You could use the Existing function:
>>
with member [Measures].[Test] as
settostr(Existing [Geography].[Country].[Country].Members)
select {[Measures].[Test]} on 0
from [Adventure Works]
where {[Geography].[Country].&[United States],
[Geography].[Country].&[Canada]}
-
Test
{[Geography].[Country].&[Canada],[Geography].[Country].&[United States]}
>>
This technique is discussed in detail here:
http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
>>
Writing multiselect friendly MDX calculations
>>
|||
Thanks a lot Deepak!
Didn't know about Exist and Existing at all.
I've also read Mosha's blog entry, and it explains exactly the internals of what I'm trying to acomplish.
Subscribe to:
Posts (Atom)