Monday, August 8, 2011

Basic XBee on NETMF

As I wait for parts for the Omnicopter, one of the things I’m getting started is the XBee radio communications between the remote/computer and the copter.  My initial design will attempt to use XBee radios for all control of the copter as well as in sharing diagnostic & control data between the base station [remote & or PC] and the copter.  I fear that XBee may end up not being responsive enough and have enough throughput to accomplish all things but that’s where I’m going to start before attempting traditional R/C radio techniques.

Xbee Series 1I want to be able to control the Omnicopter both by a remote control I’m building based on a NETMF microcontroller and with software on a PC.  The most basic of things is to make sure that the NETMF microcontroller I’ve chosen for the copter, a GHI FEZ Panda-II, can communicate with my PC. 
For the Panda-II I have bought and assembled an Adafruit Protoshield and an Adafruit XBee Adapter.  I already have some XBee Modules (series 1) from a previous project.  So, I’m using them for now.  Once I’ve proven the copter can fly and I’m brave enough to push the range limits of the XBee series 1 (300 ft.) then I can simply buy new XBee Pro radios and drop them in place of the existing radios to get a range of 6-15 miles (line of sight).
To test communications, two things are required – an XBee connected to the PC and a NETMF microprocessor with an XBee and the necessary software on both sides.

Setting Up An XBee on NETMF

An XBee radio communicates using basic RS232 type of communications.  All that’s required to connect it to the Panda-II is to give it 5V power, ground, and transmit & receive signals.
XBee Test on Panda-II w Caption

For a better view of the wiring connections, I drew it up in Fritzing.
XBee Test on Panda-II.Fritzing

Note that the receiver (RX) on the XBee is connected to the transmitter (TX) on the Panda-II and vice-versa.  This may not be obvious to those first getting started.

Next, we’ll need a NETMF C# program to install on the Panda-II.  Visual Studio 2010 (VS2010) Express or better can be used to compile and deploy the program.  GHI has a great tutorial on getting started if this is your first time to create a NETMF program.  So, I’ll jump straight to the code.  I’m using the MFToolkit open source code by Michael Schwartz to simplify the code required considerably.  An alternative assembly that is somewhat easier to understand and seems to satisfy basic XBee needs is Grommet.  Since I expect this project to get much more sophisticated over time, I’ve decided to stick with the MFToolkit. 

Once you download the MFToolkit (or Grommet) code from Codeplex and create a new NETMF C# project, you can simply copy this code into your project to have the NETMF device send a “ping” every 5 seconds.  The ping is basically the word “Hello” plus the current time in Ticks.  The following code is not meant to be “production ready”.  It’s just enough to test that the XBee is working.
public static void Main()
{
    var ledBlinkerThread = new Thread(BlinkOnboardLed);
    ledBlinkerThread.Start();

    var pingThread = new Thread(SendPingWithMFToolkit);
    pingThread.Start();
}

private static void SendPingWithMFToolkit()
{
    var xbee = new SerialPort("COM1", 115200, Parity.None, 8, StopBits.One);
    xbee.Open();
    xbee.DataReceived += XBeeDataReceived;

    while (true)
    {
        string output = "Hello " + DateTime.Now.Ticks;
        byte[] bytes = System.Text.Encoding.UTF8.GetBytes(output);
        xbee.Write(bytes, 0, bytes.Length);
        xbee.Flush();
        Thread.Sleep(5000);
    }
}

static void XBeeDataReceived(object sender, SerialDataReceivedEventArgs e)
{
    int bytesReceived = ((SerialPort)sender).BytesToRead;
    var bytes = new byte[bytesReceived];
    ((SerialPort)sender).Read(bytes, 0, bytes.Length);
    var received = new string(System.Text.Encoding.UTF8.GetChars(bytes));
    Debug.Print(received);
}
Note that the second parameter of the SerialPort constructor in SendPingWithMFToolkit() specifies the baud rate of the XBee radio.  By default, the radios will be set to 9600.  I have changed mine to 115200 prior to this writing.  If this is your first attempt with XBee then you should change this parameter to 9600 or whatever your actual baud rate is if yours have been changed to something different.  The important thing to know is that both radios must be set to the same baud rate and the software on both sides is configured likewise.

