Only display # rows per page in a SSRS report.

One problem I ran into and tried every solution on the net (that I could find) with no luck. I had to display only 22 rows per page, with blank rows -- if the data is not filled in for the other rows.

Step 1: SQL
So, first thing is to get your sql query to return, not row numbers but page numbers. So in this case, I used the row_number function found in SQL-Server subtracting 1 and then divided by the number of rows I needed per page:

((row_number() over(order by license_no)) -1) /22 as 'Page'

Step 2: Add Parent Group
In my report I needed to display a header, a footer, detail information, and empty rows if not exceeding 22. This is of course done with SSRS's Table.

First add a new parent group and in the Group Properties/General add a group on to the "Page" field. This group should span the whole page.

Don't bother with the page break sections, this will just mess things up.

Step 3: Add Child Group with blank rows
The child group is what I used to hold the blank rows.  Within that group I added 22 rows with no information in the textboxes.

Step 4: Change visibility of blank rows
Right-clicking on the row, and select Row Visibility. Select the bullet: "Show or hide based on an expression". Within each row the expression will check if the row count is less than the ((page + 1) * rows_per_page) - (rows_per_page - position)
Example (Note:"section2" is my dataset):
[Row 22 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22),false,true)
[Row 21 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22) - 1,false,true)
...


This is all that is needed to acomplish this task.

If you need to hide a row until the end, I used the following visibility expression:
=IIF(Fields!Page.Value = Last(Fields!Page.Value,"Section2"),false,true)

And of course if you need to repeat the header on each page, like I do. This is found in the Tablix Properites/General and just put a check mark in the "Repeat header rows on each page".

Precision Timer

Ran into a problem yesterday wth timing issues, this is the first time I ever needed to create a timer with extreme precision.

Note: The precision of multithreaded timers depends on the operating system, and is typically in the 10-20 milliseconds region. This class is used to generate greater precision using the P/Invoke interop and calls the Windows multimedia timer; which has a precision of 1 ms. But that increased responsiveness comes at a cost - since the system scheduler is running more often, the system spends more time scheduling tasks, context switching, etc. This can ultimately reduce overall system performance, since every clock cycle the system is processing "system stuff" is a clock cycle that isn't being spent running your application.


So here is some code I found and changed up a bit that uses the winmm.dll timesetevent:

