Saturday, December 26, 2009

Setting up my HTC Hero

Just got my hands on a brand new HTC hero as a gift.
Previous phone was N86 8MP (loved the camera ... but hey ... its always fun to try something new ;) )

Anyways. Here is a description of how I did the migration :

  • Synced my N86 with outlook using nokia pc suite. 
  • Installed HTC Sync for HTC HERO. 
  • Synced HTC hero with outlook. Now all I miss are the Notes (appointments / anniversaries / contacts are done ;) ). (I think I remember the ability to copy notes as text from the nokia phone.... but since I gave mine away ... I had to go the way of outlook as mentioned below)
  • Installed AKNote on the HTC Hero. 
  • Wrote a small script for outlook to export the notes as a text files: 
Sub exportNotesAsText()
   myfolder = "D:\COPY\PHONE\NOTES\" 'setup the folder path. Must end with backslash
   Set myNote = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderNotes)
   For ibi = 1 To myNote.Items.Count
       myNote.Items(ibi).SaveAs myfolder & myNote.Items(ibi).Subject & ".txt", 0
   Next
End Sub


All set.

Monday, December 14, 2009

SQL Server Transaction Logs taking up too much space

Transactions LOGs are used by SQL server to give transactional integerity. If you have set your DB Recovery Model to "Full" every data change query will be logged in  the Transaction log. If your transaction logs are getting too large (e.g you are retrieving large data-sets / day and deleting data-sets for old dates )  it is suggested that either you get more harddisk space OR:
  • Change the DB Recovery model (right click db in ssms -> select properties -> select options page) to "Simple"
  • Set Autoshrink to True. 
  • Organize your backup model accordingly.


In case you do not want to do that there is a quick solution (DBNAME is your database name):

USE DBNAME
GO
DBCC SHRINKFILE(DBNAME_log, 1)
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBNAME_log, 1)



But note that it can come back to bite you because:
  • you need to do a backup from the beginning after this. Previous backups are invalidated. Which means the backup model you have in place for Full Recovery model is invalidated.
  • It does not solve the underlying problem why the transaction log became this big in the first place.It will happen again.

Best of Luck! 

Check if a point is inside a polygon

Found this today:
http://alienryderflex.com/polygon/

This will be infinitely useful for my mapping applications. I will write code based on this for GDI+ sometime when I have more spare time.  Just wanted to bookmark / share it till then.

Thursday, December 10, 2009

GPS library for .NET

An amazing find. If you ever wanted to access a GPS device via .NET this is all you will ever need:
http://gps3.codeplex.com/

Enjoy!

Wednesday, December 9, 2009

Connecting to a solaris machine using XManager

I believe Xmanager to be the best XWindows remote desktop solution available as of now.
 
Here's how I connect to my solaris machines:
 
  • Start Xbrowser
  • Click on New toolbar icon - > New session wizard
  • Select Xstart and click next
  • Type in your host IP and select telnet as your protocol (if that is what you are using …. Which is generally the case from intranet applications)
  • Type in your username and password
  • Select 4 Xterm (Solaris). This will export your display to the terminal and start an xterm session. (cool stuff)
  • Click next and click finish.
 
 
Now you can even export this connection to your desktop.
 
Enjoy!
 
 

Thursday, November 19, 2009

Testing if gmail SMTP is blocked

Many times in the applications I program there is a requirement of sending an automatic email. I prefer to create a new GMAIL account to send the emails. In some organization smtp.gmail.com is blocked. Here is a simple test to check if it is going to work:

Telnet smtp.gmail.com 587

If you get the message : "Connecting To smtp.gmail.com...Could not open connection to the host, on port 587: Connect failed"
Then its blocked.

If you get something like : "220 mx.google.com ...."
It is going to work.

Enjoy!

Monday, November 16, 2009

Thursday, November 12, 2009

Best Python Setup yet!

Its true! :)

just found out :
http://www.portablepython.com/wiki/Download 

This is AWESOME. Great job guys. Thanks!

PS: I love pyscripter! Thats what I have given people that asked for an IDE since the first day. Its great that the portable python guys feel the same ;)

Enjoy!

Wednesday, November 4, 2009

Free Icons for your applications

There are a number of sites hosting free icons from various author. I particularly like:

http://www.iconspedia.com/
http://www.veryicon.com/
http://www.iconfinder.net/free_icons  (also allows searching by :"free for commercial use")


All (most) icons mention the publisher and any licensing for the icons.

Enjoy!

Tuesday, November 3, 2009

SQL Server Compact Tutorials

After you register for SQL Server compact you are given the link to the following tutorials:
http://msdn.microsoft.com/en-us/sqlserver/bb219480.aspx

Just sharing this so that I have it bookmarked :)

Enjoy!

Friday, October 30, 2009

.Net Reference vs. Value types

In .net classes are reference types whereas structs are value types.

What this means is that if you copy a class variable to another class variable it will copy the address only. Because of this after the assignment if you change data in one variable the data change will be noticed in the second variable (which points to the same instance) as well.

Here's a short demo:


namespace referencetest
{

    public struct TestValueType
    {
        public int x;
        public TestValueType(int thevalue)
        {
            x = thevalue;
        }
    }

    public class TestReferenceType
    {
        public int x;
        public TestReferenceType(int thevalue)
        {
            x = thevalue;
        }
    }

    class Program
    {

        static void ValueWithoutRef(TestValueType test)
        {
            test.x = 20;
        }
        static void ReferenceWithoutRef(TestReferenceType test)
        {
            test.x = 20;
        }

        static void Main(string[] args)
        {

            TestValueType valueTypeA = new TestValueType(0);
            TestValueType valueTypeB = valueTypeA;
            //Since A/B is a value types
            //Changes in functions or in one variable do not affect any other copies
            ValueWithoutRef(valueTypeA);
            Console.WriteLine(valueTypeA.x); //output 0
            valueTypeA.x = 10;          
            Console.WriteLine(valueTypeB.x); //output 0




            TestReferenceType referenceTypeA = new TestReferenceType(0);
            TestReferenceType referenceTypeB = referenceTypeA;
            ReferenceWithoutRef(referenceTypeA);
            //Value of A being changed Withtout a ref function
            //Causes value of B to be changed
            //since they point to the same data
            Console.WriteLine(referenceTypeB.x); //output 20



            Console.ReadKey();
        }
    }
}



The output :
0
0
20

Thursday, October 22, 2009

How to export all the SQL DDL in an SQL Server database


Once you have a project ready you need to deploy it. What you need is all the data definitions (tables / view / trigger etc.) but not the data since that was probably only for testing.
Here's how to do it in SQL Server 2008.
Launch SSMS. Right click your database and select Generate Scripts as shown:

