PivotTable Back-End Data Retrieval

[koreyjames]

Hi all,

At present, I have built a scorecard of sorts for contact centre metrics. All raw data is collected from data connection Pivots into an easy to read table that uses Vlookups.

We have 1 metric that tracks customers who have had to contact the centre within 7 days of their original phone call, and this known as a repeat call.

Obviously, we want these to be as little as possible and so one of the abilities is for us to collect the CallID and listen to the call to find out what went wrong (if anything).

Now, you can double-click on the number next to a consultant's name in the pivot on the Raw Data tab to open a new tab with the data, however because my interface if you will uses Vlookups, this doesn't work. I'm hoping to create a button that I can place next to the number on the interface that would do the same as the Pivot does.

Is there a way that I could write a macro that does the following:

- Looks up consultant's name from interface
- refers to the Raw Data tab
- Opens a new tab with the callID etc (as if it was double-clicked on the PivotTable)

I can certainly write a macro for each and every consultant, however that would be incredibly tedious and would result in having to write a new macro every time we recruited/lost through attrition. Not very productive!

Hope what I'm after makes sense! Thanks!

Corey

 

[koreyjames]

All right so how about something that uses a recorded VLOOKUP in the macro? So something like:

Sub getpivotdetails()


ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-19],'Raw Data'!C7:C11,5,0)"
   Selection.ShowDetail = True


End Sub

The only issue being that I get the "Unable to set the ShowDetail Property of the Range Class" because I feel like the macro doesn't actually select anything.

Anyone care to chip in with their ideas?

 

[Smitty]

So why can't you stick to using Pivot Tables if they have the functionality you're after?

 

[koreyjames]

Hi Smitty,

I realise now that I didn't explain it in the beginning but because there are about 6 data connections (different Cubes in COGNOS 10) all feeding into the one interface to provide an all-in-one scorecard for the Team Leaders to use, I wasn't able to use the PivotTables themselves. Thankfully I only need this to work for one of the data connections.

 

[Smitty]

Hmmm, I haven't used Cognos, but if you use Power Pivot you should be able to connect them all and Pivot them out. Then possibly use Slicers to filter down the data.

 

[koreyjames]

Would that I could, but due to the differing reporting systems and naming conventions, PowerPivot doesn't have a consistent dimension to link them together so I wasn't able to use that.

The interface was essentially my last option after I'd tried many things so I'm sort of trying to make it have the functionality I've envisioned in alternate ways. Hence this thread

 

[Smitty]

Would that I could, but due to the differing reporting systems and naming conventions, PowerPivot doesn't have a consistent dimension to link them together

True, but you can build a translation table to link them, just like you would with a database (and I just spent all day doing that for about 40 different tables from different sources and it worked just fine). And the advantage with PP is that it's SQL Server and so much faster than Access. (Plus it's deployable without the expense)

 

[koreyjames]

A translation table in Excel? Or in SQL?

If it's SQL, I don't have the level of access to be able to access the tables directly, only through the portals that they've provided. I work in a multi-national corporation so we're somewhat limited to the generic access that they provide everyone.

Haha see my predicament? I have all these ideas that I want to use that were collected when working in smaller companies but now my hands are bound by the red tape that comes with it so I'm left with doing everything in Excel.

 

[Smitty]

A translation table in Excel?

Yup, build the translation table in Excel, then reference it with Power Pivot. Kiss IT/IS dependency goodbye!

PP will let you link both native and external data, and something simple and small like a translation table is much better suited to be in Excel anyway.

 

[koreyjames]

All right sounds like a plan, thanks for the tip!

I'll have a look into it!