PASSING DATE PARAMETERS FROM SUB TO MAIN... HELP 1

[sommererdbeere]

hi,

crystal 8.5
main report date data type: Date
subreport date datat type: DateTime

i'm having a great trouble in passing date parameters from subreport to main.

i have 2 sets of date range parameter created: 1 in main and 1 in subreport. i have both of these reports Parameter Value Type as "Date". when i run the report, it seems like it only recognized the date range in main report and nothing in subreport

Problems:

1. no arguement when input the date even if the data type of subreport is datetime ( the parameter value type i've set is "Date" not "DateTime")

2. doesn't seems to give me the correct data when i input the date for subreport.

solution i've tried:

i try to used Shared Valuable in the formular to pass the date from subreport to main. however, when i go to Record Formular Selection in main, the formular i created, "{@ASP_DATE_ENTERED}" wasn't being recognized(doesn't shown in the selection:
{@ASP_DATE_ENTERED} >= {?asp_entered_date} and
{@ASP_DATE_ENTERED} <= {?asp_entered_date}

HELP:
i truly hope any of the experts can give me a great help as i've been working on it the whole nite and still can't find solution. i need to turn this report in by 3pm today. please help if anyone has any idea to this problem.

thank you very much for your time and assistance. thank you.

m

 

[lbass]

If you are trying to use the same date range in both main and subreport, then in both subreport and main report, make sure you are using the parameters in the record selection formula:

{table.date} = {?daterange}

Then in the main report, go to edit->subreport links->select the date range parameter (?daterange} in the main report and move it to the right. Then in the bottom left corner of the screen where it says "Subreport parameter field to use," click on the dropdown arrow and scroll down until you see {?daterange} (assuming you named it the same as in your main report), and choose this to link on, NOT the {?PM-?date range} field.

On another note, I've noticed that you've posted quite a few times with requests for urgent responses. Please consider "giving back" by answering other people's posts in areas where you have expertise that might be helpful to others.

-LB

 

[sommererdbeere]

yes. thank you lbass. honestly, i'm very new to crystal and oracle sql. it is my job that i started to learn crystal reports. i posted urgent mostly is i've tried to find the answer first until i really really don't have any clue then i ask questions in this forum. this forum is a great help for me.

yes, you are right that i need to give back to others as well. i'm not an expert to most of the questions, but i will reply to posts that i know about now and future as this forum and all of you help me alot. thank you for everything.

back to the problems.

i have try to use the link, but i got a little confuse. the dates from main and subreport is different ( i think i should have clarify this first).

main: closed date
subreport: entered date

both of these date are pulling out from 2 different table.

in the main, if i get little confuse as to which field i should link? meaning that which field i should have entered date equal to?

thanks for your help and i hope we can all help each other out.. thank u

m

 

[lbass]

I'm still not clear. Do you want to use the same date range for each of these two fields? In other words, are you trying to pull closed dates and entered dates from the same time period?

-LB

 

[sommererdbeere]

close date in main report is different from entered date in subreport.

this report will ask for 2 dates, 1 is the closed date in the main report and 1 is the date entered in the subreport.

clarify:

main:
- the close_date retrieves from a table called shop order
- in the record formular selection:
{Closed_date} >= {?Closed_date_begin} and
{Closed_date} <= {?Closed_date_end}

sub:
- the entererd_date retrieves from a table called customer order
- in the record formular selection:
{Entered_date} >= {?Entered_date_begin} and
{Entered_date} <= {?Entered_date_end}


problem:
when i run the report, it will ask for date:
Closed_date_begin
Closed_date_end
Entered_date_begin (asp)
Entered_date_end (asp

note: (asp) is the name for the subreport

after i enter the date for this date range, it seems like it is only given me the data from main report and nothing in subreport.

---
i used Shared datevar for Entered_date in subreport to pass it onto the main, which in the main, i create a formular for shared variable as :

entered_date:
shared datevar entered_date;
entered_date

however, when i go to Record Formular Selection in the main report , i cannot find this formular, entered_date

questions:

1. how can i pass entered_date to main report?

2. is there anyway i can have 2 sets of date range when writing this report?

3. is it true that the formular in subreport of the record selection as
{Entered_date} >= {?Entered_date_begin} and
{Entered_date} <= {?Entered_date_end}
will not be recognized in the main? meaning that the formular is useless?

thank u very much for your hlep and time. hope this will clarify more.. thank u thank u.. i really need help

m

 

[lbass]

If you want to have a different date range for closed date and entered date, then you should NOT link on the dates or date parameters. You could link on other fields, if you need to. The parameters for both main and subreport will pop up when you open the report without passing any values to the main report. The fact that entered date is a datetime and your subreport parameter is a date should not be a problem. If the subreport is not giving you the desired results, please explain what your links are, where the subreport is located in the main report, and what the structure of your main report is (groups, etc.).

Is there some other reason you want to pass values from the subreport to the main report? Do you need to use the entered date in the subreport in a calculation in the main report? To do this, in the subreport, you would create a formula:

whileprintingrecords;
shared datevar entereddate := date({table.entereddate});
//substitute the real name of your entered date field for
//{table.entereddate}

You must place this formula somewhere on the subreport canvas.

Then in the main report, in a section below the one where your subreport is located, you could create a formula that references the shared variable. Let's say you want to subtract entered date from the closed date. You would then create a formula:

whileprintingrecords;
shared datevar entereddate;
{table.closeddate}-entereddate;

If there is the possibility of a null for entered date, then you would also need to add a reset formula in the main report so that the shared variable does not carry forward into the next record when the next record has a null date:

whileprintingrecords;
shared datevar entereddate := date(0,0,0);

If you have a group on clientId (or whatever group you are evaluating the closed vs entered date for), you would add this reset formula somewhere after the shared variable is used in a calculation, e.g., the group footer, or in a section above where the subreport is executed.

-LB

 

[sommererdbeere]

hi lbass,

i passed some value from subreport to main report for calculation, which i have group them by so_partno.

in the main report, i have a partno called sp_partno, whereas in the subreport, there is also sp_partno. i linked main_sp_partno to sub_sp_partno. however, the date for main and sub retreives from 2 different table (places). the main retreive it from the Shop_order_table and sub retrieves from Cust_ord_table.

to clarify this futhur, here are some data from the 2 table:

Shop_order_table (from main)
sp_partno closed_date
3110 2/3/2004
3250 2/19/2004
3256 2/15/2004
4626 2/11/2004
4626 3/11/2004
4630 4/11/2004


Cust_ord_table(from sub)
sp_partno entered_date
1150 2/24/2004
3110 2/10/2004
3215 2/17/2004
4726 2/11/2004
4730 4/11/2004
4726 1/11/2004

it will ask 2 sets of parameter when i execute this report:
Closed_date_begin (from main)
Closed_date_end](from main)
Entered_date_begin (from sub)
Entered_date_end (from sub)

i will input the date of
Closed_date_begin --> 2/1/2004
Closed_date_end --> 2/29/2004
Entered_date_begin --> 2/1/2004
Entered_date_end --> 2/29/2004

if this so, it will give me this output:


sp_partno close_date entered_date
3110 2/3/2004
3250 2/19/2004
3256 2/15/2004
4626 2/11/2004
1150 2/24/2004
3110 2/10/2004
3215 2/17/2004
4726 2/11/2004


however, the output of my current report give me this outcome:
sp_partno close_date
3110 2/3/2004
3250 2/19/2004
3256 2/15/2004
4626 2/11/2004

it seems like it only recognized the date in main report. meaning that the date range i input for entered_date isn't being recognized.

hope this clarify more and again thank you for your reply and help..

m

 

[lbass]

I think there are some conceptual problems here. We can probably assume that every closed date has an entered date, but it is not true that the entered date will necessarily fall within your parameter period. If you link on part number, when the date is not in the parameter period, the part will not appear. Your example shows only one match between the two tables for the designated time periods (part no 3110) so that is the only part which will show a date. Not sure why the subreport date is not showing up for that part number. Please describe your groups in the main report and where you have placed the subreport.

It might also help if you explain your goal here. I wonder whether you need to use a subreport. Is there a third table that has all the part numbers in it so that you could then do a left join from that table to the shop order table and an equal join to the customer order table?

What is your report supposed to show? Are you trying to show only those records that were entered and closed within a month? Or those records that were either entered OR closed within a month? Could the parameter date periods for entered and closed be different or are they always the same?

-LB

 

[sommererdbeere]

hi lbass,

thanks for your response, you help me to think through something that i haven't really thought of and i hope you can help me as well. i was looking through my report and code closely, which i find something that i hope it makes sense; i believe this is the cause of my report.

i list the sources, tables i created then talk about the problem:

sources (tables)
i have 4 tables that i need to join them together. I have tried to join all 4 tables together (shop_order, sales_part, part_cost, customer_order), (note: shop_order and customer_order is different, in which, sales_part is the only table that can govern these 2 tables)tunfortunately, no luck because the sets got repeated (i have a post about this:

http://www.tek-tips.com/viewthread.cfm?SQID=831848&SPID=149&newpid=149&page=1

). therefore, i created 2 .ial files (2 different sql statment):

sql1:
select
sp.partno,
so.closed_date
from shop_order so, sales_part sp, part_cost pc
where
sp.partno(+) = pcbh.partnoand
pcbh.partno= so.partno

Shop_order_table (from main)
sp_partno closed_date
3110 2/3/2004
3250 2/19/2004
3256 2/15/2004
4626 2/11/2004
4626 3/11/2004
4630 4/11/2004

sql2:
select
sp.partno,
co.entered_date
from customer_order co, sales_part sp
where
sp.partno(+) = co.partno

Cust_ord_table(from sub)
sp_partno entered_date
1150 2/24/2004
3110 2/10/2004
3215 2/17/2004
4726 2/11/2004
4730 4/11/2004
4726 1/11/2004

crystal
in the main table (use sql1) group by sp.partno, whereas in the sub(use sql2) also group by sp.partno. since they both have sp.partno, i linked this field together.

problems
i believe the problem exist is in the main report, it will pull out any partno that part_cost and shop_order has which matches the sales part. then, i will these partno from sales_part to matches with the sales_part number in the subreport. isn't it true that the sp_partno exist in main matches sp_partno in sub? a simple explaination will be if main_sp_partno has 1, 2, 3, 4 whereas sub_sp_partno has 1,2,3,4,5,6 . when the report execute, it will only give 1,2,3,4 instead of all 1,2,3,4,5,6 (even if sp_partno, 5 & 6, are not in the main report)? therefore, even when i input the date for the subreport, i will not get the sp_partno of 5 &6?
if that's so, do u think i can have any ways to pull out 5 &6 as well?
i hope this make sense and clarify a little. thank you.

m

 

[sommererdbeere]

hi lbass,

i think i know the problems. i shouldn't have {sub_sp_partno} = {main_sp_partno}, because i already have sp_partno = so_partno and sp_partno = co_partno.

i'm sooo stupid after all these mess i've created, but your reply give me the clue of what i've done wrong.

however, my challenges now is i'm not sure how i can organize it as this will be a downloadable file to excel. the *newest* problem i'm having now is how to show the report?

in subreport
my Record Formular Selection has only date selection:
{Entered_date} >= {?Entered_date_begin} and
{Entered_date} <= {?Entered_date_end}

note: doesn't link to anything.

in main
my Record Formular Selection has only date selection:
{Closed_date} >= {?Closed_date_begin} and
{Closed_date} <= {?Closed_date_end}

outcome

sp_partno std price asp_gm% co_partno asp
3110 0.0002 1150 0.006
3110 0.324
3215 0.345
3250 0.897
4726 0.456
4730 0.987
4726 0.432
4630 0.987

the underline is the subreport.

since i need to calculated the asp_gm, i cannot calculated due to the layout of this report. also, i need to have all the partno on the same coln like:

want sth that look like:
sp_partno std price asp_gm% asp
3110 0.0002
1150 23 0.006
3110 34 0.324
3215 15 0.345
3250 0.897
4726 33 0.456
4730 55 0.987
4726 24 0.432
4630 0.987

-----------------------

the only reason i wasn't being to do it because i don't like anything that link from sub and main report. if i link them together, it will not pull out *ALL* the data in customer_order table.

do you have any suggestions and idea about this problem??

thanks u very very very very very very much for your help and time.. thank you.

m

 

[lbass]

I'm not understanding. What is causing the subreport part nos 1150, 3110, and 3215 to be clustered with main report part number 3110, and subreport part nos 4726 and 4730 to be clustered with main report part number 3250?

You also still have not explained the group structure in your main report and where (what section) you have the subreport placed.

Also, what is the formula for asp_gm%?

-LB

 

[sommererdbeere]

hi lbass,

i'm sorry to get you all confused as you are trying to help me.

--------------------------------------
sql:

shop order
select
sp.partno,
sp.std,
so.closed_date
from shop_order so, sales_part sp, part_cost pc
where
sp.partno(+) = pcbh.partnoand
pcbh.partno= so.partno

customer order
select
sp.partno,
co.entered_date
from customer_order co, sales_part sp
where
sp.partno(+) = co.partno

--------------------------------------
for this report, i need to pull out all the part no from both shop order and customer order which i need the part no from sales part as the govern part. i hope this structure help:

Sales_Part_Partno
| |
| |
Shop_Order_Partno Customer_Order_Partno

for both Shop and Customer Order, only their Partno is identical which i'll have to pull everything out whenever Sales Part has the partno. Therefore, i have 1 main and 1 subreport for this report.

main: i have shop order table and sales part table (sql, please refer the above)

sub: i have customer order table and sales part table (sql, please refer the above)

--------------------------------------
previous problem
the whole time i've thought i have problems with my date parameter, which i thought i have set up wrong or the link is wrong; since i cannot pull *ALL* the partno from customer order table. It seems like *EVERYTHING* i've pulled out are from shop order table.

however, when i looked closely and read your comments, i've found out that i've already have my

sales_part_partno = shop_order_partno (in my sql statement that placed it in the main report)
sales_part_partno = customer_order_partno (in my sql statement that placed it in the subreport)

then i linked both of these report:

sales_part_partno (from main)= sub_sales_part_partno(from sub)

evidently, this will not give me *ALL* the data in customer_order_partno (sub), because my subreport sql has * Already* linked

sales_part.partno(+) = customer_order.partno

--------------------------------------
design view of the report

*note: main: grp by sp.partno*
Main:
pageheader: PartNo Std Asp_Std_GM% Sub_Partno ASP
grpfooter1a: [grpName][sp.std] [sub_asp]
grpfooter1b: [@asp_std]

*note: sub: grp by sp.partno*
Sub:
grpfooter1: [grpName] [sp.partno] [asp]

--------------------------------------
if i don't have any linkage between the sub and main

**note: sp = sales_part, so=shop_order, co=customer_order
***note: ASP is from the subreport
**** Asp_Std_GM% = std- asp /std

PartNo Std Asp_Std_GM% Sub_Partno ASP
3110 0.0002
1150 0.006
3110 0.324
3215 0.345
3250 0.897
4726 0.456
4730 0.987
4726 0.432
4630 0.987

--------------------------------------
questions and outcome i'm trying to have

the above outcome is what i want, however, how can i have the partno to line up?
as you can tell, the Asp_Std_GM% doesn't shown up, because the ASP is in the subreport. I tried to use Shared Valuable, but it doesn't work. Since i will have it placed it under grpfooter1b, it the end it overlaps some of the value in the subreport.

1. how can i have partno to line up? this is important because when it export to excel, they need to use the sorting.

2. how can i get the asp value out without having it overlap?

--------------------------------------
i hope this clarify a little and i mgiht be able to seek help from you.

thank you,

m

 

[lbass]

It looks to me like you have set up right outer joins from the the sp table to the other tables, when I think you need a left outer join FROM the sp table to the co table. It looks like you have a mediating table pcbh between the sp table and the so table, and you would need to have a left join FROM the sp table to the pcbh table and also a left join from the pcbh table to the so table. Go to database->visual linking expert to verify that you have used the joins in this way, since I might be misreading your SQL statements.

In terms of getting the layout you want, I think you could place the subreport in GF#1b. In the main report you could create a formula which makes {table.std} a shared variable, as in:

whileprintingrecords;
shared numbervar std := {table.std};

Place this formula in GF#1a and suppress it. Then refer to this in the subreport to calculate {@ASP_Std_GM%}:

whileprintingrecords;
shared numbervar std;

std - {table.ASP}/std

With the {@ASP_std_ %} calculated in the subreport, you can now position the subreport so that the part number is directly under the part number in the main report.

I'm not sure whether {table.ASP} and {table.std} in the above formulas are database fields or calculated fields, so you might need to adjust that.

I'm not sure how well this will export--you'll have to try it.

-LB

 

[sommererdbeere]

hi lbass,

thanks for your reply.. i have a question (actually it happens couple times when i do report). I do the LEFT JOIN as you have suggested, which my sql look like this:

from PART_COSTpcbh, SALES_PART sp,
shop_ord so, customer_order coj
where
sp.PART_NO = pcbh.PART_NO and
sp.PART_NO = so.PART_NO and
sp.PART_NO (+) =[/red] coj.PART_NO and
pcbh.PART_NO = coj.PART_NO and
sp.PART_NO (+) = pcbh.PART_NO and
pcbh.PART_NO(+) = so.PART_NO and

it highlights the part i color it in RED[/red], what does it means? i cannot see what's wrong w/my join, it will be great if you can point to me on what i've done wrong.

also, shall i place(below) in GF#1a?


whileprintingrecords;
shared numbervar std := {table.std};


----

then, where shall i placed this part? (below)


whileprintingrecords;
shared numbervar std;

std - {table.ASP}/std


----

thanks for your reply.. i was being scolded by my boss of having this turn in late.. (the reason i'm telling this because i asked this for urgent and said the deadline) i'm really appreciate for all your help.. thank you very much

thank u,

m

 

[lbass]

I didn't mean for you to combine the SQLs--you are still using a subreport. So in the main report, do a left join FROM the sp table to the pcbh table and also a left join from the pcbh table to the so table. In the subreport, use a left join FROM sp to co. Please can you confirm that you checked the visual linking expert and that you have left joins? Your SQL statements still look to me like right joins--I think a left join would show the (+) after the right table.

As I mentioned above, place the first formula in GF#1a. The second formula goes in the subreport--again I'm not sure about your fields, so this might be the detail or the group footer--you need to figure out where that calculation occurs.

-LB

 

[sommererdbeere]

quick question.

isn't it true left join mean the (+) should place on the left?

 

[sommererdbeere]

hi lbass,

i have question about linkage problem. if i linked main_partno to sub_partno, isn' it true that will create 1-1 issue??

my question is i want to list *ALL* the parts in customer_order(sub).. let say:

main partNO: 1,2,3,4,5
sub partNO: 1,2,3,4,5,6,7,8,9

if i linked by their partNO, the output will give me 1-5. However, i want 7-9 as well from subreport.

is there any way i can do it??

sorry for causing you so much trouble and thanks for any of your reply and inputs

 

[lbass]

I thought that all customer order part numbers and all shop order part numbers were represented in the sales part part number table, and that in both the main report and the subreport you were grouping on {sp.partno}. However, I thought you were displaying the shop order part number in the main report, and the customer order part number in the subreport. This would require a left join from the sp table to the co table in the subreport, and a left join from the sp table to the intermediary table and another left join from the intermediary table to the so table in the main report.

Left joins appear with a (+) after the right hand table, not the left.

-LB

 

[sommererdbeere]

hi lbass,

thanks for all ur reply and help. i figure the table out as the steps you show me was right, but i mess up the set up of the tables. I should have customer part driven sales part, thus, i can get all the variable in customer parts.

sorry for all the mess and confusion i've made.

thank you for your great help.. thank you

m