BO Frustration. Crosstab/Table data Synch

[system]

Ok. this is probably due to my lousy brain not understanding my long ago BO training. But i’m having real problems understanding why i cannot do this.

I have searched the forum , and i have read the article on ‘Synchronizing Data Providers using BO Reporter’ by Stephen A Bickerton. but it doesn’t help much

I’m after a crosstab / table, whatever you want to call it. that displays various data for a bunch of people. I have different cubes created for each one. but i still get the wrong numbers displayed.

example.

Name -------- How many Items before period select start --------------How many items during Period selected

Bob ---------------- 10 ----------------------------- 5
John -----------------22----------------------------- 1

So all of the information comes from the same universe, but the columns of data contain different conditions.

the first column needs to display the total amount of items created before the period

The period you are in needs to be a userprompt. ie: any two dates

the second column needs to show the total amount of items, created during this period.

I can make two different tables, and show the correct numbers np. I read the article on synchronisation, which is vague, so i tried UN-LINKING, all of the items, apart from the name of the person. This produced better numbers, and in a lot of cases numbers that were accurate. BUT., some numbers were wrong when i checked the data.

So the actual measures that i have are :-

Name
Date Created
Item Number (Unique number used for summing how many items each person has)
Status (Open, Closed) etc. (used a cube filter to only give everything not closed for example)

The option that i have tried, is to put the userprompt date into a variable, i have done that, but cannot seem to able to use the variable in a formula.

One other thing that i have dug up, is a post saying this: -

A way I have dealt with it is to create common prompts as objects in the universe under a special or reporting objects class. As an example, many reports use an “As of date” prompt. I have defined the object “As of date” as @Prompt(‘As of date’,‘D’,MONO,FREE). (This object will not parse but works fine as long as used with any other valid objects.) As long as my prompt text matches, I can create conditions prompting for “As of date” and also bring back my object “As of date”. The beauty of this is that no matter what valid date format a user enters in the prompt, the object is returned in the query as a useable date. No further conversions necessary.

While this was offered as a solution, i cannot figure out from this text how to do it.

Do i need just 1 cube of data, and filter using variables and formulas >? or, do i need to create multiple cubes of data. each pulling off the right information. but somehow it doesn’t display the correct number ?

Help !

Steve

---

Mora (BOB member since 2003-06-13)

[system]

I don’t understand why this would be a synchronization issue. This sounds like a single report with an added prompt to select your period.

You would need your prompt object, and then define a variable for your “selected period” as something like:

=sum( If between and then else 0)

You will want to use a “Total” object that sums up for each individual, and you can subtract out the value above to get your totals before this range. Either that or you can create another object that does the same type summary before the

Finally… You can also create two objects in your universe that do the same type summary with something along the lines of:

Before your period:
SUM(CASE WHEN sign( @Prompt( ‘Begin Period’,‘D’,mono,free) - table.dateObj ) = -1 then table.total_items else 0 END)

During Period:
SUM(CASE WHEN table.dateObj between @Prompt( ‘Begin Period’,‘D’,mono,free) and @Prompt ( ‘End Period’,‘D’,mono,free ) then table.total_items else 0 END)

-RM

---

digpen (BOB member since 2002-08-15)