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