Controlling the display order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Controlling the display order

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-14 : 01:31:26

Using an old system, my users were accustomed to reports in which the customers were always listed in a certain order (i.e. they were listed based on the 'custcode' field).

Now they have this new "data cube/BI" solution which they very much like except for one thing i.e. the customers are not listed in the same order (which they are used to)...

I would like to use the same approach that was used in the reports of the old system but do not wish to display the customer code field/column.

Can someone please guide how to go about doing this i.e. the dimension has both the 'custcode' and 'custname' as levels and I want the data to be displayed based on the sort order of 'custcode' BUT I do not want the same to be displayed in the front-end....

I hope my post is clear

and will be very thankful for your help.

Many thanks in advance.

Thanks & Regards.

-J

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-19 : 04:21:43

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-21 : 01:32:24

Thanks for the reply. Here's an example.
My underlying view (on which the dimension is based) looks like this (note that the datatype for the CustCode field is char) :

CustCode | CustName
1001 | Paul
2001 | Alan
3001 | Mark
4001 | Frank
5001 | Oliver
6001 | Thomas

I would like my dimension to display the customer based on (sorted on the CustCode field) i.e. exactly in the same order as they are listed above BUT I do not want the CustCode field be displayed.

Though concatenating the CustCode and CustName may be a possible solution

but I do not want the CustCodes to be displayed at all.

Thanks & Regards.

-J

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 03:22:01

>>note that the datatype for the CustCode field is char

Use Proper datatype if you dont use characters

Try this

Select Custname from yourTable
Order by cast(Custcode as int)

Madhivanan

Failing to plan is Planning to fail

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-21 : 04:39:50

Thanks for the suggestion. Will give it a try.

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-21 : 16:56:06

Having both custcode as levels seem a bit superfluous.
Why not create 2 hierarchies Customer.Name and Customer.Code. (I know e.g. ProClarity has good support for hierarchies)

You can order by the key or name in a dimension level,
setting the key to cast(Custcode as int) as Maddy suggested, and order by that would do the trick.

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-24 : 00:48:56

Thanks rockmoose.
Can you share any good URL from where I can learn about dimension hierarchies.

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-24 : 04:35:40

By perusing BOL I just found two short topics:
* Dimension Hierarchies
* Creating Dimensions with Multiple Hierarchies

This document is not bad: [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/analysis.mspx[/url]
Look at "Representing Multiple Hierarchies in a Dimension"

Otherwise the lack of documentation etc is striking!

The key concept in defining multiple hierarchies is the naming of the dimension: DimensionName.HierarchyName (note the dot convention)
If you want the users to be able to drill down on the Customers either by name or by Code;
create two dimensions: (sample way to do it)
Customers.Name
- All Customers ("All Customers")
- A-Z grouping ("A") [LEFT(Customer.PresentationName,1)]
- Customer ("Doe John") [Customer.PresentationName] <Also have Customer.Code as a member property on this level>
Customers.Code
- All Customers ("All Customers")
- Some grouping ("...")
- Customer ("2345 - Doe John") [LTRIM(Customer.Code) + ' - ' + Customer.PresentationName]

In a customer (or other) dimension table I often add a column PresentationName, which could be a calculated column: Customer.LastName + ' ' + Customer.FirstName
When I present the data to the enduser I use that column.

What client tool(s) are you using?
I know that som tools such as ProClarity will group the dimensions with several hierarchies, and the end-user can choose which hierarchy to use.
Other tools might just treat each dimension hierarch as a separate dimension, which tends to make the end-user experience more confusing.

In AS there are many places where tricks can be done to format the data & dimensions:
In the SQL tables themselves with formatting and sorting columns.
In AS when building the dimensions by specifying different sql expressions for the member values & keys & member properties and telling AS to sort by key/value/property.

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-24 : 04:42:53

WOW... that's great and that's a lot of information too :)

Thanks... will try to absorb as much as possible.

Appreciate it.

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-24 : 04:47:03

You are welcome, I just corrected a spellig mistake in the previous post.
At my current job we are not -yet- using AS, but when we do it will be 2005 version.
Any plans for upgrade at your place? I know a lot of things changed between 2000 & 2005.

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-24 : 05:08:55

Thanks. But what is it that you changed??? :)

At our side and taking the best case scenario, my gut feeling would be that we might consider 2005 in about a years time... :(

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-24 : 06:05:11

I did quite a lot of AS development on SQL2000 at my previous job.

As for AS2005,
the new Sql Server Business Intelligence Studio and I still have a way to go before we get along

But I'm still in the experimenting phase.

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-24 : 06:22:57

Lucky you buddy :)

The way I see it, on a scale of 1 - 10, I probably know no more than 2. So I will be happy if during the next 1 year if I can make significant progress; in terms of learning 2000 and that beast called 'MDX' :)

BTW, our front-end is Excel and Microsoft Data Analyzer. I think it's really neat but if I was asked to select the alternative (and if budget was not a concern), I would probably select Pro Clarity.

Thanks & Regards.

-J

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-24 : 16:30:08

Ok, have fun with the stuff!
If I were You I would ask managment to get a couple of ProClarity (or other after short evaluation) licenses for the Power-Users.
And for the developers (You).

It is so much nicer to have a better OLAP client than Excel while testing stuff out and doing more in-depth analysis of the data.

rockmoose

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-25 : 00:58:35

I already did that. Want to make a guess regarding the reply i got? ;)

Thanks & Regards.

-J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources