[BOXIR2]Creating dynamic report (sort , subtotal) using VBA

sdk development
1

Hi Gurus,

I am now working on BOXI R2 and have decided to apply the VBA in desktop intelligence to build the dynamic report as below requirement.
Actually, I’m using WEBI but since WEBI could not use VBA ,so I have moved to DESKI. ( If you have any recommendation , please be welcome)

Requirement :

  1. Let’s say that the report has 15 columns
  2. My user need to identify the sort order and subtotal by herself. The column that can be sorted or sub totaled will be within this 15 columns
    -Users would enter numbers (beginning with 1) in the Sort Order column to identify the order.
    -Users would enter ‘Y’ in the Subtotals column to identify the fields to include subtotals.

I have no idea how to design VBA programming for above requirement. Does anyone help to guide me the way?

Thank you very much in advance.

Bee

Bee (BOB member since 2005-04-19)

2

Is it possible to do this? Well, yes probably … but with a LOT of effort, and the result will be fragile … hand-coded VBA userforms, storing previous choices to be used again next time, changing variable formulas on the fly, etc.

My advice, teach the users how the built-in sorting and sub-totaling functionality works.

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

3

Hi Dwayne,

Thanks for your reply. I know that it’s not worth to do but our user is the executive who doesn’t need to do sorting or subtotal.

I’ve tried to code the VBA in DESKI and still find error when using Sorts Class.

Thanks

Bee

Bee (BOB member since 2005-04-19)

4

I have done reports on this using custom objects defined in the universe combined with drill filters. It can be a bit difficult at first but the results look very nice.

When set up, the users can select a sort order from the drill bar when the report is in drill mode.

To dynamically sort a report according to column number:

Create a "Sort Order"object in the universe using a column from a derived table that just holds as an example the values 1,2,3 or 4.

Create a new data provider in the report that just uses the “Sort Order” object

Create a variable in the report that uses the DrillFilters() function to get the value the user selected for the “Sort Order” in the drill bar.

Depending on the value the user selects, set the variable to the column to be sorted on.

Apply the primary sort on this column.

Put the report into drill mode.

I hope this helps, unfortunately do not have time to do the coding.

ottoman

Ottoman :uk: (BOB member since 2002-10-04)

5

p.s.

the technique could also be used to hide section footers etc to display subtotals

Ottoman :uk: (BOB member since 2002-10-04)

6

Thanks Ottoman,

Could you please give me more information about hiding section filter for subtotal? How to do that?

Thanks,
Bee

Bee (BOB member since 2005-04-19)