Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Reports sub report issue

Posted on 2012-03-19
16
Medium Priority
?
393 Views
Last Modified: 2012-03-21
Dear Experts,


I have a report with a date parameter
screenshot 1
it passes it to the subreport


subreport has a query with a parameter

subreport param
the query is



 select distinct invntryitm_id,serial,itemdesc  from (
SELECT DISTINCT ABCOSPRO_EQTOOL.invntryitm_id, ABCOSPRO_EQTOOL.serial, ABCOSPRO_EQTOOL.itemdesc, ABCO_Inv_batch.date,case when ABCO_Inv_batch.date is null then '' else ABCO_Inv_batch.date end as thedate
FROM         ABCO_Inv_data INNER JOIN
                      ABCO_Inv_batch ON ABCO_Inv_data.batch_rn = ABCO_Inv_batch.batch_rn FULL OUTER JOIN
                      ABCOSPRO_EQTOOL ON ABCO_Inv_data.serial = ABCOSPRO_EQTOOL.serial
WHERE     (ABCOSPRO_EQTOOL.serial LIKE '%') AND (ABCOSPRO_EQTOOL.serial <> ''))x
where thedate not in  ({?Pm-@qdate} )
and serial not in (
SELECT DISTINCT ABCOSPRO_EQTOOL.serial
FROM         ABCO_Inv_data INNER JOIN
                      ABCO_Inv_batch ON ABCO_Inv_data.batch_rn = ABCO_Inv_batch.batch_rn FULL OUTER JOIN
                      ABCOSPRO_EQTOOL ON ABCO_Inv_data.serial = ABCOSPRO_EQTOOL.serial
WHERE     (ABCOSPRO_EQTOOL.serial LIKE '%') AND (ABCOSPRO_EQTOOL.serial <> '') and convert(varchar(50),date) =  ({?Pm-@qdate} ))








-------------
for some reason if I enter the date 3/19/2012 the main report takes it but the subreports sees 04/12/3765 or something


??wtf


someone please help

Thanks in advance
0
Comment
Question by:Ess Kay
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37739629
How are you linking the reports?

Can you upload the report?

mlmcc
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 37739656
0
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 668 total points
ID: 37739704
In the main report, go to the properties of the sub report.  Ensure your linking is correct for the main parameter to the sub parameter.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 664 total points
ID: 37739756
You don't have the subreport linked to the main report so there is no passing of the value.

Do you get prompted for the date?

To link the subreport
Right click the subreport
Click CHANGE SUBREPORT LINKS
Choose the main report date parameter
IN the subreport choose the subreport parameter
 Don't check the SELECT RECORDS BASED ON LINK

mlmcc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 668 total points
ID: 37740858
Technically, there is a subreport link, but it doesn't make any sense.  You have a @qdate formula in the main report that converts the main report's ?Date parameter to text, and a @qdate formula in the subreport that converts the subreport's ?Pm-@qdate??01 parameter to text, and you linked @qdate in the main report to @qdate in the subreport.

 Were you maybe trying to pass the value in @qdate in the main report to the @qdate formula in the subreport, thinking that the subreport formula would put the value in the subreport parameter?  If so, it doesn't work like that.  Formulas can only read the values in parameters.  They can't change them.

 Aside from that, you're not actually using the subreport parameter or formula in the record selection formula.  As far as I can tell, you're not really using those values at all, except where you display them on the subreport, although even when I remove them from the subreport, CR still says that they're being used somewhere, so maybe there's something buried somewhere that I just can't see.


 I would normally expect CR to prompt you to enter a value for the subreport's ?Pm-@qdate??01 parameter, but:
 1) Since you were passing a value to the subreport's @qdate formula, maybe CR wouldn't have needed ?Pm-@qdate??01 for that formula (since the result of the formula is apparently just being replaced by the value passed from the main report).
 2) The report that you u/l'ed has ?Pm-@qdate??01 on the subreport, but I don't see that in the screenshot that you posted, so maybe it wasn't on the report in that version.  If it was on the report, CR should prompt you to enter a value for it.


 As mlmcc said, it seems like you just need to link the parameter in the main report to the parameter in the subreport.  And you need to change the record selection formula in the subreport to compare the date field with the subreport parameter (the report that you u/l'ed just has some lines that are commented out).


 FWIW, I don't know where the weird value for @qdate in the subreport came from.  I've never tried linking a formula in a main report to a formula in a subreport like that, so maybe it just gets weird results.  Using ToText in both formulas might also have something to do with it, like if the @qdate formula in the subreport ends up trying to use ToText on the string that was passed from the main report, but is trying to treat it as a date.

 James
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 37742237
Ive tried several things with it. This is why it may seem confusing.




1st I passed just the regular date value straight to the subreport @date
then converted it to a string in the query. this was changed to ?pm-@date

then i tried converting it to text in the formula as @qdate and passing it
a) straight to the query as a parameter.  ?pm-qdate
b) used as a formula to change back to date and pass to query
?pm-qdate..01


