SAME metric different usage

[sanchoniathon]

Hi to all again,

I have this SALES metric that includes "sold amount", "credit amounts" for example.

We can also filter on that SALES metrics in order to only have let's the "Sold amount" without the "credit amount" included.

We have this relational report where we want to display that SALES metrics (including "sold amount" + "crédit amounts") in our first column#1 and then on column#2 we wish to show the SALES metrics (without the "credit amount").

Do you suggest having one query filtering out the "credits" from the SALES metrics and to have another query where we don't filter nothing out from the SALES metrics and thein JON those  queries or is there something easier ad simpler way to do this ?

Thank in advance for any valuable information !

[Lynn]

Do you have one measure in your package for sales and a different measure for credits? Or is there just one measure for amount with some attribute identifying the type of the transaction?

If you can explain what you do to filter out the credits it would help you get the best advice. In general I'd avoid separate queries that get joined together because the join will happen locally on Cognos and may perform poorly if you are working with large result sets.

[sanchoniathon]

Well hello Lynn,

1- Exactly, we only have one TOTAL SALES metric in our current Cognos Package that can then be filtered in the report using another field (from another dimension) called TRANSACTION TYPE

2- Transaction Type values can be for example CREDITS.

3- If there is NO filter used on the TRANSACTION TYPE then the metric will show SALES including all types of transactions.

Thank you for your fast reply by the way !

[Lynn]

If you are sure it is a relational package then you can try this approach:

- Create your query without any filters so you get all transaction types
- Include the [TOTAL SALES] metric in your query to use as your first column in the report
- Drag in a query calculation to use as your second column. Use an expression similar to below so that only the desired transaction type is included and all others, such as credits, are ignored.

Code Select Expand


CASE when [TRANSACTION TYPE] = 'SALES' THEN [TOTAL SALES] ELSE 0 END


[sanchoniathon]

Lynn,

