Annoying Little Gaps in Active Reports

I have quite a bit of work with ActiveReports for my work, and this was something I encountered so many times in creating reports, and commonly appeared in my reports with tables, where the detail section would contain the data. Like this:

What i kept on getting were these annoying little gaps between rows and I’d reduce the font size, check the lines etc and could not get rid of them. A fellow trainee asked me if i knew how to fix this and i told him i hadn’t figured it out yet and a senior told him that its normal that there were these little gaps. But as I worked more with reports, the solution was simple really:

The problem lay in the title label as shown above, even though the font was small sized enough, the box itself (the grey outline) overlapped past the yellow header box. It can so minute sometimes that you wouldn’t spot it. So in properties, make sure the height property of the size of the text box is just enough to fit the text and the box itself doesnt go beyong any boundaries. Also, remember that your section height be dragged up tight to the bottom of the header.

This is one of those kind of little issues that you may probably solve immediately or not be able to see right off the bat. Hope I sped up the process for some of you.

Setting your default database in SQL Server Management Studio

Another quick server management studio quick tip. We had just set up a test server for our project, but everytime I used SQL server management studio, and ran a query, it would run it on Master database and not on our database. Just for convenience, you can set up the default DB as so:

1. In your object explorer > Expand Security > Expand Logins
2. Right click your login (in this case WeiL) and select ‘Properties’
3. Under General, you will see a drop down called “Default Database”
4. And ta-da! its done.

Displaying user’s computer’s date and time NOT server datetime

This sounded straightforward at first, I had to display the user’s date and time on an online web report. And from searching online, seems it can only be done in Javascript. Here is the code:

var months = new MonthArray(‘Jan’,‘Feb’,‘Mar’,‘Apr’,‘May’,‘Jun’,‘Jul’,‘Aug’,‘Sep’,‘Oct’,‘Nov’,‘Dec’);
var vUsersDate = new Date();
var day = vUsersDate.getDate();
var mon = vUsersDate.getMonth() + 1;
var year = vUsersDate.getYear();
var hour = vUsersDate.getHours();
var min = vUsersDate.getMinutes();
if (hour < 12) { a_p = “AM”; } else{ a_p = “PM”;}
if (hour == 0) { hour = 12;}
if (hour > 12) { hour = hour – 12;}

//quickfix: min.length is undefined otherwise
min = “” + min;
if (min.length < 2) { min = “0″ + min; }

//dd-MMM-yyyy hh:mm tt
document.getElementById(‘cDate’).innerText = day + “-” + months[mon] + “-” + year + ” “ + hour + “:” + min + ” “ + a_p

This works great, however, since minute will only have 1 digit if its <10, I had to add a 0 in front of it. I thought it was a simple case or checking the length and adding a ’0′, but I have no clue why I get an error saying that min.length is undefined. Perhaps, someone could enlighten me on this in the comments, and that would be great, cause well quickfixes are basically, well, quickfixes

SQL tip #2

This one will be singular as its a small tip I just learnt a minute ago.

I am currently converted some Access forms and Reports, and somehow this one record source didn’t work:

SELECT TOP 100 PERCENT pl.PositionID, (SELECT Title FROM atbv_Personnel2_Positions WHERE PositionID = pl.PositionID) AS Title, pl.Percent AS PosPercent, pl.Location, pl.LocationID, p.Name, pl.FromDate, pl.ToDate, ISNULL(pl.MhrsRate, 0) AS MhrsRate, pl.CostGroup, (SELECT Description FROM stbv_System_Codes WHERE CodeType = ‘Personnel2_Location’ AND Code = pl.Location) AS LocationDesc
FROM atbv_Personnel2_PositionsLocations AS pl INNER JOIN atbv_Personnel2_Persons AS p
ON pl.Login=p.Login
ORDER BY pl.PositionID, ISNULL(pl.FromDate, ’1900-01-01′);

