February 11, 2015 at 4:37 am
Hi,
I have come across a niggle that I cant seem to resolve.
I have a number field and my report is ordered against it. However, When I looked I get the following order 1, 13,144,167,2,3,36,377
And so on.
I have done a bit of googling and a few people have also had this issue too. I have Set order by to Key in Dimension Properties and tried again and no change (within Analysis Services)
I started to suspect it may be because of the NULL values so I changed all these to 0 in Analysis Services data source. And reset the sort order to the name column. Tried again and nothing.
Im really struggling to come up with a solution to this one now. If any one had any other ideas I would be really grateful.
Debbie
February 11, 2015 at 5:30 am
Debbie Edwards (2/11/2015)
Hi,I have come across a niggle that I cant seem to resolve.
I have a number field and my report is ordered against it. However, When I looked I get the following order 1, 13,144,167,2,3,36,377
And so on.
I have done a bit of googling and a few people have also had this issue too. I have Set order by to Key in Dimension Properties and tried again and no change (within Analysis Services)
I started to suspect it may be because of the NULL values so I changed all these to 0 in Analysis Services data source. And reset the sort order to the name column. Tried again and nothing.
Im really struggling to come up with a solution to this one now. If any one had any other ideas I would be really grateful.
Debbie
Check the datatype of the column you are ordering on:
SELECT [Integer] = n FROM (VALUES (1),(13),(144),(167),(2),(3),(36),(377)) d (n) ORDER BY n
SELECT [IntegerCastedToCharacter] = n FROM (VALUES (1),(13),(144),(167),(2),(3),(36),(377)) d (n) ORDER BY CAST(n AS VARCHAR(3))
SELECT [Character] = n FROM (VALUES ('1'),('13'),('144'),('167'),('2'),('3'),('36'),('377')) d (n) ORDER BY n
SELECT [CharacterCastedToInteger] = n FROM (VALUES ('1'),('13'),('144'),('167'),('2'),('3'),('36'),('377')) d (n) ORDER BY CAST(n AS INT)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 11, 2015 at 5:32 am
Debbie Edwards (2/11/2015)
Hi,I have come across a niggle that I cant seem to resolve.
I have a number field and my report is ordered against it. However, When I looked I get the following order 1, 13,144,167,2,3,36,377
And so on.
I have done a bit of googling and a few people have also had this issue too. I have Set order by to Key in Dimension Properties and tried again and no change (within Analysis Services)
I started to suspect it may be because of the NULL values so I changed all these to 0 in Analysis Services data source. And reset the sort order to the name column. Tried again and nothing.
Im really struggling to come up with a solution to this one now. If any one had any other ideas I would be really grateful.
Debbie
Let me first say that I don't know SSAS. However, this looks (in SQL anyway) like you're sorting a string field that contains numeric data. This is why 167 would come before 2. If you're working with numeric data and you can sort it as a numeric data, you should be fine. If you have non-numeric data in there, they you have to sort it as string data.
February 11, 2015 at 6:08 am
The data source is definitely int.
One thing I can think of. I have a view in Anaylsis services data sources. I could specifically convertit again to int. that may help
Debbie
February 11, 2015 at 6:10 am
The source SQL data is int. Im thinking, go into the SSAS viewand do a convert to int over the value.
Its clutching at straws a bit but Ill have a go
Thanks
Debbie
February 11, 2015 at 7:19 am
Another failed attempt. 🙁
Over the Int source column in Analysis Services I applied a convert(int, in the analysis services data source. Updated everything in Analysis Services and Refreshed the Reports and I still have the same issue.
Is there anything else I can do to get this resolved? It seems such a silly thing to get stuck on.
Debbie
February 11, 2015 at 7:40 am
Aghhhhhh How can a sort order be so hard.
I can confirm that if you look at the data item in Analysis Services it says int against it.
Name Column Service_SEN.Days_Between_Workflow_Start_and_Issued (Integer)
I then noticed underneath Name column the Data Type. It was set to WChar. I thought, is that it? So I reset this integer. As soon as I did this I got an error ' The integer data type is not allowed for the Name Column Property. Wchar should be used.
So Ive had to set back to Service_SEN.Days_Between_Workflow_Start_and_Issued (WChar)
So then I though, OK the Name column isnt the right one then because of this so I looked at Order by and Its ordered by Name.
So what should my order by be set to? It cant be the key column because this is a composite key and I have already tried that. I tried Attribute key and I get an error. The Ordering Attribure is not set. However when I try to set the ordering attribute I only get the top level of my key. Person ID. I tried to set this manually but it wouldnt let me.
I think Im now close to just telling the users that this I cant sort this issue out for them 🙁
Completely at a loss. Again any suggestions would be really helpful
Debbie
February 11, 2015 at 10:02 am
Well if the report is using the name (which is WCHAR) and ordering by it (I take it you are ordering in SSRS via the rendering) then it will be wrong. For the column in your MDX you need to specify "Key" to be used, not Name. E.g:
[Your Dimension].[Your Hierarchy/Attribute].Currentmember.Properties("Key")
Unless it is a composite key then this will work. For composite key it would be:
[Your Dimension].[Your Hierarchy/Attribute].Currentmember.Properties("KeyN")
Where N is the ordinal number of the key element you want to use, starting at 0.
February 13, 2015 at 8:19 am
PB_BI (2/11/2015)
Well if the report is using the name (which is WCHAR) and ordering by it (I take it you are ordering in SSRS via the rendering) then it will be wrong. For the column in your MDX you need to specify "Key" to be used, not Name. E.g:
[Your Dimension].[Your Hierarchy/Attribute].Currentmember.Properties("Key")Unless it is a composite key then this will work. For composite key it would be:
[Your Dimension].[Your Hierarchy/Attribute].Currentmember.Properties("KeyN")Where N is the ordinal number of the key element you want to use, starting at 0.
Hi,
Im really not sure what you mean on this one.
yep, Its a composite key on:
Pupil ID
Stage ID
Days Between Start and End
Im in Analysis Services in properties and I change it to order by Key. Underneath this there is an order by Attribute and the only item available is Pupil ID.
There appears to be no where I can add your information above too? Unless you mean you arent in properties for that specific data item?
Debbie
February 13, 2015 at 8:58 am
No that's not what I mean. You are talking about an SSRS report right, you mentioned a report and I have just assumed? If so you will have an MDX query to get data from the cube. If by default your sorting is fine in the cube then you need to set your tablix properties to sort by that or (as I previously mentioned) change the MDX query to specify the key (if the issue is an int being read as a string).
So let's say as part of your report you have Product_Line and you want to order by it. In the tablix properties you set the order expression to be "=Fields!Product_Line.Key".
If it's not SSRS a simple way to restore a proper order to a "number as string" would be to set something up in your abstraction layer or DSV. So instead of your SQL just being:
SELECT
Whatever
FROM
Wherever
Then your SQL would be:
SELECT
CASE WHEN Whatever < 10 THEN '0'+Whatever ELSE Whatever END AS Whatever
FROM
Wherever
...with the appreopriate CASTing. This will remedy your problem.
If you always want this attribute to be sorted in this manner and it's not just the scope of this report then you will need to manipulate the properties accordingly. I can't walk you through this as I don't know your data but an example of it in action can be found here: https://msdn.microsoft.com/en-us/library/ms166763.aspx
Have a look at the section titled "Defining Attribute Relationships and Sort Order in the Customer Dimension".
February 16, 2015 at 4:38 am
Im talking about an SSRS report attatched to An Analysis Services Cube.
I thought I could sort the issue out in Analysis Services. the MDX query is built when you drag and drop all your data and add critera in reporting Services and Im trying to make sure my user doesnt have to understand or use MDX queries.
Ill certainly have a read through of that document though thank you.
Im afraid Ive trashed the AS report and built it up using the fact table in the RDBMS which works absolutely fine.
I think its something to do with the attribute in Analysis Services being incorrect.
Thanks again
Debbie
February 16, 2015 at 4:55 am
I think I can definitely use that documentation to sort my problem out.
Great! Thank you
Debbie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply