Display values from one object in separate columns

[Rafael_G]

Hello all,

I have created a WebI report for a standard employee roster in a vertical table consisting of name, status, worksite, phone, email, address, etc. The problem I’m having is the phone object has different value types (cell, home, work, etc) so I’m getting multiple rows with the same data except for the different phone number types.

I need the phone values separated into different columns. I’ve searched previous posts and some have recommended using Break or a variable or crosstab report. 

How can I easily display the values into separate columns?

Also, I noticed when selecting one value type like ‘cell’ I end up with only those rows which contain a cell number and all other employee rows are not displayed. The phone object list of values doesn’t include ‘blank’ or ‘null’ so how can I get all rows displayed with a specific value type like ‘cell’ AND any blanks? 

Thank you in advance!!

[nachtaktiv]

You have to create separate variables for the different types and add these to your report instead of the original phone-type.
With these variables you also can filter/select individual types.

[Rafael_G]

Thank you! With a little help from Google Gemini I was able to create the right variable and add them to the report. Sharing the query from Google Gemini is anyone is interested. 

------Google Gemini

You're encountering a common issue when dealing with related data in Web Intelligence. The multiple rows are appearing because the report is showing each combination of employee and phone type. Here's how you can separate the phone numbers into different columns:

1. Using IF conditions in Derived Measures (Recommended):

This is the most straightforward and generally preferred method. You'll create new measures for each phone type (e.g., "Home Phone," "Mobile Phone," "Work Phone").

• Create Derived Measures: In your report, create new derived measures using the IF function (or CASE statement if your version supports it). Here's an example:

  Home Phone = IF [Phone Type Code] = "HOME" THEN [Phone Number] ELSE ""
  Mobile Phone = IF [Phone Type Code] = "MOBILE" THEN [Phone Number] ELSE ""
  Work Phone = IF [Phone Type Code] = "WORK" THEN [Phone Number] ELSE ""

  • Explanation: These formulas check the [Phone Type Code] for each row. If it matches the specified type (e.g., "HOME"), it returns the corresponding [Phone Number]. Otherwise, it returns an empty string (""). This prevents the creation of extra rows.

• Add Measures to Report: Add the newly created derived measures ("Home Phone," "Mobile Phone," "Work Phone") to your report. Now, each employee will have their phone numbers displayed in separate columns, one for each phone type.

2. Using a Crosstab (Alternative if you have many Phone Types):

If you have a large number of phone types, creating individual measures for each might become cumbersome. A crosstab can be more efficient in this situation.

• Add Objects to Crosstab: Add the [Phone Type Code] as the "Columns" in the crosstab and the [Phone Number] as the "Values." Place the employee identifier (e.g., employee ID or name) in the "Rows" section.

• Crosstab Configuration: Web Intelligence will automatically create columns for each distinct [Phone Type Code], and the corresponding [Phone Number] will be displayed in the appropriate column.

3. Restructuring Data at the Source (Best Long-Term Solution):

The most robust and scalable solution, especially if you anticipate changes in phone types, is to restructure your data at the source (in your database or ETL process).

• Pivot Data: Pivot your phone data so that each phone type becomes a separate column. This is typically done using SQL queries or data transformation tools.

• Simplified Report: Once the data is pivoted, your Web Intelligence report becomes much simpler. You can directly add the phone number columns (Home Phone, Mobile Phone, Work Phone) to your report without any complex formulas.

Example (Derived Measures):

Let's say your data looks like this:

Employee ID Phone Number Phone Type Code

1	123-456-7890	HOME
1	987-654-3210	MOBILE
2	555-123-4567	WORK
2	111-222-3333	HOME

Export to Sheets

After creating the derived measures, your report will look like this:

Employee ID Home Phone Mobile Phone Work Phone

1	123-456-7890	987-654-3210
2	111-222-3333		555-123-4567

Export to Sheets

Which method should you choose?