At this point you should be able to compile and run your code from the Panda-II without issue and it will start sending it’s “ping”.  Also, although I am using the Panda-II for this post just about any Arduino shield compatible NETMF device such as the netduino should also work w/o any change to the code or wiring.


Setting Up An XBee on a PC

Setting up an XBee on a PC is a bit simpler since all we really have to do is connect the XBee to a breakout or dev board with a USB cable and install the X-CTU software by Digi.  No reason to write our own code for this test since there already exists this great utility that does everything we need to test & configure an XBee.  I am using Windows 7 Ultimate (x64) but the steps are basically the same for any OS.

XBIB-U-DEV w caption

I have a MaxStream (now Digi) XBIB-U-Dev (Rev 3) XBee development board from a previous project that I’m using, but you can use just about any XBee adapter including the one from Adafruit that we used on the NETMF.  On my board, I’m able to plug a USB cable directly into it.  On most of the basic adapters, you will need an FTDI cable to convert the serial communications to USB.  You will also need to locate and install the appropriate drivers for your OS.

imageOnce you have your hardware all figured out, we need to download and install the X-CTU software.
After you install X-CTU, run it and it should come up with something similar to this except the Baud setting will probably be at 9600.  Set the baud rate to match that of your modem and hit the “Test/Query” button to see if X-CTU can connect to your XBee radio.  Assuming you can connect and all is good then we’ll now see if we are receiving the “ping” from our NETMF device.  Click on the “Terminal” tab and wait 5 seconds.  You should see some text appear in red on the screen and more to be added every 5 seconds.

If we have this then we know we are properly sending from the NETMF device and receiving by the PC.  Next thing to test is that we can send from the PC and receive from the NETMF device.

First make sure that we are running our program on the NETMF device through the VS2010 debugger and not just on the board itself.  You’ll notice on our NETMF code that we are capturing XBee received data with the XBeeDataReceived() handler function.  Anytime data is received, it will be simply printed to the Output Window.

imageTo send a packet of data from the PC we hit the “Assemble Packet” button in the X-CTU Terminal screen.  We’ll send a simple “Hello World” packet.  Type that in the textbox and hit “Send Data”.  You should see “Hello World” printed in blue text in the X-CTU console and in the VS2010 output window you should see the same text also printed there.

Mission accomplished.  We have established the most basic of communications and know that all of our hardware is setup properly and is working.  Now the challenge is for you to figure out how to best utilize this power in your project by designing the data you will send and how to handle it properly when it is received.

If you are still having issues with hardware or software, you should start by checking the forums at any of the following sites.
Good luck and happy transmitting!

Sunday, August 7, 2011

Omnicopter - Getting Off The Ground

So, I've decided to start up another .NET Micro Framework (NETMF) challenge after taking almost two years off from the tiny framework.  Some things have changed since I last used NETMF and I'm having to re-learn some things.  Sadly, one thing that hasn't changed so much is the lack of abundance of documentation on some of the basic things required to get some very basic electronics goals achieved with the framework.  So, as I start on this next project I plan to document some of the things that are either not already documented well somewhere else.

Unlike many NETMF developers I do not have a degree in electrical engineering.  I'm a software guy.  In all disclosure, I did spend three years studying EE at TTU before deciding to change to computer science so I do have a little more understanding of electronics than your average bear but I've never practiced it professionally and a lot of the language used in the NETMF forums and literature is a bit alien to me.  So, as I work through this project and figure some of these things out I'm going to try and explain them as simply as possible to others such as myself.  I believe the popularity of NETMF is only going to grow and one of the biggest set of new users is going to be software developers similar to myself and young roboticists.  Both groups I believe will benefit.

So, a brief intro to the project that I've decided to tackle.  I'm going to build a multi-propeller helicopter (i.e. quadcopter, quadrotor, hexacopter, octocopter, etc.)  In the first stages, it will start out as a quadcopter but I plan for that to change as progress is made.  More about that when the time comes.  Why build a quadcopter?  Uh...they're really cool!  But beyond basic flight, I have a lot of AI related ideas that I want to work on and the copter will be a great platform for developing those applications.  Equally important is that there really doesn't exist yet a quadcopter built on NETMF that I'm aware.  It seems that most people shy away or give up quickly because NETMF isn't a "real-time" platform and that it can't send commands quick enough to enable stability of a copter.  I hope to prove them wrong.  Maybe they'll prove me wrong but I don't think so.  If they do then I'll be sure to document in painful detail why NETMF isn't a valid platform for copters.