Code Snippet

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Runtime.InteropServices;
6. using System.Diagnostics;
7.  
8. public class PrecisionTimer : IDisposable
9. {
10.  
11.     //Lib API declarations
12.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
13.     static extern uint timeSetEvent(uint uDelay, uint uResolution, TimerCallback lpTimeProc, UIntPtr dwUser, uint fuEvent);
14.  
15.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
16.     static extern uint timeKillEvent(uint uTimerID);
17.  
18.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
19.     static extern uint timeGetTime();
20.  
21.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
22.     static extern uint timeBeginPeriod(uint uPeriod);
23.  
24.     [DllImport("Winmm.dll", CharSet = CharSet.Auto)]
25.     static extern uint timeEndPeriod(uint uPeriod);
26.  
27.     //Timer type definitions
28.     [Flags]
29.     public enum fuEvent : uint
30.     {
31.         TIME_ONESHOT = 0, //Event occurs once, after uDelay milliseconds.
32.         TIME_PERIODIC = 1,
33.         TIME_CALLBACK_FUNCTION = 0x0000, /* callback is function */
34.  
35.         //TIME_CALLBACK_EVENT_SET = 0x0010, /* callback is event - use SetEvent */
36.  
37.         //TIME_CALLBACK_EVENT_PULSE = 0x0020 /* callback is event - use PulseEvent */
38.     }
39.  
40.     //Delegate definition for the API callback
41.     delegate void TimerCallback(uint uTimerID, uint uMsg, UIntPtr dwUser, UIntPtr dw1, UIntPtr dw2);
42.  
43.     private fuEvent f;
44.     private uint ms;
45.  
46.     //IDisposable code
47.     private bool disposed = false;
48.  
49.     public void Dispose()
50.     {
51.         Dispose(true);
52.         GC.SuppressFinalize(this);
53.     }
54.  
55.     private void Dispose(bool disposing)
56.     {
57.         if (!this.disposed)
58.         {
59.             if (disposing)
60.             {
61.                 Stop();
62.             }
63.         }
64.         disposed = true;
65.     }
66.  
67.     ~PrecisionTimer()
68.     {
69.         Dispose(false);
70.     }
71.  
72.     ///
73.     /// The current timer instance ID
74.     ///
75.     uint id = 0;
76.  
77.     ///
78.     /// The callback used by the the API
79.     ///
80.     TimerCallback thisCB;
81.  
82.     ///
83.     /// The timer elapsed event
84.     ///
85.     public event EventHandler Timer;
86.  
87.     protected virtual void OnTimer(EventArgs e)
88.     {
89.         if (Timer != null)
90.         Timer(this, e);
91.     }
92.  
93.     ///
94.     /// Initialize
95.     ///
96.     ///
97.     ///
98.     public PrecisionTimer(uint ms, bool repeat)
99.     {
100.         //Initialize the API callback
101.         thisCB = CBFunc;
102.  
103.         this.ms = ms;
104.  
105.         //Set the timer type flags
106.         f = fuEvent.TIME_CALLBACK_FUNCTION(repeat ? fuEvent.TIME_PERIODIC : fuEvent.TIME_ONESHOT);
107.  
108.         //Tell OS that we are about to need a precision timer.
109.         PrecisionTimer.timeBeginPeriod(1);
110.     }
111.  
112.     ///
113.     /// Stop the current timer instance
114.     /// VERY IMPORTANT TO CALL
115.     ///
116.     public void Stop()
117.     {
118.         lock (this)
119.         {
120.             if (id != 0)
121.             {
122.                 timeKillEvent(id);
123.                 Trace.WriteLine("Timer " + id.ToString() + " stopped " + DateTime.Now.ToString("HH:mm:ss.ffff"));
124.                 id = 0;
125.             }
126.         }
127.  
128.         //Tell OS that we are done using the precision timer and that it can continue back to normal.
129.         PrecisionTimer.timeEndPeriod(1);
130.     }
131.  
132.     ///
133.     /// Start a timer instance
134.     ///
135.     /// Timer interval in milliseconds
136.     /// If true sets a repetitive event, otherwise sets a one-shot
137.     public void Start()
138.     {
139.         //Kill any existing timer
140.         //Stop();
141.  
142.         lock (this)
143.         {
144.             id = timeSetEvent(ms, 0, thisCB, UIntPtr.Zero, (uint)f);
145.             if (id == 0)
146.                 throw new Exception("timeSetEvent error");
147.             Trace.WriteLine("Timer " + id.ToString() + " started " + DateTime.Now.ToString("HH:mm:ss.ffff"));
148.         }
149.     }
150.  
151.     void CBFunc(uint uTimerID, uint uMsg, UIntPtr dwUser, UIntPtr dw1, UIntPtr dw2)
152.     {
153.         //Callback from the PrecisionTimer API that fires the Timer event. Note we are in a different thread here
154.         OnTimer(new EventArgs());
155.     }
156. }

An example of calling the class:

PrecisionTimer timer = new PrecisionTimer(8500, false); //will time for 8.5 seconds before triggering an event
timer.Timer += new EventHandler(timer_Timer);

timer.Start();
//Do Stuff or something until event

My event handler

void timer_Timer(object sender, EventArgs e){

timer.Stop();

DoStuff();

}

List Creator

This is one of my favorite pieces of code I've stolen borrowed from another site. I've had made some minor code changes and additions from the original, but basically I just need to send in a DataReader after running a sql command and it will return a list of objects of that type. (Very similar to Linq to Sql)

