Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.
Thank you for dropping by to read our latest, How To, Blog Post.
Just before we begin this video a short announcement.
The world’s attention has been captured by chat GPT.
AI is now something people are talking about.
Obviously, I am a generated voice using AI.
We use these generated voices for blog posts that are purely technical.
It takes a lot less time to create these videos if we use generated voices.
So, Mihai has decided to give me a name as the BIDA AI Assistant.
Just like Apple has Siri, and Amazon has Alexa, BIDA will have Sofi.
Sofi is a very popular name in Romania, and it means wise woman.
I hope I wil be worthy of this name over the coming years.
So, from now on, I will sign off the blog posts I voice over, with my name of Sofi.
Now, back to our regular blog post.
This video is about a trick we have learned to make creating the Headings for Excel Tables easier.
Before we learned how to do this we would manually cut and paste the headings, from the Meta5 spreadsheet, to the Excel workbook.
Then, we would create the heading region inside the workbook.
Of course, cut and paste is not that time consuming, but ever second counts.
Today I will show you how to send the headings from a query tool, into the heading region, for an excel table, in a workbook.
Please note that in the demo camtasia did not capture the cursor properly.
Even so, it is pretty obvious where the cursor is most of the time.
As ever, on with the demo.
Here we are, on the Meta5 evaluation workstation desktop.
I have placed a capsule in the middle of the desktop.
We will open the capsule.
We have a query icon for the sales fact table from the World Wide Importers.
We will go and get the other icons we need, for this capsule from the new icons file drawer.
We will need some spreadsheets.
We will need an excel workbook as the target.
We will need the compress icon to remove the header row.
We will arrange the icons in the capsule, so that the flow of data is easy to understand.
We will rename the headings spreadsheet, just to make that obvious.
Now we will open the query icon.
You can see the query that is going to return about 60,000 rows of data.
We have set the “General Qualifier” field to be zero equals one.
Of course, this is evaluated as false, and so will not return any rows.
Now we will capture the SQL that will be generated.
Now we will run the query.
You can see at the end of the SQL for the query there is the phrase, and zero equals one.
This means no data will be returned.
So, from the query icon, just the headings will be returned.
Just while I am here, I will show you how to remove or add the “expert functions” from a query tool.
We will connect the sales facts to the headings spreadsheet.
Will will run the query.
The result is that the headings spreadsheet now has the headings from the query in it.
Of course, at this point, you are free to change the headings, in any way you wish.
These headings will become the field names in your Excel table, and your power pivot model.
If you believe there is a name that is better to use, than the one provided by the query, then this is the place to change the heading names.
You can see the headings go out to column tee.
Now we will open the excel workbook.
We will change the name of the first worksheet to Data Name S.
Of course, you would call the worksheet what was appropriate for your data.
We will create a region from A one to tee one.
We will call that region Data Name H to mean data name headings.
Again, it is clear you can use your own naming standards.
Just to prove the region is there we will show it in the formula bar.
Now we connect the headings spreadsheet with the excel worbook with an arrow.
We option the arrow.
We set the parameter to send the headings in to the region name Data Name H.
We will delete the arrow from the query to the headings spreadsheet.
We will run the processing from the headings spreadsheet to the excel workbook.
We will open the Excel workbook.
And, whola, there are our headings in our Data Name H region inside the workbook.
Just by way of making this demo a little more complete.
We will create a region on line two called Data Name D, for the data name data region.
Then we will update the capsule, so that data will be sent into the data name D region.
We will just let this play out in front of you in the demo because our next demo goes through this process in some more detail.
So, as ever, please just watch the demo and you will easily understand what is happening.
We will trim the video for the actual running of 60,000 rows into the workbook just to save your time.
Now I will open the first spreadsheet, so you can see the heading results, and the number of rows that were returned.
We will open the second spreadsheet, to show you the headings have been removed, and only the data remains.
We will open the Excel workbook, to show you that the data has made it into the workbook.
As you can see, there are 60,969 rows in the workbook.
It only takes a few seconds to retreive these sixty thousand rows, and send them into the workbook.
This shows you how it is possible, to build Excel reports, and send them to the people who need them.
Meta5 is the perfect tool to be able to get data from any ODBC compliant source you want, and get it into the excel power pivot model.
This includes all the data that is available through the customised ODBC drivers offered by C Data.
C Data is seemlessly integrated with Meta5.
And with that?
I would like to say thank you very much for watching our demonstration today.
I really appreciate your time and attention.
We hope you found this video interesting and informative.
We would love to hear your comments so please feel free to use the comments section below.
If you would like to get in touch with Mihai, his details are on our contact page.
I wish you a great day.
Thank you.
Sofi.
Your BIDA AI Assistant.