Can I set the target level in a hierachy for a report qualification?

[wolf5370]

The scenario is:
A report template as follows...

Attribute (left hand side)
[All Merchandise]

Metrics (top)
[sales]
[% sale floor stock]
[% warehouse stock]
[% On Order Stock]
[% Supplier Stock]
[% Other Stock]

The % metrics are compound metrics based on counts. For example [% sale floor stock] would be defined as:
([Sales Floor Stock Count] * 100.0)/[Total Stock Count]
and [Total Stock Count] is defined as:
[% sale floor stock]+[% warehouse stock]+[% On Order Stock]+[% Supplier Stock]+[% Other Stock]

OK now the [All Merchandise] colomn is the refernce data. It is actually the full system Merchandise Hierachy.
For example sake, a simplified structure for this is:
{Buying Unit}=>{Category}=>{Department}=>{Sub Dept}=>{Range}=>{Item}=>{Stroke}=>{Stroke/Colour}=>{Stroke/Colour/Size}=>{UPC}

(phew!) OK, now the rub is that this report requires qualifications for each of these metrics. The problem is that the users want to run the report at {BU}, drill to {Dept} then down again to {Stroke/Colour}. They want to choose something in the qualification like [% Sales Floor Stock]<=50 (i.e. Display only where Sales Floor Stock is less than or equal to 50%). Simple so far. However, they want this qualification to be met at {Stroke/Colour} regardless of the level at which the report is run. The reason is that they actually are asking where the {Stroke/Colours} have less than +50% within the {BU} (first run) and {bu}{Dept} first drill.

I can force the SQL to hit the {Stroke/Colour} aggregation using Dimensionality, but it then applies the qualification in a subsequant pass at the higher level.

I have tried several things (and don't want to go down the line of Drill to Template or static reports). Any ideas would be appreciated.

Thanks,

Richard

 

[wolf5370]

Sorry, should have said this is a 7.2.2 project...Ta

Richard

 

[mstrNate]

If you're having problems getting that qualification (e.g. [% Sales Floor Stock]<=50) to apply to more than one pass in your report, would you be able to run that qualification in a seperate report at the (stroke/colours) level and use that as a filter for the real report?

HTH,
Nate

 

[nlim]

Richard, I think the problem you describe is that the qualification metric must be at an aggregation level that is "independent" of the report level, and that the metric is a compound metric.

So Nate's suggestion is the most elegant solution. on a separate report, place stroke/colors on row, and %salesfloorstock on column. Add a filter level of %salesfloorstock prompted value...

then drag this report onto the original report as a filter. The report will have a separate pass that finds the stroke/colors that meet the criteria and joins it to the main report pass.

good luck.

 

[wolf5370]

Sounds interesting guys, I'll give it a go and get back.

Thanks,

Richard

 

[wolf5370]

OK, another problem caused here. The final template (i.e. not the filter report) has a hierarchy as the row ([All Merchandise]), and because the row is hard-coded to {stroke/colour} in the Filter Report, the final report is always at {stroke/colour} too - the level chosen in the original Autoprompt is ignored. For example, if I run the report and chose {department}, the report would come back at {stroke/colour}. I need to qualify at {stroke/colour}, but display the report at the autopromted level.

The reason for this is that the report shows stock availability at different parts of the supply chaon, not stock levels. i.e. If the 'product' is on the sales floor, one is added to the sales floor count for that 'product' at the UPC level. Even if it also exists higher up the supply chain, it is only reflected at the lowest position.
So, in essence, what the user is asking when run at department is: "what % of stock is avaiable on the sales floor, etc?". This would always be high, as most 'products' will be available for sale, but at the lower level of Stroke/Colour, there is likely to be more spread out. Take it that the mean of the stroke/colours at department level is, say 90%, it is still possible (and likely as it turns out) that there are going to be stroke/colours that have much lower values (even zero). So if the user wants to capture all stroke/colours that are less than 25%, lets say, they choose <25 on the % qualification, and expect to get only those returned. However, because they initially run at Department level, and that is 90%, no data is returned. What they expected to see was those stroke/colours less than 25% available for sale reflected at Department level.

Sorry, very complicated to explain. I think I am going to have to make a static second report fixed to BU|Department|Stroke/Colour (which I was trying to avoid)

Thanks for you help,
any further ideas will be appreciated.

 

[mstrNate]

Not sure if this is what you meant or not, but you might be able to use the static report you mentioned at the end as a intermediate report between the filter report and your final report. In other words, create the filter report, use it as a filter in your static report with all the attributes, and then use that report as a filter in your final report. I haven't tested that scenario, but I was wondering if it would work, because then the engine might be able to pick any of the attributes from the intermediate report depending on the prompt answers they give. Course, it might also stick to the lowest level of the dimension as well... not sure. Good luck.
Nate