If I rename a QQube data connection to something meaningful to me, The Period Names (Jan 2013....) disappear and the old names (Period 13...) show up. Is this by design.... If I bring in a new field it will show as "Jan 2013" and not as Period 13. Makes it real hard to do any formulas or lookups when so inconsistent.
Not advisable to change the QQube data connection, as what we do as a front end to Microsoft Query is partially dependent upon that. Can you give us a reason as to why you would even need to look at - never mind change - the QQube data connection name?
As a general note, the raw field names are NOT what you see in the Excel add-in. The raw field names in the QQube database are permanent to assure previous QQube compatability. The Excel Add-In is designed to be a user-friendly tool - where we have taken the liberty to rename the raw field names to reflect better understanding for the average user.
If you are bypassing the QQube Add-In to make manual queries to the QQube database, then you would always deal with the raw field names. It would not be generally advisable to combine manual SQL queries against the QQube database in the context of 'also' using the Excel Add-In; they should be indepenent of each other.
You can however use the QQube Add-In to bring in lists into Excel, and perform macros/VBA based on the list data. This would be in keeping with how QQube is designed.
Hi - thanks for response....
I really don't need to change the data connection name its just habit to make it easier to see what query is associated with what worksheet easily, especially if there are other connections from other data sources. This isnot a big issue. Especially with the financial analytic queries, I have had to look at the SQL to see what periods were being brought in as I have found that occassionally not all of the 36 period fields are included in the SQL. Not sure why. On a large project, we just updated the TB 3 year period from 2012 - 2104 to 2013 to 2015...First year is not being brought in....(Periods 1 - 12). The TB is brought down via QQube addin and has not been altered. Rarely will I change the SQL behind the connection.
Raw vs Faimilar names---
Before Version 5 all you saw was the raw names, like Period 01 amount or Period 12 TB ending amount. Then after this you added the famillar names. My TB list is now raw names. If I add in a new period from the QQube Addin List, it will come in as "Jan 2013".... Then I end up with a mixed list. (I am very concerned about this 'mix' with this large project mentioned above. The data from the TB list is referenced all over the worksheet and I do not want a mixed bag of names.)
I generally use QQube Addin List style so that I can add calculated columns. I will either then generate a Pivot table or a Report from the list depending on what the customer needs. I set up formulas to reference the Periods that customer is interested in. It could be current month, last month or whatever their needs. So if using the Trial Balance or PL Summary, I need to find the "Period" they want via formula so can add to the report.
If end result is a pivot table, I generally add a caculated column called " Current", "YeartoDate"...and the formula will find the appropiate period for reporting based upon the input from the user. . This makes it easy to generate a single pivot table for any period or combos of periods.
In Either case, to find the correct Period, I need to "Match" the period name. I find that matching to the new familar name, like Jan 2013 is not working - I will generally set up a Match( c3 & " Ending Amount", QQube[headers],0) - where c3 is "Jan 2013"..... While the resulting string look just like the ones in the QQube headers, Excel does not consider them the same. Lengh of both names are the same. Which is why I posed the query in the first place, is there something embedded that makes Excel think that the strings are not the same? I have no problem doing the string match with the raw names. I have no probem using the familar names, but I need to be able to reconstruct the string so that I can refernce them in formulas.
Naming Tables -----
Naming the table so that it is easier to access in formulas, is much more critical and problemmatic. When QQube Add in is refreshed, the Named Range for the table disappears, while the data connection name stays the same. Raw names appear. This is most annoying. I believe that the issue below is a 2013 issue as I have been able to Name a table range in Excel 2010 and it has stuck. Naming the tables makes it much easier to read formulas.
Wish I was going to SNH... I would love to spend a bit of time with you and Matt to go over some of these finer points so that I can get the most out of QQube.... I am anxious to really find some time to convert some of my projects to Power Pivot.
(a) The raw data period names in the QQube Database are Period 01, Period 02, etc.
(b) The names for the periods in the Excel Add-In use the actual month and year names
(c) Having current month, previous year month etc will be available in our next major version this fall. This has been requested many times.
NOTE: Anytime you edit/change the manual SQL Query - you need to separate it from the use of the Excel Add-In. We generally tell advanced users like you to VIEW the manual SQL so that you can use it for custom query projects that don't use the Excel Add-In. But don't try and mix the two
Choose a location