How to code a Ford Stereo (MkIV Mondeo)

I crashed the car BOO!

The garage fixed it YAY!

but they didn't put the code back into the stereo.

My wife (bless her cotton socks) was struggling, she says the manual is not clear on how to do this.

We know the code - this will not help if you don't know the code.

Lets say the code is 3214

To enter this into the stereo you would press the following keys

1,1,1,2,2,3,4,4,4,4

I.e. press the positional key enough times to get the position to the code number - Position 3 should show number 1 so you only press it once. Position 4 should show number 4 so you press it four times.

When the correct code is shown, press 5 to comitt it to the radio. the radio should now come on. If not then contact your garage, as you may have the wrong code.

SQL queries and Excel - refresh query definition

Scenario:

In SQL I have a table function which returns a set of data.

Because you can't access table functions directly in Access and Excel (I am working in Excel at the moment..) we also have to create a view

create view dbo.myview as select * from dbo.mytablefunction

When you query this view in Excel, you get back the expected data set.

Now I enhance the table function to add new fields to the data set.

If you run select * from dbo.mytablefunction() then the new fields are returned. If you try to query the view in Excel, the new fields are not available - Confounding mystery!

After some poking around and not finding a solution on the web, I 'stumbled' upon the solution.

When I ran the view (select * from dbo.myview), it also did not return the new fields. I had to modify the view (open it in MSSM and execute the alter view script) and then the new fields were available. now when I access the view in Excel, the new fields are there - hooray!!

It is a shame that SQL does not warn you (or give you a prompt to update dependent queries) - yet another reason not to use select * I guess.

Crystal Reports - Show group headings on drill down reports only

sometimes, when you want to drill down your drill down report will contain a number of records and you only want to show the headings once and only show them on the drill down report.

To achieve this, put a second section into the group header (right click on the header and choose 'insert section below)

Move the headings into the new section and then choose Section Expert

click the X-2 logo button and enter the following formula

if drilldowngrouplevel =0 then true

The group headers will then only show on the drill-down report.

crystal reports using t-sql table functions

Using MS SQL2005, I have created a table function.  Although you can see it in CR2008, when I try to access it directly I get an error message 

Database Connector Error: 'ADO Error Code: )x

Source: Microsoft SQL Native Client

Description Line 1: Invalid procedure number (0), Musst be between 1 and 32767.

SQL State 42000

Native Error: [Database Vendor Code: 1005]'

I think it may be to do with the fact that when you select from your table function in MSSM you need to put the parameters in parenthesis and even if there are no params, you still need to add the brackets to the function call.

The solution to this is to create a view which calls the table function.  This view is then available to CR2008.

HOWEVER...

If you then change the columns that are returned by the table function, the CR dataset is not automatically updated.  In order to update the dataset, you need to edit the view in MSSM and re-save it, then go into CR2008 and right click on the table and Set Datasource Location.  Your new data should now be available for the report.

This is fine for one table, where you know the data has changed.  If you are not sure, or have serveral data maps that have been modified, then it is probably best to use the menu option Database->Verify Database.

Publish Post

Lilie queries not returning values

I had written a new view in the main Lilie database which listed some data that was taken from the Episodes view. It worked fine for me in MSSM, Excel and Access, but when other users tried to run the queyr, they got the column headers (so that the query was running) but not the records (i.e. nothing was selected)

At first I thought it was database permissions. Becauase I had created the view, perhaps other people did not have the rights to execute it or run a select on it so I GRANT ed full permissions to all users, but this did not solve the problem.

It turns out that the Episodes view checks that the logged in user has the rights to access specific episdoe types at specific locations and that the UserEpisode table had not been completed when the user records were created. I added the correct types against the user names and the query ran as expected

Suppress blank pages at the end of the report

If you do a page break at a group footer, you end up with a blank page (it's not blank - it contains the report footer..) at the end of the report. To prevent this from occuring, go into the section expert and tick the box to throw a new page after the section then put the following condition into the conditional formula:

not onlastrecord

this will stop the page break after the last group footer and you can then output report totals on the rest of the page (assuming there is still room)

How to Hide an empty Sub Report in Crystal Reports 2008

I have generated a listing report which will occassionally have data in a sub report which is embedded in the details section. Despite setting the sub report to hide subreport if blank, the space taken up by the sub report object was still showing on the report.

I finally worked out that the solution was to create a subsection Details B and put the subreport in that and then set the subsection to suppress if blank. This had the desired results.