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

html - Instapaper-like algorithm -

-
does of algorithm extracts contents webpage? instapaper ? there 2 steps instapaper does: find main content block on page (excluding headers, footers, menus etc) from content block extract , format text to find content block (typically html block element, div containing key page text content) instapaper uses algorithm 1 used readability . can @ source of readability.js see what's going on, @ core tries find area on page highest text/link ratio, although has other simple scoring metrics (e.g. off top of head, things ratio of text commas, para elements etc) go heuristics. once have identified root node element, relevant content, you'll need format it, if want can pull node element containing text out of source document , insert yours, in reality you'll want remove existing styles , apply own, standard , feel. if want output nice text-only can use jericho's renderer . update1 : should mention else instapaper - follow 'pagination' links (
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