QQube field name behavior in Pivot Tables | CLEARIFY - Page 1

Hi,

I have created a list with QQube, which I extended with my own calculated columns. Then I created a pivot table based on that list (actually a table). A field used in the pivot table is for example "sales amount".

Upon a QQube "refresh all", the list of available field names in the pivot table now shows "SalesTxn Line Sales Amount" instead of "Sales Amount" and therefore the pivot table removes "sales amount" since its no longer available.

At this point if you update your pivot table, the pivot table fields list switches to "Sales amount" again, but its too late, your pivot table already removed it.

Any way to solve this annoying behaviour ?

Thanks

Ph.

Hi Philippe,

I have  experienced pivot tables losing  fields on refresh and it is quite annoying. Nicely laid out pivot and wham...all of your fields are gone on refresh.  Can't figure out what triggers. No changes to fields or views.  I used to use the "refresh all " either with a macro or excel.   This was intended to update QQube and pivot tables.     Then I found that just letting QQube do its own refresh when opening a worksheet and then refreshing  pivots manually all is good.  But it s annoying cause now the client has to manually refresh several objects(on maybe several worksheet tabs) instead of just using a "refresh all" command and have it all update. I have talked to others in this group who have experienced similar behavior. 

But to have a pivot list change on you.. wow... that is even stranger and I agree annoying.

 I find that when in a pivot, rtclick 'refresh' has been safe and works well. No weird problems..

Fran

Thank you for the workaround.

Philippe

Hi all,

I was hoping the new V6 version of QQUbe would solve this problem but it is not. Too frustrating.

As a reminder, attached is a word DOCX file embedding screenshots of the fields list available before and after a Ctrl Alt F5 (refresh all PT).

If the attached file does not show, all fields looking like, for example, "Line Sales Quantity" become "SalesTxn Line Sales Quantity" after a refresh all.

Did anyone find some trick to make this work fine with version 6 ?

Thanks

Ph.J.Drouhin

I didn't see an attachment, so not sure what you might be looking at.

I will attempt to tell you what we do, - and dont' do.  

In general, we provide the raw fields to the specific report writing application, and then the flexibility remains in the application itself.  We remain as neutral as possible; that is, we don't interfere with - or attempt to control what the application does on its own.  This is always our strategy.

There is only one exception to that rule: For the Excel Add-in we provide aliases to the actual field names to make it easier to search for them or recognize what they do.  So in Excel if you see "Line Sales Quantity", that is an alias to "SalesTxn Line Sales Quantity", which is the real name of the field underneath the hood.

Also in the Excel Add-In, when you use it to directly create a qqube connection to a list or pivot table, we also store the custom column names you give them when you save the template (not just the Excel File). 

Let's look at what Excel does on its own:

Pivot table. if you change a field name, Excel stores that information internally. You can see that by right clicking on a field and then choose field settings  So when you refresh the name should still be there. Since Excel stores that information internally for the pivot table, should never be a problem.

Table Data (List).  In the following example:

1. is the Alias name we keep inside of the QQube database specifically for the Excel Add-In.QQube keeps a copy of the real field name in the comment for that cell for the column header. 
2. Is the raw field name inside of QQube - you would see this name in all other applications (unless we change it in one of our examples.
3. That is the Field Name that you store in Excel.  We only use that information to be able to save templates.

In this scenario the custom field name is stored in the cell.  As long as you have the table properties set to this:

theoretically, you should be ok, when refreshing.

BUT, as Fran has answered previously, it is not foolproof - for some unknown reason.

You might ask Mr. Excel https://www.mrexcel.com/ and see what they say about this behavior.