Just follow the Script Wizard. Be sure to select script all objects in the database option:

That is it. I prefer to export it to a new query window:

And then fine tune the output ... but thats just me.
Enjoy!


Sunday, October 18, 2009

.NET Embedding scripting in your application

Three primary solutions come to mind:
  • Using the good old Microsoft Script Control. http://weblogs.asp.net/rosherove/articles/DotNetScripting.aspx My concern with that is simply the fact that I its VBA.
  • Use IronPython. http://www.jphamilton.net/post/Application-Extensibility-and-Embedded-Scripting.aspx Like it A LOT.
  • Use BOO. http://rasmuskl.dk/post/Embedded-Scripting-Language-Boo!.aspx  Love IT!

Somehow after 4 years of it being around, BOO is still known to the cutting edge guys only. But I truly and deeply love it. I love the duck typing ... the python lists .... and yet the COMPLETE Intellisense and the Ability to import boo based modules into your C# code.  So if you intend to add an interactive debugger / designer with your application go with boo. It will be easier. But due to BOO's lesser used nature use IronPython if official support is important to you.

Inshort :
I will go with IronPython if short on time and effort.
But do BOO if I get some more time purely for the scripting engine. And make an amazing embeddable version of sharpdevelop for my application ;).

Thursday, October 8, 2009

Create a windows shortcut for shutdown , restart and hibernate

You can create a shortcut give the shutdown program commands to shutdown / restart or the rundll program to call a WIN API function for hibernation:

Shutdown:
SHUTDOWN -s -t 01

Restart:
SHUTDOWN -r -t 01

Hibernate:
rundll32.exe PowrProf.dll, SetSuspendState

Enjoy :)

Wednesday, October 7, 2009

Best Mini PC games site

Just came across this :
http://www.reflexive.com

An amazing site for reviews of games that can run on netbook :) (generally!)

Saturday, September 26, 2009

Antialiasing graphics GDI+ in .NET

Was looking for this.
All you need to do is set the smoothing mode on your graphics object :

g.SmoothingMode = SmoothingMode.AntiAlias;

Thats all folks. Enjoy!

Thursday, September 24, 2009

Passing Large Arrays (Lists) Via WCF

Was getting the error "The underlying connection was closed: The connection was closed unexpectedly" when passing arrays from large datasets to the client.

This is the solution:

http://consultingblogs.emc.com/merrickchaffer/archive/2007/09/19/WCF-System.Net.WebException_3A00_-The-underlying-connection-was-closed_3A00_-The-connection-was-closed-unexpectedly.aspx

Basically requires adding the following to both the server and the client config files (web.config / app.config)

<dataContractSerializer maxItemsInObjectGraph="2147483646"/>


Wednesday, September 23, 2009

Best Feature of C# 4

Optional Parameters. For real ... C++ had it ... python had it...... Excel required it (which meant that when using excel COM via c# you need to lookup the documentation and put in the default values yourself...) and .. VBA (and therefore VB.net) had it. Sure you had function overloading.... but still this is more fun!

Details of how it works here: http://geekswithblogs.net/michelotti/archive/2009/02/05/c-4.0-optional-parameters.aspx

Enjoy!

Sunday, September 20, 2009

Learning videos for expression suite

I simply admire the Expression Teams effort. Like all microsoft products soon after the release you will find tons of useful video tutorials. For Expression suite you can find them here:
http://expression.microsoft.com/en-us/cc197142.aspx

Enjoy!

Thursday, September 17, 2009

VSTO video tutorials

Here are two great videos from microsoft about the VSTO in VS2008.

Taking Office Development to the Next Level

http://www.microsoft.com/resources/msdn/vstudio/media/en/vstudio2008/OFC342.wvx
The second url is from the entire sequence of VS2008 videos :
http://msdn.microsoft.com/en-us/vstudio/bb655906.aspx

Monday, September 14, 2009

Setting up validation with DevExpress XtraEditors combobox

DevExpress gives a lot of features for the amount of money that you are required to spend. Fact. I like the way everything looks so consistent.

So generally for all controls:
  • Drag and drop DxValidationProvider onto your form.
  • Click the provider.
  • Set validation mode to manual.
  • You will also be get a smart tag for "Customize Validation Rules"
  • Select your control and setup the validation rules.
  • When you want to validate (e.g. on OK button) use something like the following code:
dxValidationProvider1.Validate();
if(dxValidationProvider1.GetInvalidControls().Count!=0)
return;
this.DialogResult = DialogResult.OK;
this.Close();

Done :)

Now for setting up a combobox with valid values as no values along with a list do the following:
  • Select your combobox
  • In its properties expand the properties section.
  • set your selectables in its items collection (with a blank newline as the first line for the empty string)
  • Delete its edit value
  • Launch its validation editor (from DxValidationProvider).
  • Set "Contition Validation" along with "Conditional Operator" as "AnyOf". Edit the values and paste the same items as you did in the items collection.
Enjoy!

Monday, August 31, 2009

SSAS with SSRS filtering and sorting MDX results

In SSRS MDX Query builder I cannot find a way to filter results based on Measures. Here's what I mean:
As you can see, you can only select dimensions from the filtering section. Also there is no option for sorting. Heres how you can do it:

FILTERING:

There are two options. One from the query editor. Other from the tablix properties. I prefer the tablix properties (since sorting MUST be defined there and I found no other way to do it). I explain the first option here and the second option along with sorting.

  • Filtering using query properties:
This screenshot pretty much explains everything.

As it is ... if you press okay it will give the error : "Value is not an integer number". That is easily solved by setting the Expression for Value field as =.3 as shown.


All set. The procedure for setting the filter in tablix (your table) properties is presented next along with sorting :)

SORTING :
Select tablix properties:

Now you can add your sorts and your filters.


I would prefer the query to do this for me (to save SSRS resources and utilize SSAS resources) but I did not find that option (as of SSRS2008).

Enjoy!

Sunday, August 30, 2009

BIRT style Document Outline in SSRS

Truth be told....... I love BIRT! It is simply put: AWESOME. But SSRS is wicked cool as well. Anyways... I loved the document outline in BIRT. It allows you to copy charts around from table to table. It was also highly detailed.

The document outline window is hidden in SSRS BI Dev studio by default. But you can enable it via:
View->other windows -> Document outline.


Enjoy!

Login failed for user 'NT AUTHORITY\\NETWORK SERVICE'

I got this error when deploying my WCF + linq to SQL service to IIS. This happens because IIS runs under NT AUTHORITY\\NETWORK SERVICE. There are a couple of solutions here:

  • change IIS service to run under another user.
  • setup impersonation (complicated but ideal)
  • grant Network service access to the database ( quick but insecure)