I don't want to give up too much too soon on my plans for the project since I don't even know for sure that it will ever even leave the ground.  So, for now there are two parts to this project:
  1. Build a NETMF controlled quadcopter
  2. Build a NETMF based remote control.
I've decided to name my copter the Omnicopter - the copter with many propellers and many functions.
 
Unfortunately, it seems the only place you can really get the copter motors and other R/C parts is from Hong Kong (HobbyKing.com).  So, there is a 4-6 week wait for parts. Ugg.  I've ordered the motors and other R/C parts and they're on their way.  While I wait for them to arrive, I'm doing some basic things with parts I can either acquire locally or order from the U.S. that arrive in a few days.  I'm building the basic frame for the copter & figuring out some basic wireless communications and protocols that I want to use and playing with LEDs when I get tired of doing the real work.  I'll cover all these things in future blog posts.  Let's go have some fun!

Wednesday, May 25, 2011

Customer Service - done RIGHT!

These days it’s very rare for me to have a customer service experience that truly is satisfying.  In fact, this is the first customer service experience I’ve had in my lifetime that I felt the need to put pen to paper (well, fingers to keyboard…) and tell the world about it.  It’s certainly still possible to occasionally get good customer service when going to a local store such as Lowes and approaching someone face-to-face.  But, what I’m talking mostly about is online or over-the-phone customer service.
All companies today are doing everything possible to cut costs and one seemingly obvious way to do this is by utilizing more and more technology to streamline customer service and eliminate as many people (that have to be paid wages…) out of the process as possible.  Usually, this just means that we as customers have to deal with reduced quality in service due to shouting at the phone to make it understand what we want or answering the same questions multiple times.  Why does the automated system make me type in my account number and then not share that info with the real person when I finally get to talk to them???  Maybe companies are saving money but they ARE NOT providing a better customer service experience.
logo-logitechOne company that I recently had an experience with has figured out how to use technology to properly streamline customer service to produce a service that is truly superior than the old way of just talking to someone over the phone is Logitech.
Harmony900We recently built a new home and as part of the design we decided to put all the A/V equipment in a closet out of site.  The problem with this design is that it requires that you have a RF remote that can work through walls.  When we made this decision I was unaware at how few options there exist for universal remotes that have this capability and how expensive the ones that do exist can cost.  After much research, I decided to go with the Logitech Harmony 900.  It is a truly wonderful remote that I highly recommend but that’s a review for another blog post…
So, we had the remote for about a month before the screen turned white and would not operate any longer (WSOD – white screen of death?).  I researched all the internet posts about it and some had been able to fix it with a BIOS upgrade but this wouldn’t work for me since the remote was also no longer detected by my computer.  All of the programming and configuration of this remote is done through your computer and the settings are all saved on Logitech’s website.  A nice feature that I didn’t fully appreciate at the time…
So, after exhausting my abilities and what little info was available on the internet regarding how to fix the problem I dreadfully went to the Logitech Customer Support site one night at around 8:30PM CST and filled out a short form that basically asked for the serial number and a description of the problem.  I submitted it and expected to get an automatic email back telling me to do everything that I’d already done and to start a dreadful back and forth “conversation” that would eventually end a month later with me having to send the remote to them and having to pay to get it fixed.
Did I mention that I bought this remote “used” on eBay for about half the retail cost?  Yea, that’s why I was dreading this whole thing so much.  I knew for sure that as soon as they asked where I bought it from that any warranty would be void and I would then be stuck having to pay to get it fixed.
To my surprise, about an hour after I submitted the form I got an email back from a Logitech “Customer Care” person (thanks Devin!)  The email did of course do as I expected and suggested that I try upgrading the firmware.  However, what I did not expect was that it also said the following…
[copied from the actual email from Logitech Customer Care]
If the firmware update does not fix this problem, then I do apologize for the inconvenience, but there does appear to be a hardware issue with your Harmony remote. Before I can submit your warranty claim, I would require some additional information. I would kindly ask that you reply back to this email with the following information:   1. Full Name: 2. Street Address (No PO Boxes please): 3. City/Town: 4. State: 5. Zip Code: 6. Country: 7. Phone: 8. Harmony remote PID (Found within the battery compartment): 9. Harmony remote P/N (Found within the battery compartment):   In addition to the above information, we will require digital photos of your Harmony remote's LCD screen. When submitting your photos, I would ask you to follow the guidelines outlined below:   1. Please place a piece of paper beside/underneath the remote with your reference number 110519-001728. 2. Set digital camera to Macro. 3. Ensure your Harmony remote is powered on. 4. Take 2 pictures. One with the remote powered on and the other with the remote powered off. 5. Take images from twelve inches directly above the remote, featuring the LCD display. 6. Submit the digital images as a jpeg. 7. Ensure the file size is on the smaller side, but the digital image allows us to view the remote's LCD screen. 8. Ensure the filenames are short and in one-string. i.e. remoteimage.jpg remoteimage2.jpg 9. Submit the digital images as an attachment with your reply and do not paste the image inline. 10. Please don't use the flash.
harmony900_whitescreenNote that they never asked where I bought the remote and they are making excellent use of technology by having me send them pictures of the problem rather than having me pay to ship the item back to them for them to confirm that it’s doing what I said it was.  So, I took the pictures and sent the info as requested.  I then expected to get the email back that said “it appears that you actually have a problem, please send the remote back to us and we’ll fix it or send you another one.”  Note the case.  This is what I got back…
Dear Richard, Thank you for contacting Logitech Customer Care about your Universal Remotes Your Return Merchandise Authorization number is: RMA 110519-001728 Your order has been submitted for processing today. You will receive the tracking number in 1 -2 business days We do not require you to return the defective unit. Please note that your original defective unit has been disabled from our system. Any update on this product is not possible. You can use it with the existing configuration until you receive the replacement product. Kindly dispose of the unit after you receive the replacement product. If you have any questions about your RMA or this process, please reply to this e-mail. Thank you for your patience.
WOW!  They’re just going to send me a new remote.  Easy as that.  No 500 questions or paying the shipping fees or anything.  They’re just going to stand behind their product and provide true customer service.  That is what I call “Customer Care”.  Also, notice again the wonderful use of technology.  Because all of the settings for the remote are kept on their website and that’s the only way to program the remote, they are able to use that technology to disable my old remote and therefore prevent people from taking advantage of the system and re-selling damaged remotes or faking a good remote as a damaged one.  Because they can do this they’re saving me the cost of shipping and them the cost of handling the remote once its returned.  A truly wonderful solution.
But wait…there’s more.  So, 2 or 3 days later I get a box in the mail from Logitech.  I thought, “wow!  that was fast!” and proceeded to open the box expecting to find just a remote.  Again, they exceeded all my expectations.  Inside was a full retail boxed product – remote, charging base, battery, power cords, USB cord, RF repeaters – everything.  So, now I have an extra battery and charging dock if those should fail.  The old RF repeater doesn’t seem to work with the new remote so I had to swap that out.  However, I’m not convinced that I couldn’t get it to work if I should ever need to utilize an extra repeater.
So, that’s my experience that I wanted to share and spread some of the love I recently received from Logitech.  Although, historically, I’ve not been big fan of Logitech (I always buy Microsoft mice & keyboards) that has changed.  I’m now their biggest fan and will be checking out their other products first when I need more electronics.
My hope in writing this is that other companies will take notice and follow their example and try to focus more on trying to produce better “customer care” solutions that provide a better experience (and more loyal customers) and fewer “customer service” solutions that actually serve the company’s bottom line more than they serve the customer.
Go Logitech!
[8/26/2011 UPDATE]
Unfortunately, the replacement remote quit working and I had to go through this procedure again.  It was just as easy the second time around and I have nothing but wonderful things to say about Logitech support.  Unfortunately, I don't know that I can continue to sing praises about the Harmony 900 remote.  When it works it's the best thing since sliced bread.  But, it seems to have some quality issues in the electronics and for what it costs it should either be much more reliable or have a much longer warranty.  Unfortunately, there really aren't any other options for RF universal remotes in the same price range.  If you know of any, please link to it in the comments.

