Numbering of records in a Group

I just started learnig CR.
1) I want to know how to serially number records which form part of a particular group. "Group Number" gives the group a num and "Record Number" gives a global num. I want each list in a group should have a distinct set of numbers.
Sorry if the question is very silly, I enquired couple of people but no help!

2) I also want to know how do we display a CR from VB and print it to deskjet printer.

Version info:
CR 9 & CR 4.2
VB6Write your own formulas to count them.
e.g. in group header
whileprintingrecords;
numbervar x := 0;

in detail:
whileprintingrecords;
numbervar x := x + 1;|||Like I said in the first instance Iam a novice in CR (that explains my username). Though you have stated to add some variables in group header and increment in detail section, I donot see any "code window" as in VB where I can add the code suggested. I would be happy if you could take some pains to explain me that as well.

Also let me know how to connect, open & print CR9 report through VB6, the database being Access 2003. The connection needs to be dynamic i.e. I should be able to manipulate the connxn programatically through VB6.|||Nobody has responded. Can moderators help me.|||right click on formula fields in field explorer, select new then code window for the formula will be opened. u need to do this in crystal desinger not in vb6 form designer.

number of connections

Hi All,
where can I find the information about number of connections to the particular database?
thankssp_who
sp_who2
sp_whodunnit :rolleyes:|||Thank you.|||tom,

what is sp_whodunnit? I just googled it and all I got was some wierd page in German.|||Sean

sp_whodunnit is a seldom used stored procedure primary utilized by covert security personnel to discover the perpetrator of nefarious acts upon the public databases.

In other words, it's a small attempt at humor ... twisted humor, but humor nevertheless.

:D

edit: Captain, we've got one here. He took it hook, line, and sinkersql

Nulls when summing a group in an expression

Hi guys,

I know this has been discussed before, but not in a way that solves this particular problem. I have some data in a table that is grouped by rows, but when i sum the fields in the group i am getting an error because one of the grouped rows contains a null.

Now when i have a one to one relationship between a dataset field and a table cell, checking for a null is easy. But how do you do it when you have several fields that are being summed for that table cell?

In this particular case, i have a work around - there are only two data rows per row group (paid revenue, and unpaid revenue), so i can check them individually for nulls by using First(Fields!Revenue.Value) and Last(Fields!Revenue.Value). This is the expression i used:

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( First(Fields!Revenue.Value)),

First(Fields!Revenue.Value),

0

)

+

IIf(

Not IsNothing( Last(Fields!Revenue.Value)),

Last(Fields!Revenue.Value),

0

),

0,

true,

false,

true

),

"-"

)

The first expression in the outer IIf is worthless, because using Fields!Revenue.Value only checks the first row in the group, and in my case it is always the second row that contains the null.

What is the best way to account for the null values?* It seems i can't access the .Value field like an array (i.e. Fields!Revenue.Value(1)). Should i be passing Fields!Revenue.Value through to a piece of custom code to sum it?

Thanks!

sluggy

*The data i am working with is sourced from a cube with an mdx query. The query uses a COALESCEEMPTY(<measure>, 0) function, but it seems the data provider being used is changing the fields that were coalesced back into nulls. Which is ok - at least i am getting the rows, prior to using the coalesce the rows were being dropped. I'd rather have nulls than have the row missing from the dataset.

Not sure if I understand your question completely, but you can use IIF inside of an aggregate function if you want to skip null values. For example, =Sum(IIF(IsNothing(Fields!Revenue.Value), 0, Fields!Revenue.Value)).

|||

Hi

can you try this out and check whether it is working or not.

=IIf(

Not IsNothing( Fields!Revenue.Value) and isnumeric(Fields!Revenue.Value),

FormatCurrency(

IIf(

Not IsNothing( Cint(First(Fields!Revenue.Value))),

Cint(First(Fields!Revenue.Value)),

0

)

+

IIf(

Not IsNothing( Cint(Last(Fields!Revenue.Value))),

Cint(Last(Fields!Revenue.Value)),

0

),

0,

true,

false,

true

),

"-"

)

Thanks,

Srinivas Reddy.

|||

Srinivas, Fang,

thanks for your answers, but neither work. In this case casting the fields has no effect, as they are already being returned as ints, and you can't typecast a field that is already null.

I have noticed something further, and consequently some of my original information was incorrect. What i have noticed is that nulls are NOT being returned - zeros are. So if i code this expression:

=First(Fields!Revenue.Value) + Last(Fields!Revenue.Value)

it gets evaluated no problem - i get the revenue from the first row plus zero from the second. But if i do any sort of aggregation type function on it, like Sum or Avg etc, then i get an error.Can anyone explain what is happening here, or suggest how i can determine exactly what error is occurring?