Just to get it running on your test server you can use option three as explained below:
  • Just open up SSMS
  • log into database server
  • expand Security -> logins.
  • Right click logins and select new login
  • search login name
  • type in "network service"
  • in server roles check "public" and "sysadmin"
  • in user mapping select your database and give it "owner" and "public" rights
  • Click OK and your are all set
Enjoy!

Deploying a WCF service to IIS7

If you installed IIS after .net framwork 3.5 WCF HTTP activation will not be turned on. Without that your deployed svc's will not work. You can turn it on from windows components.

Also, I noticed that the publish option for ASP.net websites is not in the properties of the project. It is however present in the right click menu for the project :)

Enjoy!

Saturday, August 29, 2009

Vista file search options

I do not like to index my files. It just takes more memory than I like to spare (yes I love harddisk space a LOT).

So here is how I setup my windows vista file search options:

This more closely resembles windows XP but that's the way I like it.

Friday, August 28, 2009

WCF error Catastrophic Failure

My wcf service was constantly timing out. Note that I had a session based service i.e:

  • on the contract:
[ServiceContract(SessionMode=SessionMode.Required)]

  • on the implementation:
[ServiceBehavior(InstanceContextMode=InstanceContextMode.PerSession)]

Now what was happening was that I put a break point on a function and called it MANY times. It wasn't being triggered after the first couple of times. Figured it out (after mike's tutorial on sessions and instancing). Some got timed out. And because of that the queue got messed up and resulted in the catastrophic failure. Since I dont do anything thread unsafe in general I changed the behavior on the implementation to :

[ServiceBehavior(InstanceContextMode=InstanceContextMode.PerSession,ConcurrencyMode = ConcurrencyMode.Multiple)]


Got the hint from here (read the sessions and streaming section).


Enjoy!

Using a winform (.net) as a fixed size dialog

Heres how just select the dialog and set the following properties:
  • MinimizeBox = false
  • MaximizeBox =false
  • FormBorderStyle = FixedDialog
Enjoy!

Thursday, August 27, 2009

Passing LINQ to SQL objects over WCF

I came accross a cool artile that explains just that :


I just had to bookmark it.

Its pretty simple. Click the whitespace in your dbml file. Select its properties and set the serialization mode from "None" (the default) to "Unidirectional". Tada... now you can send the objects returned from linq over the wire with complete intellisense and debugging awesomeness of visual studio!

However you cannot serialize DataContext :( There is a workaround though. It involves setting up a timestamp column. So without further ado here it is.

To learn more about serialization I recommend the mike taulty video: Type Serialization

Enjoy!

WCF Discover service option in Add service reference tool

The WCF Discover service option in Add service reference tool only works when you have a web based wcf service (svc extension :) ) in the current solution.

I am refering to visual studio 2008. Googling this with this sentence would have saved me some time.


Enjoy!

WCF First time woes

I remember the first time I used WCF i came across a weird error "The type name ServiceReference1 does not exist in the type Namespace.ClassName". I solved it ... but now someone has posted it. However I would like to mention that this will occur if ANY class in your project has the same name as ANY namespace in your project. Inshort ... keep your class names different from your namespace names.

Anyways. I was googling it due to other reasons. Async callbacks.

In case you cannot generate Async callback from the add service reference tool you need to set your project to output .NET 3.5.

Enjoy!

Tuesday, August 25, 2009

WCF Video Tutorials

There is a lot of power in WCF. But then again there is nothing TRULY awesome and UNIQUE about the technology that you don't already have with java service achitecture. What you do have is an AMAZING IDE that does most of the work for you and in the most well integrated way that you can imagine. To get up to speed on what I mean reading about it won't help. But looking at it will. Here's a compilation of video tutorials for you (in order of recommendation ..... personal recommendation):
  1. http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2006/09/13/8875.aspx
  2. http://www.dasblonde.net/2007/06/24/WCFWebcastSeries.aspx|

BTW for some videos by mtaulty you can bing them since the links are expired. They will either be nuggets or screencast. (google did not have those pages indexed.)
Enjoy!

Embedding Google Earth in your .NET application

It used to be that the best integration that you could get with google earth was either:
  • make a static KML file: this gave you the opportunity to search your data from the kml)
  • make a dynamic KML. This made it possible to get the latest information always.
Well now there's a better way. Embed the google earth plugin. Its been around for sometime but I just saw it now.

Anyways. There is an amazing control that allows you to embed this (browser) plugin into your winform application writter by Fraser .

All about the winform component on google code .

Although for anything advanced I would still go for this control :)

Enjoy!

Monday, August 24, 2009

Winforms keeping a dialog open but hidden

You can keen a windows form open but hidden bringing it into focus on demand as follows:

  • Declare it in your main class
RFCManager rfcmanager;

  • create it in on form load:
rfcmanager = new RFCManager();
rfcmanager.Hide();

  • When you want to show:
rfcmanager.Show();


  • On the FormClosing Event handler :
this.Hide();
e.Cancel = true;


Enjoy!

Testing Linq

There is an amazing software called linqpad. It can help you test out your linq queries.

Other than that you might still feel the need to show the data returned from your query quickly.

Here's a code snippet that does just that:

public static void ShowInGrid(Object items)
{
Form form = new Form();
DataGrid dg = new DataGrid();
form.Controls.Add(dg);
dg.Dock = DockStyle.Fill;
dg.DataSource = items;
form.ShowDialog();
form.Dispose();

}

use at will :)

Sunday, August 23, 2009

Setting up Devexpress ribbon

Heres what I do:
  1. After adding the ribbon change the Form to inherit from: DevExpress.XtraBars.Ribbon.RibbonForm
  2. Drag and drop defaultlookandfeel component onto the form.
  3. Set the theme by selecting the defaultlookandfeel control.
Themes are all set. Now setup the icon:
  1. Select the form and point icon to an ico file.
  2. Select the ribbon and set applicationicon property to a PNG file (preferably 24x24 version of the ico file :) )

Logging parameter changes in a table using triggers in SQL Server similar to Oracle

Here is some code I wrote to make my oracle to SQL Server migration relatively painless.

For the uninitiated on Oracle here's a short description of how an update trigger in oracle looks like:

CREATE OR REPLACE TRIGGER "RFSITE"."CNA_CALLTRIG"
after update on rfsite.cna_cell
for each row
begin
if :old.CBCHD<>:new.CBCHD then
insert into cna_hist
values
(:old.cell_id,'CBCHD',:old.CBCHD,:new.CBCHD,NULL,NULL,NULL,sysdate,NULL,:OLD.BSC);
end if;
end

Notice:
  • The main code of the trigger will be looped for each row. (due to portion highlighted in bold)
  • for each row :old is a bound variable for all columns of old value.
  • for each row :new is a bound variable for all columns of new value.

