Value for distinct?! Help me!! Urgent!!

[lorenmario]

Hi guys, good day!

I have a problem here, I have a report created from a package to the Cognos DMR, the report is this:

The fields VISIT appears several times, and the problem is that I need an average amount of items, but need it to consider only the distinct visits.

Visit     ItensQty
111         2
111         2
111         2
222         1
222         1
222         1
333         4
333         4
333         4

As the report is dimensional I'm not getting the distinct direct use in the field of Visit, or will I need to create a calculated field like this:

[ItensQty] is distinct [Visit]

?????????

Any ideas, anyone can help me, please! I need to deliver this report to URGENT!

Thank you!

[MFGF]

Hi,

What is [Visit]? Is it a level of a dimension? Is it a member set? Is it a level attribute? What is the structure of the hierarchy it is coming from?

MF.

[TheCognosDave]

Hey bud,

I would do this in SQL.  Something like this...

SELECT AVG(L.ItensQty) Average_ItensQty
FROM (SELECT DISTINCT Visit, ItensQty FROM YourFavouriteTable) L

Good Luck !  ;)

[lorenmario]

Hi friend, thank you for replying!

The visit is a dimension, I'm using the level visit in my report from this field need to know the mean of items in each visit, but the field visit is generated from a coupon (the report is about supermarket), so that each client has many hits ... but this report just want to consider visiting distinct.

I have attached a picture of the structure of the scale, I hope it helps.
Thank you!

[lorenmario]

Hello,

I exported a part of the report to Excel, making it easy to view, I have attached here in the post.
As you can see, the item VISIT is repetition, and I need to return only the distinct visits in this report, only the lines in yellow ...

It would be something like:

