SUBREPORT QUESTION - PULLING DATA ON THE SUBREPORT MAIN REPORT

[ayamudua1]

HI ALL,
I HOPE THE PROBLEM TITLE IS NOT CONFUSING. LET ME TRY TO EXPLAIN THE PROBLEM
I HAVE A COMPLEX REPORT THAT PULL GENERATE INFORMATION FROM RUN TIME TABLES. THESE TABLES GET POPULATED FROM BASED ON WHAT THE APPLICATION IS DOING AT A TIME. THAT IS NOT THE PROBLEM. NOW, I HAVE A REQUIREMENT TO POPULATE CERTAIN INFORMATION ON THE MAIN REPORT WHEN A CERTAIN CONDITION IS TRUE. NOW, THIS REQUIREMENTS DEMANDS THAT I ACCESS THE ACTUAL TABLE TO BE ABLE TO GENERATE THE INFORMATION. OK, THERE IS NO PROBLEM HERE AS I AM USING SUB-REPORT TO OBTAIN THE INFORMATION. THEN, THE ISSUE NOW IS THAT, I NEED TO FILTER OUT THE INFORMATION DISPLAYED ON THE SUBREPORT IN SUCH A WAY THAT WHEN EVER THE RUN TIME GENERATED TABLE HAS THE SAME INFORMATION, IT SHOULD NOT APPEAR ON THE SUBREPORT PORTION.
SO, I HAVE TRIED SEVERAL SQL SYNTAX TO ADRESS THIS, BUT NON SEEMS TO WORK. NOW I AM RUNING OUT OF TIME. ANY HELP WILL BE APPRECIATED.

HERE IS THE SAMPLE DATA.

MAIN REPORT TABLES SUBREPORT TABLE
RUNTIMEREPTABLE1 TABLE1
FIELDS
CUSTOMER_ID CUSTOMER_ID
LISTING_ID LISTING_ID
LISTING_CODE LISTING_CODE
: :
: :

OK, THERE COULD BE 3 ENTRIES IN RUNTIMEREPTABLE1
AND 7 ENTRIES IN TABLE1.

NOW, I WANT THE ABILITY TO PRINT THE 4 ENTRIES THAT WAS NOT INCLUDED IN THE RUNTIMEREPTABLE1 WHEN THE APPLICATION GENERATES THE ENTRIES FROM TABLE1.

PLEASE HELP.

THANKS.

 

[synapsevampire]

Genrally an all caps post is considered unappetizing (generally used for screaming and ranting), if not lazy.

Sample data generally includes real data, not the design of the table.

Rather than wordy explanations, try posting real example data, and expected output based on the example data.

-k

 

[lbass]

If you are using CR 9.0 or if you don't have large numbers of entries to account for, you could do something like the following, assuming you are grouping on listing ID or customer ID:

{@reset} to be placed in main report group header:
whileprintingrecords;
shared stringvar listcode := "";