Thanks,

sluggy

|||

If you are designing and previewing the report in the report designer, you can check the error message in the task bar.

What's the data type of the Revenue field? One possible reason could be you are summing mixed types. Aggregate functions require all the values passed in are of the same type.

|||

Thanks Fang, i will check into that.

sluggy

Null result returned even though IS NOT NULL specified.

Hi guys,

I've got a query on a particular table returning an odd result:

SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStation

This query returns the rows I'd expect plus a null row. This doesn't happen in databases at other sites, or in other tables at this site. The following query behaves as I'd expect returning only non-null AccountNumbers.

SELECT DISTINCT AccountNumber
FROM Suppliers
WHERE AccountNumber Is Not Null
ORDER BY AccountNumber

I can't reproduce these results on another site on a table of the same structure, or on another table at this site.

Any suggestions as to what might be going on?

Pertinent info:
--
select @.@.Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
dbcc checkdb
Abridged result:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'POS'.
--
SELECT * INTO #Inv FROM Invoice

SELECT DISTINCT WorkStation
FROM #Inv
WHERE WorkStation Is Not Null
ORDER BY WorkStation

Does not reproduce this problem (and so is a probable fix) but the questions remains, what causes this?

TIA,
Karl.are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get|||Hi Rudy,

Thanks for your reply

are you sure it's null, and not perhaps an empty string instead? try this --SELECT sum(case when WorkStation is null
then 1 end) as nulls
, sum(case when WorkStation = ''
then 1 end) as empties
, sum(case when WorkStation > ''
then 1 end) as somethings
, count(*) as total_rows
FROM Invoice and let's see what kind of totals you get

Returns:
nulls empties somethings total_rows
---- ---- ---- ----
25212 NULL 2660565 2685777

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)|||It would seem that the nulls are slipping into somewhere they shouldn't be for you too :)no, what makes you say that?

25212 + 0 + 2660565 = 2685777

so everything is accounted for

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?|||no, what makes you say that?

An incorrect assumption on my part. Moving right along :)

you said earlier that this query returns a NULL row --SELECT DISTINCT WorkStation
FROM Invoice
WHERE WorkStation Is Not Null
ORDER BY WorkStationmay i ask how do you know it does this? where are you running the query, and how do you detect the NULL?

This error was reported by the client who uses this database (the app they use uses ADO) but I've been using Query Analyzer to see it.

My method of null detection and confirmation is twofold:
- Occular examination (it's the first record and there's only ~20 rows)
- SELECT WorkStation
FROM Invoice
WHERE WorkStation = 'NULL' returns no rows.

-Karl.|||of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL|||Thanks for your time Rudy,

of course WHERE WorkStation = 'NULL' returns 0 rows

the correct syntax is WHERE WorkStation IS NULL

My intention for that second query is to show the 'NULL' result from the first query was in fact a null, and not a string containing the phrase 'NULL' as the two appear identical in the result pane.|||I know this has been somewhat covered, but just for gits and shiggles, what happens if you do this?

SELECT DISTINCT WorkStation
FROM Invoice
WHERE IsNull(WorkStation, '') <> ''
ORDER BY WorkStation|||Thanks for your reply Teddy,

That query returns the results I hoped that I'd get from the original one. It returns a list of workstations and contains no blank, or null, records.

I think I'm just going to:
-select the data into a temp table
-drop the original table
-select the data the original table
as I believe this will cease the behaviour, but it does mean we never find out why it occurred...|||If the query I posted returns your intended results, I would highly suggest you make sure you don't have empty strings in your workstation column. What that clause does is first convert null values to an empty string, and then filter by anything that doesn't have an empty string. I would be curious if you had some fields with just spaces in them as well, as there is an environmental setting that would automagically trim those spaces to ''.

Example:

-- Create a scratch table
CREATE TABLE #NullVsEmpty (test_column char(1))

-- Dump two rows into scratch table. One is empty, one is null
INSERT INTO #NullVsEmpty VALUES ('')
INSERT INTO #NullVsEmpty VALUES (NULL)

-- Display whats in scratch table for reference
select * from #NullVsEmpty

-- Count where test_column is not null
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column IS NOT NULL

-- Count where test_column is not EQUAL TO an empty string.
-- Note this returns 0 because Null cannot be directly compared to a scalar value
SELECT COUNT(*) FROM #NullVsEmpty WHERE test_column <> ''

-- Convert test_column to a finite value and then do comparison
SELECT COUNT(*) FROM #NullVsEmpty WHERE ISNULL(test_column, '') <> ''

DROP TABLE #NullVsEmpty