I have been using Datazen for a while now, struggling with understanding how it internally works, especially with the lack of documentation about it.
Anyway, I have a SSAS tabular model which has a Datetime field in a table marked as Date. I use this tabular model to create reports in excel and it works fine.
Now, I wanted to use this tabular model in Datazen, and I was trying to create a data view that reads the measures and the attributes in MDX
The problem is when I mark the field Creation Date as DateTime in Datazen control panel, it gives me the following message: Failed to refresh in Dashboard
This is the MDX query I'm using:
SELECT NON EMPTY {
[Measures].[Count of Misuse Bugs],
[Measures].[Count of Valid Bugs],
[Measures].[Count of Bugs],
[Measures].[Count of Invalid Bugs],
[Measures].[Time Spent on invalid Bugs],
[Measures].[Time Spent on Valid Bugs],
[Measures].[Invalidity Ratio],
[Measures].[Misuse Ratio]
} ON COLUMNS,
NON EMPTY {
(
[Bugs].[BugID].[BugID].ALLMEMBERS *
[Bugs].[BugTitle].[BugTitle].ALLMEMBERS *
[Bugs].[Client].[Client].ALLMEMBERS *
[Bugs].[Current State].[Current State].ALLMEMBERS *
[Bugs].[Final Resolution].[Final Resolution].ALLMEMBERS *
[Bugs].[Internal Vs. External].[Internal Vs. External].ALLMEMBERS *
[Bugs].[Last Reasonable State].[Last Reasonable State].ALLMEMBERS *
[Bugs].[Owner].[Owner].ALLMEMBERS *
[Bugs].[Owner State].[Owner State].ALLMEMBERS *
[Bugs].[Project].[Project].ALLMEMBERS *
[Bugs].[Release].[Release].ALLMEMBERS *
[Bugs].[Responsibility].[Responsibility].ALLMEMBERS *
[Bugs].[TAR].[TAR].ALLMEMBERS *
[Creation Dates].[Creation Date].[Creation Date].ALLMEMBERS
)
} ON ROWS FROM [Bugs]
I am using SQL Server 2012. Any help would be greatly appreciated. ADDING FIELD AS DATETIME IN DATAZEN DATA VIEW
Datazen is not the most dynamic at understanding how dates are being formatted. Try formating them as yyyy-MM-dd.
Edit: I am creating a new member that is the formatted date. Hopefully this will work for you.
WITH
MEMBER DatazenDate AS
cdate(format([Creation Dates].[Creation Date].CURRENTMEMBER.MEMBER_VALUE, "yyyy-MM-dd"))
SELECT NON EMPTY {
DatazenDate
[Measures].[Count of Misuse Bugs],
[Measures].[Count of Valid Bugs],
[Measures].[Count of Bugs],
[Measures].[Count of Invalid Bugs],
[Measures].[Time Spent on invalid Bugs],
[Measures].[Time Spent on Valid Bugs],
[Measures].[Invalidity Ratio],
[Measures].[Misuse Ratio]
} ON COLUMNS,
NON EMPTY {
(
[Bugs].[BugID].[BugID].ALLMEMBERS *
[Bugs].[BugTitle].[BugTitle].ALLMEMBERS *
[Bugs].[Client].[Client].ALLMEMBERS *
[Bugs].[Current State].[Current State].ALLMEMBERS *
[Bugs].[Final Resolution].[Final Resolution].ALLMEMBERS *
[Bugs].[Internal Vs. External].[Internal Vs. External].ALLMEMBERS *
[Bugs].[Last Reasonable State].[Last Reasonable State].ALLMEMBERS *
[Bugs].[Owner].[Owner].ALLMEMBERS *
[Bugs].[Owner State].[Owner State].ALLMEMBERS *
[Bugs].[Project].[Project].ALLMEMBERS *
[Bugs].[Release].[Release].ALLMEMBERS *
[Bugs].[Responsibility].[Responsibility].ALLMEMBERS *
[Bugs].[TAR].[TAR].ALLMEMBERS *
[Creation Dates].[Creation Date].[Creation Date].ALLMEMBERS
)
} ON ROWS FROM [Bugs]