Dimension objects from separate data providers in one report 1

[tc4]

I use BusinessObjects 5.1 to report on a Social Services database. Children in care are allocated to a Social Work team, over time their team allocation can change. We want a report listing children who have been in children's homes, showing both their team when they were first placed and their team when they left - 'Team at Placement Start' and 'Team at Placement End' - preferably in the same report line. Team ID is a Dimension (alphanumeric text).

The two teams cannot be returned in the same query as the date conditions are mutually exclusive. A union query within one data provider would not put them on the same report line. New table aliases and new objects in the universe would work, but there are a large number of similar reports required and our universe is already difficult to manage due to too many table aliases & objects (not my fault - someone else created it, then left!).

I created a report with two linked data providers, identical except one had 'Team at Placement Start' and one had 'Team at Placement End'. The two objects couldn't be placed in the same report table as they are dimensions. However, I created local variables equal to the two dimensions and changed their type to measure. I could put these in the same report line. The 'measure' variables worked except where a child had two concurrent team allocations - got #MULTIVALUE (the 'measure' variables can't aggregate). However, I changed the variables' type, AFTER inserting them in the report table, back to dimension and they worked OK - correct data in two rows, one for each of the concurrent teams.

I found just one minor problem (that could easily be worked around). Once the variables' type had been changed back to dimension, the report creation tool got a bit confused and no more objects could be added to the report table at all. To add more objects, you need to change the variables to measure, add the objects then change them back to dimension.

So, what's my question? Firstly, is anyone else out there familiar with this workaround? If so, all comments on it are welcome. Most importantly, is anyone aware of any problems with it that I've failed to spot? Also, we're using BO v5.1.0 - will this work in later versions?

 

[blom0344]

The behavior you are describing is very odd indeed, I work with version 5.1.2 and generally speaking I can do the following with multiple dataproviders:

1. NOT use an unlinked dimension from either DP in table
2. Use a detail (from universe or as variable) from either the first OR from the second DP , but not from both.
3. Use measures coming from DP's from both DP's .

Personally I have always felt that the whole business of linking DP's and using objects from them is at best shaky. there seems to be different types of behavior between versions and even between reports (although I cannot prove this)

My advice is to go for UNION queries if at all possible (same type of objects retrieved , same connection)

Drawback of UNION query is that it throws information into columns from both sets, making it hard to distinguish afterwards. A way around this is to add fixed column values , a specific one for each subset of the query:


Select 'bike' , a, b, c
from table1
where ..................
UNION
Select 'fishing' , x, y, z
from table2
where ..................


This way you will be able to create variables by adding the fixed column value in the 'where'clause.

My advice is to not expect standard behavior between the 5.1.x versions and If you experience these sort of oddities try to take a different approach.....


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

 

[tc4]

Thanks for that Blom0344 - I'll tell our users not to rely on my method being reliable, or even working at all once we upgrade. I'll pass on your suggestion and hopefully they'll use your method (if not, I'll just have to add another 8 table aliases to our already bloated universe!).

If anyone else out there has ever written or seen reports using something like my 'dodgy' techniques, I'd still be curious about any comments you have.