Print Page | Close Window

Incude Groups where data is null

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21089
Printed Date: 28 Apr 2024 at 8:09pm


Topic: Incude Groups where data is null
Posted By: Dr4ke
Subject: Incude Groups where data is null
Date Posted: 01 Oct 2014 at 6:33am
Hello all,

I am currently trying to create a report that shows two types of data ('Type A' and 'Type B') and then have some dynamic text that says "There were no records of Type A in the reporting period" or "There were no records of Type B in the reporting period" in the respective 'grouping' section.

The issue arises because there aren't always records in Type A or Type B. Is there a way to ensure that Crystal Reports creates a group for them both regardless of whether there is data available or not?

I do not currently have the ability to create views on the Database.

Any help appreciated in advance.


Replies:
Posted By: DBlank
Date Posted: 01 Oct 2014 at 7:45am

What is the source or data of the 'grouping'.

Does is exist before you apply a select statment?
Can you use a command as your data source?

Posted By: z9962
Date Posted: 01 Oct 2014 at 10:46am
As you only ever have two groups, you could create something in the report header or footer with a custom suppress?

In the custom suppress use
count(Type A field)>0
Have a text box in that section with what you would like it to say

Do the same for Type B

Make sense?

Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 9:44pm
Hi DBlank - it's a MySQL Database with about 7 tables so using a command won't be an easy thing to do.

Hi Z9962 - I did try having one manual section in the page header (type a) and one manual section in the page footer (type b) in case there are months that there is no data at all (which is possible) but they are spaced too far apart...

Posted By: z9962
Date Posted: 01 Oct 2014 at 9:54pm

if it is just spacing, this should be resolvable through using custom suppress in appropriate locations.


Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 9:57pm
I did try and it didn't work but I will try again :-).

I've also had another thought of adding in additional detail sections with the custom manual information and adding conditional suppression's - fingers crossed!

Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 10:10pm
Is there a function that determines the grouping level (GroupingLevel does something different to what I was hoping), by which I mean;

If my report is looking for two 'types' of data which are grouped as 'Type A' and 'Type B' is there a function which would do the following....

Type A
Data inserted here
Grouping Level: 1

Type B
Data inserted here
Grouping level: 2

Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 10:14pm
Sorry guys I think I may have just found it ('GroupNumber') I think....

Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 10:59pm
Ok I got the below formula working BUT only when there is two groups (which obviously there isn't if the data doesn't exist) which puts me right back to square one..... Any suggestions??

GroupNumber<>2 OR
({#P2RT}>0 And GroupNumber=2)

Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 11:35pm
Update.....

I've got it working using the original suggestion (Report Headers and Report Footers) - I putting them in the Page Header and Page Footer which is why it wasn't working as I expected :-)

The challenge now comes for my next report which has more than two 'types' ......

Posted By: Dr4ke
Date Posted: 01 Oct 2014 at 11:50pm
I suppose you could get this to work, with multiple 'groups', by using multiple subreports with the groups limited to 2 per subreport....

is there a better way!?

Posted By: lockwelle
Date Posted: 03 Oct 2014 at 5:16am
how about a simple command that just returns something that is in all the records...something easily queried and group 1 and group 2 values. Then use this as the 'master' table to link to the rest of the values (outer joins...so nulls are allowed).

Lastly, do a test for if the value is null...and display or suppress your message appropriately.

Personally, I would like a view/stored proc, but that not being available, this seems like the next best idea. I am assuming that you are joining straight to the tables in the database, so the command doesn't need any parameters...

On the flip side, if it is only 8 tables, the sql can't be that difficult to create the entire query...

just some thoughts.

Posted By: Dr4ke
Date Posted: 03 Oct 2014 at 5:56am
Thanks Lockwelle,

things to ponder :-)


Print Page | Close Window