Circle Chart showing percent values automatically

Hi. I have used Circle-Charts in Access Reports. They were able to show
percent values by default, without having the percent values calculated or
delivered by SQL queries.
I am trying to get RSrv doing this also, but no success. Is it possible? Or
do I have to deliver the percent values to the diagram?
I actually am using RSrv without any SP.
Thanks, MarkusYou have to calculate the percentage value. The default data point label for
pie charts would be the absolute value rather than the percentage.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:389ADD61-DBAD-43C4-8576-8D31D819534C@.microsoft.com...
> Hi. I have used Circle-Charts in Access Reports. They were able to show
> percent values by default, without having the percent values calculated or
> delivered by SQL queries.
> I am trying to get RSrv doing this also, but no success. Is it possible?
> Or
> do I have to deliver the percent values to the diagram?
> I actually am using RSrv without any SP.
> Thanks, Markussqlsql

Churn Rate - two different date dim''s

I am trying to create a calculated measure in my cube (MDX Exprsssion), but first I want to write the query in MDX so I can test it. I am having issues getting this to be "dynamic"

WITH

MEMBER Measures.[Disconnects] AS

(

(

[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],

[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]

),

[Measures].[Subscription Cancels]

)

MEMBER Measures.[Subscriptions] AS

(

(

[New Subscription Date].[Calendar Date].[2007-07-15 00:00:00],

[Transaction Time].[Calendar Date].[2007-07-15 00:00:00]

),

[Measures].[Subscriptions New]

)

SELECT {

Measures.[Subscriptions]

,[Measures].[Disconnects]

} ON COLUMNS

FROM [MyCube]

this query above gives me the correct values for the day I have in there, but I want it to work over all days, and so the end users can slice by other dim's as well. Basically I am trying to get the number of disconnects that occured on the same day as that disconnect subscribed. I have a feeling my underlying cube/dw is too aggregated already or something else, but I can get this pretty close to what I want. Does anyone have any insight? I have been looking at this

http://sqljunkies.com/WebLog/mosha/archive/2007/06/01/count_in_flight_mdx.aspx

because it is similar, with the two different date dim's, but I just want to always get results where the two date dim's are the same day...

Thanks

If we just take the first measure (as both can use the same pattern) using link member should make the date range dynamic. I needed to pick one of the dates as the one that you will be analyzing by, so I assumed [Transaction Time] was probably the one.

eg.

MEMBER Measures.[Disconnects] AS

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

This should work OK when you are looking at specific days for Transaction Time, but if you wanted to look at the sum of all transactions where the cancellation and subscription occurred on the same date for a month, week, quarter etc. , assuming that you had a [Calendar] hierarchy then you would need to do something like the following in order to sum over a set of days.

MEMBER Measures.[Disconnects] AS

SUM(

DESCENDANTS([Transaction Time].[Calendar].CurrentMember,

[Transaction Time].[Calendar].[Calendar Date])

,

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

)

)

|||

wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?

When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run

I think the solution above is totally the right track though, maybe I just need to tweak something more...

|||

dart_board wrote:

wow yeah, ok I thought maybe LinkMember was the route to go.. thing is, I dont know about the Calendar hierarchy, I dont think mine is set up right, what woudl you suggest as a hierarchy?

When i implement the query using link member, and then say, look at one month, and if i put the date ON ROWS, every day in the month has the same values, and the query takes forever to run

In regards to the hierarchy, I used Calendar as an example as it is something that most people can understand. When I set up a date hierarchy, personally, I usually set up one (or more) hierarchies and then I usually hide the "raw" attributes that are present in the hierarchy. You don't have to do this, you can leave the attributes separate, but the important thing is to get your attribute relationships set up correctly, which it sounds like might be an issue in your case as this would lead to apparent duplicating values.

It's important the each attribute either has a direct or indirect (not both) relationship to the key attribute. And indirect relationship is where one attribute is related to another to form a sort of chain back to the key attribute. One way of thinking of relationships is that an attribute has a relationship to it's parent. So a TimeID has a relationship to a Date and a Date has a relationship to a month and so on. Notice that Year is at the top level of granularity so it is not related to any other attributes.

eg.

TimeID

- Date

Date

- Month

Month

- Quarter

Quarter

- Year

Year

If you don't create explicit hierarchies in your date dimension, or you want to leave the "raw" attributes visible, you should be able to replace the descendants function with the existing keyword which should reduce the set of Date members down to just those that are related to what ever Time attribute is currently selected. (it figures this out using attribute relationships, so these need to be right). Notice that I have used a naming convention of <dimension>.<Attribute>.<level>.members, this is important as if you just used <dimension>.<attribute>.members, it would include the default "All" member and would return all dates which is not what you want.

MEMBER Measures.[Disconnects] AS

SUM(

EXISTING [Transaction Time].[Date].[Date].Members)

,

(

LinkMember([Transaction Time].[Calendar Date].CurrentMember,

[New Subscription Date].[Calendar Date]

),

[Measures].[Subscription Cancels]

)

)

)

Hope this helps

|||yeah that totally makes sense. I brought up a while ago that our hierarchies weren't set up correctly, but no one would beleive me - The EXISTING seems to work well. My only issue now is that when I try to use BOTH caclulated measures together, one is summed up over all and one is correct, depending on which date I slice on (transaction or new subscription) Its like I can get either one or the other to work, but not both together.

I will keep digging into that, if you have any quick insight that would be awesome. You really know your stuff

checksums and data types

I'm creating a checksum column in a table that is to be calculated over several columns within the table.

One of the columns to be included in the checksum formula has a data type ntext.

But on trying to complete this new table design (or similarly using alter table in QA) - both return an error stating that the data type is invalid for the checksum function.

This happens for both ntext and text data types.

Can anyone tell me if there is a way round this without having to change the data type - or the valid data types that can be used for the checksum funciton?

Also reasons why would be helpful!

Thanksplease ignore - http://www.dbforums.com/t989557.html shows this not to be possible...

nevermind|||you might want to try something like so:

SELECT checksum(col1,col2,CAST(CAST(col3 as varchar(1)) as int))
FROM testTable

I am not sure if this totally works. Text and Ntext are meant to hold large amounts of text data like notes field in a customer service application. There is no implicit data conversion between int and ntext\text in sql server because that is just one of the rules and it would'nt make much since do so. To tell the truth it sounds like your problem is a design issue. However you can explicitly convert data types as shown above but please keep in mind if you try to cast character data in col3 above to an int, you will recieve an error. So you might have to add an IsNumeric in there as well.|||CHECKSUM works with non-numeric data, so there is no need to recast as INT in your formula.

Though I'm still not sure that is going to give him what he needs...|||Why are you doing this? To enforce data integrity upon INSERT/UPDATE? Or to support some business rule? Either way you're already using a database, so the answer should be in design, not checksum-based tricks.|||hey trotsky!!
calm down.|||I'd love to hear the reason behind this... I can't for the life of me figure out why you might want/need to do it. I'm also with rdjabarov, and think that this smells very strongly of a high GQ (geek quotient) workaround for a case of poor relational design!

-PatP|||that's twice that you have agreed with RDjabarov.
hmmmmmmm is the feud over?

:D|||Feud? Did I miss a meeting?

-PatP|||must have been the coma. when i first go here you guys would go at it like turtles and bunnies.