Wednesday, February 2, 2011

Oracle View Columns Info

Today I had a need to be able to list the columns and their data types for a view that exists in Oracle.  Since I know TOAD displays this info in a grid in the schema view, I figured the easiest way to solve this problem would be to setup a trace and see how it does it…  Here are the results in case you or I need this again in the future.
select 	cols.column_id		
        ,cols.column_name as name
        ,nullable
        ,data_type as type	
        ,decode( data_type
                ,'CHAR'         ,char_length
                ,'VARCHAR'      ,char_length
                ,'VARCHAR2'     ,char_length
                ,'NCHAR'        ,char_length
                ,'NVARCHAR'     ,char_length
                ,'NVARCHAR2'    ,char_length
                ,null) as nchar_length
        ,decode( data_type ,'NUMBER' ,data_precision + data_scale, data_length ) as length
        ,data_precision as precision
        ,data_scale as scale
        ,data_length as dlength
        ,data_default  
        ,' ' comments 
        ,data_type_mod
        ,cols.char_used
        ,initCap(histogram) as histogram
        ,num_distinct
from    all_tab_columns cols
where 1=1
  and cols.table_name = :TABNAME
  and cols.owner = :OWNNAME
order by column_id 

Monday, January 18, 2010

I’m a Semi-Finalist in the embeddedSpark 2010 Challenge!

