BusinessObjects Board

Splitting scheduled reports into multiple excel sheets

building reports
,
#1

We are using XI 4.2 sp9 . There is a scheduled report which we would like to split into multiple excel sheets in a work book . I know earlier it was not possible. Is this available now? If not then can someone suggest a workaround ?

#2

How do you want to split it? Purely based on the number of rows or some other criteria?

#3

Preferably based on the number of rows. Each sheet should display 10000 rows. If the report has more number of rows, then it should be displayed in the next sheet of the excel file. Generally the report would fetch about 50000 rows. Thanks.

#4

I came up with something that is not as clean as I would like it to be and may very well not work for you. I thought I would post it anyway hoping it might work or spark an idea to improve it.

My basic approach is to create a Var Sheet Number variable taking into account the Var Max Rows Per Sheet which is just a constant set to 15.

=Ceil([Row Number]/[Var Max Rows Per Sheet])

The primary issue here is I could not figure out how to derive a row number for my data within the report which would be maintained after filtering for each report tab (sheet). So I resorted to free-hand SQL and added a Row Number to my data as seen here…

SELECT
     ROW_NUMBER () OVER (ORDER BY Calendar.dt) AS [Row Number]
   , Calendar.dt [Calendar Date]
FROM Calendar
WHERE Calendar.dt BETWEEN '2023-02-01' AND '2023-03-31'

Then create as may report tabs as you feel you will need with a filter on each corresponding to the sheet number.

image
image1210×716 51.4 KB

I see plenty of drawbacks here…

  • I could not figure out how to add a row number without my filtering messing it up. So I had to resort to adding a row number to the query.
  • The number of report tabs is not dynamic. In my example, with my Var Max Rows Per Sheet variable set to 15 and 5 report tabs any query results beyond 75 rows of data will be suppressed. Also, any query results less than 61 rows of data will result in one or more blank report tabs.
  • When I started thinking about this I thought I could just create a sufficient number of tabs and just hide them if the table had no data, but I could not figure out how to do that.

Hope this is sufficient for your requirement or at least gets you going in the right direction.

Noel

1 Like
#5

Thank you very much. I was thinking on these lines but not sure how to go about. This will be a good one to start with .I am sure this will be very helpful for me to explore and hopefully find out a way to deal with some of the drawbacks.

1 Like