I have a OData WCF Data Service and I use Reflection Provider to expose data. Currently I expose collection of, say, Environments, which have the following structure:
{Environments}
-Name
-Id
-Description
-{UpdateTime}
--StartTime
--EndTime
, where {UpdateTime} - ComplexType, is the collection of times, when Environment was updated and relationship here is 1:N.
I'm using Excel to generate some reports from that OData service. After I imported data UpdateTime collection is not showing in a table. I've also tried $expand on the Environment collection, but it doesn't work for me as well. The only way I think of is to expose related collection as an entity type and set relationships, but {UpdateTime} collection doesn't make sense on it's own.
Question: Is there any way to make ComplexTypes be shown in Excel?
Thank you!
I'm not sure which functionality are you using to import data from your OData service. But I guess you are not using Power Query for Excel since I've been using it a lot to import data containing such expanding requirements, and it works just perfect.
Power Query for Excel is an extension of Excel officially made by Microsoft and you can download it here: http://www.microsoft.com/en-in/download/details.aspx?id=39379. It is more powerful than the "DATA" tab installed with Excel by default that you can do a lot of shaping and merging of your datasets.
After you've installed it, it will show up on Excel as a new tab and you can easily import data from an OData feed and expand complex type containing collections in the Power Query query editor.
Sorry I don't have enough reputation to attach an image. But you can navigate to this page as an example: http://office.microsoft.com/en-in/excel-help/expand-a-column-containing-a-related-table-HA103993865.aspx