Adding 'Other' segment/slice in a pie chart

Hi guys,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality in the report layout level with out writing a complex MDX query? If this is not possible, can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be easily implemented using a third pary software such as 'Dundas chart for Reporting Service'. However, my client don't want to buy this third party software.

Please let me know if anybody has came accross with similar scenario?

Sincerely,

--Amde

Please help!!!!!!!!!!

|||

Do you have to use MDX or can you use T-SQL? I once used a derived table to get this type of data. Not pretty and not the quickest thing if you have huge datasets, but it works.

SELECT grouper, sum(total_charge), sum(pct)
FROM (

select
dx1_num "Diag", -- Item to list in pie slice
sum(charge_amount) "total_charge", --
(sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) "pct", -- Percent of Everything
case
when (sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) < .05 then 'Misc' -- Interim Group
else CAST(dx1_num AS VARCHAR(15))
end "grouper" -- what kind of name do you want for free?
from ar_billtrans_charge
group by dx1_num

) Y GROUP BY grouper;

I am sure some T-SQL gods out there can do much better.

R

|||

Hi,

Appreciate your response. Basically, I am using MDX query. Do you have any idea how to do the same thing using MDX?

Thank you for your cooperation.

--Amde

|||

Please read my response with a sample report in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

Adding 'Other' segment/slice in a pie chart

Hi guys,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality in the report layout level with out writing a complex MDX query? If this is not possible, can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be easily implemented using a third pary software such as 'Dundas chart for Reporting Service'. However, my client don't want to buy this third party software.

Please let me know if anybody has came accross with similar scenario?

Sincerely,

--Amde

Please help!!!!!!!!!!

|||

Do you have to use MDX or can you use T-SQL? I once used a derived table to get this type of data. Not pretty and not the quickest thing if you have huge datasets, but it works.

SELECT grouper, sum(total_charge), sum(pct)
FROM (

select
dx1_num "Diag", -- Item to list in pie slice
sum(charge_amount) "total_charge", --
(sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) "pct", -- Percent of Everything
case
when (sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) < .05 then 'Misc' -- Interim Group
else CAST(dx1_num AS VARCHAR(15))
end "grouper" -- what kind of name do you want for free?
from ar_billtrans_charge
group by dx1_num

) Y GROUP BY grouper;

I am sure some T-SQL gods out there can do much better.

R

|||

Hi,

Appreciate your response. Basically, I am using MDX query. Do you have any idea how to do the same thing using MDX?

Thank you for your cooperation.

--Amde

|||

Please read my response with a sample report in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

Adding columns at runtime

I have a procedure that will return a dataset with an unknown number of columns (the user chooses a date range, and there will be one column per day). Since the columns are not always the same, the report designer doesn't want to help me with this. How can I make this work?

Thanks

Hello my friend,

For performance and ease-of-use reasons, I strongly recommend you take a different approach than using columns in this way, especially for reporting services. Please give details on what you are trying to do (the table structure and the query, etc) and I will try to suggest an alternative to achieving the same result.

Kind regards

Scotty

|||

Currently, I have this table:

CriticalUnitHistory
(
CritcalUnitHistory int (PK),
MarketID int,
UnitLCN int,
CriticalDate datetime,
CriticalReason varchar(50)
)

Every day, I look through a list of computers (each with a UnitLCN that is unique to its city) in different cities (MarketID corresponds to each city), and if its current status satisfies certain criteria, I add a record to this table with the MarketID, UnitLCN, current date and a short description of the criteria that it met to be included on the critical list.

I have been asked to create a report that will take a list of UnitLCNs and MarketIDs, and a date range, and show a table with the UnitLCNs down the left side, the dates across the top, and, if the computer was critical on a certain day, show the CriticalReason in the corresponding cell.

It would look something like this:

MarketID UnitLCN 1/20/2007 1/21/2007 1/22/2007 1/23/2007
1 519 No Contact No Contact
1 234 DL Error DL Error
1 219 GPS Fail GPS Fail

Hope that helps. Thanks for your assistance

|||

Hello my friend,

I take it you are having problems generating the data in this way from the original query. Refer to the following url: -

http://www.sqlteam.com/item.asp?ItemID=2955

It is really good. It shows you how to do a cross tab pivot to make data come out in this way. I tested the code myself with my own database tables and it works.

Kind regards

Scotty