SSRS: Download all report RDL files with folder specific from Report Server in single shot

SSRS Report Server does not provides visual option to download all reports at single shot. Report Manager does not support downloading all the report files (.rdl files)  at single shot.That is why we have to face crucial situation while migrate report server to higher version. 

They provided solution for that. By the help of Bulk Copy (BCP) we can able to achieve it.
For that we have to write few lines of SQL code.

Today we will achieve the same by BCP option.

To do that first we have to know some basic about report database. When we configure Reporting Services configuration manager then we have to create report database. This report database contain whole information about reports. If we connect this report server database to SSMS, we can see various tables inside it.

Here one of the most important table is dbo.Catalog. This table contain report information and report XML body. Below picture showing few column of dbo.Catalog.

Type is the one column of  dbo.Catalog Table. Here we can see some number, each number has a specific indication. 

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part
8 = Shared 

To get all the report we have to pass type as 2.

Below is the SQL scripts to download all report RDL files

DECLARE @ReportDetails TABLE

(

  Report_No int,

  ItemId uniqueidentifier,

  Report_name nvarchar(425),

  Report_Path nvarchar(425),

  Folder_Path nvarchar(425)

)

DECLARE @intFlag INT=1  /*To start with 1 in while loop*/

DECLARE @MaxReportCount INT

Insert into @ReportDetails(Report_No,ItemId,Report_name,Report_Path,Folder_Path)

