Pls note: I have looked at all the related posts / blogs for this issue and have run into unbelievable number of brickwalls that I have come to hope that posting my particular case will result in some help.
Scenario: I have three lists: E.g. Country, State, City. State has a lookup to Country and City has lookup to State. Just to be clear, State list can have many states for one country. Likewise for City list.
Now I want to combine everything and export to a single excel sheet. I can live with just exporting States and Cities under a particular Country.
Roadblocks: I tried creating a linked data source for a combined list view (hoping that I can export that data view to excel), but lookup columns seems to be returned in different format (E.g 'USA' is returned '1;#USA' in the State list lookup) so the 'joined subview' does not work. I tried using Calculated Columns but they cannot be based on a lookup field.
Is there any quick solution that I can use (hopefully without using code since business wants it yesterday)? Am I going the wrong route? I'll absolutely appreciate any help! TIA!