embeddedSparkThe results were announced on January 15th.  I never received an email, so I assumed that I didn’t make the cut.  Thought about it this morning and decided to wander over to the site and look for any announcements and low & behold there is Mr. ianlee74 on the list!  I guess it’s time to get busy!!!  More updates to come in the near future. 
Check out the list of contestants here.  You can see my entry here .

Monday, December 14, 2009

Dynamic Report Generation Using SSRS Local Reports

Recently, I’ve been working on re-architecting part of an invoicing application that has a very rigid invoice formatting design into an application that will eventually allow for almost infinitely customizable formatting in a very efficient manner.  This should lead to better customer relationships and fewer software customizations and therefore improved ROI.

PROBLEM


Think about your water bill.  Does it have all the information on it that you want?  Let’s assume that it doesn’t.  Suppose your water bill only shows the activity for the current period on it and how much money you need to pay the water company.  This is probably enough information for most people.  But, let’s suppose you’ve recently invested in some new appliances in an effort to conserve water (and money).  Now you’re probably interested in having a little more information on your invoice.  For example, maybe you would like to also see your activity for the previous period and for the same period a year ago.  This information would allow you to easily see how well your investment in new appliances is paying off (your ROI).  Now suppose that you called up your water company and asked them to add this information to your monthly invoice.  I can hear you laughing.  I can hear the water company laughing too.  Well, this is exactly the type of requests that the company I’m working for gets on a weekly basis. 

Their solution up to this point has been to have available in their custom billing software a handful of predefined hard-coded formats from which customers can pick.  However, almost monthly we get a request that doesn’t fit the mold of one of these predefined formats.  When this happens, we have to weigh up the cost of implementing a new format against the amount of money we believe we’ll make from this customer in an effort to determine if its going to pay off to give this particular customer exactly what they want or if we have to tell them that they’ll have to make due with what’s available.  Obviously, only one of these answers leads to exceptional customer satisfaction (and hopefully retention).  Of course, we want to give every customer exactly what they want but this is the real world and code changes still mean considerable investment. 

Oh, and did I mention that all of the current invoices are built using a PDF API?  This makes it very difficult for a programmer new to the app to make modifications if he’s never used the API before.
So, there are several problems here that need to be solved.
  1. Give every customer the same level of exceptional support regardless of contract size.
  2. Allow for more dynamic invoice styling while keeping a uniform appearance and format across all invoices.
  3. Eliminate use of the PDF API to make future application modifications faster (and cheaper) to implement.
  4. Eliminate the need to measure ROI of customer satisfaction.

PROPOSED SOLUTION


The solution to this problem that I proposed is to first eliminate the use of the PDF API and replace it with a SQL Server Reporting Services (SSRS) solution.  For this application I will use SSRS in local reporting mode.  This eliminates the need for a new dedicated reporting server.  We may later add an SSRS server but at this time the added cost and administration was unwanted.  Also, using SSRS in local mode gives us more options for customization of report definitions.