select ROW_NUMBER() over (order by ItemId) as Report_No,ItemId,Name,[Path],replace(SUBSTRING(PATH,1,LEN(PATH)-(LEN(Name)+1)),'/','\') as Folder_Path

from [dbo].[Catalog]

   where [Type]=2 /*Load all the reports (Type=2 indicates) from catalog table to table variable*/

select @MaxReportCount = MAX(Report_No) from @ReportDetails /*Get max count to pass as highest value in while loop*/

WHILE (@intFlag <= @MaxReportCount)  /*Declare loop to generate rdl file of all reports one by one*/

BEGIN

     Declare @FolderContainer AS NVARCHAR(200)

     declare @cmd as varchar(MAX) /*Hold xml file for report*/

     declare @Reportqueryout as varchar (240) /*Hold report output path*/

     declare @DynamicQuery AS NVARCHAR(MAX) /*Hold xp_cmdshell string for           generating RDL file*/

     Select @FolderContainer='D:\ReportDetails'+Folder_Path from @ReportDetails where Report_No=@intFlag

     Select @cmd =  CONVERT(VARCHAR(MAX),  

           CASE       

             WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''')        

             ELSE C.Content        END) 

     FROM  [ReportServerPDReport].[dbo].[Catalog] CL

       CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C  WHERE  CL.ItemID =

               (Select ItemId from @ReportDetails

     where Report_No=@intFlag)

     select * into ##Temp from (select @cmd temp) a /*store XML file to temp table*/

       ------------------------------------

      

        EXEC master.dbo.xp_create_subdir @FolderContainer

   

       ------------------------------------

        select @Reportqueryout='"'+@FolderContainer+'\'+Report_name+'.rdl"'

     from @ReportDetails

     where  Report_No=@intFlag /*Generate output path */

     select @DynamicQuery='EXEC master..xp_cmdshell ''bcp " '

                           + 'Select Temp from ##Temp" QUERYOUT '

                           + @Reportqueryout

                           + ' -c -S Localhost\reportinstance  -T''' /* Here Localhost\reportinstance  is the SQL server name with instance*/

     EXEC SP_EXECUTESQL @DynamicQuery

           

     Drop table ##Temp

     SET @intFlag = @intFlag + 1 /*By increasing this go to or fetch next report*/

END

Steps to understand this

1. Open ReportServer database in SSMS (SQL Server management Studio).
2. Select all data from dbo.catalog table where type=2 (2 indicates reports only)
3. Create table variable named @ReportDetails to store few columns form dbo.catalog              table.
4. Create 2 more variable named @intFlag and @MaxReportCount, next assign values.
5. Run while loop from 1 to @MaxReportCount, and convert content to XML file.
6. Push the XML file to ##Temp table.
7. Give path for export RDL file.
8. Create proper syntax of xp_cmdshell and store it into @DynamicQuery variable.
9. Now execute the @DynamicQuery variable.


In case of more than 500 reports in report server, we can use one more way to avoid System.OutOfMemoryException" exception

DECLARE @intFlag INT=1  /*To start with 1 in while loop*/

DECLARE @MaxReportCount INT

Select ROW_NUMBER() over (order by ItemId) as Report_No,ItemId,Name as Report_name,[Path] as Report_Path,replace(SUBSTRING(PATH,1,LEN(PATH)-(LEN(Name)+1)),'/','\') as Folder_Path

       into #ReportDetails

       from [dbo].[Catalog]

       where [Type]=2 /*Load all the reports (Type=2 indicates) from catalog table to table variable*/

select @MaxReportCount = MAX(Report_No) from #ReportDetails /*Get max count to pass as highest value in while loop*/

     Declare @FolderContainer AS NVARCHAR(200)

     declare @cmd as varchar(MAX) /*Hold xml file for report*/

     declare @Reportqueryout as varchar (240) /*Hold report output path*/

     declare @DynamicQuery AS NVARCHAR(MAX) /*Hold xp_cmdshell string for generating RDL file*/

WHILE (@intFlag <= @MaxReportCount)  /*Declare loop to generate rdl file of all reports one by one*/

BEGIN

    

     Select @FolderContainer='D:\ReportDetails'+Folder_Path from #ReportDetails where Report_No=@intFlag

     Select @cmd =  CONVERT(VARCHAR(MAX),  

           CASE       

             WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''')        

             ELSE C.Content        END) 

     FROM  [ReportServerPDReport].[dbo].[Catalog] CL

       CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C  WHERE  CL.ItemID =

               (Select ItemId from #ReportDetails

     where Report_No=@intFlag)

     select * into #Temp from (select @cmd temp) a /*store XML file to temp table*/

       ------------------------------------

      

        EXEC master.dbo.xp_create_subdir @FolderContainer

   

       ------------------------------------

        select @Reportqueryout= '"'+@FolderContainer+'\'+Report_name+'.rdl"'

     from #ReportDetails

     where  Report_No=@intFlag /*Generate output path */

     select @DynamicQuery='EXEC master..xp_cmdshell ''bcp " '

                           + 'Select Temp from #Temp" QUERYOUT '

                           + @Reportqueryout

                           + ' -c -S Localhost\reportinstance  -T''' /* Here Localhost\reportinstance  is the SQL server name with instance*/

     EXEC SP_EXECUTESQL @DynamicQuery

           

     Drop table #Temp

        set @FolderContainer=''

        set @cmd=''

        set @Reportqueryout=''

        set @DynamicQuery=''

     SET @intFlag = @intFlag + 1 /*By increasing this go to or fetch next report*/

      

END

SSAS Tabular Part 11 : Apply Dynamic row level security based on windows user

Dynamic security provides row-level security based on the windows user name or login id of the user currently logged on.

To implement dynamic security, we must add a table to our model containing the Windows user and different dimension table primary key.

For example, suppose we have a dimension table named Country. This table contain 2 columns named Country_Id and Country_Name.

To implement dynamic security, we create a table named UserPermission. This table contain 2 columns named User_Name and Dim_Id.

Here we can see 3 record exists in Country table. Next table UserPermission  contain 3 different windows username with Dim_Id. Here it means that NT-RPS\Icchasoo user has permission for country (India, United Kingdom), NT-RPS\UtkarshMis user has permission for country (India) and NT-RPS\AnimeshTha user has permission for country (United States)

Steps to do:

1. Create a tabular Project by SQL Server Data Tools.
2. Create a database connection.
3. Now click Select from a list of tables and views to choose the data to import selected, and then click Next.
4. On the Select Tables and Views page, select 2 table Country and UserPermission and click Finish. (Now we can see both table inside Grid view.
5. Go to Model and click on Roles.
6. Create a new role and give name as PermittedUserRole
7. Under Row filter TAB we can see both Country and UserPermission table. Now we have to write DAX query in DAX Filter 

           Country             --> =CONTAINS(User_Permission,User_ Permission                                                                                                                                  [User_Name],USERNAME(),User_ Permission [Dim_Id],

                                                                     Dim_Country[Country_Id

           
           UserPermission --> =FALSE()

    (We apply =FALSE() because UserPermission's data  should not show to any user)

8. Click on Members TAB and import all the username which exists in our User_                        Permission table.

  9. Deploy this solution.

10. Open excel and connect the particular SSAS Tabular Database with NT-RPS\Icchasoo          username and password.

11. We will see the permitted Country for the NT-RPS\Icchasoo user.

SSAS Tabular Part 10 : Create Partitions in Table

We apply partition to table for easy management and faster access data and performance. Partitions, in tabular models, divide a table into various logical partition of objects. Each partition can then be processed independent of other partitions and because of that the performance gets increase. 

As per my view, it is very different from how partitions are implemented and utilized for deployed models.

To know more on this click here.

Scenario:

For example, a table certain 100000 rows that contain 16 years of record(2000 to 2016). In this table some of the record changes rarely (for years 2000 to 2015), but other row sets have data that changes often(for years 2015 to 2016). In these cases, there is no need to process all of the data when you really just want to process a portion of the data. Partitions enable us to divide portions of data we need to process frequently from the data that can be processed less frequently.

In this scenario we can divide it into 2 partition. First Partition contain 2000 to 2015 years data (rarely changes data) and other contain only 2016 year data (Frequently changes data).

Steps to create table Partition

For example, I am taking Fact Internet Sales table and going to create a partition on it.

Total number of Rows present in Internet Sales table : 60398

Years present in Internet Sales table: 2005, 2006, 2007 and 2008

Now we are going to create 2 partition on Internet Sales table.

•       Internet Sales 2005to2007 --> 2005, 2006 and 2007 years data (28133 Rows)
•       Internet Sales 2008             --> 2008 data (32265 Rows)

1. In the model designer, click on the Fact Internet Sales table, then click on the Table menu, and then click Partitions.
   
2. In the Partition Manager dialog box, in the partitions list, click the Fact Internet Sales partition.
3. In Partition Name, change the name to Internet Sales 2005to2007.
4. Select the Query Editor button just above the right side of the preview window.
5. In the SQL Statement field, we got full select statement. Here we have to add WHERE statement or we can remove entire query and paste the query given below. After that Click Validate.

               Select * from [dbo].[FactInternetSales] where (([OrderDate] >= N'2005-01-01 00:00:00')

               AND ([OrderDate] < N'2008-01-01 00:00:00'))

      6.  Internet Sales 2005to2007 partition has created. For 2^nd partition click on New                    button.

7. In Partition Name, change the name to Internet Sales 2008.
8. Select the Query Editor button just above the right side of the preview window.
9. In the SQL Statement field, we got full select statement. Here we have to add WHERE statement or we can remove entire query and paste the query given below. After that Click Validate.

                Select * from [dbo].[FactInternetSales] where (([OrderDate] >= N'2008-01-01 00:00:00'))

      10. In the Partition Manager dialog box, notice the asterisk (*) next to the partition            names for each of the new partitions we just created. This indicates that the partition has not been processed (refreshed). So, we have to process the created partition now. Click the Model menu, then point to Process (Refresh), and then click Process Partitions. Select 2 partitions and click on Process.

Output:

In the model designer, click on the Fact Internet Sales table, then click on the Table menu, and then click Partitions. Now you can see 2 partitions without  asterisk (*) for Fact Internet Sales.

SSAS Tabular Part 9 : Create Hierarchies

Hierarchies are groups of columns in a table and arranged based on level and its sub levels. For example we can say Year-->Samester-->Quarter etc.

To create hierarchies, we will use the model designer in Diagram View.

Steps to create Hierarchies

For example, I am taking DATE  table and going to create a Hierarchies on it.

1. In the model designer, click on the Model menu, then point to Model View, and then click Diagram View.

2. Right-click the Date table, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.
3. In the hierarchy name, rename the hierarchy by typing Datetree, and then press ENTER.
4. In the Date table, click the CalendarYear column, then drag it to the Datetree hierarchy, releasing it on top of it.
5. In the Date table, click and drag the CalendarSemester column to the Datetree hierarchy.
6. In the Date table, click and drag the CalendarQuarter column to the Datetree hierarchy.
7. Datetree hierarchy has created.

Output:

Now we have to see the outcome of Datetree hierarchy.

1        1. To see it’s activity click the Model menu, and then click Analyze in Excel.

2. In Excel, in the PivotTable Field List, notice the Datetree field in  Date Table as well as Geography, Product, Product Category, Product Subcategory, and Internet Sales tables with all of their respective columns appear.

3. Go to Date table and select Datetree field, go to Product Table and select EnglishProductName and finally go to Measure and select TotalOrderQuantity.

4. Now we can see first column appear as Drilldown. By Clicking “+” symbol we can dill the data up to granular level. (Year --> Semester --> Quarter)

SSAS Tabular Part 8 : Create KPI (Key Performance Indicators)

Through KPI we can quickly understand a business. It indicates states of business modules success by some graphical indicator . KPIs are used to gauge performance of a value, defined by a Base measure, against a Target value.

We create KPI in top of the measure. To create KPI, we will use the Measure Grid. By default, each table has an empty measure grid.

Steps to create KPI

For example, I am taking Fact Internet Sales  table and going to create a KPI on it.

1. In the model designer, click the Internet Sales table (tab).

2. Click on the Measure Grid and select a cell.

3. In the formula bar, type the following formula and Press ENTER:     TotalOrderQuantity:=SUM([OrderQuantity]) (This measure will serve as the Base  measure for the KPI.)

4. Right-click the TotalOrderQuantity measure, and then click Create KPI.

5. In the Key Performance Indicator (KPI) dialog box, in Target, select the Absolute Value         option.

6. In the Absolute Value field, type 4300, and then press ENTER. (We set this value is the         target value)

7. In the left (low) slider field, type 1720, and then in the right (high) slider field, type 3440.

8. In Select Icon Style, select the first icon type (Circel-Red, Circel-Yellow, Circel-      Green).

9. Click OK.

Output:

1. To see it’s activity click the Model menu, and then click Analyze in Excel. 

2. In Excel, in the PivotTable Field List, notice the KPI’s and  Internet Sales measure    groups appear, as well as the Customer, Date, Geography, Product, Product Category,  Product Subcategory, and Internet Sales tables with all of their respective columns appear.

3. Now go to Product table and click on respective column EnglishColumnName. After that go to KPI’s and expand TotalOrderQuantity and click on Value and Status.

4. Now we can see the try color circle indicator (Red, Yellow, Green) for all Product Name and TotalOrderQuantity based on their value.

Apart from that we can define Target value as some measure name also. In this case STATUS THRESHOLD should be Percentage wise calculation. 
For example Assume TARGET Measure value is 100. Then we have to declare In the left (low) slider field, type 30% (Means 30% of 100), and then in the right (high) slider field, type 70% (Means 70% of 100).

SSAS Tabular Part 7 : Create Measures

A measure is a calculation of column values. We can create it using a DAX formula. One thing we have to remember is ‘It is not like column in a table’. Most of the cases it return only single line. For example SUM of a column, Count of rows for a particular column applying filter (Monthly Total, Quarterly Total, Average, Maximum, Minimum etc).

To create measures, we will use the Measure Grid. By default, each table has an empty measure grid. The Measure Grid appears below a table in the model designer when in Data View.

Apart from this, we can click the Table menu, and then click Show Measure Grid.

Steps to create measures

For Example, I am taking Fact Internet Sales  table and going to create a measure on it.

1. In the model designer, click the Internet Sales table (tab).
2. Click on the Sales Order Number column heading.
3. On the toolbar, click the down-arrow next to the AutoSum (∑) button, and then select DistinctCount.
4. In the measure grid, click the new measure, and then in the Properties window, in Measure Name, rename the measure to Internet Distinct Count Sales Order.

Here we see the simple logic for this Measure. We can write some DAX expression also.

Like this we are going to create one more measure for 'Order Quantity' column using another method.

1. In the model designer, click the Internet Sales table (tab).
2. Click on the Measure Grid and select a cell.
3. In the formula bar, type the following formula: TotalOrderQuantity:=SUM([OrderQuantity]) (Here 'TotalOrderQuantity' is the Measure name followed by ':' and then we written DAX formula for aggregation)