Total ([Items Visit Qty] for (distinct([Visit]))

I've tried every way, does not work! I have a lot of urgency in delivering this report, help me!!

Thank you!
hugs

[TheCognosDave]

Are you familiar with QueryDesigner ? Do you know how to add SQL to a Query Object ?  If you do, then performing a DISTINCT on a SQL statement is pretty straight forward.  If you've never done it before, I'm sure it's very daunting.

If you currently have a query object that looks like this:

SELECT [Visit],[Count(visit)],[Qtde Itens Visit],[Visit Value]
FROM MyTable
GROUP BY Visit   


Just add a DISTINCT statement to the query as follows:

SELECT DISTINCT [Visit],[Count(visit)],[Qtde Itens Visit],[Visit Value]
FROM MyTable
GROUP BY Visit   


If you haven't don't SQL before, and don't have a SQL in your query object, you may need a DBA to help you get started.  Sorry I can't be more helpful LorenMario, I hope this helps.

Good Luck !

[lorenmario]

Hi Friend, thanks for replying!

The report was done by using a dimensional model, this clause will not work am I right?


:(

[lorenmario]

Unfortunately we do not have time to edit the database source, so I need to resolve this issue in the report, any suggestions??

Desperate!  :'(

[MFGF]

Hi friend, thank you for replying!

The visit is a dimension, I'm using the level visit in my report from this field need to know the mean of items in each visit, but the field visit is generated from a coupon (the report is about supermarket), so that each client has many hits ... but this report just want to consider visiting distinct.

I have attached a picture of the structure of the scale, I hope it helps.
Thank you!

Hi,

If the [Visit] item in your report is a level that you have dragged in, this means you have multiple members in the level with the same caption. By default you see only the caption of each member displayed, but behind the scenes each member is identified by its MUN (Member Unique Name).

If you expend the Member tree for the dimension and look at the members of the Visit level in the tree, do you see multiple members with the same caption? You can right-click on a member and select "Properties" to see its MUN.

Can you advise if this is what you see?

MF.

[lorenmario]

Hi friend, thank you for helping me.
I expanded the tree you see visit for members, thousands of members.
I see only one member per visit, it is not showing members repeated, but I made a direct test in the database and I know that members are repeated, so I believe that the Report Studio is grouping the repeated visits and adding the value, which brings incorrect results in the report.

In the report I can not create a calculation field to bring me only the value of different tours? It is a limitation when using dimensional models in Cognos?

See the attached photo of the tree.

I am desperate because I need to deliver the report on Monday, and I do not see a solution for this!

Thanks friend!!!

[MFGF]

The image is so small I can't make out what it is showing :(

So you are seeing only one member per visit? Or are you seeing repeating members?

MF.

[lorenmario]

That's right, I see only one member per visit, I found no duplicate.

But I know that there are duplicate members, dba done on a select in datasouce and they appear several times, have visits that appear 5 times ...

Any suggestions??

[MFGF]

Try creating a simple report with your [Visit] level and a query calculation with the expression roleValue('_memberUniqueName',[Visit])

Do you see repeating visit members with different MUNs?

MF.

[lorenmario]

Hi friend, I did what you asked.

The result is in an excel spreadsheet, attached to the message ...

Run the report for one month.

What do you think it might be?

Tks!!!

[lorenmario]

Hi, I was unable to export the report to Excel with one month of visit, caught my machine, but I copied the cognos viewer a 1000 records and paste in Excel...

Hope that helps ...

[MFGF]

Hi,

How are you narrowing down the members in your original report? You didn't appear to have thousands of [Visit] entries in that one? Can you try to limit the results so we can see whether you have repeating member captions?

MF.

[lorenmario]

Hi friend,

I do not I display the members of the visits in the original report, the visit is used to calculate an indicator only appears in the query, so no need to limit the display of visits.

The report itself is very simple, a crosstab shows the evolution of 7 indicators (lines) over 13 months (columns), one of the indicators is the Average Value per visit, but the Report Studio returns a value greater than correct, it is not considering the distinct visits.

But if you want I can create a report with visits in the lines, how do I limit the members?

Thank you!

[MFGF]

Hi,

In that case, how are you calculating your indicator in the original query? What expression are you using?

MF.

[lorenmario]

Hello,

I have attached the report for you to see. The last two calculations (Average Price/Units,  Average Units/Trip) are showing incorrect values​​:

The expressions is:

Average Price/Units -> [Items Visit Qty] / [Visit Count]

where [visit count] -> count (distinct ([Visit]))

Average Units/Trip -> [Total Value] / [Visit Qty Items]

The VISIT level is in the query, use it to generate the Count visit, but it seems that is not working and it is not only the value of bringing different.

If you need more information tell me ...

Thank you!

[MFGF]

If you modify your [visit count] item to be

count(currentMeasure within set [Visit]) does this return the correct count for the number of visits you expect?

It's still not clear whether you have multiple members for the same visit caption in the Visit level...

MF.

[lorenmario]

Hi MF,

I'll try to do that, unfortunately do not know much functions dimensional, but I'll do it tomorrow morning and give you an answer!

Thank you for help, you are my last hope!
;)

[lorenmario]

MF, you still are not sure if there are several members repeated the report right?

There is the possibility of Report Studio automatically group the repeated and show one?

How can I make sure that there is repetition? If you tell me I can do a test tomorrow when you arrive at work, now I am without access to the server ...

Tks!! :)

[MFGF]

Hi,

Easy way to find out. Look at the caption of one specific Visit member and create a query calculation as follows:

filter([Visit level], caption([Visit level]) = 'the caption of a visit goes here')

Does this return a single member or multiple members?

Unfortunately it's a challenge to make multiple members with the same caption appear as a single member. At least we will know whether this is the case...

MF.

[lorenmario]

Hi friend, I'm here at work.

I have created a query with calculated field that u asked me, I chose a member I know has repeated, the member: 2013032817781527104953.

I made a query in SQL see what it returns:
VISIT                                   QTDE_ITENS_VISITA
2013032817781527104953               9
2013032817781527104953               9
2013032817781527104953               9


The same query with your calculation in dimensional model:

MF Calculation                      Qtde Itens Visita
2013032817781527104953           27

Cognos is grouping member and adding the number of items, so the values ​​do not match my report.

I know that when we use relational models can be taken to change the aggregation function of the field, but here is a DMR as I do not think it is possible right?

I can solve it in Report Studio??

tKS!!!!!

[MFGF]

Hi,

What is the correct quantity value for this? Is it 9 or 27?

If it's 9, it indicates a problem with the modelling of the package you are using - probably a missing determinant. Since the dimensional model is returning this as a single member it's tricky to find a solution within your report.

Depending how your model is working, I guess you could try a query calculation with the expression maximum([Qtde Itens Visita])

Cheers!

MF.