BusinessObjects Board

How to create Dynamic Report

building reports webintelligence
#1

Plz can any one help me to create dynamic reports,which is able to allow the user select coloums( columns names as objects in the prompt filter) based on that report will be generate.if there is 10 columns in the prompt filter,if he select 3 we have to see 3 columns only in the reports view.Plz can one help me how to create variable for slove this problem.
(Here we are giving them to select columns instead of values)
EX-Table1 columns are a,b
Table2 columns are c,d
Tablt3 columns are e,f
In query filer we we have to give prompt like ā€œSelect columns in listā€
a,b,c,d,e,f.
how many they select based on that report gives that no of columns.

Neetusai (BOB member since 2010-04-02)

#2

I do not see the point in this, the user should just use the query panel to pull in the objects/columns as needed (based on a universe).
Or the object panel after the query has been created.
To me it seems you are re-inventing the wheel.

Andreas :de: (BOB member since 2002-06-20)

#3

Hi Sir Andreas

I want to create dynamic reports the reason why i need it is every month reports are generated i want to create a report which when i open it i should get updated automatically.

for example:
i created the first report on march 1st and when i open the same report on april 1st i should get updated with the data and the date.

Need your help

Thanks

osrootofos :us: (BOB member since 2012-02-10)

#4

Teach creating reports using WebI to your users. I think this is what they are trying to do.

sunilc12005 (BOB member since 2010-09-27)

#5

There are a number of ways you can accomplish what you are trying to do.
You havenā€™t provided enough information for anyone to clearly give you the ā€˜bestā€™ option, but some things to consider.

If your users are on the low end of the tech usability spectrum, and you do not or cannot give them access to create thier own WEBI reports, you can acomplish this all via a Universe set of prompt objects.

The basic idea, is you create a prompt in the universe, that reads a table (or view or derived table) with a list of valid columnsā€¦ Prompt the user to select one of those values, and based on that selection return a specific object.

For exampleā€¦ if you had a table you create called ā€œColumnTableā€ with values of ā€œSales $ā€ and ā€œUnitsā€. (You can use a derived table for this rather easily inside the universe)

Then you would create an object with the syntax similar to:

Select:


Case When @PROMPT('Select Column1:','A','ColumnTable\ColumnField',single,constrained)) = "Sales $" THEN @Select(<Objects>\<salesobject>)
ELSE
Case When @PROMPT('Select Column1:','A','ColumnTable\ColumnField',single,constrained)) = "Units" THEN @Select(<Objects>\<unitsobject>)
Etc....
END

Then when you build your WEBI/Crystal report, you would use Column 1 in both the query filter and data objectsā€¦ showing that data object on the report somewhereā€¦ it will automatically pick the proper data from the database based on the user prompt.

Do this for all 10 columns, and you have your dynamic report.

Now, while that works, that is a lot of work for your developers to create and debug.

The easier solution is if your users are somewhat tech savy and you can give them access to create thier own reports, youā€™d be MUCH better served training them on how to use WEBI to create thier own reports from the universe you provide (It can even be a scaled back universe just for this report if needed). The gains you get here will trickle to other projects as well and releive some of the reporting needs off IT (though it might cause other issues , so itā€™s a balancing act sometimes).

I think you were looking for more of a solution like option 1ā€¦ but option 2 is a much better long term solution as others have suggested.

JPetlev (BOB member since 2006-11-01)

#6

Hi

Thanks JPetlev for the reply.

Is there any other way to achieve the same.

Thanks

osrootofos :us: (BOB member since 2012-02-10)

#7

Hi

i will explain the problem once again with example.
for example : a reported is generated every month end content of the report:

product------ qty--------price

10-------------20--------100
20-------------40--------200

like this way the data in the report reflects.

now i want that next month end when i open the report it should reflect changes according to the month example contā€¦

product------ qty--------price

10--------------0--------100
20-------------20--------200

Thanks

osrootofos :us: (BOB member since 2012-02-10)

#8

Will data tracking help ? It updates the data(measures) going up or going down.

BO_Chief :us: (BOB member since 2004-06-06)

#9

So all you really want is relative dates? User runs report in October and gets Septemberā€™s data. User runs same report in November and gets Octoberā€™s data?

Build some date objects in your universes (see sticky at top of Designer forum for code specific to your database). Build an object that always returns start of previous month and an object that always returns end of previous month and filter your data between the two. Then the same report will always show you just the previous monthā€™s data.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#10

I did not get what you want to say please explain
and also i want the data also to be reflected with the date. for explanation please see my example above

Thanks for the response

osrootofos :us: (BOB member since 2012-02-10)

#11

So whenever the report is opened, you want it to run? If so have a look at the Refresh on Open option in the document properties.

If that is what you want, then as Debbie said, there is this topic that will explain how to create universe objects with relative dates, e.g. Start of Previous Month and End of Previous Month. You can then use these objects as part of a between statement in your conditions section in the query panel.

#12

@osrootofos

What I think you want is this:

User opens report on any day in October. Report runs and gets all data from 1-30 September automatically. Report header shows 1-30 September.

User opens same report on any day in November. Report runs and gets all data from 1-31 October automatically. Report header shows 1-31 October.

So report will always automatically pull data fro whoel of previous month without the user having to enter dates at runtime.

If this is correct, you need relative dates - see sticky in Designer forum. If Iā€™m wrong, youā€™re going to need to explain more precisely what you are looking for.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#13

Hi All

Thanks Debbie and mark P for the reply.

Debbie you are right that is what i want.

Please explain how to achieve that.

Thanks

osrootofos :us: (BOB member since 2012-02-10)

#14

As discussed, the best way is to create date objects at universe level.

  1. Create a date object called Start_of_Last_Monthwhich always evaluates to the 00:00:01 on the 1st day of last month (using sysdate or getdate depending on your database)

SQL Server: dateadd(m,-1,dateadd(m,datediff(m,0,getdate()),0))
Oracle: trunc(add_months(sysdate,-1),ā€˜monthā€™)

  1. Create a date object called End_of_Last_Month which always evaluates to 23:59:59 on the last day of last month

SQL Server: dateadd(s,-1,dateadd(m,datediff(m,0,getdate()),0))
Oracle: to_date(substr(to_char(last_day(add_months(sysdate,-1))),1,10) || ā€™ 23:59:59ā€™, ā€˜DD/MM/YYYY hh24/mi/ssā€™)

Then use these objects in your report so that you have a filter where date between Start_of_Last_Month and End_of_Last_Month

Have a look at the sticky in the Designer forum which has teh code for the above and many more relative date objects.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#15

Hi All

THANKS Debbie

Thanks a lot.

ā€œHave a look at the sticky in the Designer forum which has teh code for the above and many more relative date objects.ā€

please can you provide me the link which you are talking about.

Thanks

osrootofos :us: (BOB member since 2012-02-10)

#16

Here: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#17

Hi

Thanks Debbie

Thanks

osrootofos :us: (BOB member since 2012-02-10)