Microsoft Query?

i am using the following criteria in the [DateTime] field;

>=#3/9/2007# And <=#3/12/2007 11:59:59 PM#

i am using microsft query and excel to create pivot tables, i created an odbc file to connect to my local server

[DateTime] [TagName] [Value]
2007-03-09 00:00:00.000 PS002_Pump1.Daily_Runtime 1.1833188533782959
2007-03-09 00:00:00.000 PS002_Pump2.Daily_Runtime 1.4499822854995728
2007-03-09 04:59:59.947 PS002_Pump1.Daily_Runtime 0.16666463017463684
2007-03-09 04:59:59.947 PS002_Pump2.Daily_Runtime 0.13333170115947723
2007-03-09 09:59:59.893 PS002_Pump1.Daily_Runtime 0.51666033267974854
2007-03-09 09:59:59.893 PS002_Pump2.Daily_Runtime 0.48332741856575012
2007-03-09 14:59:59.843 PS002_Pump1.Daily_Runtime 0.86665606498718262
2007-03-09 14:59:59.843 PS002_Pump2.Daily_Runtime 0.86665606498718262
2007-03-09 19:59:59.790 PS002_Pump1.Daily_Runtime 1.083320140838623
2007-03-09 19:59:59.790 PS002_Pump2.Daily_Runtime 1.1333194971084595
2007-03-10 05:59:59.683 PS002_Pump1.Daily_Runtime 0.18333110213279724
2007-03-10 05:59:59.683 PS002_Pump2.Daily_Runtime 0.18333110213279724
2007-03-10 10:59:59.630 PS002_Pump1.Daily_Runtime 0.49999389052391052
2007-03-10 10:59:59.630 PS002_Pump2.Daily_Runtime 0.56665974855422974
2007-03-10 15:59:59.577 PS002_Pump1.Daily_Runtime 0.83332318067550659
2007-03-10 15:59:59.577 PS002_Pump2.Daily_Runtime 0.93332195281982422
2007-03-10 20:59:59.527 PS002_Pump1.Daily_Runtime 1.1166530847549438
2007-03-10 20:59:59.527 PS002_Pump2.Daily_Runtime 1.2833176851272583
2007-03-11 07:59:59.420 PS002_Pump1.Daily_Runtime 0.14999817311763763
2007-03-11 07:59:59.420 PS002_Pump2.Daily_Runtime 0.24999694526195526
2007-03-11 12:59:59.367 PS002_Pump1.Daily_Runtime 0.56665974855422974
2007-03-11 12:59:59.367 PS002_Pump2.Daily_Runtime 0.69999146461486816
2007-03-11 17:59:59.317 PS002_Pump1.Daily_Runtime 0.81665670871734619
2007-03-11 17:59:59.317 PS002_Pump2.Daily_Runtime 0.98332130908966064
2007-03-11 22:59:59.263 PS002_Pump1.Daily_Runtime 1.1833188533782959
2007-03-11 22:59:59.263 PS002_Pump2.Daily_Runtime 1.3666499853134155
2007-03-12 03:59:59.210 PS002_Pump1.Daily_Runtime 0.11666524410247803
2007-03-12 03:59:59.210 PS002_Pump2.Daily_Runtime 0.08333231508731842
2007-03-12 08:59:59.157 PS002_Pump1.Daily_Runtime 0.23333048820495605
2007-03-12 08:59:59.157 PS002_Pump2.Daily_Runtime 0.28332987427711487
2007-03-12 13:59:59.107 PS002_Pump1.Daily_Runtime 0.66665852069854736
2007-03-12 13:59:59.107 PS002_Pump2.Daily_Runtime 0.76665729284286499
2007-03-12 18:59:59.053 PS002_Pump1.Daily_Runtime 0.89998900890350342
2007-03-12 18:59:59.053 PS002_Pump2.Daily_Runtime 1.0166542530059814
2007-03-12 23:59:59.000 PS002_Pump1.Daily_Runtime 1.1999853849411011
2007-03-12 23:59:59.000 PS002_Pump2.Daily_Runtime 1.3833163976669312

what i would like to do is not hard code the date for the data i need, is there a way to code the dates so that microsoft query
would pull those date from sql...ie;

[DateTime]

>= CONVERT(VARCHAR(20), GETDATE()-5, 100) 12:00:00 AM and <= CONVERT(VARCHAR(20), GETDATE(), 100) 11:59:59 PM

