PivotTable Back-End Data Retrieval

koreyjames

New Member
Joined
Jul 23, 2012
Messages
44
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

koreyjames

New Member
Joined
Jul 23, 2012
Messages
44
All right so how about something that uses a recorded VLOOKUP in the macro? So something like:

Code:
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

Legend
Joined
May 15, 2003
Messages
29,536
So why can't you stick to using Pivot Tables if they have the functionality you're after?
 

koreyjames

New Member
Joined
Jul 23, 2012
Messages
44
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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

New Member
Joined
Jul 23, 2012
Messages
44
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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

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

New Member
Joined
Jul 23, 2012
Messages
44
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

Legend
Joined
May 15, 2003
Messages
29,536
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. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,658
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top