Drill Down with multiple fields

[#1]

I want to create a drill down on my bar chart. The first layer will be the date which is no problem as the raw data is already grouped by date. I have to identify the specific project so my expression reads:

=If(Project='ProjectXYZ',Date)

The next layer I want to drill down to will be by resource. The problem is, the data is coming through with each resource showing as their own field.

The script is showing:

LOAD Date,

          Resource 1,

          Resource 2,

          Total,

          Needed,

          'ProjectXYZ' as Project

Resource 1 and Resource 2 have the hours they are allocated to that specific project for that specific week. I want to take both of these fields and group them into a 'Resource' field. That way my second level on the drill down will be this field - So when I click on a specific date, it breaks that date out by resource.

[erjohnso]

It sounds like the issue is that you need one resource dimension for the bar chart but have the data in two fields.

Could you load the table twice and concatenate the resource 1 and 2 fields into one field?

Something like...

DataTable:

LOAD Date,

          Resource 1 as Resource,

          R1 as [Resource Type]

          Total,

          Needed,

          'ProjectXYZ' as Project;

Res2Table:

CONCATENATE(DataTable)

LOAD Date,

          Resource 2 as Resource,

          R2 as [Resource Type]

          Total,

          Needed,

          'ProjectXYZ' as Project;

Then your dimension is resource type and expression involves Resource variable for hours.

[#3]

Thanks Eric,

I will try this,

I wanted to correct my original script I load. I will need to use the Resource 1 and Resource 2 fields for other dashboard elements. Will you suggestion eliminate that possibility?

[#4]

Also,

I am having issues loading the script. The semicolon after the first table load after Project; is causing the  FROM to show up with a correction mark under the entire path.

[Anonymous]

Did you sort the missing comma's ?

DataTable:

LOAD Date,

          Resource 1 as Resource,

          R1 as [Resource Type] ,

          Total,

          Needed,

          'ProjectXYZ' as Project

From Your.qvd

;

Res2Table:

CONCATENATE(DataTable)

LOAD Date,

          Resource 2 as Resource,

          R2 as [Resource Type] ,

          Total,

          Needed,

          'ProjectXYZ' as Project

From Your.qvd

;

I am guessing re your actual load script.  Maybe you could share it ?

[erjohnso]

Not at all, just add those back into the loads. I am not sure if you need to add the rvariables into the concatenated load or not, so assuming you do, I just added them in as '0' records as not to duplicate the sums of resource 1 or resource 2. You'll need to check how that is handling Total and Needed in your actual script as well.

DataTable:

LOAD Date,

          Resource 1,

          Resource 2,

          Resource 1 as Resource,

          R1 as [Resource Type],

          Total,

          Needed,

          'ProjectXYZ' as Project;

Res2Table:

CONCATENATE(DataTable)

LOAD Date,

          '0' as Resource 1,

          '0' as Resource 2,

          Resource 2 as Resource,

          R2 as [Resource Type],

          Total,

          Needed,

          'ProjectXYZ' as Project;

[#7]

Thanks! So when I run the following Script:

DateTable:

LOAD Date,

     Cobran,

     Osman,

     Cobran as Resource,

     R1 as [Resource Type],

     Total,

     Needed,

     'Cherry Wood' as Project;

Res2Table:

LOAD Date,

  '0' as Cobran,

  '0' as Osman,

  Osman as Resource,

  R2 as [Resource Type],

  Total,

  Needed,

  'Cherry Wood' as Project;    

FROM

(ooxml, embedded labels, table is [Cherry Wood Support]);

I receive the message 'Unknown Statement From:

(ooxml, embedded labels, table is [Cherry Wood Support]);

The Excel Doc (Data.xlsx) is set up with the following columns on the tab 'Cherry Wood Support'

Date

Cobran

Osman

Total

Needed