{@listcode} to be placed in the main report detail section:
whileprintingrecords;
shared stringvar listcode := listcode + totext({table.listcode},0,"&quot+
", "; //assumes listcode is a number

Then in the subreport, go to format section->detail->suppress and enter:

whileprintingrecords;
shared stringvar listcode;
instr(listcode, totext({table.listcode},0,"&quot) > 0;

This assumes that the subreport is in the main report group footer.

This works as long as you don't run into the 254-character limit for versions 8.5 and below, although I have no idea how the use of "runtime tables" might factor into it, so maybe this will help, maybe not...

-LB

 

[ayamudua1]

Thank you Synapse and lbass. Let me make myself clear and provide real life data.
First. the data need to be filtered out at the listings group level. With the live data and what I have done so far, I am sure someone will be able to help. This is the last part of my deliverables due today and it seems to give me a lot of stress.

I will be presenting 2 tables for clarity: 1 - ListingTable 2. Run-time generated table ( TempListingTable)

Table 1 is the database table and 2 is Table generated by the application and
data is from table 1.

ListingTable
CUSTOMER_ID LISTING_ID ACCOUNT_TN MAIN_LISTING_ID LISTING_STATUS LISTED_TN
------------ -------------- ---------- --------------- -------------- ----------
3331444961 330003544937 8132327600 330003544937 N 8132327600
3331444961 330003544939 8132328484 330003544939 N 8132328484
3331444961 330003544940 8137838877 330003544940 N 8137838877
3331444961 330003544941 8139098777 330003544941 N 8139098777
3331444961 330003544942 8139098777 330003544941 O 8139098777
3331444961 330003544943 8139207211 330003544943 N 8139207211
3331444961 330003544944 8139615784 330003544944 N 8139615784
3331444961 330003544945 8139615784 330003544944 N 8139615784
3331444961 330003544946 8139615784 330003544944 N 8139615784
3331444961 330003544947 8139602445 330003544947 N 8139602445

TempListingTable
CUSTOMER_ID CODE ISSUE ACCOUNT_ID LISTING_ID MAIN_LISTING_ID LISTED_TN ACCOUNT_TN
------------ ------ ------------------- -------------- -------------- --------------- -------
3331444961 012888 22 330005608853 330003544943 330003544943 8139207211 8139207211
3331444961 012888 22 330005608853 330003544947 330003544947 8139602445 8139602445
3331444961 012888 22 330005608853 330003544944 330003544944 8139615784 8139615784

As you can see above, there are only 3 listings in table 2 because those listings in the 2 has items linked to them.
As a result when the application generates the table it only pulls information related to these 3 listings

Now, i need to present the other 6 listings that is not in table 2.

A bit background.
In the main report, I am gouping each of the listings using this formula:
@listingSort
WhileReadingRecords;
totext({TempListingtable.LISTING_ID}) + totext({TempListingtable.LISTED_TN} + {TempListingtable.LISTING_ALI_CODE})

so whenever there is a change in any of the 3 fields, a new group will be created. And under the group, there will be several items pointing to that.

I created a subreport to be able pull all the information from table to display using the same formula above but with the correct table name.thus:
@listingSort
WhileReadingRecords;
totext({Listingtable.LISTING_ID}) + totext({Listingtable.LISTED_TN} + {Listingtable.LISTING_ALI_CODE})

Now this works just fine.

But the challenge I am having is the ability to suppress those groups in the subreport when the group is present in the main report.
So in this case, instead if giving me all the 10 listings, it will only give me 7.
Now, the question is, why use subreport. The reason for the subreport is so that, ALL THE LISTINGS THAT HAS NO ITEMS POINTING TO THEM TO PRINT
IN SEPERATE GROUP AFTER THOSE WITH ITEMS POINTING TO THEM.

I will give you sample data from the ITEMs & TempItemtable to give you idea of what is going on if necessary. And hope fully it will help to explain things.
Please let me know if this explanation is enough or you will like me to provide the data.

I will appreciate any help.

Thanks.

 

[lbass]

What version of CR are you using? If you are using CR 9.0, you can adapt my earlier suggestion. Group on Customer ID and then your concatenation formula (I don't think you need "WhileReadingRecords", but I don't think it hurts either). Plug {@listcode} into my formulas instead of {table.listcode}. Place the reset formula in the customer group header and then place the second formula in the {@listsort} group header. Then place the section suppression formula on the group {@listsort} header section.

As I said earlier, if you are using 8.5 or below, this probably won't work because of the 254-character limit.

-LB

 

[ayamudua1]

Unfortunately I am using 8.00, that may be the problem also. Is there no other work around you can suggest?

BONS

 

[lbass]

I figured out an elaborate way to do the suppression in the subreport, but then I reread your last post, and I think there is a simple way to do this.

If I'm understanding you correctly, the main report holds
only those groups that have items associated with them, while the subreport includes all records, but you want only to show those groups in the subreport which have NO items associated with them.

In the subreport, do a left join from the Listings table to the Items table. Then go to format section->group header (for (@listingsort})->suppress and enter:

not isnull({Item.ItemID})

Let me know if this doesn't work, and I'll provide the suppression method. If you need to go that route, you should identify what you mean by Listingtable.LISTING_ALI_CODE} since this is not identified in your post. I would also want to know the length of {@listingsort}, which I'm only guessing is about 32 to 34 characters, and your estimate of the maximum number of {@listingsort} group instances within a customer group (I'm assuming you are also grouping on customer).

-LB

 

[ayamudua1]

Thanks lb. I used the suggested method. Does not seems to work very well. I still see some listings with Items associated with them being pulled in.

Please provide the other suppression formula you mention here. The ALI code is not very relavant. The code is a 4 character identifier that is used to identify what type of listing that is. For example ALI_Code "PL" means Primary listing, while "AL" alternative listing. Its not really used for grouping per se, its included in the grouping formula because the ordering of the groups has to be in a certain way. ie, PL before AL.

The primary thing used for the pulling the groups is the Listing_ID. Because its unique.

The length of the @listingsort is not more than 26 characters. Usually between 24 -26.

Its really difficult to come up with the estimate of the @listingsort per customer because sometimes in large markets, a customer could have upto 30 or more listingsort groups. Even when this happens, the printing of the groups is not controlled by the report, rather by how many listings the user wants to print at a particular time. They may decide to print all of 30 or 1 or 10, as needed.

This is a complex report and this part jsut seems not to let me have it going. As always I will appreciate any input.

BONS

 

[lbass]

The following method is based on Jim Broadbent's approach to a different problem in another thread, and I have adapted his formulas (borrowing liberally) for use here, so if you find that this works, it is partly, or largely, to his credit. All mistakes are mine--some of this is new to me. This works when I test it here, using 8.0.

I'm also making certain assumptions here--that your subreport is linked to your main report by CustomerID, and that it is placed in the group #1b (Customer) footer and that in both reports, you have a second group on {@listingsort}.

First, create a reset formula {@reset} to be placed in the main report group #1 (Customer) header:

whileprintingrecords;
shared stringvar array listingsort := ["","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","","",
"","","","","","","","","",""];
shared numbervar pointer := 0;

...where the number of array elements initialized is well over the number of maximum expected instances of {@listingsort} per customer.

Then create {@sharedlistsort} to be placed in the main report Group #2 (@listingsort) Header:

whileprintingrecords;
shared stringvar array listingsort;
shared numbervar pointer;

pointer := pointer + 1;
listingsort[pointer] := {@listingsort};

Then you need to create display formulas. The number depends on the expected maximum number of instances of {@listingsort} per customer. You need one formula for every 9 instances, since {@listingsort} is 26 characters + 2 characters (chr(13) and chr(10)) in the following formula = 28 characters, and this is the maximum number of instances you can display within the the 254-character limit. So, if you think the maximum per customer is 30, you would need 4 formulas. If you think it could be 50, then you would need 6 formulas, etc. They would each look like the following:

WhilePrintingRecords;
shared StringVar Array listingsort;
shared numberVar pointer;
shared StringVar resultN := ""; //change the N for each new display formula from 1 to N

for pointer := 1 to 9 do //each new formula should allow for 9 group instances--the next would be 10 to 18, etc.
(
if listingsort[pointer] <> &quot;&quot; then
resultN := resultN + listingsort[pointer]+ chr(13) + chr(10);
);
resultN;

If you want to, you can eliminate the chr(13) and chr(10)--these just allow a more readable display for testing purposes. Place these formulas in the main report Group#1a footer and then suppress the Group#1a footer section. The subreport should appear below this in the Group#1b footer section.

Within the subreport, go to format->section->group (@listingsort} header->suppress and enter:

WhilePrintingRecords;
shared stringvar result1;
shared stringvar result2;
shared stringvar result3;
shared stringvar result4;
shared stringvar result5;
shared stringvar result6;
shared stringvar result7; //here N = 7--adjust according to your needs

instr(result1,{@listingsort}) <> 0 or
instr(result2,{@listingsort}) <> 0 or
instr(result3,{@listingsort}) <> 0 or
instr(result4,{@listingsort}) <> 0 or
instr(result5,{@listingsort}) <> 0 or
instr(result6,{@listingsort}) <> 0 or
instr(result7,{@listingsort}) <> 0 //again, this assumes N = 7

That should do it.

-LB

 

[ayamudua1]

I will try out your formula LB, and will give a post on the status.

BONS

 

[ayamudua1]

Finally found the a simple solution to my own problem and I thought I shought share it in case someone need it.

I used a Sub-Query on the SQL designer to filter out those listings from the listings table that listings are also in the temporary or application generated tables.

This seems to work just so perfectly, couldn't believe all the time spent in figuring this out.

Thanks everyone.



BONS

 

[sudhis]

Hi,
I saw the messages in the forums and I have the same scenario but facing problems in sharing variables among sub reports and main report.
Now I have 2 level grouping in my main report,say on first level is on transaction date and under that I have 2nd level of grouping on invoice number. Now I have linked sub 6 subreports that are linked to invoice number which displayes different details for a invoice number. I have placed SR1 & SR2 in Group Footer 2a section, SR3 and SR4 in GroupFooter2c section and SR5 and SR6 in groupFooter2e section. Now, if I don't have any records in both SR1 and SR2, then GroupFooter2a section has to be fully suppressed, or if either of them SR1 or SR2 does not have any records, then that one should be suppressed. the same rules has to be applied to SR3...SR6 subreports. I am using Version 7.0 of Crystal Reports but, I am not able to pass the value from SR1 to Mainreport based on which I can suppress GroupFooter2a section. Could you please help me on this.
Expecting an early and a positive help from you all
Regards
Sudhakara.T.P.