problem with Crosstab in Dummy Group header

[sangisiva]

Hi All,

I am having a problem in crosstab manipulation.I am using Crystal report 10 and oracle 9i.

I am currently working in a report which has one subreport with it.Subreport is placed in group footer... Below goes the structure of the report

Report Header
Page Header
Dummy Group heder - Crosstab
Group Header
Details - Main report's fields
Group Footer #1a - Subreport
Group Footer #1b - placed summary fields of main report and subreport
page Footer
Report Footer


I group the reports based on the country. And Cross tab has 2 summary fields. I have to display the main report summary and subreport summary based on the grouping in corsstab.I can get the subreport summary value group wise using shared variable. But the Problem is I am not able to bring this to crosstab which is in group header. I know it is because crosstab gets evaluated before subreport evaluates.

I tried in the other way too by putting the subreport in the dummy groups group header#1a and placed the crosstab in group header #1b. But the problem here is I am not getting the values as per grops. I am getting the last value (I mean last groups value). I know it is becase this section executes only once (not for each group)


I tried using dynamic array variables to get the summary field for each group. and tried with evaluateafter funtion to get it in Group header. But don not why this is also not working properly. In this case also , I get the summary array values properly in group footer...

Please esperts help me if there is any workaround for this issue...

Thanks in Advance

 

[sangisiva]

anybody could look into it ??

 

[Madawc]

You need to explain more clearly. (It would also help to spell-check the explanation before posting it.)

You could put a crosstab subreport. Does this help?

Another option is a 'Mock-Crosstab', a set of running totals. Except they cannot be in a header, must be in a footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[sangisiva]

Hi Madawc,

Thanks for the reply.

The problem is I am not able to get the shared variable in crosstab...

 

[lbass]

You won't be able to use a shared variable in a crosstab, since crosstabs are executed in an earlier pass than subreports. Are you sure you need to use a subreport and shared variables for whatever it is you need in the crosstab? There might be another approach.

-LB

 

[sangisiva]

Hi lbass,

There is 2 set of different requirment should come in a single report.

So I have taken the first part in the main report details section and for the second requirement I used the subreport and that is placed in the group footer.

We have a defined Crystal report standard.As per the standard that crosstab should come in the Dummy Group header.

I tried creating that crosstab as a subreport too.. But it didn't workout...

:-( I am new to crystal report and This is my second report... couldn't find any workaround for this...

 

[sangisiva]

Even I tried adding two unrealted tables in a single report just for taking the summary part...

But there is a problem with Links.. It seems we can not have unrelated tables in a single report... :-(

 

[lbass]

I can't help unless you provide specifics of what the sub does, what field you are trying to provide to the crosstab, etc. It might help to understand what the report is intended to do.

-LB

 

[sangisiva]

Hi lbass,

I take EMP,DEPT and JOB_HISTORY tables.

For the main report, I take records from EMP and DEPT table ... For the subreport , I take data from JOB_HISTORY table...

SQL query goes here...

For Main Report

SELECT "DEPT"."DEPTNO", "DEPT"."DNAME", "EMP"."EMPNO", "EMP"."ENAME"
FROM "SCOTT"."EMP" "EMP", "SCOTT"."DEPT" "DEPT"
WHERE ("EMP"."DEPTNO"="DEPT"."DEPTNO")

For subreport -

SELECT "JOB_HISTORY"."EMPNO", "JOB_HISTORY"."DEPTNO", "JOB_HISTORY"."JOB"
FROM "SCOTT"."JOB_HISTORY" "JOB_HISTORY"
WHERE "JOB_HISTORY"."DEPTNO"=10
ORDER BY "JOB_HISTORY"."DEPTNO"

I group the reports by DEPTNO.... I am passing the report grouping as parameter to subreport and having this condition in record selectino forumla
{JOB_HISTORY.DEPTNO}={?dept}
for getting corresponding dept data from subreport...

I have placed the duplicate sub report just before the cross tab section to get the value of shared variable.. But getting only last dept number's value ... :-(


I tried to share the report file .. But i couldn't...

 

[lbass]

You cannot use a shared variable formula as a row/column/summary field in a crosstab. What did you intend to show in the crosstab (what column, row and summary fields did you intend to use)?

-LB

 

[sangisiva]

Hi lbass ,

Yes. I am trying to use shared variable formula in crosstab summary field.

Row is --> Group Name (Country name)
Having 2 summarized fields
1) Number of records for the main report

Shared variable formula for this :
shared numbervar Con_Cnt:=distinctcount({Command.id},{@Grouping})


2) Number of records for the sub report

