I enjoy programming and creating great applications for users to electronically track and store their data, but the users will invariably want me to create reports for them from the data which we collect, and I do not enjoy this at all. It is tedious work, and it is never ending. There are always new ways to calculate or format the data which result in request to change the existing reports or add new reports. This is just not a good use of my time. So, I frequently try to expose the data to the users so that they can access it using Excel and create their own reports from there.
In one particular case I wanted to have an inline bar graph to visually show how each of the rows in an Excel spreadsheet related to the other rows. What I was looking for was a sideways candlestick chart of sorts. After some searching I found a solution built into Excel. It is possible to use the REPT function to repeat a character a specified number of times. My favorite solutions are the simple ones, and this is about as simple as it gets, here is an example: =REPT(“█”,L8) The image shows an example of the results.
At work I maintain some databases that are updated on a daily or weekly basis from a master data source. I inherited a stored procedure that would clear the data and then import the new data, but occasionally there would be problems with the import that would leave the data table empty. Also, the new data set from the master system only includes active records, so I would not have a way to maintain data that is no longer active. So, to improve things and make my job easier I created a Microsoft SQL Server DTS (Data Transformation Services) package that would automatically insert new records and update existing records
I work for an international manufacturing and engineering company, and like other companies in this category we must extensively use standards. But, in IT, we tend not to follow some established guidelines. One example is the date and time format which is used by staff when they update our help desk software. In Europe they write day/month/year and in the US we write month/day/year, so at the beginning of the month at the beginning of the year it can be hard to tell if a service request is only a day old or a month old.
I have experienced numerous cases where I wanted to dump database information into an Excel sheet for the users. Usually, the application that the data is entered through is a ColdFusion web application, so it makes sense to dump the data directly from the web-based application. To do this I have used two basic techniques. The first technique is to generate a ColdFusion page which returns an Excel file as the result so that users can save the file. The second technique I use is to dump the data to a web page that can be accessed inside of Excel using the import data from a web query method. Below I will give some examples of both methods.
Technique #1, Method #1
<cfsetting enablecfoutputonly="yes">
<cfset tab="CHR(9)">
<cfquery name="get_Data" datasource="Deg">
SELECT username,EMail,
FROM Tbl_Data
</cfquery>
<cfheader name="Content-Disposition" value="inline; filename=export.xls">
<cfcontent type="application/msexcel">
<cfoutput query="get_Data">
#Currentrow##TAB##username##TAB##EMail##chr(13)#
</cfoutput>
When you need to perform a SQL select and do substitution at the same time (for example if you would like to remove nulls from the selection) you can use the CASE command. Here is an example.
SELECT
RTRIM(LTRIM(AssemblyNumber)) AS AssNum,
RTRIM(LTRIM(PartNumber)) AS PrtNum,
CASE WHEN ItemNotes IS NULL THEN '' ELSE RTRIM(LTRIM(ItemNotes)) END AS ItemNotes,
CASE WHEN PATINDEX('%[^0-9]%', ISNULL(ItemQuantity, '*')) > 0 THEN 1 ELSE RTRIM(LTRIM(ItemQuantity)) END AS ItemQuantity,
CASE WHEN ItemHeight IS NULL THEN '' ELSE RTRIM(LTRIM(ItemHeight)) END AS ItemHeight,
CASE WHEN ItemWidth IS NULL THEN '' ELSE RTRIM(LTRIM(ItemWidth)) END AS ItemWidth,
CASE WHEN ItemLength IS NULL THEN '' ELSE RTRIM(LTRIM(ItemLength)) END AS ItemLength,
CASE WHEN ItemMaterial IS NULL THEN '' ELSE RTRIM(LTRIM(ItemMaterial)) END AS ItemMaterial,
CASE WHEN ShapeIH IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIH)) END AS ShapeIH,
CASE WHEN ShapeIW IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIW)) END AS ShapeIW,
CASE WHEN ShapeIL IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIL)) END AS ShapeIL
FROM zzzImportManualBOMs
ORDER BY
RTRIM(LTRIM(REPLACE(REPLACE(AssemblyNumber, '.', ''), '_', ''))),
RTRIM(LTRIM(REPLACE(REPLACE(PartNumber, '.', ''), '_', '')))
I have a small problem with many of the web-based, database-driven apps that I make for my company. The problem is that the users want to have a drop-down box with all the possible user names (or person’s names) when they need to select a persons’ name, which is reasonable until there are multiple selections like this on one page. Currently there are around 1000 users at my company, and we have some pages with up 40 select boxes for user names. That’s over 40,000 lines of code just to display the user select boxes, which means the page size would be well in excess of 4 megabytes.
This page has been moved …