See how simple it is to store the history. Well not that easy in SQL Server. Here's how its different:

  1. In SQL server trigger is called ONCE will all the old values in deleted and new values in inserted .
  2. No option like 'for each row'
So in case you want to loop through variable the same as oracle you need to write a LOT of boiler plate (to my knowledge..... I would LOVE to be proven wrong)

Heres a short description of how to do this:
  1. Declare column members as variables.
  2. Declare a cursor for select * from inserted inner join deleted on ()
  3. loop through cursor pushing the values into your declared variables.
  4. Now that you have the variables do what you want with them.

Now that you have the theory down heres a short sample of how to do it.
  1. DECLARE
    @RFCIDOLD int,
    @RFCIDNEW int,
    @ProjectOLD varchar(255),
    @ProjectNEW varchar(255) ........etc
  2. DECLARE Item CURSOR FOR

    select
    deleted.RFCID as RFCIDOLD,
    inserted.RFCID as RFCIDNEW,
    deleted.Project as ProjectOLD,
    inserted.Project as ProjectNEW ...... etc
    from
    deleted inner join inserted on
    (
    inserted.RFCID=deleted.RFCID
    )
  3. Open Item
    fetch Item into
    @RFCIDOLD ,
    @RFCIDNEW ,
    @ProjectOLD ,
    @ProjectNEW .....etc
  4. While @@Fetch_Status = 0
    begin
    --You now have the required data in the new and old values same as oracle. Do as you like.
    if @RequestedByOLD <> @RequestedByNEW
    begin
    Print @RequestedByOLD +' Changed to ' +@RequestedByNEW
    end

    fetch Item into
    @RFCIDOLD ,
    @RFCIDNEW ,
    @ProjectOLD ,
    @ProjectNEW .... etc

Of course you will need to end the loop close the cursor, deallocate it and end the trigger (more boiler plate).

Anyways. This post is not about explaining the process as much as it is about automating it.
Enter: Trigger maker

Heres how it works:
(sample RFC table ddl is provided in the download)
  • Put a file ".txt" in the input folder containing your table DDL (generated from SSMS option to script to a new window)
  • Put the file ".k" in the input folder continaing your keys seperated by newlines.
  • Put the file ".i" containing columns you do not want to audit changes for.
  • Put the file ".a" containing the audit code. e.g: 
    • Insert into CNAH values (@BSCNEW,@CELL_IDNEW,'%(param)s',@%(param)sOLD,@%(param)sNEW,null,null,@NCELL_IDNEW,null,null,GetDate())
  • Run process.py
  • Your trigger will be present in the output folder as ".txt>"

Note trigger maker does one more cool thing for you. Here's how I like my auditing structure:
  • Say you have table
  • I like my trigger name to be (tablename)AUDIT


Enjoy!

Creating a shared analysis services datasource in SSRS

Here are the steps:
  • Open report manager
  • Click on New datasource
  • Type in the name
  • Type in the Description
  • Set Data source type to SQL Server Analysis Services
  • Paste in the connection string
  • In connect using: Credentials stored securely in the report server. Type in the username with access to SSAS (no need to give fully qualified username if user is on localhost).
  • Be SURE to check use windows credentials when connecting to the datasource.

All set. Enjoy!

Getting Yesterday date properly formated in SSRS expressions ready for MDX

Suppose I want yesterdays value for use in MDX from SSRS. It will be something like:

[DPTime].[THEDATE].&[2009-08-22T00:00:00]

Here you can see that I have a Time heirarchy DPTime->THEDATE and in that the member for the time.

Heres how to do it in SSRS expressions. The following expression with take you 7 days back:

="[DPTime].[THEDATE].&["
&
Year(DateAdd("D",-7,DateTime.Now()))
&"-"&
IIf(Month(DateAdd("D",-7,DateTime.Now()))< 10,"0","")
&
Month(DateAdd("D",-7,DateTime.Now()))
&"-"&
IIf(Day( DateAdd("D",-7,DateTime.Now()) )< 10,"0","")
&
Day(DateAdd("D",-7,DateTime.Now()))
&"T00:00:00]"

The above expansion of the expression makes it pretty much self explanatory. But still note the following:
  • -7 i.e the number of days to go back occurs 5 times.
  • "[DPTime].[THEDATE].&[" is your heirarchy (bound to be different in your case).

Enjoy!

Getting yesterday date properly formated using SSIS expressions

Well In case you want today in the following format:
2009-08-23

Notice the trailing zero before the 8. Heres how:
(DT_WSTR, 4) DATEPART( "yyyy", DATEADD( "dd" , -@DateCounter, GETDATE() ) )
+ "-" +
Right("0"+(DT_WSTR, 2)DATEPART( "mm", DATEADD( "dd" , -@DateCounter, GETDATE() ) ),2)
+"-"+
Right("0"+(DT_WSTR, 2)DATEPART( "dd", DATEADD( "dd" , -@DateCounter, GETDATE() ) ) ,2)
+" 00:00:00"

the hours mins and seconds are non dynamic in this case but I hope you get the drift.

The variable DateCounter will take you back as many days as you like.

A little more one how adding the zero worked:
  • DT_WSTR,2 made the "mm" section return X8 where X is null.
  • "0" + "X8" = "08" . But had it been say "11" then "0"+"11" = "011".
  • Right (,2) made "08" = "08 but had it been "011" as explained above it would be "11"
Enjoy!

SSRS forcing chart column colors

Amazing article:
http://msdn.microsoft.com/en-us/library/aa964128%28SQL.90%29.aspx

setting up chart colors is easy. There are two options.
  • For items that exceed a certain threshold kindly review the article.
  • Second a custom color series. Read on!

Steps:
  • Select the chart.
  • Goto Properties.
  • Set Palette to Custom
  • Drop down the property : CustomPaletteColors
You are all set.

Enjoy!

SSRS forcing labels orientation to be vertical and showing all values all

The question is longer than the solution :). Simple. Right click the horizontal axis and select "Axis Properties"
  • In "Axis options" set the interval to 1.
  • In "Labels" select disable autofit and then set rotation to 90.
Enjoy!

Best google maps ASP.net control

I have previously reviewed google maps controls for winforms.

But now I Just reviewed the options for googlemaps in asp.net. There are MANY so I will not go into all of them and just point to what I found to most FREQUENTLY updated and yet feature loaded. (The frequency of update is very important for commercial use of a control. Since two days without your users being able to see their favorite map could be disastrous!)

Here it is : http://googlemap.codeplex.com/

LINQ with WCF

There is something very important I want to say. I LOVE Microsoft. It has made my life as a programmer and DBA so FANTASTIC in so many ways that I just had to say it.

