Issue with line Chart display

[Former Member]

Hi,

My requirement is to show year, state wise sales revenue in a line chart along with total sales revenue for all the states against each year.

I can achieve this by creating a derived table in the universe level like

select state,year,sum(salesrevenue) from tableA

group by state,year

union 'all states',year, sum(salesrevenue) from tableA

group by Year

and dragging these objects to a line chart.

But the performance of my report is very bad and it is fetching lacs of record to the report level. So thought of using any approach to avoid union query at the universe level and tried to calculate the total at the report level.

And am able to achieve this in table format by using Nofilter and forall functions (i.e by using the function =NoFilter(Sum([Sales revenue]);All) ForAll([State])

)

But am facing issue with the legend if i show the data in chart format as attached in the screenshot.

Legend is displaying double times. Even though the function is aggregating the data for all the states the legend showing the measure name for each country along with the other state wise legend.

Thanks for your help

[former_member210032]

Hi Divya,

In your previous attachment you apply break on year,

if you apply section on year then you get each year sales revenue of one state as well as the target sale revenue.

Regards

Mustafa

[Former Member]

Hi Mustafa,

I need to show data in chart not in charts, so cannot apply breaks.

Just for explanation i have used table in my attachment.

I need to show line chart with year, state, sales revenue with separate line for each state and one for all the states (combined data)

[former_member210032]

Hi Divya,

You want to show target revenue in your line chart right? see the below screenshot

ytd is sum of all revenue

if your requirement like that then create object in universe 'ytd' put in select block

in webi new object merge with state give name mrg_obj

create a new variable target =if [mrg_obj]='ytd' then sum([query1].[sale revenue]) else [query2].[sale revenue]

Regards

Mustafa

[Former Member]

Hi Mustafa,

Your approach works if you have only one dimension but I have two dimensions Year and State. My requirement is to show in each year sales revenue of one state as well as all the states for that particular year. As shown in my previous attachment.

[former_member203850]

Is it possible to remove state object from Region colour ??

Or

Create separte variable for each lovs of state and then hide it from the report.

Let me know how it goes.

Regards,

Pranay

[Former Member]

Hi,

What is your Business Objects version (With Service Pack and Patch)?

Kind regards,

Wafa

[Former Member]

Am using BOXI 4.1 SP3

and issue is also there in 3.1SP5