• Derived Measures: Best for a small to moderate number of phone types. Easy to implement within Web Intelligence.
• Crosstab: Good for a large number of phone types, but might not be ideal for all report layouts.
• Restructuring at the Source: The best long-term solution for scalability and maintainability, but requires changes to your data source.

I recommend starting with the derived measures approach. If you have a very large number of phone types or if performance becomes an issue, consider restructuring your data at the source.

Question: Using the Derived Measure method provided returns a value "#MULTIVALUE". What am I doing wrong?

The #MULTIVALUE error in Web Intelligence means that your formula is trying to return multiple values for a single cell, which isn't allowed. This usually happens when you're using an aggregation function (like SUM, AVG, MAX, MIN) in combination with a condition that doesn't properly group the data.

Here's a breakdown of the problem and the solution in the context of your phone number example:

Problem:

Your derived measures likely look something like this (simplified):

Home Phone = IF [Phone Type Code] = "HOME" THEN [Phone Number] ELSE ""

The issue is that for a single employee, there might be multiple rows where [Phone Type Code] is "HOME" (though ideally, there should only be one). The IF statement returns the [Phone Number] for each of those rows. Since a cell can only hold a single value, Web Intelligence throws the #MULTIVALUE error.

Solution: Ensure Proper Aggregation and Grouping

You need to tell Web Intelligence how to handle the multiple phone numbers if they exist. Most likely, you want to just display one of them (ideally, the only one that should exist). Here are the corrected approaches:

1. Using MAX or MIN (If only one value should exist):

If, logically, each employee should only have one phone number of each type, use MAX or MIN. It doesn't matter which you use as long as the data is correct and there is only one value.

Home Phone = MAX(IF [Phone Type Code] = "HOME" THEN [Phone Number] ELSE "")
Mobile Phone = MAX(IF [Phone Type Code] = "MOBILE" THEN [Phone Number] ELSE "")
Work Phone = MAX(IF [Phone Type Code] = "WORK" THEN [Phone Number] ELSE "")

2. Using FIRST or LAST (If order matters or you want the first/last entered):

If there's a specific order to the phone numbers and you want the first or last one, use FIRST or LAST:

Home Phone = FIRST(IF [Phone Type Code] = "HOME" THEN [Phone Number] ELSE "")
Mobile Phone = LAST(IF [Phone Type Code] = "MOBILE" THEN [Phone Number] ELSE "")

3. Check Your Data (Most Important):

The most important step is to investigate your data. Why are there multiple rows for the same employee and phone type? This indicates a data quality issue that needs to be addressed at the source. Fixing the data is the best long-term solution.

Example (Corrected):

Let's say your (incorrect) data looks like this:

Employee ID Phone Number Phone Type Code

1	123-456-7890	HOME
1	987-654-3210	MOBILE
1	111-222-3333	HOME (Duplicate!)

Export to Sheets

With the corrected formula using MAX:

Home Phone = MAX(IF [Phone Type Code] = "HOME" THEN [Phone Number] ELSE "")

The report will display:

Employee ID Home Phone Mobile Phone

1

123-456-7890

987-654-3210

Export to Sheets

It will choose the "largest" value. If the phone numbers are all different, this will effectively pick one. Again, the best solution is to remove the duplicate "HOME" phone number for Employee 1 in your source data.

Debugging Tips:

• Add Employee ID to the Report: Make sure your report includes the [Employee ID] so you can clearly see which employee's data is causing the problem.
• Filter by Employee: If you're having trouble pinpointing the issue, filter the report to show data for just one employee to see exactly what's happening with their phone numbers.
• Check Data at the Source: Use a SQL query or data browser to examine the underlying data in your database or universe. This will help you identify the root cause of the duplicate phone numbers.

By using the correct aggregation function (MAX, MIN, FIRST, or LAST) and, most importantly, addressing the underlying data quality issue, you should be able to resolve the #MULTIVALUE error and display the phone numbers correctly.