You just need to make sure that anything that is return through the query is defined in the object as a write property.

So here is the List Creator code, which does the creation via reflections:

Code Snippet

1. public class ListCreator where T : new()
2. {
3.      public List FindAll(IDataReader iDataReader)
4.      {
5.          List returnList = new List();
6.  
7.          try
8.          {
9.              //need a Type and PropertyInfo object to set properties via reflection
10.              Type tType = new T().GetType();
11.              PropertyInfo pInfo;
12.  
13.              //x will hold the instance of until it is added to the list
14.              T x;
15.  
16.              //use reader to populate list of objects
17.              while (iDataReader.Read())
18.              {
19.                  x = new T();
20.  
21.                  //set property values
22.                  //for this to work, command’s column names must match property names in object
23.                  for (int i = 0; i < iDataReader.FieldCount; i++)
24.                  {
25.                      pInfo = tType.GetProperty(iDataReader.GetName(i));
26.  
27.                      pInfo.SetValue(x, (iDataReader[i] == DBNull.Value? null:iDataReader[i]), null);
28.                  }
29.  
30.                  //once instance of is populated, add to list
31.                  returnList.Add(x);
32.              }
33.          }
34.          catch (Exception ex)
35.          {
36.              Logging.Logging.Error(ex.ToString());
37.          }
38.  
39.          return returnList;
40.      }
41. }

So in my DAL I might have something like:

Code Snippet

1. public List<PartsOrdering> GetAllPartsOrdering()
2. {
3.    List<PartsOrdering> li = new List<PartsOrdering>();
4.    try
5.    {
6.       using (SqlConnection cn = new SqlConnection(connString))
7.       {
8.           ListCreator<PartsOrdering> PartsOrders = new ListCreator<PartsOrdering>();
9.           SqlCommand cmd = new SqlCommand("dbo.selPartsOrdering", cn);
10.           cmd.CommandType = CommandType.StoredProcedure;
11.           cn.Open();
12.           li = PartsOrders.FindAll(ExecuteReader(cmd));
13.       }
14.   }
15.   catch (Exception ex)
16.   {
17.      Logging.Error(ex.ToString());
18.   }
19.   return li;
20. }

Code Snippet

1. [Serializable]
2. public class PartsOrdering : IFormattable
3. {
4.     #region Properties
5.     [DefaultValue(-1)]
6.     public int PartsOrderingID
7.     {
8.         get;
9.         set;
10.     }
11.     [DefaultValue("")]
12.     public string ToolID
13.     {
14.         get;
15.         set;
16.     }
17.     [DefaultValue("")]
18.     public string PartNumber
19.     {
20.         get;
21.         set;
22.     }
23.     [DefaultValue("")]
24.     public string Description
25.     {
26.         get;
27.         set;
28.     }
29.     [DefaultValue(0)]
30.     public int Quantity
31.     {
32.         get;
33.         set;
34.     }
35.     [DefaultValue(1)]
36.     public int PartsOrderingLocationID
37.     {
38.         get;
39.         set;
40.     }
41.     [DefaultValue(1)]
42.     public int PartsOrderingUrgencyID
43.     {
44.         get;
45.         set;
46.     }
47.     [DefaultValue("")]
48.     public string Comment
49.     {
50.         get;
51.         set;
52.     }
53.     public DateTime RequestedDateTime
54.     {
55.         get;
56.         set;
57.     }
58.     [DefaultValue("")]
59.     public string RequestedUser
60.     {
61.         get;
62.         set;
63.     }
64.     public string LocationName
65.     {
66.         get
67.          {
68.              List&lt;PartsOrderingLocation&gt; locations = new PartsOrderingLocation().List();
69.              return locations.Where(n =&gt; n.PartsOrderingLocationID.Equals(this.PartsOrderingLocationID)).Select(n =&gt; n.Name).First();
70.          }
71.         set
72.          {
73.              List&lt;PartsOrderingLocation&gt; locations = new PartsOrderingLocation().List();
74.              this.PartsOrderingLocationID = locations.Where(n =&gt; n.Name.Equals(value)).Select(n =&gt; n.PartsOrderingLocationID).First();
75.          }
76.  
77.     }
78.     public string UrgencyName
79.     {
80.         get
81.          {
82.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
83.              return locations.Where(n =&gt; n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n =&gt; n.Name).First();
84.          }
85.         set
86.          {
87.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
88.              this.PartsOrderingUrgencyID = locations.Where(n =&gt; n.Name.Equals(value)).Select(n =&gt; n.PartsOrderingUrgencyID).First();
89.          }
90.     }
91.     public int UrgencyRank
92.     {
93.         get
94.          {
95.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
96.              return locations.Where(n =&gt; n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n =&gt; n.Rank).First();
97.          }
98.     }
99.     [DefaultValue(-1)]
100.     public int PartsOrderingGroupID
101.     {
102.         get;
103.         set;
104.     }
105.     [DefaultValue(true)]
106.     public bool IsActive
107.     {
108.         get;
109.         set;
110.     }
111. }

