Another Report Studio Question

[UcfMike]

Here's the setup I have 4 columns I want to display on my report. The first is store, second product code, third product name, and 4th Price. Store information is stored in a table, product code and price are in another table, and price is in a third table. A store can have many products and a product can have many stores. Over time a product can have many prices.

I'm grouping on store, product code and group spanning product name on product code.

I only want to display the most current price. To do this I am adding the price tables unique key to the query and filtering descending on the unique key in the query so the latest unique key is on top. This causes the latest price to be on top as well. Now I only want to display the latest price, so I group span Price and Price Unqiue Key on Product Code.

This produces exactly what I want, most of the time. I've found though that often after saving the report and closing it, I'll come back to the report and the oldest price will display instead of the latest price. I'll then have to redo all of the steps mentioned above to get the report to display properly.

Any thoughts on why?

 

[UcfMike]

Clarification.

The above works fine until I remove the Price Unqiue Key Column from the report page. Then it's 50/50 as to which price will be displayed, the oldest or the latest.

 

[mfgf]

Hi,

When you refer to filtering descending on the price unique key in the query, do you mean sort descending?

My guess is that as soon as you remove the price unique key from the list display, C8 is being all efficient on you and ignoring the key (and thus the sort). Try selecting the list object, opening up the 'Properties' property, and checking the price unique key to make it a property of the list. Now go to the Grouping and Sorting property of the list and make sure that the sort descending on the key is still in place.

Does this fix your problem?

Best regards,

MF.

 

[UcfMike]

I had the properties selected thanks to a previous post of yours. What does the properties do to the report anyway, do I want to select all of my columns?

The grouping and sorting is correct as well.

I just reran the report and this time it ran incorrectly.

 

[UcfMike]

Looking through the sql, I thought this line was odd.

min("PRICE"."PRICE_VALUE") "C6"


Why would it automatically take the minimum?

 

[mfgf]

Hi,

The Properties property allows you to associate items from the query with your display object (list, crosstab, chart etc) where they are not included in as displayed items.

What is the default aggregation set to for the PRICE_VALUE column? (Aggregate Function property of the column)

MF.

 

[UcfMike]

I've set all of the aggregation properties to none for now on that column.

The problem usually rears its ugly head when I group span the price on Product Code. Then its like the two columns from the same table are acting independtly of each other.

 

[UcfMike]

I've solved my problem for on this report with filters that were needed.

Once I can get the summary information to work properly in the filter I'll be all set.

Thanks for the help.

 

[mfgf]

You're welcome (for what it was worth). Glad you got it sorted.

MF.