Thats quite a bit of SQL to look at, so just look at the code in red. I need to select the field Percent from the view atbv_Personnel2_PositionsLocations and Percent is a command in SQL, so you will get the following error:
“Incorrect syntax near the keyword ‘Percent’.”

So, my colleague showed me a simple solution just SELECT pl.[Percent] instead, as these brackets represent selecting a column, so this works fine!

Simple basic everyone should learn i reckon.

displaying numbers with thousand separator and .00

On my previous project, I needed to select a number stored in the database, such as 36749384 or 934834.23 which were monetary values, and display them as 36,749,384.00 and 934,834.23. Basically, in monetary terms.

I was thinking of using string manipulation but that would only work in my .net development, so if you need these kinds of format in other applications, you can do it in the SQL statement like so:

SELECT convert(varchar(50), convert(money, Actual), 1) AS Actual from table

And even though I realised after that in ActiveReports you could actually just apply a mask to the textfield or label (D’oh!), in properties > OutputFormat > #,##0
I thought this SQL statement was a pretty nifty way of getting the desired output i wanted without doing any programming on the data.

SQL tips #1

definitely purely for newbies, helpful tips my colleague in Houston passed on to me, so thanks to Alex, SQL guru.

Playing it safe
This is very useful if you are running SQL that will change data or insert data. So this is how I recently wrote a buncha of SQL codes that would modify menus, set up data for report collections etc.

BEGIN TRANSACTION
…
(SQL code)

COMMIT
ROLLBACK

Firstly run the code together with BEGIN TRANSACTION. After this, check the affected tables, views etc, or how it has changed your web app if applicable and if all is in working order, just select COMMIT and run this and the changes you have made would become done. If its not what you desired, just highlight ROLLBACK, and the code you have run would not be applied. Do note that if you neither COMMIT or ROLLBACK, the SQL code would not have been, well, commited.

sp_help and sp_helptext (MS SQL Server Management Studio)
run
sp_help table/view
sp_helptext view/stored procedure
in MS SQL Server Management Studio

and sp_help displays all kinds of useful info about the table/view, like columns, keys, constraints. sp_helptext displays the sql definition of the view/stored procedure. This quick method has worked out to be very useful for me.

shortcuts (MS SQL Server Management Studio)
hold down SHIFT + F1 when selecting any SQL syntax, meaning SQL code like GROUP BY, DELETE, ALTER, IN, UPDATE… (you get the picture), for me basically any code that’s in blue
and this opens a help window, showing the definition of the syntax and how it is used.

There are many other useful shortcuts which I will probably discover in time to come, but these are the few that I have been using quite a bit of lately. I will definitely be posting up any other SQL tips I learn as I go along for sure.

Render HTML tags in DB in ActiveReport pdf or HTML

I had to tackle showing actual HTML formatting that was stored in the DB by our company’s own texteditor which had the basic formatting like bold, italics, font colout etc, and digging around the active reports forum basically just told me I needed to version 4.0+ for it to be automatically rendered in RichTextBox.

The solution that arose from asking around was that in the Detail_Format() section in the code behind of the report put this line:
ctl.cComments.Html = fld.Comments.ToString()

Where cComments is a RichTextBox and Comments is the name of the datafield

and that worked beautifully! Except it would not render in Arial, the default font, even though I formatted the richtextbox to be so. Thinking of many complicated ways to do this, the solution was as simple as:

ctl.cComments.Html = “<font face=Arial>”+fld.Comments.ToString()+”</font>”

This is probably not a fix and more of a quickfix or hack, but works as well otherwise.

Mandatory rambling first post

This blog was born out of the necessity of my limited brain to retain all I learn on the job in dealing with technologies alien to me. I am hoping this will become a place I can help others like me who need things explained in simple terms that newbies can understand as well. Some things that appear here may be specific to my company’s technology, but I will try and log stuff here that I believe can help others as well. Feel free to comment or email me to discuss anything on this site. Here’s to the first post!