Monday, March 26, 2012
Ragged Dimension
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim].[Attrib].CURRENTMEMBER.
MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim].[Attrib].
CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim].
[Attrib].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim].
[Attrib].[all].children ON ROWS FROM CUBE
The Value for this parameter in reports is ParameterValue and Label is
caption.
Problem is that this dimension is ragged and when th attrib name is null, the
report shows the uniquename. How can I make the report dropdown show only the
non null attributes? I tried using an IIF to select null when the
currentmember.name is null, else uniquename - but that does not work - the
currentmembwe.name never returns null. I also tried isempty and that did not
work either.
thank you!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1I have seen people in the group say to try isnothing ... rather than
isnull or isempty
"Angie via SQLMonster.com" wrote:
> I am creating a picklist in reporting services as below
> WITH MEMBER [Measures].[ParameterCaption] AS '[Dim].[Attrib].CURRENTMEMBER.
> MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim].[Attrib].
> CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim].
> [Attrib].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
> [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim].
> [Attrib].[all].children ON ROWS FROM CUBE
> The Value for this parameter in reports is ParameterValue and Label is
> caption.
> Problem is that this dimension is ragged and when th attrib name is null, the
> report shows the uniquename. How can I make the report dropdown show only the
> non null attributes? I tried using an IIF to select null when the
> currentmember.name is null, else uniquename - but that does not work - the
> currentmembwe.name never returns null. I also tried isempty and that did not
> work either.
> thank you!
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>
Ragged Dimension
displayed properly in Excel. Whereever empty level is there, excel displays
same value as parent. Any idea why it is happening ?Hi,
this is the expected behavior, if your dimension based on a flat dimension
table. To avoid this try a parent child dimension table.
hth
NGE
Am Thu, 11 Dec 2003 19:58:06 +0530 hat Zubin Dhamodiwala
<zubin@.godrej.com> geschrieben:
quote:
> I have a created a ragged dimension on OLAP manage but it is not being
> displayed properly in Excel. Whereever empty level is there, excel
> displays
> same value as parent. Any idea why it is happening ?
>
Erstellt mit M2, Operas revolutionrem E-Mail-Modul:
http://www.opera.com/m2/|||Disadvantage with Parent Child is that you cannot levels in it. It works
with only two column in a table, a key and parent id. But my dimension have
multiple levels and they are unbalanced.
"Nice Guy Eddie" <a_hard_days_night@.web.de> wrote in message
news:oprz2a0xqw1ak0mc@.news.misag.com...
quote:|||Hi,
> Hi,
> this is the expected behavior, if your dimension based on a flat dimension
> table. To avoid this try a parent child dimension table.
> hth
> NGE
> Am Thu, 11 Dec 2003 19:58:06 +0530 hat Zubin Dhamodiwala
> <zubin@.godrej.com> geschrieben:
>
>
> --
> Erstellt mit M2, Operas revolutionrem E-Mail-Modul:
> http://www.opera.com/m2/
I'm not sure, if I understand...In a parent/ child table you can have
levels and you can have unbalanced trees. Why not?
Example:
Dimension table
col_child (pk) col_parent
child_1 parent_1
parent_1 parent_11
child_2 parent_11
Result:
parent_11
parent_1
child_1
child_2
You have tree levels and a unbalanced hierarchy...
Greetings, NGE
Am Fri, 12 Dec 2003 16:29:05 +0530 hat Zubin Dhamodiwala
<zubin@.godrej.com> geschrieben:
quote:
> Disadvantage with Parent Child is that you cannot levels in it. It works
> with only two column in a table, a key and parent id. But my dimension
> have
> multiple levels and they are unbalanced.
>
> "Nice Guy Eddie" <a_hard_days_night@.web.de> wrote in message
> news:oprz2a0xqw1ak0mc@.news.misag.com...
>
Erstellt mit M2, Operas revolutionrem E-Mail-Modul:
http://www.opera.com/m2/
Friday, March 23, 2012
R
Here's how the error appears in the different clients -
SQL BI Studio:
TITLE: Microsoft Visual Studio
A list of actions for the selected items cannot be obtained because of the following error.
XML parsing failed at line 12, column 62: Illegal xml character.
.
This error may have occurred because the definition for at least one action is not valid. Verify the definition for each action using the Actions view.
ADDITIONAL INFORMATION:
XML parsing failed at line 12, column 62: Illegal xml character.
. (Microsoft SQL Server 2005 Analysis Services)
Reporting Service's VS query builder complains:
Error:
' ', hexadecimal value 0x08, is an invalid character. Line 1, position 94333. (System.Xml)
In SQL Management Studio:
When expanding the dimension's children, a message yields "Error occured
retrieving child nodes, The server sent an unrecognizable response"
3 free bugs for any SDETs out there :-)
Are there any known work arounds? What's the best way to filter out these characters (and which characters to we need to filter?)
Probably your dimension table contains invalid XML character as part of member name or key. You can go to the attribute which causes this problem and change the bindings on the NameColumn or KeyColumn (depending which one caused the problem) for InvalidXmlCharacters from the default Preserve to Remove or Replace. This will slow down processing of the attribute a little bit, but it should solve the problem.
HTH,
Mosha (http://www.mosha.com/msolap)
Wednesday, March 7, 2012
quick clarification on dimension structure
I am new to the whole idea of dimensions. I have an hour dimension that tells the hour of the day (0-23) a lab test was run. I have a dimension table that basically just had columns HourKey and Hour, where HourKey=Hour. I just added a third column, shift which tells what shift each hour is in. The rational behind the identical columns was just so i had a dimension table. I don't know if that is correct but now i want to use this new shift column in my cube. Do I get rid of my Hour column? How would I setup my heirarchy with the shift attribute? Thank you very much.
PS Its hard to explain why i have a separate hour dimension, i also have a date dimension, trust me on that one
PPS Does anyone have any tips for changing tables and updating them in the DSV and cube without having to readd them and remake dimensions?
You only need a single HourKey column. For any attribute, the name binding is optional, and if unspecified, will reuse the key column. Make Shift another attribute which is related to the Hour attribute. You have to rebuild dimension each time you change its metadata, adding a new attribute is changing metadata.Monday, February 20, 2012
Questions on translation and naming attribute
Hello I have a few questions regarding the translations.
1. Is it possible to create this neat auto-generated date dimension in any other languages than english?
2. When I use the Currency format string on my autogenerated measure groups they all turn out as $, but when I use it on a calculated member it chooses the correct currency. How can I change this for the auto generated measures?
3. After creating the member, how do you define the deafult member show from the dimension. Say for example that I have DimEmpl(Code, Name). I would like the Name attribute to be the attribute showing by default. I know this is possible while creating the dimension, but what if I want to change it later?
Thanks
// Richard
Hi Richard,
1. Yes, at the bottom of the wizard you can pick the language for the dimension.
2. I don't understand the question.
3. If I understand you correctly, You want show the name instead of the value for the code. To do this you have to choose the NameColumn property for the code hierarchy to be the Name field in the dimension table. Its kinda hard to find, be sure to click the plus sign to specify the type
Hope this helps
David Botzenhart.
|||When I choose the Currentcy format string for a measure SSAS makes the results like so: "$1,xxx,xxx". Since I'm creating a swedish solution
I need the answers to be "1,xxx,xxx Kr" instead. Where do I set this? If I make a calculated member it works, but not on the auto-generated members.
As for the date-dimension, there's no swedish language in the drop-down-list. Can it be added?
Thanks
// Richard
|||Here is a blog which explains exactly how to do it: http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx
HTH,
Mosha (http://www.mosha.com/msolap)
|||
2. In BIDS choose the "Cube Structure" tab. Place yourself on the cube and choose properties. In here, you can select at language for the cube. Set it to Swedish and you should be good to go.
3. Is this an issue with your client application? Do you want the name attribute to be used by default when you select the dimension? In that case, what app. are you using?
|||Thank you both, your posts helped me understand how it works. But unfortunatley, the documentation inclined by your post where I can find the language codes doesn't seem to contain them? Atleast not my Visual Studio documentation, which are you referring to?
Michael, I have the language set to Swedish for the cube. That's part of the problem, it doesn't work.
Mosha, if I find the language code, can I then set it as a calculation for the entire cube in the calculations tab? Something like having the line
Language(CURRENT CUBE) = 'some code' ?
Thanks
|||You can get the list of LCID codes here: http://msdn2.microsoft.com/en-us/library/ms903928.aspx (although they are listed in HEX, you will need to translate to decimal)
Once you find your code, simply add
LANGUAGE(this) = code;
at the end of your MDX Script. It will apply to the whole cube.
|||Thanks, but it doesn't work. I still get $'s in my Currency format :(, any ideas?
|||Well - what's the locale of the user connecting to the cube?|||All regional settings in windows are Swedish|||What is the locale ID that you use in this expression ?|||Hello Mosha. Can we get a list that is not listed in HEX but is translated?
I am from Sweden so I can confirm that SSAS2005 currency format will not change depending on your local regional settings for the O/S. In Excel, or other Office applications and SQL Server 2005, it will but not in SSAS2005.
Your recommendation, on your BLOG, to script this will work, but I have not found the link to the codes until you published it.
A better and translated list of these codes will be much appreciated.
Regards
Thomas Ivarsson
|||Sweden's locale ID is 1053. The following quick test confirms that Language=1053 does shows currency as Krones, not as dollars
WITH MEMBER Measures.x as 100, format_string='currency', language=1053
SELECT x ON 0 FROM [Adventure Works]
HTH,
Mosha.
|||I ran this query against my cube now exactly like this:
WITH MEMBER Measures.x as 100, format_string='currency', language=1053
SELECT x ON 0 FROM [BIDM]
and the result was: $100.00
which confirms that the language parameter has no effect on my system then?
|||Hello again Mosha. Running the same query as you I first get the result $100 instead of SEK.
Changing the currency format, for regional settings, in the control panel, from USD to SEK, do help. New result of your query is 100 SEK.
The problem seems to be that Swedish Currency settings (SEK) is ignored if I have USD in my regional settings.
If I change your language setting, in the query, to 1052 I will get 100 Lek with USD in the regional settings for the O/S.
The only way your example will work with SEK is if we change the O/S regional settings for currency to SEK.
This can be a problem if we have several currencies in a cube.
If regional O/S currency settings is not SEK, SEK will not show up.
This must be a bug?
I am running SP2 CTP2 (November).
Kind regards
Thomas Ivarsson
Questions on translation and naming attribute
Hello I have a few questions regarding the translations.
1. Is it possible to create this neat auto-generated date dimension in any other languages than english?
2. When I use the Currency format string on my autogenerated measure groups they all turn out as $, but when I use it on a calculated member it chooses the correct currency. How can I change this for the auto generated measures?
3. After creating the member, how do you define the deafult member show from the dimension. Say for example that I have DimEmpl(Code, Name). I would like the Name attribute to be the attribute showing by default. I know this is possible while creating the dimension, but what if I want to change it later?
Thanks
// Richard
Hi Richard,
1. Yes, at the bottom of the wizard you can pick the language for the dimension.
2. I don't understand the question.
3. If I understand you correctly, You want show the name instead of the value for the code. To do this you have to choose the NameColumn property for the code hierarchy to be the Name field in the dimension table. Its kinda hard to find, be sure to click the plus sign to specify the type
Hope this helps
David Botzenhart.
|||When I choose the Currentcy format string for a measure SSAS makes the results like so: "$1,xxx,xxx". Since I'm creating a swedish solution
I need the answers to be "1,xxx,xxx Kr" instead. Where do I set this? If I make a calculated member it works, but not on the auto-generated members.
As for the date-dimension, there's no swedish language in the drop-down-list. Can it be added?
Thanks
// Richard
|||Here is a blog which explains exactly how to do it: http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx
HTH,
Mosha (http://www.mosha.com/msolap)
|||
2. In BIDS choose the "Cube Structure" tab. Place yourself on the cube and choose properties. In here, you can select at language for the cube. Set it to Swedish and you should be good to go.
3. Is this an issue with your client application? Do you want the name attribute to be used by default when you select the dimension? In that case, what app. are you using?
|||Thank you both, your posts helped me understand how it works. But unfortunatley, the documentation inclined by your post where I can find the language codes doesn't seem to contain them? Atleast not my Visual Studio documentation, which are you referring to?
Michael, I have the language set to Swedish for the cube. That's part of the problem, it doesn't work.
Mosha, if I find the language code, can I then set it as a calculation for the entire cube in the calculations tab? Something like having the line
Language(CURRENT CUBE) = 'some code' ?
Thanks
|||You can get the list of LCID codes here: http://msdn2.microsoft.com/en-us/library/ms903928.aspx (although they are listed in HEX, you will need to translate to decimal)
Once you find your code, simply add
LANGUAGE(this) = code;
at the end of your MDX Script. It will apply to the whole cube.
|||Thanks, but it doesn't work. I still get $'s in my Currency format :(, any ideas?
|||Well - what's the locale of the user connecting to the cube?|||All regional settings in windows are Swedish|||What is the locale ID that you use in this expression ?|||Hello Mosha. Can we get a list that is not listed in HEX but is translated?
I am from Sweden so I can confirm that SSAS2005 currency format will not change depending on your local regional settings for the O/S. In Excel, or other Office applications and SQL Server 2005, it will but not in SSAS2005.
Your recommendation, on your BLOG, to script this will work, but I have not found the link to the codes until you published it.
A better and translated list of these codes will be much appreciated.
Regards
Thomas Ivarsson
|||Sweden's locale ID is 1053. The following quick test confirms that Language=1053 does shows currency as Krones, not as dollars
WITH MEMBER Measures.x as 100, format_string='currency', language=1053
SELECT x ON 0 FROM [Adventure Works]
HTH,
Mosha.
|||I ran this query against my cube now exactly like this:
WITH MEMBER Measures.x as 100, format_string='currency', language=1053
SELECT x ON 0 FROM [BIDM]
and the result was: $100.00
which confirms that the language parameter has no effect on my system then?
|||Hello again Mosha. Running the same query as you I first get the result $100 instead of SEK.
Changing the currency format, for regional settings, in the control panel, from USD to SEK, do help. New result of your query is 100 SEK.
The problem seems to be that Swedish Currency settings (SEK) is ignored if I have USD in my regional settings.
If I change your language setting, in the query, to 1052 I will get 100 Lek with USD in the regional settings for the O/S.
The only way your example will work with SEK is if we change the O/S regional settings for currency to SEK.
This can be a problem if we have several currencies in a cube.
If regional O/S currency settings is not SEK, SEK will not show up.
This must be a bug?
I am running SP2 CTP2 (November).
Kind regards
Thomas Ivarsson
Questions on translation and naming attribute
Hello I have a few questions regarding the translations.
1. Is it possible to create this neat auto-generated date dimension in any other languages than english?
2. When I use the Currency format string on my autogenerated measure groups they all turn out as $, but when I use it on a calculated member it chooses the correct currency. How can I change this for the auto generated measures?
3. After creating the member, how do you define the deafult member show from the dimension. Say for example that I have DimEmpl(Code, Name). I would like the Name attribute to be the attribute showing by default. I know this is possible while creating the dimension, but what if I want to change it later?
Thanks
// Richard
Hi Richard,
1. Yes, at the bottom of the wizard you can pick the language for the dimension.
2. I don't understand the question.
3. If I understand you correctly, You want show the name instead of the value for the code. To do this you have to choose the NameColumn property for the code hierarchy to be the Name field in the dimension table. Its kinda hard to find, be sure to click the plus sign to specify the type
Hope this helps
David Botzenhart.
|||When I choose the Currentcy format string for a measure SSAS makes the results like so: "$1,xxx,xxx". Since I'm creating a swedish solution
I need the answers to be "1,xxx,xxx Kr" instead. Where do I set this? If I make a calculated member it works, but not on the auto-generated members.
As for the date-dimension, there's no swedish language in the drop-down-list. Can it be added?
Thanks
// Richard
|||Here is a blog which explains exactly how to do it: http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx
HTH,
Mosha (http://www.mosha.com/msolap)
|||
2. In BIDS choose the "Cube Structure" tab. Place yourself on the cube and choose properties. In here, you can select at language for the cube. Set it to Swedish and you should be good to go.
3. Is this an issue with your client application? Do you want the name attribute to be used by default when you select the dimension? In that case, what app. are you using?
|||Thank you both, your posts helped me understand how it works. But unfortunatley, the documentation inclined by your post where I can find the language codes doesn't seem to contain them? Atleast not my Visual Studio documentation, which are you referring to?
Michael, I have the language set to Swedish for the cube. That's part of the problem, it doesn't work.
Mosha, if I find the language code, can I then set it as a calculation for the entire cube in the calculations tab? Something like having the line
Language(CURRENT CUBE) = 'some code' ?
Thanks
|||You can get the list of LCID codes here: http://msdn2.microsoft.com/en-us/library/ms903928.aspx (although they are listed in HEX, you will need to translate to decimal)
Once you find your code, simply add
LANGUAGE(this) = code;
at the end of your MDX Script. It will apply to the whole cube.
|||Thanks, but it doesn't work. I still get $'s in my Currency format :(, any ideas?
|||Well - what's the locale of the user connecting to the cube?|||All regional settings in windows are Swedish|||What is the locale ID that you use in this expression ?|||Hello Mosha. Can we get a list that is not listed in HEX but is translated?
I am from Sweden so I can confirm that SSAS2005 currency format will not change depending on your local regional settings for the O/S. In Excel, or other Office applications and SQL Server 2005, it will but not in SSAS2005.
Your recommendation, on your BLOG, to script this will work, but I have not found the link to the codes until you published it.
A better and translated list of these codes will be much appreciated.
Regards
Thomas Ivarsson
|||Sweden's locale ID is 1053. The following quick test confirms that Language=1053 does shows currency as Krones, not as dollars
WITH MEMBER Measures.x as 100, format_string='currency', language=1053
SELECT x ON 0 FROM [Adventure Works]
HTH,
Mosha.
|||I ran this query against my cube now exactly like this:
WITH MEMBER Measures.x as 100, format_string='currency', language=1053
SELECT x ON 0 FROM [BIDM]
and the result was: $100.00
which confirms that the language parameter has no effect on my system then?
|||Hello again Mosha. Running the same query as you I first get the result $100 instead of SEK.
Changing the currency format, for regional settings, in the control panel, from USD to SEK, do help. New result of your query is 100 SEK.
The problem seems to be that Swedish Currency settings (SEK) is ignored if I have USD in my regional settings.
If I change your language setting, in the query, to 1052 I will get 100 Lek with USD in the regional settings for the O/S.
The only way your example will work with SEK is if we change the O/S regional settings for currency to SEK.
This can be a problem if we have several currencies in a cube.
If regional O/S currency settings is not SEK, SEK will not show up.
This must be a bug?
I am running SP2 CTP2 (November).
Kind regards
Thomas Ivarsson