Line Chart Issue

[former_member402770]

Dear All,

Can you quickly solve this line chart issue which iam struck up more than one day. Attached an .txt pls open with excel or screenshot for your reference, I have 3 combo box which has "all" case. Earlieri created an webi report as cross tab for this requirement as x axis would be 6 month period, y aixs as qty volume.. Now iam finding very hard to write the combination of filtering combo box items for display into line chart and also data values for line chart (refer Manipulation part)..

Appreciate your help guys. Possibly if u guys have any sample file with formulas to display data on line chart with three combo box on filtering show up the data would be great..

Thanks,

Sara44

Line Chart Issues.txt.zip

[Former Member · Accepted solution]

Hi Sara44,

First, you want to add a 'Code' column to your 'Source' table at the bottom, as this image shows:

The formula is B13 = C13 & "_" & D13 & "_" & E13

And similarly for B14, B15, etc.

Then, the formula for cell D2 is

D2 = =INDEX($B$12:$K$17,MATCH($B$6&"_"&$B$7&"_"&$B$8,$B$12:$B$17,0),MATCH(D$1,$B$12:$K$12,0))

What this formula does is find the value in the source table where (1) the 'code' is equal to "Combo1_Combo2_Combo3" and (2) the date is equal to cell D1.

Then copy this formula onto D2:I2

Let me know if you run into any issues

[former_member402770 · Accepted solution]

Hi Alex,

I have issue on filtering combo.Whenever iam selecting the third combo it is not filtering based on second combo and it is showing all items the result is showing all the data always for all cities on third combo. And when trying to implment ur logic ur source table is more like my destination table where i dump the data based on hide/show filtered combo(4th combo). I need help on implementing ur logic in a sample .xlf file and could you pls help me asap so that i can interpret how do you bind else u can use my attached file and make the modification ie. formula manipulation part too(where we use index match). Attached file is on BO 4.0 edit and open with xlf and this is exactly what my real data looks like. Can you pls help on this.

Thanks,

Sara44

ListTest.txt.zip

[Former Member · Accepted solution]

Hi Sara44,

Unfortunately, we don't have the same version of Dashboard Design, so I can't open your file.  Could you post a screenshot instead?

[former_member402770 · Accepted solution]

Hi Alex,

  I think it would be very difficult to view it in screenshot becuz
iam using an formula for the combos for filtering mainly
to filter some items in combo(cascading combo's i mean). I would send
the screen you by tonight, meanwhile i have an doubt can we bind
combo's and months data directly to the source. If webi refreshes(iam
using live office) hapen in a loop, My question is will the bindings
to the combo what we selected range for first time might go or not. Do
we have to bind the range again when another time webi refresh hapens.
Is it right way  Correct me if iam wrong.

query 1: like if my selection of items in the three combo
are similar and the items has two different sets of data values for
every month.  How it works?

query 2: Can the combo of your example can be made as cascading combos becuz the lovs may increase havenot filtered in your example..

query 3: have u faced this issues like

Whenever i try to connect the SAP-BI server in IDT to call the cube (or) Bex query i am getting the following error.

java.util.concurrent.ExecutionException: com.businessobjects.sdk.core.server.CommunicationException$UnexpectedServerException: [[error.openSapBwBrowsingSessionFailed] 0] <com.sap.sl.olap.impl.ConnectionSpecificationImpl@e3b642, networkLayerType: SAP_BW_BICS, providerType: SAP_BW7X, server: null, catalogName: ZC_MDS, cubeName: ZBI_SQ, language: EN)>,<Error: Name or password is incorrect (repeat logon) on 172.25.69.27 sysnr 02
Key: JCO_ERROR_LOGON_FAILURE
Group: 103
Type:

when i log in to SAP-BI, it says "too many attempt failures and user id is locked", i tried to change my password and tried again in IDT again the error and user id is locked in BI. Its getting into a loop again and again i don't know what to do.

The same user id was working fine previously, before a week now its throwing the error.

Pls advice.

Thanks,
Sara44

[former_member402770 · Accepted solution]

Alex,

As you posted in one of the above eg.on Line chart ALL functionality works good like when i select combo 1 some item other than ALL case i can able to see combo 2, combo 3 by default "Al"l but it should display data too. How can this be achieved, Could you kindly help me on this.

say eg: In Combo 1(Country) iam selecting as India, then by default i ll see combo(State)  as "All" case for that i should see data too (India+All) i mean..can u pls send the steps like above one with screen it was very very good...

Thanks,

Sara44

[Former Member · Accepted solution]