Latest discovery ... LINQ! Known about it since it came out but started using it now :) due to work on other ...cough ... java ... cough ... projects).

Anyways anyone wondering how LINQ can fit into your WCF client / server application.

Well for one:
  • Create a statefull service
  • Create a DataContext for each session. There is no theoratical limit on the number of DataContexts you can use. They are not the same as your traditional ORM. Infact to cancel a DB transaction you should set your DataContext to null or just forget about it :). The only time a trascation takes place is in the SubmitChanges() type functions which start ... carry out the changes ... and finish the transaction. (of course selects also do transactions but you get the idea)

Enjoy!

Tuesday, August 18, 2009

How to process SSAS Cubes from SSIS

In case you do not know I LOVE SSAS. It makes sooo many things sooo easy (with great performance J) that it should be a sin for anyone to charge implementing it. But shhhhh don’t tell anyone.

 

Anyways you can refresh Cubes from SSIS using analysis services processing task. Its in the main SSIS panel.

 

One error that you may encounter is that it will fail with some weird error like “x cube failed because primary key y not found” or something. That is because the fact table will contain data not found in the dimension.

 

Here’s a tip about how SSAS works. Dimensions are processed separately (dimension processing) and fact tables are processed separately (cube -> fact table -> partition processing) with only liking to the dimensions.  The error occurs when data present in the fact tables against a dimension is not in the last processed dimension. To avoid this process the cubes AND dimensions. Dimensions are pretty cheap to process since they are only select distincts. Sure you might have data for say day x in Cell level, BSC level, Whatever other level but its still one day x. Get the drift?  

 

So to process the partition you need to process the Dimensions first. But since the “Analysis Services Processing Task” does not order your processing sequence as you would like (no feature to move steps up and down) you will need two Analsysis Services tasks. In sequence refresh dimensions in first and refresh cubes (fact tables / partitions) in second.

 

Enjoy!

Monday, August 17, 2009

Great SSAS for Excel find

I love using Excel 2007 for analysis. But I find it limiting in terms of features. Also I like to know a little about what is going on under the hood.

So, If you want to view MDX that is generated from you modifying various pivot field there is a software that can do just that. Its called PTPower.

I also allows you to add your own calculated members :)

Sunday, August 16, 2009

SQL Server profiler simplest settings

Got this off the LINQ videos on ASP site. (BTW cool stuff LINQ!)

  • start the sql profiler
  • click new trace
  • connect to your database
  • click on event selection and uncheck all
  • still in event selection check show all events and also check show all columns
  • Now check SP:StmtCompleted in events.
  • Next check the TextData column for this event.
  • Click Run
Enjoy!

Friday, August 14, 2009

SSAS Tools shortlisting

I am in the process of shortlisting the best of tools (on basis of features / price) from:

INTEGRATION CASE
For integration into your own applications I found an AMAZING tool at :

They offer .NET components for your use in both ASP and WinForm applications.

A close second (and one I recommend due to price) is XtraPivotGrid. If you get their suite editions there is no beating the price.


STANDALONE DESKTOP CASE
Their Cube Player is the best MDX editor you can find. For that alone it is worth it!

Wednesday, August 12, 2009

SQL Server Clustered and Non clustered indicies

Indicies are the best performance gain you can get from a table in any database.

For SQL Server indicies can be classified into clustered and non-clustered. There is a facinating article that explains the difference to be found at:
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

Here's the crux:
For speeding up single records and minimizing index space use non-clustered.
If select performance (rather than insert performance) is paramount... use clustered idices.

Note: A table can have only one clustered index. It is recommended to keep that index same as the primary key :)

So when adding indicies you will generally be adding non clustered indices. Enjoy !

Saturday, August 8, 2009

Disabling USB drives in VISTA

You can do this via a group policy. First copy the data presented in this article from microsoft into a .adm. The data you need to paste is also presented below:

CLASS MACHINE
CATEGORY !!category
CATEGORY !!categoryname
POLICY !!policynameusb
KEYNAME "SYSTEM\CurrentControlSet\Services\USBSTOR"
EXPLAIN !!explaintextusb
PART !!labeltextusb DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 3 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
POLICY !!policynamecd
KEYNAME "SYSTEM\CurrentControlSet\Services\Cdrom"
EXPLAIN !!explaintextcd
PART !!labeltextcd DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 1 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
POLICY !!policynameflpy
KEYNAME "SYSTEM\CurrentControlSet\Services\Flpydisk"
EXPLAIN !!explaintextflpy
PART !!labeltextflpy DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 3 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
POLICY !!policynamels120
KEYNAME "SYSTEM\CurrentControlSet\Services\Sfloppy"
EXPLAIN !!explaintextls120
PART !!labeltextls120 DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 3 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
END CATEGORY
END CATEGORY

[strings]
category="Custom Policy Settings"
categoryname="Restrict Drives"
policynameusb="Disable USB"
policynamecd="Disable CD-ROM"
policynameflpy="Disable Floppy"
policynamels120="Disable High Capacity Floppy"
explaintextusb="Disables the computers USB ports by disabling the usbstor.sys driver"
explaintextcd="Disables the computers CD-ROM Drive by disabling the cdrom.sys driver"
explaintextflpy="Disables the computers Floppy Drive by disabling the flpydisk.sys driver"
explaintextls120="Disables the computers High Capacity Floppy Drive by disabling the sfloppy.sys driver"
labeltextusb="Disable USB Ports"
labeltextcd="Disable CD-ROM Drive"
labeltextflpy="Disable Floppy Drive"
labeltextls120="Disable High Capacity Floppy Drive"
Enabled="Enabled"
Disabled="Disabled"


Run the group policy editor: "gpedit.msc"

Afterwards goto "Computer Configuration>Administrative Templates" since you want to disable it for the computer. Right click "Administrative Templates" and select "add / remove template". Click "Add" and browse to the .adm file you made. After adding the file close the dialog.

A new section "Classic Administrative Templates (ADM)" will appear. Go inside that folder and you will see "Custom Policy Settings>Restrict Drives". In case the Restrict Drives folder is empty you need to do the following: goto "View>Filtering" and uncheck "Only show policy settings that can be fully managed".

You will now see the option to "Disable USB Drives" along with others in the "Restrict Drives" folder. Click on it. Select Enabled and also "Disable USB Ports" to Enabled.

Tuesday, August 4, 2009

Getting the week on sybase

I wanted to get the week number from sybase ASE. The requirement was simple:
  • Monday is the first day of a week ( a week is from mon - sun)
  • Even if 1st January was a Sunday, the week 26 Dec - 01 Jan would be called week 1 (and not week 53).
This is known as ISO standard 8601.

