Hi Everyone,
I have a report that takes 15mins to get to the prompt box stage when you refresh it in Full Client. It has about 90 data providers, all linked on all dimensions except a measure and its associated dimension.
The reason that I have structured the report in this way is that each measure is a count as per…
Count(( Case When GuestServices.bouser.CENTRE_PARC.Col025=1 Then 'Excellent'
When GuestServices.bouser.CENTRE_PARC.Col025=2 Then 'Good'
When GuestServices.bouser.CENTRE_PARC.Col025=3 Then 'Fair'
When GuestServices.bouser.CENTRE_PARC.Col025=4 Then 'Poor' end
))Each measure counts a different column in the same table. I need to show the measure breakdown as per
Case When GuestServices.bouser.CENTRE_PARC.Col025=1 Then 'Excellent'
When GuestServices.bouser.CENTRE_PARC.Col025=2 Then 'Good'
When GuestServices.bouser.CENTRE_PARC.Col025=3 Then 'Fair'
When GuestServices.bouser.CENTRE_PARC.Col025=4 Then 'Poor' end
in one table so I link all dimensions and can put all measures into one table, with the associated result breakdown i.e. the table shows the Number of Excellent responses to question 1, then the Number of Good responses etc, then the same for question 2 etc etc etc.
I cant include all measures in the same Data Provider as the data returned is skewed for some reason… (I tried all measures and one ‘breakdown dimension’ as per the above object, but it evaluated the measures incorrectly, and I can’t put all measures and Dims into one dp as the sql is too long! - so I have had to rebuild with one Data Provider per measure…?)
Has anyone any advice on how I can structure the report so that either all of the measures can go into one Data Provider or such that the report generally runs a lot quicker…? Each Data Provider is fairly simple - consisting of 11 time and date dimensions (all from a separate table) and a location dimension from the main table, then the measure and its associated dimension, again from the main table. So each DP only hits two tables (with a complex join). There are two prompts in each Data Provider - one for the location, and the other for the date range. The reason that I have included so many time and date dimensions is that I wanted to write a template report that I can use to build the rest of the suite of reports from…
I have 14 reports of similar formats to complete asap!
I have read Sandys similar post https://bobj-board.org/t/16858
which was very informative, but it hasnt helped… (NB: Supervisor settings for “Do Not Always Regenerate SQL” are disabled at the root and inherited all the way down)
I hope someone can help - this has been a headache and the users are starting to get bored by my excuses as to why they are not finished yet… and I am out of ideas now
I want to give up on it, but am the only BO guy here so I cant! 
I can attached a cut-down example report if anyone likes…?
In anticipation… thanks
Steve.
PS Using BO 6.5.2, against a SQL Server 2000 DB, accessed via ODBC.
snakeboy 
(BOB member since 2003-12-02)