Shared variable formula for this :
shared numbervar Act_Cnt:=distinctcount({Command.prid},{@Grouping})


Is this not possible at all ?

I am now working to have crosstab as a seperate subreport.. But even though I am not able to have this subreport at Dummy GH.. Because the subreport which passes shared variable to this report is placed at GF and executing later :-(

Is there any other way that I can have the summary part at Dummy Group Header ?

Hope I am clear...

 

[sangisiva]

Hi lbass,

I have uploaded my report here

http://www.esnips.com/web/sangi

file name is "Shared Variable in Crosstab(in Dummy GH).rpt"

 

[tcgoth]

Crystal 8.0
SQL 2000

Two problems that I need help with. I have reviewed all prior posts and cannot find a solution. I should note that we are moving to Crystal 11 soon but that does not help my problem now!

1.) Dealing with field lengths greater than 254 characters. I am trying to display Verbatim Survey Responses. I tried to write a SQL view to break the field into chunks of 254 characters and bring them into crystal to join them back together - but Crystal still will not recognize any field that goes beyond the initial 254. Any other approaches I can use?

2.) As I mentioned I am tring to display the results of a survey in the report using a Cross-tab.

I want the data to display like this:

Section 1 Section 2
q1 q2 q3.... Q1 Q2 Q3
Joe Blow 5 3 5 4 3 4
John Doe 4 5 3 5 4 5

A.) I think I have the structure set-up Ok but the data always has to be summarized. Is there anyway to get around using a "Summary" function in a cross-tab in 8.0?

There are 40+ questions so I don't relish the idea of having to do a manual cross-tab but maybe that is the only way?

Not sure if pertinent but all of the responses are in separate datafields - 5 Scaled Scores from Excellent to Unsatisfactory (i.e. s1.chk_excellent, s1.chk_satisfactory, etc., 2 fields for Yes/No questions (s1.chk_yes, s1.chk_no), and 1 verbatim response (s1.verbatim). I wrote a Formula Field to calculate the responses to a numerical value.

Thanks for any assistance!

 

[lbass]

tcgoth,

Please start a new thread.

-LB

 

[tcgoth]

Sorry for the inerrant post from tcgoth!

 

[sangisiva]

Hi lbass,

Did you happen to see the report attached ?

Thanks in advance.

 

[lbass]

Okay, I'm not sure how you got the shared variable field into the crosstab, since it isn't in the field list, but I'm guessing it was in the field list BEFORE you placed it onto the report by entering it into the crosstab.

Anyway, let me know if the crosstab ultimately works. The issue is that if you are linking the sub on the department field, you need to place it in a Group #2a section, with the crosstab in 2b and the other Group 2 sections moved down accordingly.

-LB

 

[sangisiva]

Hi lbass,

Obviously it will work if I move down the cross tab to Group #2b section.

But the user wants the summary should be at the front of all details data.He wants it in Empty Group header (Please refer the attached report where I have placed the crosstab)

In the report , I have created a empty group as
if len(totext({DEPT.DEPTNO}))>0 then
1
else
0

in this empty group header I placed the crosstab...

when we have the crosstab in the group footer or header, It will work fine since GF/GH evaluates for each group.

as the empty group header executes only once, I could not get all the group values into crosstab. It works if i have only main report.

But when we use subreport and it is placed in the GF, and it evaluates after the crosstab, we could not get the values.So that I have placed the duplicate subreport before the crosstab section.. But I get only the last value even there...

User wants the summary part to be first in the report and the details part next.

I tried all the way I know to make the summary part at front. but could not make it.Before telling this to the user that it is impossible to have the summary part at the front when we have the subreport, I want to confirm it with the Crystal report Experts.

Thanks
Sangeetha Sivakumar

 

[lbass]

I really don't see why you are using a subreport in the first place. Why not link on the dept number, and bring in the job history to the main report? Another approach you could take is to use a union all in a command, in order to bring in both sets of fields (main and sub), without creating a lot of duplicates. I can see that the employee numbers don't match, but is there another table that would allow a crosswalk between the numbers possibly?

It would really help me think about this if you explained how the report will be used. How does the job history information relate to the main report info? What question is the report intended to answer?

-LB

 

[sangisiva]

I am not working on the report I attached . The report I attached is simply a model report which shows my requirement.

In the other way, I can say this as :
My requirement is to have the Crosstab in report header(which has two summarized part one from main report and the other from subreport)

Subreport is placed at Group footer.

If we have crosstab at report header if we just have main report, It will work.

Is there any workaround to make it work even if we have subreport?

I tried placing the duplicate subreport just before the crosstab section to get the data to crosstab.. there is no luck...

Thanks in Advance.