Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Cross Tab Problem Post Reply Post New Topic
Author Message
AmitJ
Newbie
Newbie


Joined: 30 Dec 2010
Online Status: Offline
Posts: 14
Quote AmitJ Replybullet Topic: Cross Tab Problem
    Posted: 20 Apr 2011 at 11:37pm
Hello All,
 
I am new to crystal report and need to implement following scenario with cross tab. Following is my table structure-
 
City Store Name Revenue
A PQR 500
A XYZ 700
B XYZ 400
C PQR 600
C XYZ 400
 
Here data for Store Name 'PQR' for City 'B' is not available in database itself.
currently my report is diaplaying output as-
 
PQR XYZ
A 500 700
B   400
C 600 400
 
However requirement is that if there is any blank cell in cross tab, then 'Not Required' should be displayed in cross tab cell as follows-
 
PQR XYZ
A 500 700
B Not Required 400
C 600 400
 
How to achieve this, as there is no record present in database itself for City 'B' and Store Name 'PQR' ? Confused
 
Thanks in advance..!!
 
Amit
 
 
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 21 Apr 2011 at 7:05am
Create a formula that looks like something this:
 
if IsNull({table.revenue}) then "Not Required" else ToText({table.revenue}, 0)
 
Use the "Max" of this formula in your crosstab instead of the sum of the number.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
AmitJ
Newbie
Newbie


Joined: 30 Dec 2010
Online Status: Offline
Posts: 14
Quote AmitJ Replybullet Posted: 23 Apr 2011 at 11:39pm

Hello Dell,

 

Many thanks for your help..!!

 

I already tried this solution. However it will display 'Not Required' only if null value is assigned to revenue in database for City 'B' and Store 'PQR'.

 

This formula is not working as there is no corresponding record present in database. Ouch

 

So, is it necessary that database should have one record with NULL value for corresponding city and store name? and we can not achieve this if no record is present in database column?

 

Regards,

Amit 

 
IP IP Logged
AmitJ
Newbie
Newbie


Joined: 30 Dec 2010
Online Status: Offline
Posts: 14
Quote AmitJ Replybullet Posted: 25 Apr 2011 at 12:59am

Hello All,

How to achieve this? Dead
 
Amit
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 25 Apr 2011 at 5:34am
Are you joining two tables together?  For example, on with the Store Names and another with the Revenue?  If so, link FROM the stores table TO the revenue table.  Then right-click on the link, select something like "Join Options" and make this a left outer join.  This might work for you.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
AmitJ
Newbie
Newbie


Joined: 30 Dec 2010
Online Status: Offline
Posts: 14
Quote AmitJ Replybullet Posted: 25 Apr 2011 at 6:34am
Hello Dell,
 
Report is retriving records from single table. i.e all the fields (City, Store Name, Revenue) are comming from single table.
 
-Amit


Edited by AmitJ - 25 Apr 2011 at 6:35am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 25 Apr 2011 at 6:37am
There is no way to meet this requirement, then.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.