[SOLVED] Bullet Bar - Master Detail Relationship

[andy_mason_84]

Hi All,

So I have a list report  which has

Suppliers            Average Days to Pay
Supp A                             5
Supp B                             10
Supp C                             7

I have but a Bullet Bar chart in the list which has a Master Detail Relationship with the List based on Supplier.

The user sets the target for the bullet bar on the prompt page.

I am then using that Target figure to set the Green/Red 'Colored Regions'

The Green section I have set to start at 0 and end at [Target] which is the figure the user entered on the prompt page.

Now the red section starts at the same [Target] value but I want it to end at Max(Days to Pay for Report) so that all the Bullet Bars are using the same scale and it is dynamic enough to not have to hardcode the end figure for the Red region.  Using the table above the end of the red section would be 10 for each bullet bar.

The problem I am having is that when using Max(Days to Pay for Report) is that it seems to take the supplier into account and only finds the max for that supplier which means all the bullet bars are different.

I have had a read around and some of the bumf I have read leads me to believe that Cognos creates a seperate query for each instance of the Bullet Bar, so if there are 15 Suppliers on the report it would query the DB 15 different times for each bullet bar, if this is the case it would explain why the MAX for Report is not working but is this actually the case?

If so anyone have any ideas on how else I can get this working?

Cheers,

AM

[schrotty]

Hello Andy,

yes, for every master detail Relationship cognos generates a seperate query

Try to calculate the Max(Days to Pay for Report) in the List query (Master) and create a new Parameter in your Bullet-Chart query (detail). In the Master Detail relationship set the Parameter to the calculated Item.

schrotty.

[andy_mason_84]

Hey,

Thanks for the response.

I am using the same query for both the list and chart.

But I followed your advice and created

[Max] = Max(Days for Report)

And then another Data Item called [Bullet Max] = #prompt('BulletMax','integer')#

Then in the master detail relationship i have mapped

[Max] -> [Bullet Max]

Now the [Max] figure is 150.  However the [Bullet Max] figure is 0.  If i delete the master detail relationship between these two items it then prompts me for the value.  So it must be passing something to stop it prompting me but it certainly is not passing the 150.

Any ideas?

In the mean time I will try and recreate with 2 queries to see if that makes a difference.

Cheers.

[andy_mason_84]

Schrotty your plan worked.

2 queries.

One for the list which has the Max (Days for report) data item in

Second Query for the Bullet with the End of the Red Region being set to #prompt('BulletMax', 'integer')#

Then in the Master Detail Relationship I have linked the Max (Days for report) data --> #prompt('BulletMax', 'integer')#

Cheers.