The basic format of all invoices is the same.  There’s a header, a footer, and a body which contains all of the “real” information that customers are interested in (and in customizing).  So, my plan is to build a main SSRS report that would have the header & footer information and a placeholder for the body of the invoice.  The body would be a separate subreport.  This subreport is the part we’ll be interested in for the rest of this article.  The basic idea is replace this subreport with customizations per customer.

I decided to approach this in two different phases.  First, I will get the basic workings of this new approach in place by building a framework that allows the app to create SSRS reports to be exported as PDF files and put the tools in place to allow the subreport to be easily replaced with a customized version per customer.  In this first phase, I decided to create several static subreports (.rdlc files stored as embedded resources) using the Visual Studio reporting designer that would just replace the formats that were currently available via the PDF API code.  This would allow me to focus on the SSRS code without having to worry about any new UI pieces.  Depending on the customer’s current invoice style selection, I will dynamically replace the subreport placeholder with the appropriate subreport.  Completion of this phase should also help me determine if there are any limitations in SSRS local reporting that would prevent this proposal from working as planned.  This is where I am today and what I’ll be detailing in the rest of this article.

After the first phase is completed and working as planned.  The second phase of the project will be to replace the embedded resource subreports with user designed subreport layouts.  This phase should be rather easy since SSRS (.rdlc) files are really just XML files.  The most difficult part of this phase will be getting the UI designer right.  It’ll be more tedious than difficult and I’ll document it at some later date when its complete.

IMPLEMENTATION


This should be very easy, right?  It all sounds very simple.  Create a report definition in the designer and drop in a subreport and then at runtime figure out which subreport should be there and modify some subreport name property and there you go.  Right?  Wrong.  Actually, its not much more difficult than that once you finish this article and benefit from my week of research and experimentation and a call to Microsoft tech support (which eventually led to them telling me it wasn’t possible…).

So, I started by creating my report definitions and getting them all tied into the subreport selection logic and data sources.  This all compiled and executed properly and it seemed that I was done.  Until I noticed that the subreports weren’t actually being replaced and all I was actually getting was the subreport that compiled into the main report definition.  Well, this wasn’t going to work.  I was basically doing something like the following.

public virtual void RenderReport(SsrsReportTypes reportType)
{
    LocalReport report = new LocalReport();
    report.ReportEmbeddedResource = "DynamicSubreporting.Reports.DefaultInvoice.rdlc";
    report.LoadSubreportDefinition("InvoiceBody2", GetSubreportStream());
    report.Render(...);
}

It seemed that no matter what I did I could not get SSRS to recognize the new subreport definition provided it by LoadSubreportDefinition().  Originally, I thought the problem must have to do with the way I was binding the subreport or the way that the subreport was identified by the LocalReport object.  I tried everything I could think of to make this work.  I desperately needed it to work for this project to be successful.

An alternate solution to this problem that I experimented with and got working was to dynamically write out the main report definition and replace the subreport name in the XML prior to writing out the file.  This solution had several limitations that I didn’t want to have to compromise on.  Mainly, it was going to be much messier to utilize this in phase 2 of the project and would probably lead to some multithreading and performance issues.

I read every article on the net that I could find regarding the LocalReport object and it appeared that no one had ever tried doing exactly what I was doing before and documented it or perhaps they just didn’t have problems…  Regardless, this seemed like a good reason to document it and here we are…

So, after reading everthing I could find and trying everything I could think of I finally resorted to creating a Microsoft support ticket and eventually got in contact with a Sr. SQL Server support engineer.  I described the problem and even created a stand-alone project that could be run without the rest of the app & database that he could use to debug my solution (you can download it here).  After a couple days of sending emails back & forth and him talking to his colleagues, they finally came to the conclusion that what I wanted to do just wasn’t possible without using some sort of work-around hack and he sent me a snippet of text from the LocalReport object documentation
The ReportViewer control requires the definitions for all subreports before it can process a report. If the local report was loaded from the file system by specifying the ReportPath property, the ReportViewer control automatically loads the subreports from the file system. In cases where the local report was not loaded from the file system, these methods may be used to load subreport definitions.
This statement turned on a light bulb.  Up until this point, I’d always thought the problem had something to do with LoadSubreportDefinition().  Now I was thinking perhaps the problem was actually in the ReportEmbeddedResource property.  Although this property wasn’t mentioned in this quote of the documentation I thought maybe since I was pulling the embedded resource version of the report that it was also automatically pulling the subreport definitions at that time and I had lost my opportunity to override the subreport.  That’s when I remembered seeing a function called LoadReportDefinition().  Suddenly, it made sense that maybe this function and LoadSubreportDefinition had to be used together.  Sure enough, by replacing ReportEmbeddedResource with a call to LoadReportDefinition() all of my problems were solved.

