I’ve spend a good three days on and off working with the new “Microsoft” DataZen dashboard tool and I think this is going to be significant addition to the enterprise BI offering. It’s not quick and easy to setup the server, configure data sources, data view queries and design dashboards but the end result is worth the investment. This is an impressive tool and users are going to love the dashboard experience.
I’ve updated my original post with my latest progress and I’ll be working on a comprehensive review and eventually an end-to-end walk-through. Stay tuned for more.
![image_thumb[1]](https://i0.wp.com/sqlserverbi.blog/wp-content/uploads/2015/04/image_thumb11.png "image_thumb[1]")
Hi Paul! Great material.
I have this problem: In my “where” section I use date from and date to parameters, which I want to filter with a Navigator.
and (created> = {{@dateFrom}} and created <{{@dateTo}})
If I want to use Group By and Limit the result of this query, it stops working.
The query is written with mysql and I don't have permissions to use stores procedures in the database, so I have to solve it with a query from here.
Thank you very much for your help!
Cristina
I forgot to say that without the parameters this worked ok
And I am using MySql
Thanks for this great post Paul!
Datazen is missing documention 🙁
Any idea on how to use SelectedItems from a multi-value Selection List ?
(for exemple on a SQL Server Data View)
Parameterized query is not working!!!!!!!!!!!!!!!!! Query get Executed properly but with zero result..
if i remove the parameter ,,, i am able to find the aspected result …………
Parameterized query is not working!!!!!!!!!!!!!!!!! Query get Executed properly but with zero result..
if i remove the parameter ,,, i am able to find the aspected result …………
Hi Paul
Have you tried to create a view using MDX and parameters ? I have not been able to find the correct way to do it :o(
I defined a parameter and used it like this {{ @MyParameter }} in my WHERE section but when I check with the profiler it is replaced by @p1 …
Thanks !
Paul
Hi, Paul. Yes. There’s no documentation for this which is why I’m working on some material that should be published in the next few weeks. Here’s an example from a parameterized MDX query I have working in a published dashboard. Keep in mind that a parameterized query runs live and the results don’t get cached like typical non-parameterized data views so they can run considerably slower. It’s best to keep queries simple and result sets as small as possible.
…
WHERE
(
STRTOMEMBER( “[Date].[Year].&[” + ‘{{ @Year }}’ + “]” )
);
Great ! This is exactly what I was looking for !
One thing I noticed is that I had a chart that was linked to a time navigator that used to work fine but since I parameterized the dataview to affect the WHERE, the time navigator selection is not affecting it anymore…
Thanks !
Paul
Hi – I get this to work for single-value parameters, but not sure how to handle multi-select filters.
E.g. I want to allow selection of multiple categories in a filter that then gets passed to the query. I can bind “.SelectedItems” (plural) to the parameter, but I can’t figure out a way to use the comma-separated list in a IN-operator (I am working in T-SQL) – any ideas?
– And I also get the issue with the time navigator not working – necessary to implement as extra parameters (SelectedStart and SelectedEnd) in the parameterized query.
Good to hear, Andrew. Please share your thoughts as you build your solution. I think many are watching with interest as we explore the new product.
Paul,
Thank you it was the Service account that did not have privileges to do much.
We fixes the service to run with appropriate privileges and it worked.
thank you.
Thanks, Andrew. I’d like to know what you experience.
I would first verify that the Datazen services are running under the identity of your service account, make sure you have a SQL Server login created for that account and then check the user mapping for the login to verify that it is in the db_datareader role for the database. The connection should be using Integrated Security. When you say “data connection to my sql”, I assume you mean for your SQL Server instance and not “MySQL”. If this doesn’t work, run a SQL Server Profiler trace and see how the connection is trying to authenticate.
Thank you Paul. I will try that. Yes I’m using SQL 2008 R2.
I will keep you updated on this.
thank you,
Andrew
I’m having problems with the data connection to my sql.
When I test the connection it tell me it fails for the User domain\server$
Any advice to connect correctly?
thank you,
The data source and data view setups are particularly poor. The requirement to know MDX at an extremely advanced level (at least to my skill set) was disappointing to say the least. Hoping that Microsoft fast tracks this to their programmers and polishes up these backend issues since the visualization part is nearly perfect.