Querying SSRS Report Definition Using T-SQL

Do you want to have all reports that used a table in their report definition?

Are you looking for a report that has a desired parameter name?

Have you written a new version of a SQL view or stored procedure and you need to modify all the reports working on top of the version of the object, but, you don’t know what those reports are?

Have you modified an SSAS object and you need to know which reports might be affected?

If you have any of the above questions or in general you need to retrieve all SSRS reports which have a specific string in their report definition, just connect to the SQL Server instance which holds your   REPORTSERVER database through SSMS and simply execute the SQL scripts below:

SELECT C.NAME

       , CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) AS REPORTXML

FROM  REPORTSERVER.DBO.CATALOG C

WHERE  C.CONTENT IS NOT NULL

            AND  C.TYPE = 2

          –AND  C.NAME LIKE ‘%REPORT_NAME%’

     AND CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.CONTENT))) LIKE ‘%DESIRED_STRING%’

Enjoy!

Hold Your Dashboards in Your Pocket, Part 1: Use Your Predefined Dashboards on Your IOS Devices

Now it is time to take a step further and learn how to access our dashboards from our IOS or Windows devices. Microsoft designed a very good and handy app for IOS and Windows based tablets. At the moment the Windows app is only available for your laptop or on your Windows based tablet device. First of all you need to download the app on your device.

In this post I explain how to use your IOS devices to browse your dashboards everywhere that you have access to the Internet.

iPhone:

  • Sign-in into Microsoft Power BI website
  • From the right menu click on Download then click on “Power BI for IOS”

  • Click “Download on the App Store”

  • Select your IOS device and then click on “View in iTunes”

Continue reading “Hold Your Dashboards in Your Pocket, Part 1: Use Your Predefined Dashboards on Your IOS Devices”

Build You First Dashboard in Microsoft Power BI Designer, Access Analytics Everywhere

This is the third article from Power BI Designer series. To fully understand this article you need to read my previous posts “Build Your First Report in Microsoft Power BI Designer Part 1, Basics” and “Build Your First Report in Microsoft Power Bi Designer Part 2, Make it More User Friendly” as well. In this article I explain how to publish your predefined reports on www.powerbi.com website which is free. So after publishing the reports, you can create flashy reports very easily. By very easily, I mean it! Creating dashboards is even easier than dragging report objects and dropping them somewhere on the tool! I’ll explain how that is possible. Actually, it is all about the awesomeness of the online BI Designer.

Frist of all, you need to create an account in www.powerbi.com. Unfortunately, you’ll need to have a corporate email address that means you’re NOT allowed to use free email accounts like MSN, Hotmail, Yahoo, and Gmail and so on. But, if you’re a student with a valid university email address or if you’re an employee with a corporate email address, then you’ll be fine.

Let’s start.

  • Browse www.powerbi.com
  • Enter your valid email corporate or university email address then click on “Use it Free”

Continue reading “Build You First Dashboard in Microsoft Power BI Designer, Access Analytics Everywhere”

Batch Index Rebuild without Using Cursor

Today I came across a cube processing performance issue with one of our clients. So I started a step-by-step troubleshooting including optimising named queries. In some cases the named queries were actually querying some SQL views from the source data warehouse.

After all, I created about 35 new indexes and I needed to justify that all of those indexes are really used. As I processed the faulty cube several times during my step-by-step troubleshooting process it seemed all of those indexes were used.

But, I knew that I created some indexes that covered by some of the new ones and those indexes won’t be used.

I needed to rebuild all the indexes, however, rebuilding all of those indexes from SSMS UI would be such a pain. So I needed to do a batch index rebuild.

So I googled and I’ve found some scripts which actually are doing the job, but, all of them were using cursors. Sadly, I hate cursors so they are the last item in my book. Indeed, I’ll never use cursors until it’s absolutely necessary and there is no other better choices.

Therefore, I decided to do it in my way and I wrote the following script. I thought I’d be happy to share it with you guys as it might help some of you as well.

declare @ix varchar(max), @tbl varchar(max), @counter int, @CustomIx Varchar(max)

declare @table table (id int, tbl varchar(max), ix varchar(max))

set @CustomIx = ‘YOUR_INDEX_NAME_STARS_WITH’ –Custom index name will be like MY_IX_***

insert into @table (id, tbl, ix)

SELECT   ROW_NUMBER() over (order by ix.[NAME]) id

           , OBJECT_NAME(ixstat.[OBJECT_ID]) AS [OBJECT NAME]

         , ix.[NAME] AS [INDEX NAME]

FROM     SYS.DM_DB_INDEX_USAGE_STATS AS ixstat

         INNER JOIN SYS.INDEXES AS ix

           ON ix.[OBJECT_ID] = ixstat.[OBJECT_ID]

              AND ix.INDEX_ID = ixstat.INDEX_ID

WHERE    OBJECTPROPERTY(ixstat.[OBJECT_ID],‘IsUserTable’) = 1

          and  ix.[NAME] like @CustomIx+‘%’

 

set @counter= (select max(id) from @table)

 

while @counter >=1

begin

    set @ix = (select ix from @table where id = @counter)

    set @tbl = (select tbl from @table where id = @counter)

    exec(‘ALTER INDEX ‘+@ix+‘ ON [dbo].[‘+@tbl+‘] REBUILD PARTITION = ALL ‘)

    print @tbl + ‘.’ +  @ix + ‘ Rebuild successful’

    set @counter-=1

end

Continue reading “Batch Index Rebuild without Using Cursor”