| Author |
Message |
bradlee27514
Newbie
Joined: 24 Jun 2009
Location: United States
Online Status: Offline
Posts: 20
|
   Topic: Selecting a specific record value to input header
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
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
Edited by DBlank - 30 Sep 2010 at 6:33am
|
|
IP Logged |
|
bradlee27514
Newbie
Joined: 24 Jun 2009
Location: United States
Online Status: Offline
Posts: 20
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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'
|
|
IP Logged |
|
bradlee27514
Newbie
Joined: 24 Jun 2009
Location: United States
Online Status: Offline
Posts: 20
|
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?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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( {@HeaderName}) and palce this in your report header.
|
|
IP Logged |
|
bradlee27514
Newbie
Joined: 24 Jun 2009
Location: United States
Online Status: Offline
Posts: 20
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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( {@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.
|
|
IP Logged |
|
bradlee27514
Newbie
Joined: 24 Jun 2009
Location: United States
Online Status: Offline
Posts: 20
|
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 :)
|
|
IP Logged |
|
|
|
