Using Power BI and SSRS for visualizing SQL Server and R data (Part 4)

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  January 23, 2017 at 9:27 pm

  #320849

  Comments posted to this topic are about the item Using Power BI and SSRS for visualizing SQL Server and R data (Part 4)

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 3:40 am

  #1927931

  I get this error, when running the R script in powerBI (the first one in your powerbi R visualisation examples). I have installed the ggplot2 package and can run the script in sql server 2016.

  R script error.
  Error in library(ggplot2) : there is no package called 'ggplot2'

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  February 11, 2017 at 3:58 am

  #1927933

  Hi,

  thanks for your reply.

  So you get error with the PowerBi file that is available with the article?
  Which sheet does the problem?

  What you can do immediatelly is to check in power BI  the version or R:
  go to File -> Options and settings -> Options -> R scripting.
  There you will see : Detect  R  home  directories: (mine for example is: C:\Program Files\Microsoft SQL Server\140\R_SERVER\) . In this directory make sure go to folder \Library and look for folder ggplot2. If there is none, you might not have installed package to correct location.

  Second thing you  need to check is in "Services" and make sure that Service Name: "MSSQLLaunchpad"  is up and running!

  Best, Tomaž

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 4:26 am

  #1927934

  Also in SSRS example, following your steps but in the end the report shows nothing, it seems running successfully. I tried preview in ssrs server and bids preview.

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 4:39 am

  #1927935

  tomaz.kastrun - Saturday, February 11, 2017 3:58 AM

  Hi,

  thanks for your reply.

  So you get error with the PowerBi file that is available with the article?
  Which sheet does the problem?

  What you can do immediatelly is to check in power BI  the version or R:
  go to File -> Options and settings -> Options -> R scripting.
  There you will see : Detect  R  home  directories: (mine for example is: C:\Program Files\Microsoft SQL Server\140\R_SERVER\) . In this directory make sure go to folder \Library and look for folder ggplot2. If there is none, you might not have installed package to correct location.

  Second thing you  need to check is in "Services" and make sure that Service Name: "MSSQLLaunchpad"  is up and running!

  Best, Tomaž

  Thanks, now works!  I found many packages are missing from R home ( mine is C:\Program Files\Microsoft SQL Server\130\R_SERVER\library), so I copies all packages from C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library to R home and it works. It seems install.packages (“packageâ€) only install packages to R_SERVICE not R home?

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  February 11, 2017 at 4:44 am

  #1927936

  can you run R code using sp_execute_external_script on your SQL Server from SSMS? Try to run the code there, before going into Power BI and SSRS.

  best,tomaž

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 5:02 am

  #1927937

  tomaz.kastrun - Saturday, February 11, 2017 4:44 AM

  can you run R code using sp_execute_external_script on your SQL Server from SSMS? Try to run the code there, before going into Power BI and SSRS.

  best,tomaž

  yes i can run that in ssms.

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  February 11, 2017 at 5:12 am

  #1927938

  cool.
  and can you also run the Procedure for SSRS and the code for powerBI?
  including the one  where you are referring to the ggplot2 package?

  library(ggplot2)
clusters <- hclust(dist(dataset[,c(1,2,3)]), method = 'average')
clusterCut <- cutree(clusters, 3)
ggplot(dataset, aes(Profit, Quantity, color = dataset$SalesTerritory)) +
geom_point(alpha = 0.4, size = 2.5) + geom_point(col = clusterCut) +
scale_color_manual(values = c('black', 'red', 'green','yellow','blue','lightblue','magenta','brown'))

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 5:54 am

  #1927939

  tomaz.kastrun - Saturday, February 11, 2017 5:12 AM

  cool.
  and can you also run the Procedure for SSRS and the code for powerBI?
  including the one  where you are referring to the ggplot2 package?

  Yes can run the procedure in ssms no problem. The powerbi one now runs fine. Just the SSRS shows empty. ggplot2 package issue is resolved (i copied that and others missing to the r home).

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  February 11, 2017 at 6:36 am

  #1927941

  Very good.
  Have you tried to execute only a simple R script that will return a dataframe / table in SSRS?

  For example create  simple procedure like:
  CREATE PROCEDURE [dbo].[test_SSC]