any ideas?

Can't you just use:

WHERE [DateTime] BETWEEN GETDATE() - 5 AND GETDATE()

Chris

|||no , because you only get the data for the current date and time. I need to code this so that it will pick up all data between 12:00:00 am and 11:5:59 pm for each date.|||

Just to clarify - if you ran the query now (15-Mar-2007 20:23:32.000) then you would expect to return data from 10-Mar-2007 00:00:00.000 to 15-Mar-2007 23:59:59.999 inclusive.

Is this correct?

If so, then this could help:

WHERE [DateTime] >= CAST(CONVERT(VARCHAR(8), GETDATE() - 5, 112) AS DATETIME)
AND [DateTime] < CAST(CONVERT(VARCHAR(8), GETDATE() + 1, 112) AS DATETIME)

Chris

|||Thank You Chris, that worked like a charm...Have a great weekend.

Microsoft Please help

A report I designed looked perfect html and pdf but when exported to excel it chops off the last column when printed. Does the report grow when exported to excel? How can I fix this? If I change the margins it fixes on the excel side but then the page doesn't look right on the pdf side. Thank you in advance for any help.Microsoft any suggestions please.

Microsoft Excel Add-In for Analysis Services

Our customer is using the latest version of the Microsoft Excel Add-In for Analysis Services in a Microsoft Excel ver 10.6823.6735. When using a filter on a report that queries a SSAS 2005 (SP1) cube Excel stops responding. Does anyone have an idea what to do or what to look for? Any help is welcome.

Thanks

Hi,

By 'Stop responding',

Do you mean that excel does not display any data?

or applying the filter does not filter the necessary data?

Regards

sql

Metadata Refresh

-We are using SSIS packages for various kind of data load from excel source.

-If there are any change in the data type or format of excel, the package cries for the Metadata mismatch.

-During design time if you accept the metadata changes, all things work fine.

But in our case we have deployed the packages on Production Server, now the excel file format/data has changed. The packages are expecting a different metadata so they are not working at all.

Do you have any suggestions for the above problem?

Thanks, Vijay.

You'll have to edit the package. Metadata cannot change once it has been designed into the package.

Metadata Definition ?

Hello, i would like to know if it's possible to generate automatically a word document or an excel document that will contain all the metadata definition, for example containing the source columns names, their datatype, and the destination with their datatypes, so that it would easy to create a data dictionnary .

Thank you in advance.

Microsoft have talked about a tool called "SQL Dcoumenter" that will do this. No news about when it will be released though.

-Jamie

|||Thank you for your answer, i will do that manually.

Merging two reports into one excel workbook.