For sybase this was pretty simple (since the server was configured to use ISO 8601). This pretty much explains itself:

select datepart(cwk, convert(datetime,'2009/01/03',111)) as thisyear
, datepart(cwk, convert(datetime,'2008/12/28',111)) as lastyearlastweeklastday
, datepart(cwk, convert(datetime,'2008/12/29',111)) as thisyearfirstweekfirstday

This returns 1,52,1.

Enjoy:)

Tuesday, July 21, 2009

Getting row counts in all excel file by sheet names

The scenario:
You have a number of excel files (xls). In each excel file there various sheets. Each sheet has a header and a number of lines following it. You want an output of the format (csv):
filename1, sheet name 1, row count , sheet name 2, row count, sheet name 3, row count
filename2, sheet name 1, row count , sheet name 2, row count, sheet name 3, row count

Well here is a script that does just that!
You will need python and dexutils both downloadable from : google code
make a folder (i will call it SCRIPT) and make a file getsheetrowcounts.py containing the following data :


import dex
from dex import xlrd

decrementoneforheader
= True

files = dex.glob('./input/*.xls')

book
= {} # book['filename']=[]

for file in files:
wb = xlrd.open_workbook(file)
file = dex.GetFilenameWithoutExt(file)
sheets = wb.sheet_names()

for sheetname in sheets:
sh = wb.sheet_by_name(sheetname)
rowcount = sh.nrows

try:
book[file]
except:
book[file]=[]
book[file].append(sheetname)
if (decrementoneforheader):
rowcount = rowcount-1
book[file].append(str(rowcount))


# now we have the data
outlines = []
for bookname in book.keys():
line = bookname
rows = book[bookname]
for column in rows:
line = line + "," + column

outlines.append(line)

dex.writelines('output.csv',outlines)




Next make a folder "input" inside of SCRIPT and place all the excel files in this folder. Finally run getsheetrowcounts.py and you will get the desired output.csv

DEXUTILS: A python utility library I wish I had in EVERY language

I have been working with python for 2 years now. It is by far the most general purpose language and productive programming language I have experienced to date.

The first thing that any professional programmer should get for python is an AWESOME IDE. I HIGHLY recommend wingware's Python IDE. It truly is worth the money and comes for all flavors of operating systems. For those using windows and wanting something free software I recommend using mmm-experts python scripters. Both have an amazing feature of clicking (while holding the ctrl key) to navigate to a function. So I basically have a folder put in the Environment variable PYTHONPATH and in that I place a folder by the name dex. Now in my code I do import dex and I can type function name. If any is not working I can modify it there and then in my script.

I do a lot of general purpose programming and over the time dex (aka dexutils ) has expanded exponentially. So without further ado I present you dexutils.

It comes with a simple distutils installer (for windows users an installer is also always present

For quick programming hacks I dont want to waste time opening / reading and then closing a file. So I made a quick function about it:

import dex
lines = dex.readlines('filename')

For saving a lines to a file you can do :

import dex
lines = ['1','2','3','4','5']
dex.writelines('temp.txt',lines)


This makes using python faster than writing a macro in vba anyday!
There a LOTS of functions in it. Some quite advanced. Almost all of them come with __doc__ strings so if you are using wingware or pyscripter you get cool help information.
Here a a couple more:
Check if a string can be made an integer:
from dex import stringutils
print stringutils.IsInt('123')

Splitting a CSV string: (will print ['1', '2', '3', 'stuff', '5'])
from dex import stringutils
print stringutils.SplitCSV('1,2,3,"stuff",5')

Print the current scripts filename :

from dex import stringutils
print stringutils.GetFilename(__file__)

How about reversing a string : '123' -> '321'
from dex import stringutils
print stringutils.ReverseStr('123')

There are various modules e.g stringutils , fileutils, arrutils. Just read the docs :) I am sure you will find it helpful. Also any cool library I found highly useful I have made a part of the code. e.g. xlrd (more on that later).

The link (again) : dexutils

Enjoy!

Monday, July 20, 2009

Using SQL Server Analysis Services with Sybase ASE 15

Sybase 15 ships with ADO.net and OLEdb drivers. These come as a part of the Sybase PC client download. Here I describe how you can use Sybase with SQL Server analysis services (SSAS). The following is the software I am using :
  • sybase_pcclient_ASE_15.0.2.zip
  • SQL Server 2008 Analysis services
While installing the PCClient making sure that you select the data access modules. (These were checked by default in my installation but it never hurts to check). Now start the Data Source Administrator:


Click on Add, Fill out the details, And test to see it is working:



Finally click ok and then close the application.

Also (not neccessary but I like to do):
{
  • check cursors
  • Trasactions tab: Server initiated transactions .... as well as ..... XA Protocol
  • Finally set the database name in the connection (Sybase data source administrator) as well as in the Data source(BI Dev studio)

}

Congrats. The difficult part is done :)

Now in BIDS add a new datasource. Click on new to add a data connection. In the connection manager select Sybase OLEDB as your provider as shown:


Fill out the form particularly the Server name as you did in the data source administrator (OSS in my case).



Click on Test connection and you are all set.

Enjoy!

I will try and post about the performance I am getting from my cube some time later on.

Update: For SSIS in OLE DB data source you will need to set "AlwaysUseDefaulCodePage" Custom Property of the OLE DB Source control to True. You can read more here . Also use SQL Queries for data retrieval.

Update2:For SSIS running under SQL Server agent (for scheduling) in case it fails due to "Could not obtain connection" or "Invalid port number" it may be because of one of following resons:
  • the drivers may not be accesible to the user that is used to run the SQL Server agent (the service property : log on as). Login as that user. Reinstall the client tools (do a full install) of sybase ASE.
  • The package has ProtectionLevel property (SSIS property) as "EncryptAllWithUserKey". This makes connections unavailable to anyone but the developer. Use "EncryptSensitiveWithPassword" which makes it accessible to any domain users that have the password you will be asked to set.
  • and Finally: Run all packages as sa (SQL Server agent property). Running them as other users is a complicated process that you can check out AFTER your package has started to work in schedule :)
Enjoy!

Sunday, July 19, 2009

Setting up an Oracle an PHP development environment

There are a number of tutorials on setting up php and mysql. And there are awesome projects e.g. WAMP and XAMPP to get you started quickly.

I use Nusphere's PHP ed as my programming tool. It is simply the best php environment (at least about an year back ... when I researched into the options available).

There is also an article explaining the process on OTN:
http://www.oracle.com/technology/pub/notes/technote_php_instant.html

However as per my observation I did not need to replace the php_oci8.dll . In fact doing so cause my php code to break.

