DistinctCount

I am running into a problem using DistinctCount - I have values in the report
that can either be a valid value, an empty string or null - I believe the
nulls are being discarded in the counts, but is there a way to make sure that
the empty strings are not being counted?This may be out of your jurisdiction, but it sounds like the data needs to be
cleansed? If NULL is valid in the column then you probably shouldn't have
empty string. In any case, if you're using stored procedures, which would be
the recommendation, you can perform some data cleanup there so you're left
with valid values. I'd check with the DBA on why there are both empty
strings and NULLS and use one or the other for absence of "valid data"
"Myles" wrote:
> I am running into a problem using DistinctCount - I have values in the report
> that can either be a valid value, an empty string or null - I believe the
> nulls are being discarded in the counts, but is there a way to make sure that
> the empty strings are not being counted?|||Yes J.P., thank you - you hit the nail on the head - on all accounts.
Unfortunately, I am already 'blue' in the face - but suppose the right thing
to do is hit my head again...I am not sure it is going to change anything,
however, and so am still looking for a way to filter this stuff out of the
counts. Thanks for the reply!
"JP.Sklenka" wrote:
> This may be out of your jurisdiction, but it sounds like the data needs to be
> cleansed? If NULL is valid in the column then you probably shouldn't have
> empty string. In any case, if you're using stored procedures, which would be
> the recommendation, you can perform some data cleanup there so you're left
> with valid values. I'd check with the DBA on why there are both empty
> strings and NULLS and use one or the other for absence of "valid data"
> "Myles" wrote:
> > I am running into a problem using DistinctCount - I have values in the report
> > that can either be a valid value, an empty string or null - I believe the
> > nulls are being discarded in the counts, but is there a way to make sure that
> > the empty strings are not being counted?|||Myles,
Try using the COALESCE(fieldname,0) function in your SQL to change Null
into what is more appropriate, or you could use the CASE WHEN trim(fieldname)
= â'â' THEN null ELSE fieldname END statement to change the empty strings to
nulls.
You could also consider using the filter section of the dataset.
HTH
-walter
"Myles" wrote:
> Yes J.P., thank you - you hit the nail on the head - on all accounts.
> Unfortunately, I am already 'blue' in the face - but suppose the right thing
> to do is hit my head again...I am not sure it is going to change anything,
> however, and so am still looking for a way to filter this stuff out of the
> counts. Thanks for the reply!
>
> "JP.Sklenka" wrote:
> > This may be out of your jurisdiction, but it sounds like the data needs to be
> > cleansed? If NULL is valid in the column then you probably shouldn't have
> > empty string. In any case, if you're using stored procedures, which would be
> > the recommendation, you can perform some data cleanup there so you're left
> > with valid values. I'd check with the DBA on why there are both empty
> > strings and NULLS and use one or the other for absence of "valid data"
> >
> > "Myles" wrote:
> >
> > > I am running into a problem using DistinctCount - I have values in the report
> > > that can either be a valid value, an empty string or null - I believe the
> > > nulls are being discarded in the counts, but is there a way to make sure that
> > > the empty strings are not being counted?

Displayong "Empty string" to a textbox on the report

Hi All!

I was checking the value of a field and if it is empty sending empty string to the textbox if not only the first few values and it is working but on the empty field something like "#Error" is being displayed.

here is the code:

=Iif(Fields!Lname.Value <>””, Fields!Lname.Value.ToString().Substring(0,10),"")

What I want to acheve is : If it is not zero to take the first 10 characters and if not to send an epmity string to the textbox.

Any help plz?

Thank you in advance!

In your expression, you are making the assumption that the string will be at least 10 characters. If it isn't 10 characters you will get an error. I am not sure what you are trying to accomplish but see the expression below. It will truncate the field if it is over 10 characters.

=Iif(Fields!Lname.Value.ToString().Length() > 10, Fields!Lname.Value.ToString().Substring(0,10), Fields!Lname.Value)

|||

here is the code:

=Iif(Fields!Lname.Value <>””, Fields!Lname.Value.ToString().Substring(0,10),"")

What I want to acheve is : If it is not zero to take the first 10 characters and if not to send an epmity string to the textbox

Thank you.The one that you send to me is not doing what i was looking for. Thank you very much.

|||

My expression does the exact same thing as yours except when there are less than 10 characters it will not attempt to truncate.

Input and output for my expression:

"" -> "" "foo" -> "foo" "bar" -> "bar" "SomeReallyLongString" -> "SomeReally"

Input and output for your expression

"" -> "" "foo" -> "#Error" "bar" -> "#Error" "SomeReallyLongString" -> "SomeReally"

|||

If I am getting it right, the problem I think is that when that field is empty or NULL, it returns an error:

So in your code actually, the first line for input and output would be:

"" --> #Error

