This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Greetings All,
I have an SQL report from Maximo 4.1 that I run in Excel 2010. The report is working but for some reason the column I list last in the SQL populates the first column in Excel. Any thoughts?
That last column is "enteredby" (I think) and the first name in the list is "AARON". Could this be some type of Sort issue?
Thanks
GR
Hi Glen,
SQL can be a wonderful beast, so have you checked to see the output in (either) SQL Analyser or SQL Management Studio.
Further how are you getting the SQL results into Excel?
I have used a 1500 line SQL query to create a series of tables in the DB (customised to my requirements) and then run a relatively simple Excel macro to link to the DB using ODBC, which copies the tables to the required spreadsheets within the workbook.
Hope that this is helpful and if you have any question please do not hesitate to get back to me, I'll even take a look at the code if you want - those of us who are still using ver. 4.1.1 are a dying breed. :(
Regards
Stephen Jury
Maintenance Systems Analyst
eMail stephen.jury@subsea7.com<mailto:stephen.jury@subsea7.com>
Tel +441224 265028
Mob +44 7884 491930
Fax +44 1224 527000
Website www.subsea7.com<http://www.subsea7.com>
SAFETY | INTEGRITY | INNOVATION | PERFORMANCE | COLLABORATION
Please consider the environment - only print this e-mail if absolutely necessary
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of GlenR
Sent: 21 August 2013 13:25
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] When last is first?
Greetings All,
I have an SQL report from Maximo 4.1 that I run in Excel 2010. The report is working but for some reason the column I list last in the SQL populates the first column in Excel. Any thoughts?
That last column is "enteredby" (I think) and the first name in the list is "AARON". Could this be some type of Sort issue?
Thanks
GR
This message may contain confidential information which may also be legally privileged and is intended only for the use of the parties to whom it is addressed. If you are not an intended recipient you are hereby notified that any disclosure, copying, distribution or use of any information in this e-mail is strictly prohibited. If you receive this message in error please notify the sender by return e-mail and then destroy it. Further, we make every endeavour to keep our network free from viruses. However, you do need to verify that this e-mail and any attachments are free of viruses as we can take no responsibility for any computer viruses which might be transferred by way of this e-mail. All information and attachments remain the property of Subsea 7 and should be held as confidential.
Hello Stephen,
Yes I proved my code in SQL Server Management Studio and it worked as expected.
I put my code directly into Excel and have it run when I open the file.
Here's how I do it.
Under the DATA tab in the Connections section, I select Properties. To the right of the Name entry box, I click on the icon. Next I click on the Definition tab. With SQL selected in the Command Type box, I paste my code into the Command Text Box.
Under the Usage tab I select Enable Background Resresh and Refresh Data When Opening File.
My code is simple.
SELECT [transdate] AS Date_Entered
,[payrate] AS Per_Hour_Cost
,[eqnum] AS Equipment
,[wonum] AS Work_Order
,[regularhrs] As Hrs
,[linecost] AS Subtotal
,[laborcode] AS Tech
FROM [MAXIMO].[dbo].[labtrans]
Where transdate >= '01/01/2010 00:00:00'
Any thoughts why laborcode would end up in the first column and be
sorted A to Z?
Thanks
GR
--- In MAXIMO@yahoogroups.com, Stephen Jury <stephen.jury@...> wrote:
>
> Hi Glen,
>
> SQL can be a wonderful beast, so have you checked to see the output in (either) SQL Analyser or SQL Management Studio.
>
> Further how are you getting the SQL results into Excel?
>
> I have used a 1500 line SQL query to create a series of tables in the DB (customised to my requirements) and then run a relatively simple Excel macro to link to the DB using ODBC, which copies the tables to the required spreadsheets within the workbook.
>
> Hope that this is helpful and if you have any question please do not hesitate to get back to me, I'll even take a look at the code if you want - those of us who are still using ver. 4.1.1 are a dying breed. :(
>
> Regards
>
> Stephen Jury
> Maintenance Systems Analyst
>
> eMail stephen.jury@...<mailto:stephen.jury@...>
> Tel +441224 265028
> Mob +44 7884 491930
> Fax +44 1224 527000
> Website www.subsea7.com<http://www.subsea7.com>
>
> SAFETY | INTEGRITY | INNOVATION | PERFORMANCE | COLLABORATION
>
> Please consider the environment - only print this e-mail if absolutely necessary
>
> From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of GlenR
> Sent: 21 August 2013 13:25
> To: MAXIMO@yahoogroups.com
> Subject: [MAXIMO List] When last is first?
>
>
>
> Greetings All,
> I have an SQL report from Maximo 4.1 that I run in Excel 2010. The report is working but for some reason the column I list last in the SQL populates the first column in Excel. Any thoughts?
>
> That last column is "enteredby" (I think) and the first name in the list is "AARON". Could this be some type of Sort issue?
>
> Thanks
> GR
>
>
> This message may contain confidential information which may also be legally privileged and is intended only for the use of the parties to whom it is addressed. If you are not an intended recipient you are hereby notified that any disclosure, copying, distribution or use of any information in this e-mail is strictly prohibited. If you receive this message in error please notify the sender by return e-mail and then destroy it. Further, we make every endeavour to keep our network free from viruses. However, you do need to verify that this e-mail and any attachments are free of viruses as we can take no responsibility for any computer viruses which might be transferred by way of this e-mail. All information and attachments remain the property of Subsea 7 and should be held as confidential.
>
>
>
I found it! I had renamed an existing file to create this one. In the Connections Properties box I had a box checked that preserved the sorting and filtering from the previous file. Once I removed the checks it worked.
Thanks again
GR
--- In MAXIMO@yahoogroups.com, "GlenR" <grayco4@...> wrote:
>
>
>
> Hello Stephen,
> Yes I proved my code in SQL Server Management Studio and it worked as expected.
>
> I put my code directly into Excel and have it run when I open the file.
>
> Here's how I do it.
> Under the DATA tab in the Connections section, I select Properties. To the right of the Name entry box, I click on the icon. Next I click on the Definition tab. With SQL selected in the Command Type box, I paste my code into the Command Text Box.
>
> Under the Usage tab I select Enable Background Resresh and Refresh Data When Opening File.
>
> My code is simple.
>
> SELECT [transdate] AS Date_Entered
> ,[payrate] AS Per_Hour_Cost
> ,[eqnum] AS Equipment
> ,[wonum] AS Work_Order
> ,[regularhrs] As Hrs
> ,[linecost] AS Subtotal
> ,[laborcode] AS Tech
>
> FROM [MAXIMO].[dbo].[labtrans]
>
> Where transdate >= '01/01/2010 00:00:00'
>
> Any thoughts why laborcode would end up in the first column and be
> sorted A to Z?
>
> Thanks
> GR
>
> --- In MAXIMO@yahoogroups.com, Stephen Jury <stephen.jury@> wrote:
> >
> > Hi Glen,
> >
> > SQL can be a wonderful beast, so have you checked to see the output in (either) SQL Analyser or SQL Management Studio.
> >
> > Further how are you getting the SQL results into Excel?
> >
> > I have used a 1500 line SQL query to create a series of tables in the DB (customised to my requirements) and then run a relatively simple Excel macro to link to the DB using ODBC, which copies the tables to the required spreadsheets within the workbook.
> >
> > Hope that this is helpful and if you have any question please do not hesitate to get back to me, I'll even take a look at the code if you want - those of us who are still using ver. 4.1.1 are a dying breed. :(
> >
> > Regards
> >
> > Stephen Jury
> > Maintenance Systems Analyst
> >
> > eMail stephen.jury@<mailto:stephen.jury@>
> > Tel +441224 265028
> > Mob +44 7884 491930
> > Fax +44 1224 527000
> > Website www.subsea7.com<http://www.subsea7.com>
> >
> > SAFETY | INTEGRITY | INNOVATION | PERFORMANCE | COLLABORATION
> >
> > Please consider the environment - only print this e-mail if absolutely necessary
> >
> > From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of GlenR
> > Sent: 21 August 2013 13:25
> > To: MAXIMO@yahoogroups.com
> > Subject: [MAXIMO List] When last is first?
> >
> >
> >
> > Greetings All,
> > I have an SQL report from Maximo 4.1 that I run in Excel 2010. The report is working but for some reason the column I list last in the SQL populates the first column in Excel. Any thoughts?
> >
> > That last column is "enteredby" (I think) and the first name in the list is "AARON". Could this be some type of Sort issue?
> >
> > Thanks
> > GR
> >
> >
> > This message may contain confidential information which may also be legally privileged and is intended only for the use of the parties to whom it is addressed. If you are not an intended recipient you are hereby notified that any disclosure, copying, distribution or use of any information in this e-mail is strictly prohibited. If you receive this message in error please notify the sender by return e-mail and then destroy it. Further, we make every endeavour to keep our network free from viruses. However, you do need to verify that this e-mail and any attachments are free of viruses as we can take no responsibility for any computer viruses which might be transferred by way of this e-mail. All information and attachments remain the property of Subsea 7 and should be held as confidential.
> >
> >
> >
>
Hi Glen,
Great that you have fixed the problem, but I think that I can be of further help, therefore please contact me on my direct email 'stephen.jury@subsea7.com' and supply your direct email, as I'd like to forward some modifications (to your code etc) directly to you.
I'll send you some of my excel code and see if that can help by making it even more efficient.
Regards
Stephen Jury
Maintenance Systems Analyst
eMail stephen.jury@subsea7.com<mailto:stephen.jury@subsea7.com>
Tel +441224 265028
Mob +44 7884 491930
Fax +44 1224 527000
Website www.subsea7.com<http://www.subsea7.com>
SAFETY | INTEGRITY | INNOVATION | PERFORMANCE | COLLABORATION
Please consider the environment - only print this e-mail if absolutely necessary
From: MAXIMO@yahoogroups.com [mailto:MAXIMO@yahoogroups.com] On Behalf Of GlenR
Sent: 21 August 2013 23:41
To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Re: When last is first?
I found it! I had renamed an existing file to create this one. In the Connections Properties box I had a box checked that preserved the sorting and filtering from the previous file. Once I removed the checks it worked.
Thanks again
GR
--- In MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>, "GlenR" <grayco4@...<mailto:grayco4@...>> wrote:
>
>
>
> Hello Stephen,
> Yes I proved my code in SQL Server Management Studio and it worked as expected.
>
> I put my code directly into Excel and have it run when I open the file.
>
> Here's how I do it.
> Under the DATA tab in the Connections section, I select Properties. To the right of the Name entry box, I click on the icon. Next I click on the Definition tab. With SQL selected in the Command Type box, I paste my code into the Command Text Box.
>
> Under the Usage tab I select Enable Background Resresh and Refresh Data When Opening File.
>
> My code is simple.
>
> SELECT [transdate] AS Date_Entered
> ,[payrate] AS Per_Hour_Cost
> ,[eqnum] AS Equipment
> ,[wonum] AS Work_Order
> ,[regularhrs] As Hrs
> ,[linecost] AS Subtotal
> ,[laborcode] AS Tech
>
> FROM [MAXIMO].[dbo].[labtrans]
>
> Where transdate >= '01/01/2010 00:00:00'
>
> Any thoughts why laborcode would end up in the first column and be
> sorted A to Z?
>
> Thanks
> GR
>
> --- In MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>, Stephen Jury <stephen.jury@> wrote:
> >
> > Hi Glen,
> >
> > SQL can be a wonderful beast, so have you checked to see the output in (either) SQL Analyser or SQL Management Studio.
> >
> > Further how are you getting the SQL results into Excel?
> >
> > I have used a 1500 line SQL query to create a series of tables in the DB (customised to my requirements) and then run a relatively simple Excel macro to link to the DB using ODBC, which copies the tables to the required spreadsheets within the workbook.
> >
> > Hope that this is helpful and if you have any question please do not hesitate to get back to me, I'll even take a look at the code if you want - those of us who are still using ver. 4.1.1 are a dying breed. :(
> >
> > Regards
> >
> > Stephen Jury
> > Maintenance Systems Analyst
> >
> > eMail stephen.jury@<mailto:stephen.jury@<mailto:stephen.jury@%3cmailto:stephen.jury@>>
> > Tel +441224 265028
> > Mob +44 7884 491930
> > Fax +44 1224 527000
> > Website www.subsea7.com<http://www.subsea7.com>
> >
> > SAFETY | INTEGRITY | INNOVATION | PERFORMANCE | COLLABORATION
> >
> > Please consider the environment - only print this e-mail if absolutely necessary
> >
> > From: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com> [mailto:MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>] On Behalf Of GlenR
> > Sent: 21 August 2013 13:25
> > To: MAXIMO@yahoogroups.com<mailto:MAXIMO%40yahoogroups.com>
> > Subject: [MAXIMO List] When last is first?
> >
> >
> >
> > Greetings All,
> > I have an SQL report from Maximo 4.1 that I run in Excel 2010. The report is working but for some reason the column I list last in the SQL populates the first column in Excel. Any thoughts?
> >
> > That last column is "enteredby" (I think) and the first name in the list is "AARON". Could this be some type of Sort issue?
> >
> > Thanks
> > GR
> >
> >
> > This message may contain confidential information which may also be legally privileged and is intended only for the use of the parties to whom it is addressed. If you are not an intended recipient you are hereby notified that any disclosure, copying, distribution or use of any information in this e-mail is strictly prohibited. If you receive this message in error please notify the sender by return e-mail and then destroy it. Further, we make every endeavour to keep our network free from viruses. However, you do need to verify that this e-mail and any attachments are free of viruses as we can take no responsibility for any computer viruses which might be transferred by way of this e-mail. All information and attachments remain the property of Subsea 7 and should be held as confidential.
> >
> >
> >
>
This message may contain confidential information which may also be legally privileged and is intended only for the use of the parties to whom it is addressed. If you are not an intended recipient you are hereby notified that any disclosure, copying, distribution or use of any information in this e-mail is strictly prohibited. If you receive this message in error please notify the sender by return e-mail and then destroy it. Further, we make every endeavour to keep our network free from viruses. However, you do need to verify that this e-mail and any attachments are free of viruses as we can take no responsibility for any computer viruses which might be transferred by way of this e-mail. All information and attachments remain the property of Subsea 7 and should be held as confidential.
You should also be able to cut a column in exert and paste/insert to the location you want. This will not affect the data refresh an you columns should stay the way you want.