reporting services - SSRS line graph: x-axis time-scale with gaps in data -

-

i have dataset counts number of produced pallets per hour, eg

11/11/2010 22:00 --> 22
11/11/2010 23:00 --> 12
11/12/2010 00:00 --> 18
11/12/2010 01:00 --> 19
11/12/2010 03:00 --> 20

as may notice, there gap between 01:00 , 03:00 since there no data hour. data gets visualised in ssrs 2005 using graph time-scale x-axis. when graph type 'column', there no problem @ since 02:00 gets visualised no (0) value , gap visible in graph. when graph type 'line' or 'area', 02:00 visualised on graph no 0 value: there connection line between value of 01:00 , 03:00. when looking line graph, 1 conclude there production @ 02:00 not true, line connects value of 01:00 value of 03:00.

example of same data in area graph alt text(original image: http://img577.imageshack.us/img577/9616/area.jpg)

and column graph alt text(original image: http://img577.imageshack.us/img577/7590/column.jpg)

should explain problem.

does know how resolve problem? thank you!

probably, have found answer question now. others might stumble upon question. solution applicable if using sql server 2005 or above data source reports. example uses common table expressions (cte) achieve desired results mentioned in question. example mentioned below uses ssrs 2005 , sql server 2008 r2 database. ctes supported in sql server 2005 or above.

step-by-step process:

  1. create table named dbo.timescaledata , populate data provided in question. refer screenshot #1. create scripts of table provided under sql scripts section.

  2. create cte take minimum , maximum date values in table dbo.timescaledata , generate time values every hour between given range. use outer apply between cte , table dbo.timescaledata fetch data. time range doesn't have matching record assigned value 0. refer screenshot #2. query used in ssrs report create dataset. query provided ssrs dataset query section.

  3. screenshot #3 shows cte query being used create report dataset. screenshots #4 - #7 shows how chart control configured use dataset.

  4. screenshot #8 shows report output against data in sql server table.

hope helps.

sql scripts:

create table [dbo].[timescaledata](     [id] [int] identity(1,1) not null,     [datetimevalue] [datetime] null,     [palletsproduced] [int] null,  constraint [pk_timescaledata] primary key clustered ([id] asc)) on [primary] go

ssrs dataset query:

declare @startdatetime  datetime; declare @enddatetime    datetime;  select  @startdatetime  = min(datetimevalue)     ,   @enddatetime    = max(datetimevalue)    dbo.timescaledata;    cte(datetimerange) (         select  @startdatetime datetimerange     union         select  dateadd(hour, 1, datetimerange)            cte           dateadd(hour, 1, datetimerange) <= @enddatetime ) select          cte.datetimerange             ,   coalesce(tsd.palletsproduced, 0) palletsproduced            cte outer apply     (                     select  palletsproduced                        dbo.timescaledata tsd                       tsd.datetimevalue = cte.datetimerange                 ) tsd;

screenshot #1:

1

screenshot #2:

2

screenshot #3:

3

screenshot #4:

4

screenshot #5:

5

screenshot #6:

6

screenshot #7:

7

screenshot #8:

8


Comments

Post a Comment

Popular posts from this blog

android - Spacing between the stars of a rating bar? -

-
ho can set spacing beween stars? thats ratingbar: ratingbar = (ratingbar) inflater.inflate(r.layout.ratingbar, null); ratingbar.setlayoutparams(new layoutparams(layoutparams.wrap_content, layoutparams.wrap_content)); the layout: <?xml version="1.0" encoding="utf-8"?> <ratingbar xmlns:android="http://schemas.android.com/apk/res/android" style="@style/mystyle" android:layout_alignparentright="true" android:layout_height="19dp" android:layout_width="wrap_content" android:numstars="5"> </ratingbar> the style: <style name="mystyle" parent="@android:style/widget.ratingbar"> <item name="android:progressdrawable">@drawable/android_r2_ratingstar_yellow</item> <item name="android:indeterminatedrawable">@drawable/android_r2_ratingstar_grey</item> </style> thanks, cheers you have ...
Read more

c# - How to execute a particular part of code asynchronously in a class -

-
i creating speech recognition engine that's going respond user commands.i have created button enable , disable speech recognition per user convenience.i have used dispose() of speech engine disable speech recoginition.here code private void button1_click(object sender, routedeventargs e) { engineon = !engineon; if (engineon) { speechengine = speech.createspeechengine(); //speech class creates , returns new speech engine. speechengine.audiolevelupdated += new eventhandler<audiolevelupdatedeventargs>(speechengine_audiolevelupdated); // use system's default microphone speechengine.setinputtodefaultaudiodevice(); speechengine.loadgrammar(new dictationgrammar()); // start listening speechengine.recognizeasync(recognizemode.multiple); } else { speechclass.myengine.dispose(); } } but disposal of speech obj...
Read more

c# - Asterisk click to call -

-
maybe of may know how achieve this. want this: click on link/button my phone rings, pick asterisk dials number me recipient phone rings i'm using asterisk 1.2 . i tried dial out . can make call 1 side. thanks in advance. you can use call files . read: asterisk auto-dial out . i have made simple cgi script called via web server creates call file (remember use temp directory) , moves /var/spool/asterisk/outgoing , asterisk rest of work. user perspective works described. remember normalize phone numbers (on web pages can have spaces, hyphens etc, while in call file must dialable numbers).
Read more