I dunno how to resolve this in RS as I tried various things and they didn't work (like length = 0 etc.), only thing I can think of for now is to modify your query itself to return the substring instead of the field and then use this new field..

e,g,

select .....,..,.., substring(ISNULL(OldFieldName,''), 0, 10) as NewFieldName
from TableName

Ryan Ackley MSFT wrote:

My expression does the exact same thing as yours except when there are less than 10 characters it will not attempt to truncate.

Input and output for my expression:

"" -> "" "foo" -> "foo" "bar" -> "bar" "SomeReallyLongString" -> "SomeReally"

Input and output for your expression

"" -> "" "foo" -> "#Error" "bar" -> "#Error" "SomeReallyLongString" -> "SomeReally"

|||

Thank you very much. This is exactly what the problem that I am facing now let me try to see some other things and I will do as you suggest. Thank you.If you find anything new plz let me know.

Ephi

Display or export empty report

The report is running well. However, nothing will be displayed if there is no
data return from database. I would like to display an emty report (the report
frame) when there is no data. Could someone help?
Thanks,Why would you like to display an empty report when there is no data ?
Are you running it from some application, if so just display a message
that there are no records found for the criteria entered.
Billy wrote:
> The report is running well. However, nothing will be displayed if there is no
> data return from database. I would like to display an emty report (the report
> frame) when there is no data. Could someone help?
> Thanks,|||Some scheduled reports (subscription) runnning every day and sending to
customers by emai in EXCEL format. When there is no qualified data, customers
do not want to see an empty file. Instead, they want to see an empty report
(display the report frame)
thanks,
"Sara" wrote:
> Why would you like to display an empty report when there is no data ?
> Are you running it from some application, if so just display a message
> that there are no records found for the criteria entered.
> Billy wrote:
> > The report is running well. However, nothing will be displayed if there is no
> > data return from database. I would like to display an emty report (the report
> > frame) when there is no data. Could someone help?
> >
> > Thanks,
>|||If you have a table, list or subreport, you can set the the NoRows property ="No data found".
"Billy" wrote:
> The report is running well. However, nothing will be displayed if there is no
> data return from database. I would like to display an emty report (the report
> frame) when there is no data. Could someone help?
> Thanks,|||Thanks William. Even the report frame is not displayed by setting this
property, the email attachement is not an empty file now.
"William" wrote:
> If you have a table, list or subreport, you can set the the NoRows property => "No data found".
> "Billy" wrote:
> > The report is running well. However, nothing will be displayed if there is no
> > data return from database. I would like to display an emty report (the report
> > frame) when there is no data. Could someone help?
> >
> > Thanks,

Display None if field is empty

I am grouping my report on a field called Crew. This field is sometimes empty and so the group header doesn't show anything. I want that if this field is empty the report should display 'none' instead of just leaving it blank. How can I do that?
ThanksUse a formula to display the group header. Ie, if Crew is Group #1 :

If IsNull(Group#1Name) then
"None"
else
Group#1Name

Group Header names are available through formula editor the same as all other report/database fields.|||Thanks for replying kristyw. I am new to Crystal. Where should I put this formula?|||Create a new formula and drag it onto your form.|||It gives me an error saying "The ) is missing" and highlisghts Group on the first line.

If IsNull(Group#6Name) then
"None"
else
Group#6Name|||Here's my working formula :

If IsNull(GroupName ({VRP_CUST_DETAILS.ACCOUNTNUMBER})) then
"None"
else
GroupName ({VRP_CUST_DETAILS.ACCOUNTNUMBER})

Learn how to use CR Formula Editor - it's very handy when it comes to creating these types of formulas as you can drag the field names into it and easily find all the available functions.|||Thanks, that works just fine.

Display empty rows

I am creating some reports with crystal reports 8.5

I made crosstabs listing the sales of certain products by store and month.

The stores are listed in the rows, products and months are in the columns. In the complete yearly reports everything is fine, because there are no empty rows.

When i create subreports for just a certain month or product, there might be empty rows, like, there might have been no sales of that particular product, in that store in that month. Instead of just displaying a row with the store name and Zero value, it just doesn`t show the row at all.

I need those empty rows to show and display zero values. The option "suppress empty rows" is not checked.

Any ideas ?

Example
...............January 2007
...............Product 1
Store1......0 <- this row is not displayed
Store2......6
Store3......24You'll need an outer join somewhere, rather than an inner join - read the help on join types, or describe your current tables and joins.|||I am just beginning in crystal but I did have the same issue but my training yesterday was about table joints.
You have to have your primary table as your "source" table where you have most of your data. When you link your secondary table, go to link options and you need to have the linking as "left join or outter join ...not Equal". Even though you have no value on that one record, it will show it with an empty cell or box but you will see everything else from that record. I still wouldn't know how to display the zero.