Power BI – How to convert PBIX file to PBIT file

Overview:
PBIX – PBIX is the file format or file structure of Power BI report. It consists of the following:

      1)    Report Layout/Visualization definition

      2)    Data Model

      3)    Dataset/Queries

      4)    Imported data

 

PBIT – PBIT is the template file of Power BI report. It consists of the following:

     1)    Report Layout/Visualization definition

     2)    Data Model

     3)    Dataset/Queries

PBIT does not contain the data. During deployment from one environment to another. We provide PBIT file only as part of deployment. We can convert the PBIX file to PBIT.

Following are the steps to convert the PBIX file to PBIT

     ·        Open the Power BI Report

     ·        Click on Fileè Save as

     ·        Select “Save as file” as Power BI Template files (.pbit)

     ·        It will save the file in PBIT format

       

Power BI – How to add search text box in the slicer

Overview:
Adding search box in the slicer is very useful in case when we have a very long list of items in the slicer. If we have larger number of items in the slicers then user has to do up / down scroll to find the right value to view the report which is time consuming process. We can add search box in the slicer to find the right value for slicer.

 

Steps to add search box in the slicer:

      1)    Go to the slicer

      2)    Click on … More options è Searchè It will add a search box in the slicer

3)    Now search and select the value

 

 

-------------------------------End of Article-------------------------------

Power BI – How to see tabular data from a chart report

1)      Open the chart report in Power BI app or Power Designer

2)      Click on … More options è show as a table

     

                            

            

Power BI - How to pull the data from Web

1)     Objective

The objective of this article is to provide an easiest way of pulling the data from web that can be utilize as data source for reporting. In this article, we will show you how to pull the data from https://www.espncricinfo.com/ for Cricketing stats e.g. Most double hundreds in a career in test cricket.

 2)     Pre-requisites

a.  Power BI Designer should be installed in your machine

b.  Website where you have access or any publicly access website

c. For this article, we are taking espncricinfo stat page for most double hundreds in a career in test cricket.

https://stats.espncricinfo.com/ci/content/records/230344.html

                   

 3)     Steps to pull the data from web

a.  Open the Power BI Designer

b.  Click on Get dataè Web 

                   

c.  It will ask for the URL from where we need to pull the data

                           

d.  It will establish the connection with web page      

e.  Then Navigator window will ask for which information need to pull from web page. In this example, choose Most double hundreds in a career in test cricket. You can also see the data preview. Click on Transform

 

f.  Here you can see first row is showing column Name. Rename the Column manually with first row of column.

                        

 

g.  Now we will remove the first row. To remove the first row, click on Remove Rowè Remove First Row. It will remove first row. Click on Apply & Close

 

h.  It will load the data into Power BI Model.

                

i.   Now you can see newly created table in Power BI Model.

j.   We can utilize this table for Report development.

4)     Conclusion

By using above steps, we are able to pull the data from publicly access website (www).

rsReportServerDatabaseError - Timeout or low disk condition within the database

Error: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError)"

While configuring Report Snapshot / Caching, we face above mentioned issue because of Role/Permission issue in SSRS. Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database.

RSExecRole should exists in Master, MSDB, ReportServer & ReportServerTempDB. Try the below code, it may solve your issue.

USE master
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO

USE msdb
GO
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO

SSRS - How to run SSRS report based on either of the 2 parameters

We need to write dataset query in IF ELSE manner with Parameter. Below is the example:

Suppose @QueryType is your input parameter:

/* Run first query*/

IF@QueryType = 1

BEGIN

       SELECT t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t3.id

       FROM t1

              JOIN t2

                     ON t1.id = t2.id

              JOIN t3

                     ON t2.wfid = t3.id

       WHERE t1.col1 = @Param

END

/* Run Second query*/

ELSEIF @QueryType = 2

BEGIN

       SELECT t1.col1, t1.col2, t1.col3, t1.col4, t1.col5, t3.id

       FROM t1

              JOIN t2 On t1.id = t2.id

              JOIN t3 On t2.wfid = t3.id

       WHERE t3.Id = @Param

END

SSRS - How to dispaly data from Large Dataset without performance issue

We can enable report caching or Report snapshot if it fits in the requirement. There will be little data latency in Report/Dataset caching.

SSAS - How to change SUM function to AVG function in Grand Total

 Go to the Measure property and change the aggregate function from SUM to AVG.