the query in the subreport works fine actually as you can see from the screenshot. the values get passed incorrectly between them

it appears that the number gets deranged when it gets passed to the subreport regardless of how i format it



This is its use in the SUBQUERY
subquery
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37742528
You have to pass the date in the proper format to the parameter fromthe COMMAND.

You are getting the wierd values because the command parameter has a default value.

Edit the command and delete the default value from the parameter.  I had a similar issue and the only way around it was to not have a default value for the parameters.

mlmcc
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 37742554
i always left the default empty

default field
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 37742606
strange I reapplied the parameters another ten times and it started to work again.


linked the parameters straight from the report this time and its starting to pull correct data



works now


still not clear of what the problem was, but i think i should just stop touching it and step away
0
 
LVL 15

Author Closing Comment

by:Ess Kay
ID: 37742627
God I hate SAP always some problems with their programs

thanks for taking the time out of your lives to try to help me guys
0
 
LVL 35

Expert Comment

by:James0628
ID: 37745759
Ah.  I hadn't noticed that you were using a Command in the subreport, so I didn't realize that you were using the parameter there.  It seems like the report that you posted should have been prompting you for the subreport parameter.  That issue aside, I don't know why simply linking from the main report parameter to the subreport one wouldn't work (or why you would have trouble getting it to work).  If you're converting the date parameter to a string in the report, and converting a date field to a string in the Command, then you need to make sure that both conversions use the exact same format, since you're going to be comparing strings.  But that wouldn't explain the strange dates you were seeing.


 This is unrelated, but FWIW ...

 You used this in 2 WHERE's in the subreport Command:

(ABCOSPRO_EQTOOL.serial LIKE '%') AND (ABCOSPRO_EQTOOL.serial <> '')

 I think you could simplify that to

(ABCOSPRO_EQTOOL.serial LIKE '_%')

 That should match a serial that is one or more characters, which seems to be what the first 2 tests are doing.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37747100
ONe thing to try, delete the parameter in the command and add it back in with a more conventional name.

Also, unless the field in the database is a string, there is no reason to convert the date from the main to a string.

mlmcc
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 37747507
James, thanks for that idea  i just dont want it to be null or blank, your way is simpler

mlmcc,

1_  the fields in the database are strings. i have to convert them to date so that the user can have a dropdown calender to select a date then convert it back to a string

2_  if i add a 'more conventional name' it gives me two parameters to enter in the beginning instead of one.
it has to match.  or thats a whole different problem. the reason it ended as {?Pm-@qdate??1} is because

(and FYI)

{?Pm-@qdate??1} breakdown:
?PM means its a parameter
@ means the value i passed was a formula
qdate is the actual formula i pass
??1   means its the second parameter named like that because the first didnt work the first one is actually ?Pm-@qdate
0
 
LVL 35

Expert Comment

by:James0628
ID: 37747710
It seems like there should be a more direct way to handle this, although since you're using MS SQL, a date (as opposed to datetime) parameter could be a problem, since MS SQL didn't have a date data type until SQL Server 2008.  I don't know if you could drop a date parameter into a Command with the earlier versions.

 If nothing else, I would think that you could use a string parameter in the subreport Command and a date parameter in the main report, use a formula in the main report to convert the date parameter to a string, and link that formula to the string parameter in the subreport.  You would just need to make sure that CR produced a date string that was in the exact same format as the date strings in your db.

 James
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 37747739
works fine now, im afraid if i change something it might hiccup again
0
 
LVL 35

Expert Comment

by:James0628
ID: 37747774
I get that, but thought I'd throw that out there.  Maybe next time it decides to act up.  :-)

 James
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question