This type of behavior is common to cubes because of the way cubes are designed.
However, you might try something like this:
- Create a new data source (something like a text file or Excel) with one row of data (doesn't matter what).
- Connect to that data source
- Create the calculated field you mentioned above in the new data source
- in the sheets you want to show/hide, drag and drop the field from the secondary source onto the Filters shelf and filter to the appropriate value.
- You might get a warning message that there are no relationships defined for the blend, but you can safely ignore that in this case, because you just want to use the blend to show/hide the sheet.
Hope that helps!
Thanks for the prompt reply but when I tried creating a excel data source and applying calculated field to the filter(show/hide)
I am getting the error like
sheet is kind of hiding.Please let me know what mistake I am doing here.
one quick question, if I create a sample excel data source and connect it and apply a calculated field to the sheet to get sheet selector and if it works good (like your idea), how does it work with my live cube data, I mean when my dashboard completes and export to the server,my user has the cube data but they don't have excel file. Excel file is my local excel file and its not live right and not stored in my server?
I am a newbie kindly suggest me.
creating a second data source and applying sheet selector is working with the cubes but when I try to create a dashboard, I am losing the cube connection this is happening only while creating a dashboard not while working with the sheets and the error is
This is what happening while creating a dashboard, does anyone know what causing this error ?
Did you get it solved ??
I had a very similar situation and I got it to work.
Let me know if you need help.
Thank You Nikhilan Albert, I got it worked..
Process I did:
step 1-Created a Parameter "select sheets" and used Integer data type and next selected List
and in values typed 1 and Display as sheet 1
value name as 2 and display as sheet 2
value name as 3 and display as sheet 3
step 2-Created a calculated field named as sheet select filter and dragged the parameter [select sheets]
step 3-for sheet 1, I dragged the sheet select filter into filter and selected range as 1-1 and applied show parameter to the view
for sheet 2, I dragged the sheet select filter into filter and selected range as 2-2 and applied show parameter to the view
for sheet 3, I dragged the sheet select filter into filter and selected range as 3-3 and applied show parameter to the view
This is the process I did for sheet swapping with cube data, let me know if have a more simple procedure or different procedure, I would always love to learn something new..
That's nice that you found a way.
I used a much simpler way.
So with cubes, parameters have to be used in certain ways.
1. Created the parameter
2. From an existing filter, In the conditional tab under formula section, called the parameter,
3. Do the same thing for every sheet and it works. Easy :-)