Hallo all,
Its vey urgent for me ,please help me to solve the problem.
I am having two reports in a report project. when exporting to excel , I
have to export these two reports into a single excel workbook. Each report
should be exported to different sheets in same workbook. How is it possible?
Please help me.
I am really badly in need of help
thanks in advance to all
miikaOk, it can be done. Place a Subreport control in the main report (Report1)
and refer the other report(Report2) and place a Rectangle on the subreport
and on the properties check "insert before rectangle" there by it gives
pagination, which is the main thing for creating multiple sheets. now export
the main report you get both the reports in a single workbook but 2 sheets.
Check out.
Amarnath, MCTS.
"aka" wrote:
> Hallo all,
> Its vey urgent for me ,please help me to solve the problem.
> I am having two reports in a report project. when exporting to excel , I
> have to export these two reports into a single excel workbook. Each report
> should be exported to different sheets in same workbook. How is it possible?
> Please help me.
> I am really badly in need of help
> thanks in advance to all
> miika|||Hi,
Thanks alot for your valuable suggestion.
It worked fine, but I am having parameters in the two reports.
So If I make any one of the two as main report,and the second one that is
declared as subreport cannot be run as the parameter is null.It is gtting
exported into same excel workbook in two dofferent sheets. The sub report is
displaying only header part as the parameter is null.
How can I deal with parameters?
Can you please reply be as soon as possible.
Its very urgent to fix this task.
thanks in advance
miika
"Amarnath" wrote:
> Ok, it can be done. Place a Subreport control in the main report (Report1)
> and refer the other report(Report2) and place a Rectangle on the subreport
> and on the properties check "insert before rectangle" there by it gives
> pagination, which is the main thing for creating multiple sheets. now export
> the main report you get both the reports in a single workbook but 2 sheets.
> Check out.
> Amarnath, MCTS.
> "aka" wrote:
> > Hallo all,
> > Its vey urgent for me ,please help me to solve the problem.
> > I am having two reports in a report project. when exporting to excel , I
> > have to export these two reports into a single excel workbook. Each report
> > should be exported to different sheets in same workbook. How is it possible?
> > Please help me.
> > I am really badly in need of help
> >
> > thanks in advance to all
> > miika|||And also When exported to excel,in excel sheet ,I am having the main report
page header in the subreport also.
How can I avoid the main report page header in the subreport sheet?
Can you please help me?
I also want to have the sheet1 and sheet2 names as report names.
not simply sheet1 and sheet2.
IS it possible?
Please help me as soon as possible.
waiting for your suggestions
miika
"aka" wrote:
> Hi,
> Thanks alot for your valuable suggestion.
> It worked fine, but I am having parameters in the two reports.
> So If I make any one of the two as main report,and the second one that is
> declared as subreport cannot be run as the parameter is null.It is gtting
> exported into same excel workbook in two dofferent sheets. The sub report is
> displaying only header part as the parameter is null.
> How can I deal with parameters?
> Can you please reply be as soon as possible.
> Its very urgent to fix this task.
> thanks in advance
> miika
>
> "Amarnath" wrote:
> > Ok, it can be done. Place a Subreport control in the main report (Report1)
> > and refer the other report(Report2) and place a Rectangle on the subreport
> > and on the properties check "insert before rectangle" there by it gives
> > pagination, which is the main thing for creating multiple sheets. now export
> > the main report you get both the reports in a single workbook but 2 sheets.
> > Check out.
> >
> > Amarnath, MCTS.
> >
> > "aka" wrote:
> >
> > > Hallo all,
> > > Its vey urgent for me ,please help me to solve the problem.
> > > I am having two reports in a report project. when exporting to excel , I
> > > have to export these two reports into a single excel workbook. Each report
> > > should be exported to different sheets in same workbook. How is it possible?
> > > Please help me.
> > > I am really badly in need of help
> > >
> > > thanks in advance to all
> > > miika|||Aka, sorry for the late reply. You need to have group header in subreport so
that you dont have to rely on the main header. Unfortunately you dont get the
sheet's name. it will be sheet1, sheet2 etc... If the parameters are similiar
then you can select the parameter from the subreport properties from the
expression. if not then you need to hard code it, there is no way.
Amarnath,MCTS
"aka" wrote:
> And also When exported to excel,in excel sheet ,I am having the main report
> page header in the subreport also.
> How can I avoid the main report page header in the subreport sheet?
> Can you please help me?
> I also want to have the sheet1 and sheet2 names as report names.
> not simply sheet1 and sheet2.
> IS it possible?
> Please help me as soon as possible.
> waiting for your suggestions
> miika
>
> "aka" wrote:
> > Hi,
> > Thanks alot for your valuable suggestion.
> > It worked fine, but I am having parameters in the two reports.
> > So If I make any one of the two as main report,and the second one that is
> > declared as subreport cannot be run as the parameter is null.It is gtting
> > exported into same excel workbook in two dofferent sheets. The sub report is
> > displaying only header part as the parameter is null.
> > How can I deal with parameters?
> > Can you please reply be as soon as possible.
> > Its very urgent to fix this task.
> > thanks in advance
> > miika
> >
> >
> > "Amarnath" wrote:
> >
> > > Ok, it can be done. Place a Subreport control in the main report (Report1)
> > > and refer the other report(Report2) and place a Rectangle on the subreport
> > > and on the properties check "insert before rectangle" there by it gives
> > > pagination, which is the main thing for creating multiple sheets. now export
> > > the main report you get both the reports in a single workbook but 2 sheets.
> > > Check out.
> > >
> > > Amarnath, MCTS.
> > >
> > > "aka" wrote:
> > >
> > > > Hallo all,
> > > > Its vey urgent for me ,please help me to solve the problem.
> > > > I am having two reports in a report project. when exporting to excel , I
> > > > have to export these two reports into a single excel workbook. Each report
> > > > should be exported to different sheets in same workbook. How is it possible?
> > > > Please help me.
> > > > I am really badly in need of help
> > > >
> > > > thanks in advance to all
> > > > miika