Hello Listers:
Using v.5.0.1 - Query returns product id (dimension), sales amt (measure) and cost amt (measure). I defined measure variable “Margin $” (sales-cost) and want to rank on this. I can rank on sales and cost but Margin $ does not appear as a candidates for ranking? What am I missing?
Listserv Archives (BOB member since 2002-06-25)
Are you sure that “Margin $” is defined as a MEASURE?? If you define a variable as a measure and you are ranking the “Product
ID”, “Margin $” should be available as a “Based on” for ranking…
If not… supply more details…
How you defined variable, how ather variables are defined, etc… -------------
On Tue, 22 Feb 2000 10:38:39 Peggy Hallmark wrote:
Hello Listers:
Using v.5.0.1 - Query returns product id (dimension), sales amt (measure) and cost amt (measure). I defined measure variable “Margin $” (sales-cost) and want to rank on this. I can rank on sales and cost but Margin $ does not appear as a candidates for ranking? What am I missing?
MailCity. Secure Email Anywhere, Anytime! http://www.mailcity.com
Listserv Archives (BOB member since 2002-06-25)
Thank you for responding. Here’s my scenario… Query 1 returns Dimensions: Branch ID, Prod ID, Prod Desc, Transaction Code and Measures: Unit Sales, Dollar Sales. Query 2 returns Linked Dimensions: Branch ID, Prod ID, Transaction Code and Measures: Cost I defined the following in the report:
Gross SDV = Sum(<Sales @ SDV>)Where(<Transaction Code(Sales)>=“SAL”)+Sum(<Sales @ SDV>)Where(<Transaction Code(Sales)>=“ROL”)+Sum(<Sales @ SDV>)Where(<Transaction Code(Sales)>=“GRT”) Gross Cost =Sum() Where (<Transaction Code(Cost)>=“SAL”)+Sum() Where (<Transaction Code(Cost)>=“ROL”)+Sum() Where (<Transaction Code(Cost)>=“GRT”)
Margin $ = If IsNull() Then 0 Else Sum()-Sum()
Margin % = If <>0 Then Sum(<Gross Margin $>)/Sum()Else 0
Of the above variables only Gross SDV is available in the “Based On” list of the ranking window. I need to rank one report on Margin $ and one report on Margin %.
Similarly, I have defined variables for Unit Sales and Cost - only Gross Unit Sales appears.
Are you sure that “Margin $” is defined as a MEASURE?? If you define a variable as a measure and you are ranking the “Product
ID”, “Margin $” should be available as a “Based on” for ranking…
If not… supply more details…
How you defined variable, how ather variables are defined, etc…
Listserv Archives (BOB member since 2002-06-25)
The problem lies in the fact that you have 2 seperate data providers. BO cannot figure this out.
It will only offer the Measures that were related to the dimension’s data provider. It should work if you only use ONE data provider. Even if BO creates two distinct Select statements. Simply having the data providers Linked doesn’t cut it… If this is not possible (seems like it should be), then tell me again & I will come up with a different solution for you…
MailCity. Secure Email Anywhere, Anytime! http://www.mailcity.com
Listserv Archives (BOB member since 2002-06-25)
Thank you 
The problem lies in the fact that you have 2 seperate data providers. BO cannot figure this out.
It will only offer the Measures that were related to the dimension’s data provider.
It should work if you only use ONE data provider. Even if BO creates two distinct Select statements. Simply having the data providers Linked doesn’t cut it… If this is not possible (seems like it should be), then tell me again & I will come up with a different solution for you…
Listserv Archives (BOB member since 2002-06-25)