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 Measures to Report - receive error

Hello,

My report, which has a matrix and a chart work fine. Then...

I click on the Data tab, drag a measure into the results grid. At this point I haven't added the new measure to the matrix or anything in Layout.

I click the Preview tab and get the following message: "The definition of the report XXX is invalid. More than one data set, data region, or grouing in the report has the name 'YYY'. Data set, data region and grouping names must be unique within a report.

Ok, I get the point. But why and how would adding a measure give me this issue? Where can I even find where this is being duplicated?

The message refers to a name that is a parameter, and I don't see anything in there that might cause this.

Thank you for the help.

-Gumbatman

I've just got this same message myself - and am trying to figure it out. I had a report that was working fine in Preview...then I went to try to add a parameter, couldn't get it to work so deleted it. Now the report won't preview at all...keep getting this message.

I've even deleted ALL of the layout fields - but still get the error.

The DATA tab retrieves my data fine without a problem.

Help please ?

Phil