Heres what I did to get it running:
  • Install Nusphere's PHP ed.
  • Modify php.ini (c:\Program Files\NuSphere\PhpED\php5\php.ini) that is formed uncommenting the line : extension=php_oci8.dll
  • Extract the instant client (I tried : instantclient-basiclite-win32-10.2.0.5.zip from http://www.oracle.com/technetwork/topics/winsoft-085727.html ) to c:\instantclient_10_2
  • Put c:\instantclient_10_2 in your PATH
  • If you are going to use TNSNAMES.ora you can put it in c:\instantclient_10_2 and add a system environment variable TNS_ADMIN and SQL_PATH pointing both to c:\instantclient_10_2
That is it. You are all set.

Say you have an entry in tnsnames.ora as :


RX =
  (DESCRIPTION = (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 150.236.167.30)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = 69333)
    )
  )



Just put the IP .. e.g HOST=150.236.161.1
You can now do a simple connect via:

$conn = @OCILogon($user,$password , "rx") or die ("Database Connection Error in file...");

Sunday, July 12, 2009

WPF / Winforms Mapping control

I have been looking for a WPF mapping control since as long as I have learnt about WPF.

Finally an awesome find:

It has multiple data source selections including google maps and open street map so you will not be left hanging. It support offline caching of map images via sql lite. This is just WAY too cool.

It supports custom markers as well.

BTW: I found this right after I have decided that WPF is just not ready for prime time yet :) at least not for me........... I love it and a I love blend .... but winforms just performs better on my client systems. At least for now. Also, the programmer made a winform version of the control as well :)

Update: Feb8 2010
I moved to WPF because it is the future. I have found it to be EXTREMELY fun to work with ( I love a pretty interface ). I have been using this control for 6 months now and it is HIGHLY stable ... and easily customizable too.
Also here is another way to do it (but I wouldn't go there since I dislike interop) :
http://code.google.com/apis/maps/articles/flashmapinwpf.html


Also check out :

It allows download of google map tiles amongst other cool stuff.

As usual Enjoy!

Sunday, June 21, 2009

BIRT Subreport Tutorial

Say you have a list of BSCs and you want to display information for each BSC. You have a query that takes input as BSC. And another query that list your BSCs. Here's how to merge the two using BIRT.

First your BSC query:
select bsc from rx_bsc_region order by BSC asc

Create the query using BIRT as shown. Nothing fancy to do here. Just a simple query:

Now simply drag and drop this query into the layout to create a table:
You might want to make the [BSC] body section bolder to highlight BSC sections in the table. Also add a row below the BSC using right clicking:

You might want to add another detail row in case you want multiple sub tables each with a different header:


Now you can make a query that takes BSC as input . e.g. :
Select OSS_CELL_AVGVIEW.CELL , OSS_CELL_AVGVIEW.bsc ,(OSS_CELL_AVGAVG.f_tch_availability- OSS_CELL_AVGVIEW.f_tch_availability ) as TCHAvailDec from oss_CELL_AVGview INNER JOIN OSS_CELL_AVGAVG ON (OSS_CELL_AVGVIEW.CELL = OSS_CELL_AVGAVG.CELL) WHERE OSS_CELL_AVGVIEW.thedate > (sysdate -2) and (OSS_CELL_AVGAVG.f_tch_availability- OSS_CELL_AVGVIEW.f_tch_availability ) > 1 and bsc = :BSC ORDER BY TCHAvailDec DESC


Notice the bsc = :BSC. A a query parameter as you like:


Preview results to check everything is working fine and click ok.

Next drag and drop this table into the detail row you added earlier. You should get:

Now select the newly added table and select the binding tab. Click on Dataset parameter binding. You should see the parameter binding input as shown:

Finally modify it (using edit button) to point the parameter to the running detail row value as shown:

Click ok... You are all set. Preview to see the result:


So in short:
  • make the main query
  • make the sub query using a constant parameter
  • Make the main table
  • Drag the sub table into a new detail row.
  • Link the sub tables dataset parameter to the main row detail using : row["columnname"] format

Enjoy!

Saturday, June 20, 2009

Winkey + R for linux

I have a habit of putting every single useful program renamed shortcut to folder I call run. Then I add the folder to the system path.
Now I am able to run the program by using the (winkey+r) shortcut and typing in the renamed shortcuts name.

I have often wanted to do winkey+r on linux. I found out the shortcut is ALT+F2. It is called Exectue Command. e.g. for cmd on windows use sh on linux. Enjoy! :)

Tuesday, June 9, 2009

Symbian Signing

You can sign your application quickly online from here:
https://www.symbiansigned.com/app/page/public/openSignedOnline.do
All you need is a valid email address. You get a mail to verify that you want the application. After confirmation you get another mail with the signed application link :). Therefore:
1 link (as given above)
2 mails incomming.
2 Link click in email (one confirmation ... one download)
and you are all set.

For advanced users:
Here is a link to offline self signing your symbian apps and how it works : http://wiki.forum.nokia.com/index.php/How_to_sign_a_.Sis_file_with_Self-Sign_Certificate

A bit of history:

I have been using a brand new nokia 5800 for about two weeks now. No Regrets! It is an awesome phone. Its accelerometer really got me thinking about cool programs like the ones I play on my mom's iPhone. Tried programming with PyS60. Really cool. Loved the audio.say api.
Here is a cool short code that saves a screenshot to e: (your memory card)
import graphics
#Take the screenshot
img = graphics.screenshot()
#Save it to the specified path
img.save(u"e:\\screenshot.jpg")
And another one that says what ever you want:

import audio
audio.say("Cool isn't it")

Both are complete python programs (needless to say... but mentioned for the un initiated).
And by using the bluetooth console (using my pc) I could freak out people in the room :).
But then I felt an urge that I had the first time I made a useful program.
Back in first semester while studying C++ i discovered a cool windows function that allowed me to send anonymous net send messages NetMessageNameAdd . It was nothing extraordinary but it facinated me. Because I could use my MFC skills to make an application that would actually do something that didn't come by default with windows. I wrote a complete multi destination/Multisource/cool gui application staying up all night :).

So, Now:
After looking around a bit at carbide c++ I can only wonder what information the symbian phone can give to a programmer that is not given away by any default application. Specifically I am interested in GSM parameters etc. lets see :)

Other useful links:
You can get the latest development releases of PyS60 from (The sourceforge page does not have development versions and I just want to play with my 58000 accelerometer) :
https://garage.maemo.org/frs/?group_id=854

Tuesday, May 26, 2009

Oracle list based queries ORA-00907

This should be helpful in case you get an error missing right parenthesis in oracle when using a list based query.

Needless to say... first check the parenthesis.

Next check if you are using sorting in the subquery.

E.g:

The following will not work:

