powerbi pro report builder
4 Topics
PowerBI Report- Deleting some code on DAX Query generated from Performance analyzer
Hi I have a DAXquery generated from Performance analyzer. it is restricting the number of records to 501 and inned to removed this and also how could I removed the sub toital and grand total from this script. I tried to delete some code but i'm getting an error. Not able to identify the exact code that i should be removed. Thank you. DEFINE VAR __DS0FilterTable = TREATAS({2021}, 'DateTable'[Received]) VAR __DS0FilterTable2 = TREATAS({"November"}, 'CCST'[MonthName]) VAR __DS0FilterTable3 = TREATAS({"LEGACY"}, 'CCST'[Model]) VAR __DS0FilterTable4 = TREATAS({"DIY"}, 'CCST'[Type]) VAR __DS0FilterTable5 = TREATAS({"Normal"}, 'CCST'[Status]) VAR __DS0FilterTable6 = TREATAS({"OTHERS"}, 'CCST'[Category]) EVALUATE TOPN( 501, SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], "IsGrandTotalRowTotal", ROLLUPGROUP( 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month], 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo] ), "IsDM0Total" ), __DS0FilterTable, __DS0FilterTable2, __DS0FilterTable3, __DS0FilterTable4, __DS0FilterTable5, __DS0FilterTable6, "SumReceived", CALCULATE(SUM('CCST'[Qty])), "Good", 'CCST'[Good], "Good__", 'CCST'[Good %] ), [IsGrandTotalRowTotal], 1, 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], 1, [IsDM0Total], 1, 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo], 1, 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month], 1 ) ORDER BY [IsGrandTotalRowTotal], 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], [IsDM0Total], 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo], 'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month]Solved1.2KViews0likes2CommentsTrying to get a CTE into a PowerBI report and receive an error.
The query works in SSRS. I am able to preview data in PowerBI. However, when I apply on the powerquery editor, I receive "Microsoft SQL: Incorrect sysntax near the keyword 'Declare'. Incorrect syntax near ')'." I have tried adding in ; to a couple of places then removing. I am not very good at powerbi but everything looks good in SQL. What am I missing? USE [DWDataMart2]; DECLARE Anonymous_CancelledId int = 7; DECLARE Anonymous_CompletedId int = 9; -- First CTE WITH WI_RA AS ( SELECT WIActivity.WorkItemDimKey ,WIActivity.[WorkItemContainsActivity_ActivityDimKey] ,CONVERT(int,Review.[DisplayName]) AS 'Order' ,A.[Id] ,A.[Title] ,A_Status.EnumDisplayName AS 'RA Status' ,Review.ReviewerDimKey AS 'Review_ReviewerDimKey' ,Review_AssignedUser.UserDimKey AS 'ReviewerAssigned_UserDimKey' ,Review_VotedByUser.UserDimKey AS 'ReviewerVoted_UserDimKey' ,Review_AssignedUser.DisplayName AS 'Reviewer Assigned' ,Review.Veto AS 'Has Veto' ,Review.MustVote AS 'Must Vote' ,Review_VotedByUser.DisplayName AS 'Reviewer Voted' ,R_Decision.[Decision] ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Review.DecisionDate) AS 'Decision Date' FROM [dbo].[ReviewActivityHasReviewerFactvw] HasReviewer (nolock) JOIN [dbo].[ActivityDimvw] A (nolock) ON A.ActivityDimKey = HasReviewer.ActivityDimKey LEFT JOIN ( SELECT Enum.[ActivityStatusId] AS 'EnumDimKey' ,DS.[DisplayName] AS 'EnumDisplayName' ,Enum.[ParentId] as 'EnumParentDimKey' FROM [dbo].[ActivityStatusvw] Enum (nolock) JOIN [dbo].[DisplayStringDimvw] DS (nolock) ON DS.[BaseManagedEntityId] = Enum.[EnumTypeId] AND DS.[LanguageCode] = 'ENU' ) AS A_Status ON A_Status.[EnumDimKey] = A.[Status_ActivityStatusId] JOIN [dbo].[WorkItemContainsActivityFactvw] WIActivity (nolock) ON WIActivity.WorkItemContainsActivity_ActivityDimKey = A.ActivityDimKey JOIN [dbo].[ReviewerDimvw] Review (nolock) ON HasReviewer.ReviewActivityHasReviewer_ReviewerDimKey = Review.ReviewerDimKey LEFT JOIN ( SELECT reviewUserFact.[ReviewerDimKey], AssignedReviewer.[UserDimKey], AssignedReviewer.[DisplayName] FROM [dbo].[ReviewerIsUserFactvw] reviewUserFact (nolock) JOIN [dbo].[UserDimvw] AssignedReviewer (nolock) ON AssignedReviewer.UserDimKey = reviewUserFact.ReviewerIsUser_UserDimKey ) AS Review_AssignedUser ON Review_AssignedUser.ReviewerDimKey = Review.ReviewerDimKey LEFT JOIN ( SELECT votedByFact.[ReviewerDimKey], VotedBy.[UserDimKey], VotedBy.[DisplayName] FROM [dbo].[ReviewerVotedByUserFactvw] votedByFact (nolock) JOIN [dbo].[UserDimvw] VotedBy (nolock) ON VotedBy.UserDimKey = votedByFact.ReviewerVotedByUser_UserDimKey ) AS Review_VotedByUser ON Review_VotedByUser.ReviewerDimKey = Review.ReviewerDimKey LEFT JOIN ( SELECT R_Decision.[ReviewerDecisionId], R_Decision.[ID] AS 'DecisionEnumName', DS.[DisplayName] AS 'Decision' FROM [dbo].[ReviewerDecisionvw] R_Decision (nolock) JOIN [dbo].[DisplayStringDimvw] DS (nolock) ON DS.[BaseManagedEntityId] = R_Decision.[EnumTypeId] AND DS.LanguageCode = 'ENU' ) AS R_Decision ON R_Decision.[ReviewerDecisionId] = Review.[Decision_ReviewerDecisionId] --Order by not allowed inside a WITH thingy --ORDER BY CONVERT(int,Review.[DisplayName]) ASC ) -- End of First CTE , -- Start 2nd CTE CR_LastFinished AS ( SELECT CR_StatusDur.[ChangeRequestDimKey], MAX(CR_StatusDur.[StartDateTime]) as 'FinishedDate' from [dbo].[ChangeRequestStatusDurationFactvw] CR_StatusDur (nolock) WHERE CR_StatusDur.[ChangeStatusId] IN (@CR_CancelledId, Anonymous_CompletedId) AND CR_StatusDur.[FinishDateTime] IS NULL group by CR_StatusDur.[ChangeRequestDimKey] ) -- End 2nd CTE , -- Start 3rd CTE WIAA_Latest AS ( SELECT WIAA.[WorkItemDimKey], MAX(WIAA.[CreatedDate]) as 'MaxCreatedDate' from [dbo].[WorkItemAssignedToUserFactvw] WIAA (nolock) group by WIAA.WorkItemDimKey ) --End 3rd CTE --Main Query SELECT CR.[Id] ,CR.[Title] ,CR.[Description] ,WI_RA.[Id] AS 'RA Id' ,WI_RA.[Reviewer Assigned] ,WI_RA.[Reviewer Voted] ,WI_RA.[Decision] ,WI_RA.[RA Status] ,[Decision Date] ,AssignedTo.[AssignedToUser] AS 'Assigned To' ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CR.[CreatedDate]), 'MM/dd/yyyy h:mm tt') AS 'Created Date' ,FORMAT(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CR_LastFinished.[FinishedDate]), 'MM/dd/yyyy h:mm tt') AS 'Finished Date' FROM [dbo].[ChangeRequestDimvw] CR (nolock) JOIN [dbo].[WorkItemDimvw] WI (nolock) ON WI.[EntityDimKey] = CR.[EntityDimKey] -- Joining CTE's LEFT JOIN WI_RA ON WI_RA.[WorkItemDimKey] = WI.[WorkItemDimKey] LEFT JOIN CR_LastFinished ON CR_LastFinished.[ChangeRequestDimKey] = CR.[ChangeRequestDimKey] LEFT JOIN WIAA_Latest ON WIAA_Latest.WorkItemDimKey = WI.WorkItemDimKey -- End Joining CTE's LEFT JOIN ( SELECT AssignedToFact.WorkItemDimKey, AssignedToFact.CreatedDate, AssignedToFact.DeletedDate, AssignedToUser.DisplayName AS 'AssignedToUser', AssignedToUser.UserDimKey FROM [dbo].[WorkItemAssignedToUserFactvw] AssignedToFact (nolock) JOIN dbo.UserDimvw (nolock) AS AssignedToUser ON AssignedToUser.UserDimKey = AssignedToFact.WorkItemAssignedToUser_UserDimKey ) AS AssignedTo ON AssignedTo.WorkItemDimKey = WI.WorkItemDimKey AND AssignedTo.CreatedDate = WIAA_Latest.MaxCreatedDate ORDER BY CR.[Id], WI_RA.[Order]Solved7KViews0likes6Comments
what is Power BI Report Builder
Hi, What is the PowerBI Report Builder mentioned in the chart on this page? https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/05/17/a-closer-look-at-power-bi-report-server/ We are looking to add an on-prem PowerBI Report Server through Software Assurance but it also says something about PowerBI Pro Report Builder and I cant find any direct references of that. Is it just an old name for PowerBI PReport Build or is there specific relevance to the Pro bit? Thanks, GrahSolved6.2KViews0likes1Comment