Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Display 1

Status
Not open for further replies.

Crystaluser66

Programmer
Jun 29, 2008
11
US
I have my data in this way.
Region East (Group)
edition id edition name entry
123 strjournal 10
124 spring 0
125 Cityjournal 0

Region West
edition id edition name entry
127 citylife 0
129 spring 0


Region NorthWest
edition id edition name entry
131 strjournal 0
132 spring 0
133 City 0

Region South
edition id edition name entry
134 strjournal 10
135 spring 0
136 Cityjournal 30

I am using a running total in a cross tab to show,other details are suppressed except the summary

Region Region Region Region
East West NorthWest South

Edition id


Summary 1 NA 0 2

The summary should be calculated as follows
case 1:if editionname like journal and entry > 0 in a region then count( editionname) in a journal.

case 2 :if editionname not like journal i.e, region doesn't contain any journal then show
NA in summary column

Case 3: if editionname like journal in a region but if entry = 0 then 0

The running total works fine in cases 1(count) and 2(if 0 then in the display string shown as NA) but how to display case 3 in summary
Is it possible to do this.Bear with me for the lengthy xplanation.
any help is appreciated.


 
Create a formula {@jrn}:

if {table.editionname} like "*journal*" then 1

Then in the display string area, create a formula like this:

if sum({@jrn},{table.region}) = 0 then
"NA" else
totext(sum({@jrn},{table.region}),0,"")

-LB
 
Hi lbass,
When I use the formula everything is becoming NA.
the summary formula is calculated using running total
as
field to summarize Entry
type of summary count

Evaluate Entry > 0
and editionname like "journal"

Reset
On change of Group Region.


 
You don't really need to use running totals. Please explain exactly how you implemented my suggestion, as I believe it should work as is.

-LB
 
OK,I tried directly without using running total as follows
but still got NA

formula {@jrn}
if {table.editionname} like "*journal*" then 1
Rows : Edition id
Column : Region
Summarized fields : Sum of {@jrn}

then Right click at the totals on the bottom of the summary, went to display string.

if sum({@jrn},{table.region}) = 0 then
"NA" else
totext(sum({@jrn},{table.region}),0,"")

Am I doing something wrong
One Question,the formula {@jrn} is calculating like "*journal*",but not entry >0
 
I didn't realize you were using an inserted crosstab. Can you please show how you want the crosstab to display at the row level as well as the grand total level?

-LB
 
At the row level,I am using edition id which could be in multiple regions, row level counts the number of edition ids per region.

and the grand total gives specialized summaries as

Region Region Region Region
East West NorthWest South

Edition id 3 2 3 3


Summary 1 NA 0 2
 
What you want to do would be more easily done in a manual crosstab, which should be simple to do if you only have a few regions. Is this the case?

-LB
 
Actually there are many regions and also they wary based on parameter selection.
 
Okay, so you currently have count of edition.ID as your first summary field. Create another formula:

//{@jrnentry}
if {table.editionname} like "*journal*" and
{table.entry} > 0 then 1

Add sum of {@jrnentry} as your second summary field. Then add sum of {@jrn} as your third summary field.

Select the column total for {@jrnentry}->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar jrnentry := currentfieldvalue;
false

Then select the column total for {@jrn}->right click->format field->common tab->suppress->x+2 and enter:

whileprintingrecords;
numbervar jrn := currentfieldvalue;
false

Then on the same screen for {@jrn}, go to display string and enter:

whileprintingrecords;
numbervar jrnentry;
numbervar jrn;
if jrnentry = 0 and
jrn <> 0 then
"0" else
if jrnentry = 0 and
jrn = 0 then
"NA" else
totext(jrnentry,0,"")

You can suppress the inner cells for the last two summaries, and suppress the unwanted column total summaries and resize them so that they don't affect the display.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top