public virtual void RenderReport(SsrsReportTypes reportType)
{
    LocalReport report = new LocalReport();
    report.LoadReportDefinition(GetReportStream("DynamicSubreporting.Reports.DefaultInvoice.rdlc"));
    report.LoadSubreportDefinition("InvoiceBody2", GetSubreportStream());
    ...
    report.Render(...);
}
In hindsight, this seems like such a simple problem but it ended up taking a lot of time to get working and since there seems to be little information on solutions using the LocalReport object on the web, I thought that this article seemed worthwhile.  Hopefully, someone will gain something from it. 

The complete demo project can be downloaded here.  There’s a lot more to making this work that I didn’t discuss that you can easily decipher from the code.  This project was created by simply stripping out the bare minimums needed to demonstrate this one piece of functionality.  Nothing else about it should be considered an example of model coding practices.

CONCLUSION


The SSRS LocalReporting object can be used to create a truly dynamic reporting framework that requires a minimal amount of coding yet provides a great amount of power and functionality.  If you find that you are regularly making code “enhancements” in order to provide custom-specific reporting, this approach should be examined to see if it can help you make a real enhancement that provides real ROI.

Wednesday, October 28, 2009

U.S. Census Regions

Tonight I was working on a project for my A.I. class I’m taking at MTSU and had a need to have a table of U.S. regions that I could use to join to some other data to allow me to regionalize the data.  I thought that a quick Bing search would have revealed all that I could handle and the problem would be solved.  Well, it turns out that all I could find were all the maps galore but no where could it be found in tabular form.  So, I had to hand enter it based on this info from the U.S. Census Bureau website.  I decided it might be good to post it here for others searching for it and for that time a year from now when I need it again.  It’s totally denormalized for easy use in Excel.  For database work, you’ll probably want to normalize into states, regions, & divisions tables.  Enjoy!

RegionNum Region DivisionNum Division State
1 Northeast 1 New England CT
1 Northeast 1 New England ME
1 Northeast 1 New England MA
1 Northeast 1 New England NH
1 Northeast 1 New England RI
1 Northeast 1 New England VT
1 Northeast 2 Middle Atlantic NJ
1 Northeast 2 Middle Atlantic NY
1 Northeast 2 Middle Atlantic PA
2 Midwest 3 East North Central WI
2 Midwest 3 East North Central MI
2 Midwest 3 East North Central IL
2 Midwest 3 East North Central IN
2 Midwest 3 East North Central OH
2 Midwest 4 West North Central ND
2 Midwest 4 West North Central SD
2 Midwest 4 West North Central NE
2 Midwest 4 West North Central KS
2 Midwest 4 West North Central MN
2 Midwest 4 West North Central IA
2 Midwest 4 West North Central MO
3 South 5 South Atlantic DE
3 South 5 South Atlantic MD
3 South 5 South Atlantic DC
3 South 5 South Atlantic WV
3 South 5 South Atlantic VA
3 South 5 South Atlantic NC
3 South 5 South Atlantic SC
3 South 5 South Atlantic GA
3 South 5 South Atlantic FL
3 South 6 East South Central AL
3 South 6 East South Central KY
3 South 6 East South Central MS
3 South 6 East South Central TN
3 South 7 West South Central AR
3 South 7 West South Central LA
3 South 7 West South Central OK
3 South 7 West South Central TX
4 West 8 Mountain AZ
4 West 8 Mountain CO
4 West 8 Mountain ID
4 West 8 Mountain NM
4 West 8 Mountain MT
4 West 8 Mountain UT
4 West 8 Mountain NV
4 West 8 Mountain WY
4 West 9 Pacific WA
4 West 9 Pacific OR
4 West 9 Pacific CA
4 West 9 Pacific AK
4 West 9 Pacific HI