PREVIEW and DESIGN views in Report Writer - is this a bug or user problem?

lchance

is this a bug or can someone explain what i may be doing wrong? when using Report Writer - i often see differences between PREVIEW and DESIGN.

for instance, these two screenshots show this - notice the differences in DATE TIME in Preview and then in Design. or is this a non-issue and can be expected?

PREVIEW

DESIGN 

Yann

Hi,

You might have found a bug.

If you edit your Report and Select the Summarization tab, selecting a Summarization setting (Hour, Week, Month) will solve the issue.


However the Summarization options do not allow you to summarize with something lower than an hour, if you need to show e.g. each ten minutes there is a work around detailed below:

Set the Summarization parameter to one hour.

Click on Report Show SQL.

Click on the SQL Tab. Copy the query in Notepad.

Change the below line from:

Convert(DateTime,Floor(Cast(DateTime as Float)*24)/24,0) AS SummaryDateTime

To:

Convert(DateTime,Floor(Cast(DateTime as Float)*144)/144,0) AS SummaryDateTime

And the below line from:

GROUP BY Convert(DateTime,Floor(Cast(DateTime as Float)*24)/24,0)

To:

GROUP BY Convert(DateTime,Floor(Cast(DateTime as Float)*144)/144,0)

to show an entry each ten minutes.

Modify the ( DateTime BETWEEN 39814 AND 39845 ) string in the where condition:

To:


DateDiff (m, DateTime, getdate()) = 1

for the last month.

To:

DateDiff (wk, DateTime, getdate()) = 1

for the last week.

To:

DateDiff (d, DateTime, getdate()) = 30

for the last 30 days.

More info about the DateDiff function:http://msdn.microsoft.com/en-us/library/ms189794.aspx

Once the query is ready, Create an advanced SQL report.


File > New Report Select the Advanced SQL Type and click on Ok. Paste your SQL query and click on Execute/Preview. Save you report.


Let me know if that helps.

Thanks,

Yann