Dynamic Dimension Buckets

Hi,

I have a measure [count of bookings] and dimensions for [trucking company], [calendar], [booking type]. I need to create a dimension for buckets based on the count of bookings.

The buckets I need to create are ([> 500],[201 - 500],[101 - 200],[51 - 100],[21 - 50],[11 - 20],[1 - 10]) based on the count of bookings. From this dimension I am hoping to create a measure that will show the count of trucking companies in each group (using the calendar dimension), and I am hoping to use the existing count of bookings measure to see how many bookings fall into each category by trucking company.

Eg. For the [> 500] member there may be 1 trucking company, with 857 bookings, 666 bookings that are of a specific booking type in the a specific month. I also will be creating % of measures for these.

I've been able to create a member but I can't get it to show anything but #value. I've used this expression:

filter([trucking company],[count of bookings] > 500)

Any help is really appreciated.

Take a look a creating new attribute in your bookings dimension and then use combination of DiscretizationMetod and DiscretizationBucketCount property to tell server to create groups of members.

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

|||

What do I create the attribute from? Because I need the grouping based on the count of bookings (which is the count of rows in my fact table) I can't just create buckets based on a number (like age in the examples I've seen). And I can't see anywhere within the dimension attribute properties that I can specifiy that I want to use the count of a particular column.

And because I want it to return different results for the count of trucking companies etc when sliced by this new attribute and the time dimension I can't create a view to base the attribute on (at least I don't see how I would do that).

Dynamic Date/Calendar

Hey,
I have a financial query that has to retrieve sales totals for each day of
the month for the current year and prior year. Some days though (current an
d
prior) don't have any sales on those days so I still have to return zero. I
have this working just fine...but to do it I created a month_day_year table
that has all the month/day/year columns in it. I join to this and thus am
always assured to have rows/columns returned regardless if there are sales
for those days. The problem is the maintenance of this month_day table. If I
forget to add new months...things go bad. Surely there has to be a better wa
y
to do this...like some way to dynamically generate the month/day/year idea a
t
query run time...and not use this table to join to to ensure I get something
whether data is there or not.
Thanks in advance,
Paul
--
---
Jackson-Reed, Inc. www.jacksonreed.comActually, a calendar table is a great way to handle financial data like
this. As you know fiscal periods don't always line up to calendar
periods; a calendar table let yous manage this and other problems.
Managing this table shouldn't be a big deal; 10 years of days is only
slightly more than 3600 rows; 20 years is still less than 10,000 rows
of data, which should be nothing to manage. I would just generate the
calendar entries for 10 to 20 years, and leave it alone.
Stu|||> for those days. The problem is the maintenance of this month_day table. If
> I
> forget to add new months...things go bad.
Why do you need to add months and days in an ongoing fashion? Just put the
next 30 years in the table, and you might have to think about it again once
before you retire. 10,000 rows is less stressful than a walk in the park
for SQL Server.
http://www.aspfaq.com/2519

Dynamic Column amount query

I want to write a query that will give me a dynamic amount of columns back.
What I want to do, I want to create a calendar application, in which for
each employee, I want to show if he is in the office or not.

this should look like:
ID, Name, 1,2,3,4,5,6,7,8
88,Leo,0,0,1,1,1,0,0,1

The amount of columns is dynamic, and is a period of time, with a column for
each day.
Any suggestions what the best approach to this could be?

Thanks

LeoIn SQL, a table has a known number of columns. What you are trying to
do is destroy First Normal Form (1NF). In a tiered architecture, such
fomatting is done in the front end and not the database.|||Leo Muller (leo_muller_israel@.hotmaill.com) writes:
> I want to write a query that will give me a dynamic amount of columns
> back. What I want to do, I want to create a calendar application, in
> which for each employee, I want to show if he is in the office or not.
> this should look like:
> ID, Name, 1,2,3,4,5,6,7,8
> 88,Leo,0,0,1,1,1,0,0,1
> The amount of columns is dynamic, and is a period of time, with a column
> for each day.

As Joe Celko says, relational databases are not meant for this kind of
thing. A query returns a table, and a table has a fixed number of columns.

This sort of thing may be best do client-side.

Nevertheless this is possible in SQL Server, but you will have to resort
to dynamic SQL, and it's all quite complicated.

An alternative is to use the third-party tool, RAC, see
http://www.rac4sql.net.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You may find this useful:

http://www.windowsitpro.com/Article...3140/43140.html