Adding input to a report

[soaring_eagle]

Hi,

I've got a query which asks for the first date and last date to search within. I've created a query and now a report which successfully gives a list of people who have a date of birth between these values

Now I'd like to add the inputted dates to the title of the report. Is this possible?

Thanks,
Naomi

[Sliderule]

Now I'd like to add the inputted dates to the title of the report. Is this possible?

Yes.

I've got a query which asks for the first date and last date to search within.

Your Query, is composed of fields ( in the Select clause . . . or . . . in the GUI ( Graphical User Interface ) on the Field Line ) . . . so . . . include the prompted for date range as a part of the Query. This means, each line of the Query will include the date range in the output.

Therefore, when you create your report layout, you include these fields in the appropriate place, per your description, in the Report Title.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[soaring_eagle]

I've had a go at doing this, but I'm obviously going wrong somewhere! This is my query and what I've done for my report.

Thanks,
Naomi

[Sliderule]

In your graphic above, it shows ONLY the use of one table "tblMember" in the Query, therefore, I have created the Query below to reflect that.

Follow these instructions, and, see if you get what you want:

1. Open your OpenOffice / LibreOffice Base file ( *.odb )
2. On the left, click on Queries
3. Under Tasks, click on: Create Query in SQL View...
4. Copy and paste the following select SQL:
   

   Code: Select all

   Select
      "tblMember"."FirstName",
      "tblMember"."SurName",
      "tblMember"."DateBaptised",
      TO_CHAR((Select MIN("tblMember"."DateBaptised") From "tblMember" Where "tblMember"."DateBaptised" >= :From_Date and "tblMember"."DateBaptised" <= :To_Date),'MM/DD/YYYY') as "From Date",
      TO_CHAR((Select MAX("tblMember"."DateBaptised") From "tblMember" Where "tblMember"."DateBaptised" >= :From_Date and "tblMember"."DateBaptised" <= :To_Date),'MM/DD/YYYY') as "To Date"
   
   From "tblMember"
   
   Where "tblMember"."DateBaptised" >= :From_Date
     and "tblMember"."DateBaptised" <= :To_Date

5. Run the Query, EITHER:
   1. Press F5
   2. On the Toolbar, click on Run Query (F5) icon
   3. From the Menu: Edit -> Run Query
6. Answer the two prompts
7. Review the Query results
8. Save the Query to a name of your choice
9. Now, you will have two NEW fields, "From Date" and "To Date" that can be used in your Report Title.
10. Smile and say: "Gee Sliderule, that was easy, now all I have to do is mark the forum post as [Solved] per the instructions at the bottom."

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[soaring_eagle]

Thanks! I totally understand what you were trying to say before now.

However, I've moved the "From Date" and "To Date" fields and they aren't coming up, instead the text is just displayed. Is there a special way of moving these headings so that they retain the values?

My report in design view:

The report in report view:

Thanks,
Naomi

[Sliderule]

You said:

However, I've moved the "From Date" and "To Date" fields and they aren't coming up, instead the text is just displayed. Is there a special way of moving these headings so that they retain the values?

I have no idea what your words mean. Is the Query returning the 'values' you expect? What SHOULD be returned, are, TEXT fields ( not date fields ) because I used the TO_CHAR function to convert the calculated fields to text. In the report 'location' you are using the fields, are they formatted as text?

Can you include here, a copy of your database, with the report and specific instructions on what you are doing, what you expect, what it does produce?

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[soaring_eagle]

I've uploaded a copy of the database with just the table, query and report in it.

example database.odb

(17.11 KiB) Downloaded 416 times

I've also added a word document to hopefully explain what I mean.

explanation.doc

(116.5 KiB) Downloaded 407 times

Thanks,
Naomi

[Sliderule]

Naomi:

I elected to use Oracle Report Builder, to create the report you want. Therefore, in the attached database, you will find 2 new entries by me ( you should be able to open both the attached database, and, your database, and, DRAG the two new entries from the attached to your database ) :

1. A new Query, by the name of: qryBaptised_Ask_Date_Range
   
   the above query is 'simpler' and consists of the following SQL:
   

   Code: Select all

   SELECT 
      "tblMember"."FirstName", 
      "tblMember"."Surname", 
      "tblMember"."DateBaptised" 
   
   FROM "tblMember" 
   
   WHERE "tblMember"."DateBaptised" BETWEEN :From_Date AND :To_Date

2. A new Report by the name of: qryBaptised_Ask_Date_Range
   
   the above Report was built with the Oracle Report Builder, and, uses an internal Oracle Report Builder Function, to build the title you want, using the values input by the user when prompted by the Query.

example database.odb

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[soaring_eagle]

That's great, but I can't get the Oracle report builder to work for me

I posted about this on: http://forum.openoffice.org/en/forum/vi ... 42&t=57062

[Sliderule]

That's great, but I can't get the Oracle report builder to work for me

I do not know what to tell you, except, if does work for me. Sorry.

Perhaps someone else can help.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[soaring_eagle]

Thanks for trying! I've got something that I can use if I can get it working properly

[Sliderule]

I am sending you a PM ( Private Message ).

Sliderule