Crosstab at the Beginning of the report (RH) 1

[sangisiva]

Hi,

I am working in CR version 10.

I have a report which has two unrelated tables.So I created subreport for the second table. Grouping both the reports by Business Unit (Country name).

I have a crosstab which is placed in Report Header. Crosstab contains count of records from both the sections for each group (country name)


Here I am facing problem in bringing the subreport's count to crosstab... Any simpler way of doing it?

Any help is appreciated.

Thanks in Advance.

 

[lbass]

Crosstabs are processed before subreports, and you also cannot share variables with sections above the one containing the subreport, so I don't think you will be able to do this for two reasons. You will have to bring both tables into the main report.

-LB

 

[Madawc]

Or put the crosstab in the subreport.

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

 

[sangisiva]

Hi,

Thanks for the response.

You will have to bring both tables into the main report.

Is that possible to have the unlinked tables in a single report... I tried but could not make it.... Any workaround to have unrelated tables in a single report ?

I can have the crosstab in subreport... In another report of mine has 9 unrelated tables where I have to create 9 subreports and one more subreport for crosstab...as I am using country name for grouping I have to use array to pass the summary of each group for each section to subreport... I wanted to check with is there any other simpler way to do this...

Thanks in Advance.

 

[lbass]

Sorry, I missed the "unrelated." But they do have the country field in common and you have some logic for putting results from both tables in the same crosstab. In what way are they unrelated?

-LB

 

[sangisiva]

I am taking all the records from one table.But the requirement for Detail sections are unrelated.

Consider I have a table with fields as

Project id
Project name
Project created date
Launch date
target launch date
stage

For the First requirement: A
I have filter criteria as
All active projects with (target launch date - project creation date) >5 years

For the second requirement: B
All active projects with target launch date-sysdate<1 Month

Third one : C
All active projects with launchdate-sysdate<0

Fourth one : D
Here I have two tables where I can link those using project id..

like wise I have 9 Details sections... I do not want to use subreports.. I just want to give a try to do it without subreports...

I created custom groups for each of the above using formula ..But I am missing something to make it work..

The layout of report should be as

Country Name - Highest Grouping
Detail Section A
Projectid ProjectName Projectcreateddate TargetLaunchDate
Detail Section B
Projectid ProjectName Launch Date
Detail Section C
Projectid ProjectName Stage Target Launch Date

I created formula for A as

[COLOR=blue]
@A
datediff('yyyy',{Command.target launch date},{Command.project created date})>5
[/color]

I selected this formula in Group Expert.. but It is not working...

Hope I am clear...

 

[sangisiva]

I made it worked..

The first DetailA section is working now. I placed the formula there in that detail section and in the section expert of the DetailA put the formula in suppress @A<5..

I am working on the other sections to check whether I can have more than one groups in a single report...

 

[sangisiva]

But the drilldown is repeating :-(

Any workaround to suppress it...

 

[lbass]

What happened to the crosstab issue? Are you no longer trying to do that? What is your current question? If you had a new, separate question, you should have started a new thread.

-LB

 

[sangisiva]

Hi lbass,

Sorry for the confusion... At very first of this thread I put a generalized question.

The report currently I am working on has 9 (unrelated ) detail sections, But the records are taken from one table as I explained in the above post.

My queries are :

If I use subreports for all 9 details sections, I will not be able to use crosstab at the beginning of the report since Crosstabs are processed before subreports.

I can have crosstab at the report header if I have all these in a single report... So now I am trying in the other way that without creating 9 subreports , I want to do it in a single report by using custom group function which uses formula as explained above..

I want to know whether it is possible to create 9 (unrelated ) detail sections in single report using custom groups...

Thanks in Advance

 

[lbass]

I'm guessing that you are hoping the same project will be able to appear in multiple groups. I think your best bet is to set this up as a command where you add a separate union all statement per set of conditions. Something like:

Select 'A' as Detail, `Country`, `Projectid`, `ProjectName`, `Stage`,`Projectcreateddate`, `TargetLaunchDate`, `LaunchDate`
From Table
where `TargetLaunchDate` - `Projectcreateddate` > 5*365.25

Union All

Select 'B' as Detail, `Country`, `Projectid`, `ProjectName`, `Stage`,`Projectcreateddate`, `TargetLaunchDate`, `LaunchDate`
From Table
where `TargetLaunchDate` - {fn now()} > 0 and
`TargetLaunchDate` - {fn now()} < 31

Union All

Select 'C' as Detail, `Country`, `Projectid`, `ProjectName`, `Stage`,`Projectcreateddate`, `TargetLaunchDate`, `LaunchDate`
From Table
where `TargetLaunchDate` - {fn now()} > 0 and
`TargetLaunchDate` - {fn now()} < 0

//etc.

The punctuation/syntax will be specific to your datasource. Then you could use {command.detail} to create a Group #2 within your country group, and you would also have these values available for a crosstab.

-LB

 

[sangisiva]

Hi lbass,

Thanks for the reply.

I will try designing the query using union all. Hope the Detail would be just for grouping purpose.

Will post my queries as I am working on.


Thank you.

 

[sangisiva]

Hi lbass,

This is an valuable suggestion. I started forming a query. At present I have completed with first two sections. Working on the third one.

Thank you.