Need distinct rows in BO XiR3 Webi report

[Former Member]

I have a report which is similar to below example. Report has multiple other columns like Issue owner, created date, target date etc which are different (means data doesnt repeat in any row and all are dimensions)

1. User wants one single row for each ID. How to achieve this in BO Webi XiR3?

ID	Issue	Severity
1	1.1	High
1	1.2	High
2	2.1	High
2	2.2	Medium
2	2.3	Low
3	3.1	High
3	3.2	Medium
3	3.3	Medium

2. Above table is a detail tab. There is also summary tab which is as below. Requirement is that Count for High, Medium and Low in Summar tab should match with the count in detail tab and also should match with the row count in detail tab. This means only when ID column is unique, it will match with row count

Original Target Dt	Current Target Date	Primary Owner	High	Medium	Low	Total
1-Jan	15-Jan	A	4	3	7	14
30-Jan	14-Feb	B	6	2	0	8
24-Feb	17-Mar	C	5	6	7	18
		Total:	15	11	14	40

Please help me to achieve the above target in BO report

[Former Member]

Hi Sandeep,

I think you can achieve this by applying break in detail tab.

And with the help of aggregate functions you can count and sum in summary tab.

Please have a look at screen shots attached.

Let me know if you want more details,

Hope it helps!

Thanks,

Shardendu

[Former Member]

Hi Sandeep,

The column "Issue" itself has distinct values. In your scenario that report itself has distinct rows.

Another way is use a black cell & try using this formula:

=count(issue) where (severity="High") or just use =count(issue)

It results the distinct rows.

See if this resolves your issue.

Regards,

Naveen

[Former Member]

No User wants to see all the columns and all columns are dimensions. Not sure if this is possible

[Former Member]

Hi,

Create measure Variable for Severity as below .

var_dummy_Measure

=   if [Severity] = "High"

     Then 3

     Else if [Severity] = "Medium"

     Then 2

     Else if [Severity] = "Low"

     Then 1

     Else 0

1)

now add this variable in report apply ranking

Or

2)

var_max = Max(var_dummy_Measure In([ID]) )

var_filter = (var_max = var_dummy_Measure)

use above variable to filter other rows.

[Former Member]

No Luck... It did not work as the report has other columns that are not unique and are not measures either. Its a set of only dimensions

[Former Member]

Hi,

Create Measure as mention in previous reply and then add it in your report .

use it and hide it .

[Former Member]

Hi Padmashree,

The detail report is showing two records per ID as there are two seperate Issue values. You need to remove this column in order to get single row.

In order to get equal number of records in detail and summary report, you need to have same dimensions in both.

Regards,

Yuvraj

[Former Member]

1)

Please Check Universe Parameter .

It Should Be Distinct .

If It is Distinct Then In Web Trim all objects using formula.

If ID is your granular level it should bring unique row for it ,

For Unique row your  [ID] should be granular level .But in your image [Issue] is granular level .

So you can do one thing apply Group on ID & Show Sum of Your KPIs in footer

with specific color code for group total row.

2)

in Summary part

Create Crosstab

			[Sevirity]	Total
[Original Target Dt]	[Current Target]	[Primary Date]	{ Count([ID / Issue] ) }	Sum(use  Count)

[Former Member]

1. Universe Parameter has the value Distinct.

2. Yes, ID column is not the lowest granular

3. Requirement is like this. If there are multiple issues, for the same ID, then ID with highest severity Issue should be displayed. If ID has 2 issues, 1 High and 1 medium, the row with Severity High should be displayed

[Former Member]

Hi,

Add Three Columns

Id , Severity_Issue & Your Measure .

Apply Ranking on Your_Measure Ranked By [Severity_Issue] .

Top 1 .

[rakeshkumar_bhure]

If you provide your raw data, then it help us to understand the scenario.