Software Development

Crystal Reports: How to make a report with a parameter which can turn report detail on or off

A friend of mine recently asked me if I could make an example for him of a Crystal Report with a parameter to control whether the report detail is shown or hidden. So I decided that I would answer his question through this post on my blog. I decided to approach the problem by having a sub-report to display the detail data, there are also other ways to achieve the same result – this is the method which came to mind at first. When a parameter called ‘ShowDetail’ is set to ‘1’ then the sub-report will not be suppressed, and when the parameter is set to ‘0’ the sub-report will be suppressed.

For this example I have used the Microsoft Northwind database. You can download a backup of the database from this link Northwind.bak and restore it to your MSSQL database server. Restore the database with the name ‘Northwind’. Here is the link to my Crystal Report file.

Once you have restored the database you will need to set up an ODBC connection to your database using the ODBC wizard. Make sure that the ODBC wizard is for the same platform as your Crystal Reports is for. ie if you are using a x86 version of Crystal Reports you will need to use the x86 ODBC wizard. For my example I also created a separate SQL authenticated login to the database called ‘northwind’ and assigned the ‘Northwind’ database as the default database for the user.

When using Crystal Reports I prefer to use a database ‘Command’ to select the database. Once you have added the ODBC connection in Crystal Reports one needs to select the ‘Add Command’ button under the connection.

Create the sub-report, you will see that in the example I have made the sub-report really small (you may have trouble finding it), but have allowed it to grow, this is to make sure that when it is suppressed that it does not cause the report to have big spaces between the rows.

When entering the database command for the sub-report, you will see that I have created a parameter called ‘PRM_OrderID’ which is used in the SQL where clause to filter the data. This parameter needs to be set as numeric, as if it is not numeric then it will not match when the report is linked from the main report. When viewing the main report, right click on the sub-report area and choose the option ‘Change Subreport Links…’. Here you will see how I linked up the OrderID from the main report to be carried forward and mapped to the parameter which I created in the database command. I prefer to do the data filtering using the database rather than in the report itself as I understand the performance of the report will be better.

In the main report I have added a formula for the detail row fields to control whether the field is displayed with a bold font or a regular font. To see the formula right click a field and choose ‘Format Object’ and select the ‘Font’ tab, then select the formula button on the right of the Style parameter. The formula is as follows:

IF {?ShowDetail} = ‘1’ THEN crBold ELSE crRegular

A similar formula is used to display or hide the sup-report itself. When you right click on the sub-report select ‘Format Object’ you will see that there is an option to suppress the sub-report. Click on the formula button and you’ll see that the I have entered the following formula which will suppress the sub-report and thus hide the report detail.

IF {?ShowDetail} = ‘1’ THEN FALSE ELSE TRUE

The next step of this project would be to build this report into an application and have the user select whether the report detail is to be shown through a user interface. The application would then pass the selected parameter through to the report. A future blog post could be done on how to do this in the future if there is enough interest shown in this post.

I hope that you have fun experimenting with Crystal Reports.

Boys and their toys: My first Ardunio project

Recently I have started wondering how I can expand myself in programming and software development. For a while it seems like I have not been making much progress forward. So I attended the Tech4Africa conference with a friend of mine, and one of the things I learnt about during the conference was Ardunio electronic kits.

Part of my history includes loving electronics as a child, and all that I wanted to do was to attend the technical high school where I would learn about electronics. I did really well at school, and just missed being the top student in the class. And after school I had hoped to attend Teknickon to study electronics further, however that plan was stumped when my application was rejected and they accepted me for heavy current, which I turned down as I was not interested in heavy current. So I ended up doing a BCom majoring in Infomatics. I did really well in my programming and IT classes, and eventually ended up taking a short Java programming course with VZAP after I finished my degree. And that took me into the world of being a software developer which I have been doing for the last 10 years.

Now it’s like a flood coming back to me, diving yet once again back into the world of electronic, but not just any electronics, the nice thing about the Ardunio kits is that one can write small programs which are uploaded into the micro-controller to control the behaviour of the chip.

You can program the chip to listen for buttons being pressed, and then do things like turns LEDs on when the button is pressed. And as you’ll see in the video you can change the behaviour quite easily by manipulating the program, so when the button is pressed you don’t just have the light turning on, but you can control different lights to turn on and in different patterns.

Basically the Ardunio allows one to write programs to allow a computer to interact with the outside world. I am looking forward to making more complex projects than the one seen in the video. Keep coming back here for updates, and if you have any experiences with Ardunio programming or have any questions or comments, please post away and I’ll respond.

Dynamic SQL queries within a trigger using ‘sp_executesql’

This week I was working with some triggers which had a lot of repeated queries which did exactly the same job, with the only difference is that each query inserted data into a different table. Each of the repeated queries were in an if statement to check if the location was correct and if it was the same location then the data was inserted into a location specific table.

When ever I see code that is repeated it makes my blood curl, I much perfer it when the code is simple and can be maintained easily, because who wants to come back next month, or next year and have to remember that all the queries must be updated, and whats worse what if the change is complex, then it means that each of the queries must have the same complex change made to it.

So I googled and found that there is a function is SQL called sp_executesql which can execute a sql command which is stored as a variable. So I took the original query and turned it into a string. And then concatenated the different table names, depending on the location to the end of the table name in the sql statement.

Have a look at the example code:

SELECT @locationsuffix = SUFFIX FROM LOCATIONMAP WHERE LOCATION = @location

SET @sqlcommand = ‘SELECT @content=DATA.CONTENT ‘
+ ‘FROM DATA’ + @locationsuffix + ‘ AS DATA ‘
+ ‘WHERE DATA.NAME = ”’ + @name + ””

EXECUTE sp_executesql @sqlcommand, N’@content nvarchar(31) OUTPUT’, @content = @content OUTPUT

In this example the command executes the dynamic sql which selects data out a location specific table and returns the content back to the main context do that the content which was selected can be used further.

Please leave comments or feedback for me.

How to write an ebook on Business Management Software

One of my latest ideas is to write an eBook on Business Management Software.. This will be the first book that I am writing, so it’s going to be a short book which covers some of the main points of what I think Business Management Software is about, but I would really like to hear from people who are looking for business management software for them to tell me what they would like as content in the book, what topics would you like me to discuss, what pains do you currently experience with your current software which is making you look for something better?

Some of the topics I will be writing about include:

Type of business management software:

Accounting
ERP
Time management
Stock Control
Custom written software vs Off the shelf software

What to look for in business management software.

Winning staff over to use the software (Staff buy in)

What makes business management software successful?

Reporting, what kind of reporting would you expect to get?

Mobile? Cloud?

Please feel free to leave comments with questions or topics which you would like me to include in my book.

Thank you.