Synchronisation/Context Problem

[DoubleStew]

I have 2 queries. Query1 returns Employee Number and Store Name - both of these are dimensions. Query2 returns Employee Number and a Cash Value(pay) - Employee Number is a dimension cash value is a measure.

I now create a 3rd report based on these 2 data providers, with Employee Number as the linked dimension, displaying Employee Number, Store Name and Cash Value. I then turn this report in to a Master Detail report with Store Name as the master cell. The problem comes when I try to get a total of the Cash Value by store. I get the total for the whole report at each Store Name section.

I can't introduce Store Name in to both queries and I've tried using the multicube function but that doesn't work.

Is there anyway I can sove this problem using contexts?

 

[blom0344]

Apart from your immediate question, I feel that this should be a one query approach (if impossible I suspect that your universe is not properly set-up)

There is a natural hierarchy between Store, Employee and value. From what sort of tables (data or dimension) are you drawing the data?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[skrandel]

This sounds like a classic &quot;Multicube&quot; problem. You simply need to change your measure column on the report to say: Multicube(<Cash Value>).

I won't give you the big explanation, but this function allows BO to use 1 query's dimensions as a lookup for another.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

Steve,

Doublestew didn't get it to work with the multicube function, neither can I get this to work on a similar testreport (with version 5.1.3.)

I expected too that Multicube would work for this case of unbalanced dimensions, but some element to the solution seem to be missing.



T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Try this formula to get the total of Cash Value By Store..

=Sum(<Cash>) In <Emp No(Query1 Object)>

Sri

 

[DoubleStew]

Thanks everyone. I've tried Sri's idea already and it doesn't work. It does a strange thing. If the Store has only 1 employee, it works. If the store has more than 1 employee it gives the total for the last employee in the block.

I suspect I'm going to have to do a bit work on the Universe so that I can get everything into the same data cube. I was hoping that someone would come up with a magic formula that would be a panacea for more problem but without the facility to upload and example of the report on to the site it is a bit difficult for other members to properly analyse the problem.

 

[blom0344]

Stew,

I would hang on for the moment, cause Steve's solution should somehow work I feel. He will probably be on line this afternoon again (if we take time differences into account). Hope he has something to add.......

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Stew,

Okie...How is the relationship between Emp No and Store????

Sri

 

[Sridharan]

Stew,

Check out this thread....

http://www.forumtopics.com/busobj/viewtopic.php?t=19405

and try the solution given by Hary... It should work...i.e as outlined by Hary... create a new variable New Store and make it a Detail Variable and link it to Query1 Emp No.. Now use that in the report... It gives correct sum...

Good Luck

Sri

 

[blom0344]

Sri,

Did you test the suggestion of Hari in the BOB forum?
My testdocument still comes up with Grand totals if I apply Hari's solution ..............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DoubleStew]

I made the store a detail of the employee number (seems the wrong way round but it has to be this way as employee number is what links the 2 data cubes) and used this as the master detail for my sectioned report and ....... it now totals up correctly, (apart from one area but I pretty sure its a problem with my Universe).

The cash value works whether or not it is multicubed.

I still feel that this is a bug in Business Objects in 2 respects:

1) Multicube should be 'built in' so that you don't have to elect to use it.
2) Multicube should total properly in Master detail reports!

Ho Hum off to change my report and fix the Universe. Thanks for the help guys.

 

[Sridharan]

blom,

Yeah it worked. I will take you thru to what I did. Using eFashion have 1st DP return State and City and add another DP with City and Sales Revenue. Now its a classic unbalanced dimension. Now make a Master - Detail report with State as Master and City and Sales Revenue making the detail section. Now if we do a SubTotal we get the wrong result. So to avoid this create a new variable say NewState with =<State> and make it a detail to City. Use this Detail Variable as the Master the sum comes up properly. Maybe I will send you the document if you need.

Stew,


Stew Wrote:

(seems the wrong way round but it has to be this way as employee number is what links the 2 data cubes)


It may be but it all depends on the relationship.

Sri