BusinessObjects Board

Using prompt values in report detail and on export

building reports desktopintelligence
#1

I have a report which requires the user to enter a date range.
I would like to take the yr and the month from the date entered and include them as a column in the report and I would like those values to be exported.
Is there some way to do this?
Tia
Darlene

hueymoo (BOB member since 2005-02-25)

#2

If two date prompts are ‘Start Date’ and ‘End Date’, define two variables StartDate and EndDate as below

StartDate
=(ToDate(UserResponse(DataProvider() ,“Start Date”) , “mm/dd/yyyy”)

EndDate
=(ToDate(UserResponse(DataProvider() ,“End Date”) , “mm/dd/yyyy”)

Where is any object selected in the Query.

Now Month and Year part of the StartDate can be extracted by following formulas

=Month()
=Year()

And Month and Year part of the EndDate can be extracted by following formulas

=Month()
=Year()

hth

AskMe(Dhirendra) (BOB member since 2005-05-13)

#3

Thanks Dhirendra
That works to a point in that we can get the columns to show on the report.
However, when we try to export the results to an rdbms, the new fields don’t show.
Any idea how to get them there?
We’re trying to dump this into an access database. Not really sure why at this point.
Thanks
D

hueymoo (BOB member since 2005-02-25)

#4

Report level variables (like UserResponse) are not going to be included in the data provider export (by definition). Your alternative is to create a simple report that displays every row and column from the data provider. You can then add the report variables to that same report. The bad news now is that exporting report tabs is limited to tab-delimited text files. You can’t export directly to the RDBMS, but MS Access can easily link to / import from the tab-delimited text file. Take a look at the white paper attached to this topic for more details / options.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#5

I have done this. You will have to hard code the SQL (check the Do Not Genereate SQL before running box) to accomodate this.

  1. Create two objects to be dummy or place holder objects and these to your report in the place where you want them to be in the export.

  2. Edit the dataprovider, edit the SQL

  3. Click the ‘Do Not Generate SQL before running’ check box on the SQL Viewer screen.

  4. Now find one of your prompts in the SQL, copy and paste the prompt in place in the SELECT portion of your SQL.

  5. Repeat as needed.

You should have SQL that looks something like:


SELECT
  COLUMN1,
  @VARIABLE('ENTER START DATE:'),
  @VARIABLE('ENTER END DATE:'),
  COLUMN4,
  .
  .
  .

I have used this from time to time with version 6.1b.

estaup (BOB member since 2002-10-15)

#6

Or better yet, create actual Designer objects for the @variable or @prompt syntax. Then simply include them in the query panel as a result object.

I create a separate class in my universe that holds one object for each prompt. The object contains only the @prompt syntax. Any place else that the prompt is needed, I reference the one object with the @select syntax. That way if I ever need to change the prompt (the text, the LOV, etc.), I only change it in one place. The side benefit is that I have an actual object that can be used to return the prompt choice as part of the data provider if needed.

If Darlene is using query panel based prompts (not in Designer) then the “Do not regenerate SQL” approach is another option for getting the prompt into the data provider. Generally, “Do not regenerate SQL” is to be avoided for production use (can be a maintenance nightmare), but it is an option.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)