Hi Sara44,

Without knowing what your data looks like, it's very hard for me to give you a formula which would fetch this data in the right way.  If you can (1) give me a screenshot of what your data looks like and (2) write a step-by-step instruction of what you want to see happen (for example: "When I click on Combo1, I want combo2 and combo3 to show 'All' and the data to sum up all the India data"), then I will be able to provide you with detailed instructions.

[former_member402770 · Accepted solution]

Hi Mike ,

  Lets take the this  data as example in contradictory to your first example post where concatenating of All_All_All hapens only once what about this case as  aa_All_All, bb_ff_All, All_ff_All pls. refer my screenshot

1. "When I click on any item other than "All" in  Combo1, I want combo2 and combo3 to show 'All' and display the data in graph

2. "When I select any item in Combo1 and Combo2 , I want combo3 to show 'All' and display the data in graph

3. The user may try any option on choosing the combo's according display of data should hapen.

If you may wish to take the above example or any example of your wish for explanation in steps would be really great..

Thanks,

Sara44

[Former Member · Accepted solution]

Hi Sara44,

Here are the equations that you want in your spreadsheet.  If you select, for example, [Combo1 = M1, Combo2 = All, Combo3 = Channel1], then the chart will sum up all entries where Combo1 = M1, Combo3 = Channel1, and Combo2 can be anything.  And similarly for all combinations.

However, I don't understand what All_All_All means in your data.  If the user selects All_All_All, surely we should just be summing all the data?

In any case:

Basically, I've added 3 columns, 'Check1, Check2, Check3'.  They tell us, for each entry, whether it is currently selected by Combo1, Combo2, Combo3.  If a row has all of Combo1, Combo2 and Combo3 equal to 1, then it should appear in the chart.

The formulas are:

J2 =IF(OR($B$18=A2,$B$18="ALL"),1,0)   Copy this down to all of J

K2 =IF(OR($B$19=B2,$B$19="ALL"),1,0)   Copy this down to all of K

L2 =IF(OR($B$20=C2,$B$20="ALL"),1,0)   Copy this down to all of L

M2 =J2*K2*L2   Copy this down to all of M

E19 =SUMIF($M2:$M15,1,D2:D15)

Copy this across to E19:J19.  This is the chart data.

Hope this is finally what you wanted.

[former_member402770 · Accepted solution]

Dear Alex,

  I have few  things to clarify here.

1) What hapens if i try to import my latest data everytime from the live office to xcelsius, the problem here is your cell number might change that is exactly hapen to me wherever we used in the formula and also concatenation of combo filters cell numbers too, the cell numbers changes often because we do it source data i guess. Is there any workaround to avoid changing the cell numbers even if we delete the live office object/ while refreshing the live object connections to get the data. Iam Manually changing the cell numbers whenever i try to import  new/update the latest data which is already binded to the cell.

2)The one of your immediate above answer we have sumif. Is there a way we can avoid the sumif function above because it might degrade the performance of the dashboard

Thanks,

Sara44

[former_member402770 · Accepted solution]

Dear Alex,

   Remainder for the above issue

Thanks,

Sara44

[Former Member · Accepted solution]

Dear Sara44,

Regarding the SUMIF function, it really won't degrade the performance nearly as much as they make it seem.  Most of the guides suggesting not to use the SUMIF function were written a decade ago and with much less powerful computers.  Unless you have thousands of rows, I wouldn't bother with it.

Regarding your LiveOffice connection, which rows are changing, in particular?  Is the issue simply that you sometimes have more rows?

[former_member402770 · Accepted solution]

Dear Alex,

  From One of the first reply to my post by you there you have an formula like B13 = C13 & "_" & D13 & "_" & E13 for combo concatenation. The change occurs here in C13,D13,E13 , the cell number changes when i try to import the data from the live office if i modify the live office object properties to say ondemand/report saved data/my instance whatever the cell number gets collapses it shows some other number, even in the index match formula too where i refer the cell too  i am manually changing few times i donot know y it hapens but it hapens few times.

On the Live office for some webi report i have more than 1000 rows of data may be even 3000 rows have seen for one of the report.

Regarding the SUMIF function, in one of your above post reply, i am seeing previous to the one you were started helping me using index match function for displaying chart data. But since i have asked you for All All All, Combo1 All Combo 3, Combo1 Combo2 All  etc... cases which actually using sumif . Can you do reply me using index match function along with filtered check for combos  for these cases instead of sumif because i already implemented indexmatch so now it would be difficult for me to use sumif

pls advice.

Thanks,

Sara44