How to update large count of rows without locking them

Found this interesting, how to update large databases, so that you don't have to lock rows
http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx

So instead of the usual:

 UPDATE dbo.Foo

SET Column = 'Value'

Try:

DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)

DECLARE @var INT

SELECT @var=0 -- this resets @@ROWCOUNT=1

WHILE @@ROWCOUNT >0

BEGIN

UPDATE TOP(1500) BAR

  SET Column='Value'

OUTPUT inserted.PK_ID

INTO  @UpdatedRows

FROM  dbo.BAR as BAR

WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)

END

I prefer one of the ways the commentator offered: (easier to follow)

WHILE @@ROWCOUNT >0

BEGIN

 UPDATE TOP(1500) BAR

   SET Column='Value'

 WHERE

   Column <> 'Value'

END

Converting XPS to Bitmap

One thing I had to do recently, for a SSRS report, was attach a 3 page pdf to a notice that is mailed out. Since, I can't just attach a pdf directly to the report, I figured I'll just take the images.

So, first thing I did was print the pdf to a XPS file. Now I just need to take that XPS file and convert it to some type of usable image that SSRS can recognize.

Found a solution on one of the MSDN message boards; however, I had to make some updates to it:

Code Snippet

1. static public void SaveXpsPageToBitmap(string xpsFileName)
2. {
3.     DirectoryInfo di = new DirectoryInfo(xpsFileName);
4.     XpsDocument xpsDoc = new XpsDocument(xpsFileName, System.IO.FileAccess.Read);
5.     FixedDocumentSequence docSeq = xpsDoc.GetFixedDocumentSequence();
6.  
7.     //DocumentReferenceCollection drc = docSeq.References;
8.     for (int i = 0; i < docSeq.DocumentPaginator.PageCount; i++)
9.     {
10.         DocumentPage docPage = docSeq.DocumentPaginator.GetPage(i);
11.         BitmapImage bitmap = new BitmapImage();
12.         RenderTargetBitmap renderTarget = new RenderTargetBitmap((int)docPage.Size.Width, (int)docPage.Size.Height, 96, 96, PixelFormats.Default);
13.     
14.         renderTarget.Render(docPage.Visual);
15.  
16.         BitmapEncoder encoder = new BmpBitmapEncoder();
17.         encoder.Frames.Add(BitmapFrame.Create(renderTarget));
18.  
19.         FileStream pageOutStream = new FileStream(di.FullName.Substring(0,di.FullName.Length - 4) + "_Page_" + (i+1).ToString() + ".bmp", FileMode.Create, FileAccess.Write);
20.         encoder.Save(pageOutStream);
21.         pageOutStream.Close();
22.     }
23. }

