Hi all,
I am using dashboards 4.1 and have a query bringing 10 columns and about 30 rows. I want to display different columns to different components in my dashboard so i am basically linking certain columns to others in my spreadsheet, it works but the empty rows in the source columns display 0 in the destination ones, which then shows 0 in some of my components. How can i just link them and not display 0? Thanks
NycPriya (BOB member since 2010-11-04)
Using this forumla
=IF(Sheet1!AD2<>"",Sheet1!AD2, "")So far it seems to work but lets see how it performs on the dashboard.
NycPriya (BOB member since 2010-11-04)
I guess your bringing the data in through a connection. Most of the time I perform periodic dashboard updates weekly/monthly (nothing that requires a live feed) and the client desires the portability of exportable swf files. So, with that flexibility, I do most of my updates by compiling/appending periodic data sets and use pivots to generate summary trend data in offline Excel spreadsheets. The data from the pivots is then mapped into a summary workbooks and it is the summary workbook that is copied into the dashboard data sheets… copy - paste values.
Ok, with that process said… I also had a problem with zero’s where I wanted blanks. Rather than all the bloat of a formula in a bagillion cells to mask Bill Gates infamous zeros, there is an options setting to use zeros or blanks - in my offline spreadsheet that I copy into the dashboard. Obviously there are no excel options in the dashboard. But this saves all those zeros in large data streams. Some components have the checkbox to ignore “blank” cells but don’t work correctly with a big fat donut hanging out. So, in the end rather than copying all those zeros it just copies the blank cells into the dashboard.
Open Excel, File, Options, Advanced, (scroll half way down)
uncheck - show a zero in cells that have zero value… WA LA!
This will work for XML connections but the blanking or nulling of the data needs to be done before the file is generated. I’m sure other connections follow suit. Just need to think about the data from a dashboard designer view. DBMs don’t care - they’ll toss zeros all day long. Only ask for a nullment if she…
Every bit of added formula bloat is one step closer to hitting that magical ceiling where it says:
“Sorry, you wanted to generate a SWF? - what the F were you thinking. Wait till I grow up and lose the 32 bit memory space (~1.8g) and become a real 64-bit program someday. Please point blame on your Administrator - as he has no clue why I failed you.”
datawizard (BOB member since 2015-01-20)
Here’s a trick to lose the zero’s… forever (workbook dependent)
Open Excel
by default you have three workbooks Sheet1, Sheet2, and Sheet3.
double click Sheet1 tab and rename to Data. Same for Sheet3 call it Data2.
for Sheet2 rename to Dash Calc.
Select Data tab workbook then go to File, Options, Advanced, (scroll half way down) uncheck - show a zero in cells that have zero value.
Select Data2 tab workbook then go to File, Options, Advanced, (scroll half way down) uncheck - show a zero in cells that have zero value.
Test Data and Data2 workbooks that when a zero is entered in a cell and you click on another cell the zero disappears. Select the Dash Calc tab workbook and enter a zero in a cell and click another and see that the zero is persistent. Save the spreadsheet as Test.xlsx
Open Dashboards (Xcelsius) “new with spreadsheet” and point to the Test.xlsx we just saved. The dashboard embedded spreadsheet should have three tab as we created. These tabs now carry the settings we did offline. Need to expand to another data sheet - just copy the tab in the dashboard.
Give it a whirl… copy an array of data with zeros in cells and paste it directly into the dashboard data tabs - the zeros disappear.
If there are other critical settings you need, do it offline and import prior to dashboard development. You may be able to do this on an existing dashboard. Export the Dashboard XLS, make setting changes, and import the spreadsheet back in. Don’t change any data or your bindings may be lost. Always make a copy before you do this.
This process bypasses the inability to use excel options in the dashboard. Also remember that a lot of stuff is not compatible. So don’t make a Frankenboard! Have fun.
datawizard (BOB member since 2015-01-20)