select thedate,bsc,cell,ccalls, cmsestab
from
oss_cell_avg
where cell in
(
select cell from
oss_cell_avg
where
oss_cell_avg.ccalls > 0
and oss_cell_avg.cmsestab = 0
and oss_cell_avg.thedate >= (sysdate -2)
and oss_cell_avg.cell not like 'EC%'
order by cell asc
)

order by thedate desc,cmsestab asc


But the following will :


select thedate,bsc,cell,ccalls, cmsestab
from
oss_cell_avg
where cell in
(
select cell from
oss_cell_avg
where
oss_cell_avg.ccalls > 0
and oss_cell_avg.cmsestab = 0
and oss_cell_avg.thedate >= (sysdate -2)
and oss_cell_avg.cell not like 'EC%'

)

order by thedate desc,cmsestab asc

Enjoy!

Sunday, May 24, 2009

BIRT Report Scheduler

I am considering writing a BIRT report scheduler for a recent project.

I already have a working system in place using what I learnt from the following:
http://digiassn.blogspot.com/2005/10/birt-report-server-pt-2.html

It works pretty neat. I did need to make a couple of modifications to make it work on solaris (needless to say). First wget is not in system path therefore I needed to do the following :

WGET="/usr/sfw/bin/wget"

And later on:

$WGET "$RUNURL=RegionDailyPeak.rptdesign&__format=doc&Region=Central" -O "$EXPORTDIR/CentralDailyPeak.doc" -q

I also hid the server birt viewer url into a variable as :
RUNURL="http:///ReportXViewer/run?__report"

and EXPORTDIR as the path where I want the download.

Also I wrote some code to send emails as well and used that as a script. Additionally I needed to used the zip command to zip large reports before mailing them :

e.g.:
zip $EXPORTDIR/TCHGPRSTraffic.zip $EXPORTDIR/TCHGPRSTraffic.xls

and then mailed the zip file. Its working fine . Any parameters needing any form of dynamic nature were put into the report using a method I posted previously.


I am thinking of encapsulating all these cool things into a web server application using flex?

Anyone interested in such an application?

The following is a list of the features:

  • A simple admin login screen
It is intended to be a single user application. One admin password
  • Server Settings
Lists:
  1. adminusername / adminpassword.
  2. BIRT report viewer url (the run url... to which only report and further parameters will be passed as shown above)
  3. The report folder url.
  4. Notification email address
  5. mailer settings:
servername, Username, password, from , port, ssl

  • A simple report schedules list screen.:
This will have a list of schedules setup into a data grid with two columns. Crontab scheduling string, and a schedule name. Click on any one and select edit (other options: delete, new, duplicate)

  • Options for scheduling:
  1. crontab string,
  2. Select report from a combo box
  3. outputfolder
  4. zip or not to zip
  5. notify of running by email
  6. format : valid formats : pdf, doc, ppt, xls..... but not html. A simple downloader that will download a single url will be used. (KISS)
  7. mail to. (comma seperated destination addresses)
  8. append a date string to ( low priority )
  9. A parameters table. Which lists parameter vs. value. Nothing dynamic about it. You need to know the paramter names yourself to be able to use it. (low priority .... make parameters list available once you select the report)
  • A reports management screen:
Lists the .rptdesign files in the reports directory. With options of upload, delete, download, rename, copy

Seems simple enough :) But requires dedicated time.... which I am short on right now.... I need motivation... anyone interested in giving me some?

Optimizing Birt Viewer for solaris in simple steps

I was working on BIRT in windows. The performance in windows was awesome but when I moved the code to our solaris server a report that would take round about 10 seconds on windows took over a minute!

Here is how I improved the performance: (Note that I prefer jetty to tomcat due to its lightweight nature ..... Actually I use hightide ... but that's another story)
  • Make the default memory allocation greater:
open your tide.sh in the bin directory (hightide.sh in my case). Find and modify the following:

# JAVA_OPTIONS

# Extra options to pass to the JVM

TO:

JAVA
_OPTIONS="-server -Xmx3800 -Xms3800"

this basically tells the JVM to run in server mode i.e. -server (slower startup time.... faster execution time :)) and to start of with a default of 4GB ram with a max of 4GB ram (Xmx and Xms) . Change this to how much RAM you want allocated to JVM by default

  • Update your BIRT runtime to the latest and greatest version
I am telling you.... don't take this as a joke! :)

  • Update your JDK to the latest version
Again ... no kidding. Solaris 10 comes with JDK 5 by default. JDK 6 is so much better. Just download the .sh file for jdk from the sun website and install it. It is a simple command line script. You do not even need root privledges to run it. All you need is access to a folder you can write to (your home dir ?) and thats it.

After installation it note the location of your installation dir
e.g. /home/bla/Desktop/1.6.13_10
or whatever.

again modify jetty.sh (or hightide.sh) changing:

# JAVA_HOME
# Home of Java installation.

to :

JAVA_HOME="/home/bla/Desktop/1.6.13_10"

Thats all. Now give BIRT a spin. My runtime went from 10 secs to 7 secs :)!!

Monday, May 18, 2009

BIRT dynamic parameters

BIRT is simply amazing. There is no denying that. The best thing about it is that it is open source and free to use. This makes it simply an amazing solution to work with. It is really great as long as they keep the normal report rendering api free and open source :)

This post is about a particular scenario in reporting. It relates to time reporting. Most commonly we need trend analysis of the past week to better analyze the data. But you dont want to put in the date / time of the last week manaually. Using oracle BI Publisher I am used to taking care of these using sysdate inside of SQL Queries. But BIRT provides a better way.

Heres a step by step howto.

Create two report parameters. One for start and one for the stop time:

Remeber to uncheck the Is Required property:

This is because we are going to generate the value dynamically in case the parameters are not set :)

Needless to say you need to link these to your query (data set) parameters:


Now click the main surface of your report in the layout tab:


Then click the script tab and add the following code to the initialze event handler as shown:


function getdateBehindByDays(days)
{

var cal = java.util.Calendar.getInstance();
cal.add(java.util.Calendar.DAY_OF_MONTH, -days);
return (cal.get(java.util.Calendar.MONTH) + 1 ) + "/" + cal.get(java.util.Calendar.DAY_OF_MONTH) + "/" + cal.get(java.util.Calendar.YEAR);

}

if (params["StartTime"].value == null)
{
params["StartTime"].value = getdateBehindByDays(7);
}

if (params["StopTime"].value==null)
{
params["StopTime"].value = getdateBehindByDays(0);
}


What this code is doing is that it is sending the start date back by one week and the stop time to today :). Just change the values '7' and '0' to what you require :)

Now just run the report and set the parameters you want to be made dynamic to null:

Viola! welcome to the wonderful world of automated reporting :)