dynamic named ranges

other Xcelsius - Crystal Dashboard Design
1

from what i have read, xcelcius 2008 does not support dynamic named ranges. basically i need to be able to use something like QAAWS to retrieve a variable number of records. i want to to be able to display them all.

i can’t find any answer to this problem. that is, how can you retrieve a variable number of rows from qaaws or a live office excel spreadsheet?

and then select all rows that have been retrieved, but no others.

dbobcoe (BOB member since 2009-05-15)

2

Can you be a little bit more specfic? What you have said is kinda vague.

  1. A dashboard will only show whatever you design it to show
  2. dynamic and logic will go hand in hand

katullus :us: (BOB member since 2009-08-21)

3

sure. and thanks for the reply.

we have a project which will look at the different types of data sources we will support. so we are doing research.

we have xcelcius 2008 enterprise.

one of the complaints we have from customers is that if you use, for example, live office and build a connection to a universe, the number of rows you receive will vary over time. it could chg hourly or daily or by minute.

from what i have found, it seems that looking at this from an excel perspective, one way around this problem is to use named fields/columns/rows. that way, regardless of how many rows are returned, you get, well, all the rows.

but xcelcius does not seem to handle that. if i build a bar chart, for example, i have to specify the exact number of rows to display in the chart. but when a user refreshes the dashboard, the customer has no idea how many rows will be retrieved. they just know they want all of them.

this is not to say there are thousands of rows. but even if it is the difference between 10 and 15 rows, the dashboard needs to be able to handle that constant change.

does that make it clearer? or do you want more info? or an actual example?

dbobcoe (BOB member since 2009-05-15)

4

You can apply filters to the report or can do some excel calculations to show how much data you want to display.

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

5

thanks for the reply.

i don’t think i understand your suggestion.

i attached a spreadsheet to show some sample data.

in this example, we have bus drivers who are id’ed by their badge number.

and for a given day it shows how many times they changed buses.

so on monday there are 6 drivers.

on tuesday there are 8 drivers.

on the next day there might be 4 drivers.

let’s say i build a simple dashboard that has a pie chart. each segment of the chart shows how many changes the driver had.

So that means when the dashboard is refreshed, the internal excel spreadsheet in xcelcius must handle a different number of bus drivers. and then the pie chart has a different number of segments.

i could guess at the absolute maximum number of drivers possible and assign that to the pie chart.

but this is a simple example. other cases have a more data and more complicated data. so i want the number of items for a chart to match the amount of data actually received.

i am looking for a method that i can teach all the users rather than telling them to guess at the maximum number of rows they might get. i can see someone guessing incorrectly and then getting misleading results.
example.xls (25.0 KB)

dbobcoe (BOB member since 2009-05-15)

6

hi,

almost every chart have the option to ignor blank values in rows/columns.

so select a max range and ignore blank values, so the empty cells/rows are not shown in the chart.

best regards

Despicatus :switzerland: (BOB member since 2010-05-07)

7

yeah, i was afraid that might be the only choice. i was hoping to find something that dynamically changed the number of rows.

thanks.

dbobcoe (BOB member since 2009-05-15)