AS
EXECUTE sp_execute_external_script
     @language = N'R'
    ,@script = N'OutputDataSet <- InputDataSet'
    ,@input_data_1 = N'SELECT TOP 10 number from master..spt_values WHERE number > 0'
    WITH result SETS ((Number INT))

  and use following commmand in SSRS for executing

  -- and execute
EXECUTE dbo.test_SSC

  if procedure executed in SSRS works fine and returns result in table, you will know that there might be a problem with either T-SQL code, R code or displaying graph, but you will know that R will work with SSRS.

  Best, Tomaž

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 11:26 am

  #1927949

  tomaz.kastrun - Saturday, February 11, 2017 6:36 AM

  Very good.
  Have you tried to execute only a simple R script that will return a dataframe / table in SSRS?

  For example create  simple procedure like:
  CREATE PROCEDURE [dbo].[test_SSC]
AS
EXECUTE sp_execute_external_script
     @language = N'R'
    ,@script = N'OutputDataSet <- InputDataSet'
    ,@input_data_1 = N'SELECT TOP 10 number from master..spt_values WHERE number > 0'
    WITH result SETS ((Number INT))

  and use following commmand in SSRS for executing

  -- and execute
EXECUTE dbo.test_SSC

  if procedure executed in SSRS works fine and returns result in table, you will know that there might be a problem with either T-SQL code, R code or displaying graph, but you will know that R will work with SSRS.

  Best, Tomaž

  This works. But the graph is still not working.

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  February 11, 2017 at 11:52 am

  #1927952

  Glad that this is working.

  then just double check all the settings in SSRS, that datasets are correctly set.
  and I strongly suggest that you also execute the  stored procedure in SSMS. Query must  return a  single row of varbinary text.
  execute this stored procedure in SSMS
  
USE WideWorldImportersDW;
GO
EXECUTE ClusterAnalysis_Plot;


  and as a result you should get something like:
  

  if this works, you know that there is a setting that does not work in SSRS. did you try to execute the report in designer mode?

  best, tomaž

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 12:10 pm

  #1927954

  tomaz.kastrun - Saturday, February 11, 2017 11:52 AM

  Glad that this is working.

  then just double check all the settings in SSRS, that datasets are correctly set.
  and I strongly suggest that you also execute the  stored procedure in SSMS. Query must  return a  single row of varbinary text.
  execute this stored procedure in SSMS
  
USE WideWorldImportersDW;
GO
EXECUTE ClusterAnalysis_Plot;


  and as a result you should get something like:
  

  if this works, you know that there is a setting that does not work in SSRS. did you try to execute the report in designer mode?

  best, tomaž

  yeah  i executed the proc successfully in SSMS, it returns the same result as yours. I tried in both designer mode and from ssrs server when deployed.

• nzhang6666

  SSCrazy

  Points: 2217

  More actions

  February 11, 2017 at 12:13 pm

  #1927955

  i do get this warning when running the report, not an error, just a warning, the report still runs.

  Severity    Code    Description    Project    File    Line
  Warning        [rsInvalidExpressionDataType] The Value expression used in textrun ‘plot.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid.        c:\users\nz\documents\visual studio 2015\projects\R_visulization_SSRS\R_visulization_SSRS\Report1.rdl    0

• tomaz.kastrun

  SSCrazy

  Points: 2141

  More actions

  February 12, 2017 at 2:43 am

  #1927983

  Hi,

  I can not reprocduce the error 🙁 Try to handle the datatypes in report. maybe the image is in wrong format. Can you try that.

  best, tomaz

  Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog:  https://tomaztsql.wordpress.com/