Solved

Excel joining 2 Olap Pivot tables together

Posted on 2014-03-06
6
976 Views
Last Modified: 2016-02-18
Dear all
I created my Cube using SQL Server 2012.
I want to create my dashboard using Excel 2013.
The idea is I have two pivot tables that I want to link together.
How can I do that?
Later I will create my chart on this new pivot table that contains both Pivot tables.

Thanks,
0
Comment
Question by:RamzyNEbeid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

by:RamzyNEbeid
ID: 39910211
like vlookup in excel.
0
 

Author Comment

by:RamzyNEbeid
ID: 39910387
i tried this in the calculated member of the olap Pivot table

VLOOKUP(B4,A120:B128,2,FALSE)

and this was the result = there was a problem creating the calculation exception from result = 0x800A03EC.

thanks,
0
 

Author Comment

by:RamzyNEbeid
ID: 39911672
please i need help.

thanks,
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 14

Assisted Solution

by:Zack Barresse
Zack Barresse earned 500 total points
ID: 39913586
Hi there,

What version of Excel are you running? If you have ProPlus or Professional Plus you can get Power Pivot and use that. In any case you'll need to relate the data somehow. It sounds like you have a single data source but two distinct PivotTables from that one source? Your best bet would be to have this field already in your cube. If you used Power Pivot you would create a measure for this (or, as an alternative, a calculated column). Although without knowing more there's little we can do to help you. Do you have a sample file you could attach? Or describe, in detail, what it is you're trying to achieve?

Regards,
Zack Barresse
0
 

Author Comment

by:RamzyNEbeid
ID: 39932897
i use Excel 2013.

the idea is that i want to make a relation between the tables.

the 2 tables are pivot tables and are from the same CUBE.

if you see the first table you will find column named: "vlookup" i want to replace this column with the value: 4572010 because the Month of the first table = Month of the second table.

please let me know if you need more calcification.

thanks,
excel.jpg
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 39938693
Assuming you have Power Pivot, load the PivotTable data caches into the data model (i.e. Power Pivot), relate the tables, bring in the other field as desired. If you don't have Power Pivot then you'll either need to join the data prior to returning from SQL Server to get your values that way, or you'll need code to dynamically clear/enter formulas to retrieve data.

So what version of Office do you have? Is it ProPlus, Professional Plus, or standalone Excel? If not, do you have access to one of those versions? It can be built by one of those versions and consumed by all versions of Excel [2013].

Zack
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question