Cube views are opened in excel all the time and there should be no issues with that and using parameters. The issue is isolated to spreadsheets where cube views are opened, that spreadsheet is saved then embedded in a dashboard. Parameters can drive it, just need to make some combo boxes, bind them to the combo boxes as bound params and then specify each param to refresh your dashboard. those selections will refresh your content.
Just to be clear - i can create a spreadsheet with a cube view with parameters in a spreadsheet, attach it to a spreadsheet component and run it fine. it is when i create a spreadsheet with a cube view with parameters then suppress those parameters using page 147 instructions and embed that into a dashboard is when i have the issue
Oh, I totally get the CV in ""real"" Excel bit -- it works just fine with the parameters and that was the path: Excel -> Spreadsheet object -> spreadsheet as dashboard.
When you say comboboxes, I do have those and they’re used for the CV-in-Data Explorer which is what I tried to put into the spreadsheet.
Ah, I think I get it finally: if the dashboard with a spreadsheet content component is just run as it is, the default dimension/parameter dialog box is what causes the error. Run it within a normal dashboard framework where comboboxes are driven by bound parameters that in turn drive the spreadsheet and it’ll work.
I’ll try it.
Actually, just tried it. IT WORKS! Thank you ever so much. Seriously, I was dreading this not working as the requirements for the report are way out there and can only really be handled using XFGetCell. Now I can combine the dimension selections from a simple CV, create them as named ranges, put them into XFGetCell formulas and off to the races.
One thing that stands out is that the behavior is a bit different than that of a typical CV as it requires a refresh on combobox change. On reflection that’s in line with how Excel works and the users can certainly live with that.
P.S. A question though: what is a spreadsheet component vs. a content component? I created a content component of type spreadsheet and then uploaded the Excel file as a maintenance unit file. Is there another way to do it?
Just making sure but if you have to manually refresh after a combo box change then we want to fix that! If so go into one of your combo boxes did you specify a dashboard action of “refresh” then in the immediate row below specify the dashboard to refresh it automatically for you?
Probably a better explanation in the ref guide but a spreadsheet component only excepts spreadsheet saved files and has specific options to remove the excel ribbon and things like that.
A content component is used when you need to create a report from a data set sourced in a data adapter. A data adapter could be one of a 3 sources; a cube view, custom sql query (against OS or an external source), or a method query. A data adapter is then attached to a content component which doesn’t format the data set. You’d have to go out to studio at that point.
I do have that -- I think this is because I'm using XFGetCells in the sheet. I use a hidden (it will be, it's visible right now) CV to drive parameters which seems to change on combobox select. The XFGetFormulas look at that CV to get the entity, UD1, UD3, Scenario as named ranges which then drive what gets retrieved. I don't think there's a way round that because it really is a change to the content of the formula.
