Doubt with query based on multicube

[Former Member]

Hello gurus...

I have an Issue related to multiproviders, and hope you can help me to solve it.

I have 2 infocubes:

CHAR1 CHAR2 CHAR3 KF1

-

-

---

1 2 3 10

1 4 5 20

2 4 4 15

CHAR1 CHAR2 CHAR4 KF2

-

-

---

1 2 3 100

1 4 6 120

2 4 4 115

And then a multicube based on these two cubes, and that uses all the characteristics and key figures.

What I'd like is to have a query based on the multicube where I would see only the rows where char3 = char4.

So the query result would be something like this:

CHAR1 CHAR2 CHAR3 CHAR4 KF1 KF2

-

-

---

1 2 3 3 10 100

2 4 4 4 15 150

Is it possible to have a query like this?

Thanks in adnvace.

Sebastián.-

[Former Member · Accepted solution]

Sebastian,

This can definitely be done...very along answer... wait for some time while i am composing it.

Gova

[Former Member · Accepted solution]

Sebastian,

Five steps.

<b>Step 1:</b>

In the below post, I showed you how to set up the keyfigures for this situation with "constant selection"

Create your keyfiures in the same way. Now, you shouldbe able to see the data in this format.

CHAR1 CHAR2 CHAR3 CHAR4 KF1 KF2

-

---

1 2 3 3 10 100

1 4 5 6 20 120

2 4 4 4 15 150

Once you arrive at this step, move to step 2.

<b>Step 2:</b>

You need to create two formula variables one for CHAR3 and one for CHAR4.

Create them as follows.

For CHAR3

>Processing Type Should be "Replacement Path"

in second step

> Select Characteristic as "CHAR3"

in next step

>Replace variable with "Key" and leave all othes default.

in next step

>Dimension ID as "Number"

Create one more for CHAR4.

<b>Step 3:</b>

Once you create the two formula variables, you need to create two formulas (local calculated keyfigures)KF3 and KF4

>right click on the "Keyfigures" structure and create formula

>simply insert your formula variable for CHAR3 there and say OK.

> Do the same for CHAR4.

Now, you just converted your Characteristics into keyfigures.

<b>Step 4:</b>

Create a new KF5 to compare KF3 and KF4. You can use Boolean Opeator "equal to"

your formula should look like this

"KF3 == KF4"

For all those records where KF3 = KF4, this will be one and for others it will be zero.

<b>Step 5:</b>

Now create a condition on KF5 as follows.

KF5 Equal to 1. (do not change any other settings in the condition).

Hide your KF3, KF4 and KF5.

Now run it.

Good Luck

Gova

[Former Member · Accepted solution]

Pretty way to solve it Gova, thanks, I'm gonna try this and tell you the result.

Just one more question, what happens in case that CHAR3 and CHAR4 are not numbers and they are, for example, strings or dates, Is this solution still possible?

Thanks again.

Sebastian.-

[Former Member · Accepted solution]

If they are dates, its possible. But if they are character strings, then its not possible...

[Former Member · Accepted solution]

Gova your solution worked fine, at least upto point five, condition is not working as desired, and no lines are showed independently of the value of the last KF.

I defined the condition as you said selecting "All characteristics in Drilldown independent", and in others querys I have conditions like this working fine, but in this case is not working. If I don't select "All characteristics in Drilldown independent" and I mark each characteristic it works fine, but later in the query when I pivot some characteristic It doesn't work anymore.

I also have other question: how can I do to consider only in results values the lines that are shown after conditions, because when lines are not showed in query because of a condition they are counted in results anyway.

Thanks again, hope you can give some help with this two issues...

regards.

Sebastián.-

[Former Member · Accepted solution]

Sebastian,

So, once you have the characteristics as keyfigures, are you displaying them and checking manually to see if those are displayed as you wanted.Unhide these two and manually check it.

Also unhide the condition and see if that is working fine. Remember KF5 is based on KF3 and 4 which inturn are based on the characteistics. KF3 and KF4 can only be calculated when both these characteristics are in the drilldown.

Hmm...Condtion should work fine when "drilldown independent" option is selected.

As a first check, unhide all of the above and test it. Once you run the query, also deactivate the condition and see if you notice any difference.

Default behavior for the result woulld be that. In the properties for the keyfigures, right click and set "Calculate Result as" equal to "Summation". Now it should sum only the displayed values.

Good Luck

Gova

[Former Member · Accepted solution]

Gova I already did all what you said, but when I activate the condition al rows dissapear (with the exption of the result row).

Any idea about what could be happen?

[Former Member · Accepted solution]

Sebastian,

So you defined a condition on KF5, which has only values 0 and 1.

Condition is defined as this

KF5 equal to 1. So that anything other than zero are hidded.

May be for all the rows, KF5 = 0, i.e. the two characteristics are not equal? Did you rule out this possibility?

Do you have any other conditions in the query?

Can you send screenshots of the following to my email id.

1. Query definition.

2. Keyfigure KF5 definition

3. Condition definition.

4. Screenshot of the results with all KFs "UNHIDE", condition INACTIE

5. Screenshot of the results with all KFs "UNHIDE", condition ACTIE