Forum Discussion

lovishsood1's avatar
lovishsood1
Icon for Resolver I rankResolver I
2 years ago
Solved

Not able to save CTE as Table or View

Hello Everyone,   I have created a CTE as follows;   WITH cte as ( SELECT Month_Name, Month_Number, Project_Category, 100.0 * Actual_Hours / NULLIF(SUM(A...
Data Integration
data science
data warehouse
one lake
power bi
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    I think this is now a Power BI issue rather than a Synapse issue. You will be better served posting this logic issue in the Power BI forum

HimanshuS-msft's avatar
HimanshuS-msft
Icon for Microsoft Employee rankMicrosoft Employee
2 years ago

Hello lovishsood1 
When you say "I want to save this SCRIPT as TABLE "  , I am asuming that the intend is just to have these rows on some report , please correct me if I am wrong . I think we can use the view route for this for now.

 

Since you have a CTE which you are running and i suggest you can create a view using the the CTE ( AndyDDC  was also pointing that out ) and the use that view to get the data in the report . I have some dummy data with which i tried and it worked . 

Create TABLE TimelogByType
(Month_Name varchar(100) ,Month_Number int ,Project_Category varchar(100) , Actual_Hours int  )
 
 
INSERT INTO TimelogByType values ('Jan',202301,'AAA',8)
INSERT INTO TimelogByType values ('Jan',202301,'AAA',18)
INSERT INTO TimelogByType values ('Feb',202302,'BBB',118)
INSERT INTO TimelogByType values ('Feb',202302,'CCC',1118)
 
CREATE view dbo.vwGetData
As

WITH cte as
(
SELECT
        Month_Name,
        Month_Number,
        Project_Category,
       
        100.0 * Actual_Hours / NULLIF(SUM(Actual_Hours) OVER (PARTITION BY Month_Name, Month_Number), 0) AS Percentage
    FROM
        [dbo].[TimelogByType]
        )
        select cte.Month_Name,
        cte.Month_Number,
        cte.Project_Category,Sum(cte.Percentage) AS Percentage from cte GROUP by cte.Month_Name,cte.Month_Number,cte.Project_Category;
 
select * from dbo.vwGetData
 

 

 You can also call this a inn powerBI report as this . 

 



 let me know if you have any question .
Thanks 
Himanshu 









 

 

 

 

 

lovishsood1's avatar
lovishsood1
Icon for Resolver I rankResolver I
2 years ago

I think this worked but problem is 

I'm still not able to resolve what I actually I want to achieve.

 

This should be my end output:

But the Problem is My data is coming out of 100% and Above Output is showing DATA according to Total Hours for that month.

 

I hope I'm able to describe my problem statement.

Current Table : 

 

 

Output Table : 

 

I want to achieve like this so that I can create the graph as shown above.

You can ignore the Month Column of Output table as I'm okay with using Month Name & Month Number column as it is.

 

I hope my problem is clear.

 

 

Script I'm using I'm already mentioned above.

  • AndyDDC's avatar
    AndyDDC
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    I think this is now a Power BI issue rather than a Synapse issue. You will be better served posting this logic issue in the Power BI forum