So this program works nicely, only problem I currently have is that I have to convert these large size bitmaps (~5MB per page) to something more reasonable. Especially, since the SSRS deployment gives me a SOAP error because of the size. I'll probably just end up converting them to Jpeg later on.

Creating an Psion Tecklogix Scan Gun App

In 4 simple and easy to follow steps:

Step 1: Getting Psion Resources

You will have to register to get access to the page.
(https://teknet.psionteklogix.com/ptxCMS/Teknet.aspx?s=us&p=DevKits)

Download and install their "Mobile Devices SDK"


Step 2: Create Project

If you go to the "Smart Device" section, you will notices a "Psion Teklogix Device Project". This add the Psion TeklogixNet reference and also the PtxSdkCommon.dll in the root directory, which is a dependency when running your app on device.
















Step 3: Add Items to the Toolbar, if needed

Right-click on the Toolbar, and select Choose Items.
Browse to C:\Program Files\Psion Teklogix\Mobile Devices SDK V3.1\DotNet2 and select the PsionTeklogixNet.dll

This should add:




If not already done so, drag each of those items onto the form.

















The default app gives a good example, it uses the ScanCompleteEvent; which send in a string value representation of the barcode and places it text into a textbox for the user to see.

Step 4: Deploying

Code Snippet

1. /* $Revision 1.1.1.1 */
2. /* Copyright Psion Teklogix Inc. 2007 */
3. /*
4. * File: Form1.cs
5. *
6. * Description:
7. * Implementation file for Form1 class in PtxApp1
8. *
9. */
10. using System;
11. using System.Collections;
12. using System.Collections.Generic;
13. using System.ComponentModel;
14. using System.Data;
15. using System.Drawing;
16. using System.Runtime.InteropServices;
17. using System.Text;
18. using System.Windows.Forms;
19. using PsionTeklogix.Barcode;
20. using PsionTeklogix.Barcode.ScannerServices;
21.  
22. /*! <summary>
23. Contains the class and functions related to PtxApp1 scanner application
24. </summary>
25. */
26. namespace PtxApp1
27. {
28.     /*
29.     * Form1
30.     *
31.     */
32.     /// <summary>
33.     /// The Form1 class generates the Graphical User Interface for PtxApp1.
34.     /// </summary>
35.     public partial class Form1 : Form
36.     {
37.         /*
38.         * InitializeScanner
39.         *
40.         */
41.         /// <summary>
42.         /// Initialize components for PtxApp1.
43.         /// </summary>
44.         private void InitializeScanner()
45.         {
46.             scanner.Driver = scannerServicesDriver;
47.             scanner.ScanCompleteEvent += new ScanCompleteEventHandler(scanner_ScanCompleteEvent);
48.         }
49.  
50.         /*
51.         * Form1
52.         *
53.         */
54.         /// <summary>
55.         /// Required for Windows Form designer support.
56.         /// </summary>
57.         public Form1()
58.         {
59.             try
60.             {
61.                 InitializeComponent();
62.                 InitializeScanner();
63.             }
64.             catch (Exception ex)
65.             {
66.                 MessageBox.Show("Failed to initialize component: " + ex.ToString());
67.                 this.Close();
68.             }
69.         }
70.  
71.         /*
72.         * Button1_Click
73.         *
74.         */
75.         /// <summary>
76.         /// This method is called when the scan button is clicked and will scan the
77.         /// barcode.
78.         /// </summary>b
79.         /// <param name="sender">
80.         /// The calling object that represents the user that sent the message.
81.         /// </param>
82.         /// <param name="e">
83.         /// Contains event data of the Scan Button control
84.         /// </param>
85.         private void button1_Click(object sender, EventArgs e)
86.         {
87.             try
88.             {
89.                 scanner.Scan();
90.             }
91.             catch (Exception ex)
92.             {
93.                 MessageBox.Show("Scan error: " + ex.ToString());
94.                 this.Close();
95.             }
96.         }
97.        
98.         /*
99.         * Scanner1_ScanCompleteEvent
100.         *
101.         */
102.         /// <summary>
103.         /// This method is called when the scan complete event occurs. The method is
104.         /// called by its respective handler and then displays a text representation
105.         /// of the barcode on the display.
106.         /// </summary>
107.         /// <param name="sender">
108.         /// The calling object that represents the user that sent the message.
109.         /// </param>
110.         /// <param name="e">
111.         /// Contains event data once the scan is complete
112.         /// </param>
113.         delegate void scanner_ScanCompleteDelegate(object sender, ScanCompleteEventArgs e);
114.  
115.         private void scanner_ScanCompleteEvent(object sender, ScanCompleteEventArgs e)
116.         {
117.             if (!InvokeRequired)
118.             {
119.                 textBox1.Text = e.Text;
120.             }
121.             else
122.             {
123.                 Invoke(new scanner_ScanCompleteDelegate(scanner_ScanCompleteEvent),
124.                 new object[] { sender, e });
125.             }
126.         }
127.     }
128. }

When deploying, remember to set the device to PtxPxa27c: ARMV4I_Release

P.S.
The size of the form shouldn't be bigger than 245 x 300

Project Euler Problem 1 (C# vs F#)

The problem:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

C#

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Diagnostics;

namespace Euler

{

class Program

{

//Add all the natural numbers below one thousand that are multiples of 3 or 5.

//0.001 seconds

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

sw.Start();

int total = 0;

for (int i = 1; i <>

{

if (i % 3 == 0 || i % 5 == 0)

{

total += i;

}

}

Console.WriteLine("Answer: " + total);

sw.Stop();

Console.WriteLine(sw.Elapsed);

Console.WriteLine(sw.ElapsedTicks);

Console.ReadLine();

}

}

}

The F# solution:

F#

#light

//0096404 milliseconds

open System.Diagnostics

let stopWatch = new Stopwatch()

stopWatch.Start()

printfn "%A" (List.sum(List.filter (fun n-> (n % 3) = 0 or (n % 5) = 0) [1 .. 999]))

stopWatch.Stop();

printfn "%A"stopWatch.Elapsed

printfn "%A"stopWatch.ElapsedTicks

open System

Console.ReadKey(true)

C# does it in

0.001 seconds

3606 ticks

F# does it in

0.0096404 seconds

24380 ticks

There is probably some more efficient way of doing the F#, this was however my first attempt at learning the language. Will have to see how it can do the more tougher problems.

Link Server limited return row size from Informix

So I ran into the following error while trying to run a query.

"Maximum output rowsize (32767) exceeded"

The problem with this, was that I was using a SQL SERVER link server, called ETS, to an Informix database. The problem was that I could not limit the size on the "Notes", so basically I ended up using an exec and open query just to accomplish this task.

declare @Query varchar(max)

set @Query = N'select

wrkhdr.region_no,

notif.atten,

notif.dba,

notif.owner,

notif.addr1,

notif.addr2,

notif.city,

notif.state,

notif.country,

notif.postal_code,

notif.notif_id,

notif.notif_date,

notif.start_date,

notif.end_date,

wrkhdr.create_user,

wrkhdr.rid,

wrkhdr.license_no,

notif.license_status,

rtrim(CAST(notes.note as nvarchar(250)))

FROM

notification notif

join workq_hdr wrkhdr ON wrkhdr.workq_id = notif.workq_id

left join document_header ON document_header.filing_id = wrkhdr.filing_id

left join document_header_notes ON document_header.recno = document_header_notes.document_header_notes_id

left join notes ON document_header_notes.notes = notes.note_id

where notif.notif_id = ' + convert(varchar(30),@Parameter1)

set @Query = N'select * from openquery(ets2, ''' + REPLACE(@Query, '''', '''''') + ''')'

exec (@Query)