Print Page | Close Window

Selecting a specific record value to input header

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11283
Printed Date: 06 May 2024 at 6:39pm


Topic: Selecting a specific record value to input header
Posted By: bradlee27514
Subject: Selecting a specific record value to input header
Date Posted: 30 Sep 2010 at 6:05am
I am attempting something that I think should be rather simple.  I think I can do it inside the report but perhaps I need to do something in my SQL command.  Any help is appreciated.

My command is pretty simple:
select
  gl00100.actindx,
  gl00100.actnumbr_1,
  gl00100.actdescr,
  gl10110.actindx,
  gl10110.year1,
  gl10110.periodid,
  gl10110.perdblnc,
  gl10110.crdtamnt,
  gl10110.debitamt
from
  gl00100
left join
  gl10110
on
  gl00100.actindx=gl10110.actindx


I initially thought I just needed to display all this data in the details section.  However, I actually need to pull some values and enter them in the Report Header (Page Header would be acceptable as well).  I can't seem to figure out how to do this.

Basically in the header I want to say show me 'gl10110.perdblnc where gl10110.actindx=35'

I can get this in the footer by using a running total (with a formula saying actinx has to =35), but I need it in the header.

Any Ideas?

cheers,
brad


Replies:
Posted By: DBlank
Date Posted: 30 Sep 2010 at 6:32am
you would have to make it a sub report or do something outside in the SQL that would either allow you to use an CR insert summary option which can be displayed in a header or force it into the first row whcih can be dispalyed in a header.

Posted By: bradlee27514
Date Posted: 30 Sep 2010 at 9:28am
I have very little experience with sub reports.  I need the user to pass a parameter for the year and period fields.  Would this be possible with a subreport?

My thinking was that there would have to be something I could add to the sql command, but I can't find anything.

Posted By: DBlank
Date Posted: 30 Sep 2010 at 9:48am
you can pass the variables to sub reports but maybe you can use a summary function.
whjat exactly is the data for
'gl10110.perdblnc where gl10110.actindx=35'

Posted By: bradlee27514
Date Posted: 30 Sep 2010 at 10:13am
they are financial tables from a Microsoft Dynamics db.

gl10110.perdblnc is a currency
gl10110.actindx=35 is an account ID

The user will be entering the year and period id (month) and they will get that month's data.

i now have two reports with the data I need.  For the part I was trying to get into the intial report I just used running totals in the header section (with function criteria actindx=x for each of those accounts I wanted to put in the header).

I just need to get these two reports into one now I think.  One report only has data in the details section (I need this to be the bottom of the report), and the other just has information in the report footer.  Can I enter the initial report as a subreport of my new report and just put it at the bottom of my current footer content?

Posted By: DBlank
Date Posted: 30 Sep 2010 at 10:19am

maybe. YOu have to change the way the params work in the subreport unless you want the user to enter them twice.

assuming by currency you mean a string that described the curerncy type, for you header info you can try a formula called 'HeaderName' as:
if gl10110.actindx=35 then gl10110.perdblnc
Then use an insert summary as the MAXIMUM( mailto:%7b@HeaderName - {@HeaderName }) and palce this in your report header.

Posted By: bradlee27514
Date Posted: 30 Sep 2010 at 10:21am
i tried this
if gl10110.actindx=35 then gl10110.perdblnc

it yields no data, was the first thing i tried.  then i realized a running total would only work in the foooter.

Posted By: DBlank
Date Posted: 30 Sep 2010 at 10:34am
if gl10110.actindx=35 then gl10110.perdblnc
is a row by row. Place it on your detail and you should see the value you are looking for appear only on the rows that have the 35 value.
using the MAXIMUM( mailto:%7b@HeaderName - {@HeaderName }) can be placed in a header or footer to show the maximum of the result of the formual field.
All this is under the assumption that the currency type returned is always the same. If not please explain the data a little further.

Posted By: bradlee27514
Date Posted: 30 Sep 2010 at 10:51am
thank you so much for your help.  I believe I got it.  Now if only I could get my users to stop asking for changes :)


Print Page | Close Window