I tried that already all in the same query but with a DATA item and it was showinf me majority of 0 values in the 2nd column but i also have the same type of result when using a Query Calculation instead (still only on the same query and with no filter.

I did a validation using PowerPlay Studio and in many cases where i should have a value i get 0 in report Studio.

i will double-chekc if it's not the way i'm displaying the cube using the different dimension in PowerPlayStudio that isn't the source of the discrepencies but IS THERE SOMETHING else we might have forgotten in the way ?

[MFGF]

Hi,

Can you clarify something? First you stated:

We have this relational report

Then in subsequent posts you mentioned the following:

1- Exactly, we only have one TOTAL SALES metric in our current Cognos Package that can then be filtered in the report using another field (from another dimension) called TRANSACTION TYPE

I did a validation using PowerPlay Studio and in many cases where i should have a value i get 0 in report Studio.

i will double-chekc if it's not the way i'm displaying the cube using the different dimension in PowerPlayStudio that isn't the source of the discrepencies but IS THERE SOMETHING else we might have forgotten in the way ?

See the terms in bold. These indicate you have a dimensional package, not a relational package - is this the case?

The techniques for reporting off dimensional packages are entirely different from those for reporting off relational packages. Can you give us a definitive answer which you have?

MF.

[sanchoniathon]

Hi MF,

Sorry if i previously wrote wans't cleat enough but please take 30 seconds to take a look at the Print Screen in attachment.

Regards !

[Lynn]

Hi MF,

Sorry if i previously wrote wans't cleat enough but please take 30 seconds to take a look at the Print Screen in attachment.

Regards !

You have a relational package and a cube but your relational report result doesn't match what you see in the cube. Is that right? Are you sure that both have the same data? Are you sure there aren't any transformation differences? It seems to me you should validate the result based on what is in the database that your relational package points to and leave the cube out of the equation.

Lynn,

I tried that already all in the same query but with a DATA item and it was showinf me majority of 0 values in the 2nd column but i also have the same type of result when using a Query Calculation instead (still only on the same query and with no filter.

I did a validation using PowerPlay Studio and in many cases where i should have a value i get 0 in report Studio.

i will double-chekc if it's not the way i'm displaying the cube using the different dimension in PowerPlayStudio that isn't the source of the discrepencies but IS THERE SOMETHING else we might have forgotten in the way ?

Regarding your attempts to follow my suggestion, you haven't provided any details other than that you get a zero result that isn't what you expect. How about posting the expression you tried? Is it possible your condition is never met because you don't have the proper transaction type values? Have you flipped things around to test for <> instead of =  to see if you can troubleshoot the expression?

[sanchoniathon]

Lynn,

1- Yes the cube we see in PowerPlay Studio SHOULD have the same data and not the first time i use it to validate what we develop in Report Studio.

2- Please take 1 minute and take a look at the print screens in attachment.

3- The main question remains the same: Can we display 2 columns referring to the same matric or do we need to create 2 different queries and then linked them ?
The first column beeing the unchanged "Total Sales" metric coming directly from the cognos package and the other column, let's call it "Other Transaction Type" that is mainly a case that SHOULD only display sales DATA for those transaction types.

Note: The second column (Other TRansaction Type) is referring to the first column (Total Sales) but we only need the "Total Sales" when the Transaction Type = 'W' in this example.

[Lynn]

Lynn,

1- Yes the cube we see in PowerPlay Studio SHOULD have the same data and not the first time i use it to validate what we develop in Report Studio.

2- Please take 1 minute and take a look at the print screens in attachment.

3- The main question remains the same: Can we display 2 columns referring to the same matric or do we need to create 2 different queries and then linked them ?
The first column beeing the unchanged "Total Sales" metric coming directly from the cognos package and the other column, let's call it "Other Transaction Type" that is mainly a case that SHOULD only display sales DATA for those transaction types.

Note: The second column (Other TRansaction Type) is referring to the first column (Total Sales) but we only need the "Total Sales" when the Transaction Type = 'W' in this example.

I don't see any reason why one query with calculations to bucket the metrics any way you like shouldn't work.

Is there a reason you use a crosstab rather than a list? Do you get the right result using a list? It is a relational package as you've explained so a list should be fine. Are you sure the value is W? Your expression refers to an item in the query [Transact Type] rather than an item in the package e.g., [Something].[Something].[Transact Type] so is it possible that the query item expression is altered? When you filter for W do you use the same expression for comparison as you do in your query calculation? Does the generated SQL provide any clues?

[sanchoniathon]

I don't see any reason why one query with calculations to bucket the metrics any way you like shouldn't work. 
==> I see.

Is there a reason you use a crosstab rather than a list?             
==> Nope, i simply started with a LIST and then with a crossTab

Do you get the right result using a list?                   
==> Same result.

It is a relational package as you've explained so a list should be fine.
==> Ok

Are you sure the value is W?
==> Yes


Your expression refers to an item in the query [Transact Type] rather than an item in the package e.g., [Something].[Something].[Transact Type] so is it possible that the query item expression is altered?
==> [Trans Type] = Comes from the same cognos package and is define as: Fact_Sales.transact_type

When you filter for W do you use the same expression for comparison as you do in your query calculation?
==> Nope, when i filter in the QUERY, i put the simple following filter: [Transact Type] = 'W'

==> And when i filter in the Query Calculation i use the following code as suggested by you which i already tried before but with a DataItem:
   CASE when [Transact Type] = 'W' THEN [Sales].[Fact Sales].[Total Sales Amount] ELSE 0 END

Does the generated SQL provide any clues?
==> As for now i don't see anything in the SQL itself !

[Lynn]

The only other troubleshooting advice I can provide is to change your case expression to use <> 'W' and see if you get any result other than 0. Take a look at the tabular data to see if anything catches your eye there. Also check the aggregation property for your calculated query item. If you don't see any problem in the SQL then that means you should be able to run it directly against the database and get the values you expect. Is this the case?

Good luck to you. I don't see anything wrong with the information provided so maybe others can chime in to help you find a solution.

[sanchoniathon]

Wholly $/$/$,
  :-[

Lynn that was it ... the freaking Data Item's "Aggregation Function" propertie was set to Automatic and didn't even think about changing that to TOTAL.
Woks fine now as both columns display expected Total Sales as well as Amount for Transaction Type = 'W'.

So far so good and i can now continue to finisht the entire report.
:)

Thanks to Lynn and anyone in this great forum.

[Lynn]

Yay!! Glad you got to the answer!