SSRS HTML

SSRS supports functionality when Report Rendering engine interprets HTML tags. It can be useful when you would like to format textbox within Tablix component. Be aware that support of HTML tags is very restricted. Here https://bit.ly/2KA357l you can find list of supported tags.

GOAL

Make format in Sales Report (you can get from one of previous post SSRS Cascading Parameters ), that members of the first group are formatted with first bold letter.

Let’s change Tablix textbox properties supporting Html tags. Click by left mouse to the Tablix Textbox of English Country Region Name, that field definition is highlighted and by right click you can see menu with Placeholder Properties item.

Placeholder Properties
Picture 1 – Placeholder Properties

By clicking on the item Placeholder Properties window appears, check Html – Interpret HTML tags as styles at the bottom of the window and confirm. Now we set the Textbox to support HTML tags.

HTML settings
Picture 2 – HTML settings

Let’s make requested format now. Go to Tablix component, English Country Region Group textbox field, open expression box and put formatting formula bellow. Use <b> tag to change letter font to bold. LEFT function ensures that bold format will be applied to first letter only. The rest of the text will be finished using MID and LEN function.

Formatting Expression
Picture 3 – Formatting Expression

LEFT function returns defined number of letters of the string from the left. MID function extract substring from defined string starting with defined position and ending with finishing position. For finishing position, we use LEN function returning number of chars from defined string.

See final format of Sales Report,
English Country Region Name column.

Sales Report Tablix
Picture 4 – Sales Report Tablix

SSRS report caching

There are several techniques to optimize report processing. One of the solutions is to enable report caching. When you enable this feature in SSRS report manager (as described below) the data are cached to the Reporting Services Temporary database (see queries below) Reporting services database. There are few possibilities of how to set the caching strategy, from planned approach to the solution with expiration cache. But, be aware that in case the user changes report parameters, the stored cache will not be used and there will be new data cache created. Keep it in mind when you set the caching strategy.

Go to the report manager.

Go to report Properties click on right arrow right to the report name and select properties form menu.

Report Properties
Picture 1 – Report Properties

Go to Processing Options section. And set Cache a temporary copy of the report. Expire copy of report after a number of minutes: and put time you would like to hold the cache in database.

Processing options
Picture 2 – Processing options

When you choose scheduled strategy. Expire copy of report on the following schedule or Render this report from a report snapshot, schedule has to be set. You have to fill the data on picture bellow. Be aware the SQL agent has to be running to use scheduling feature.

SQL agent settings
Picture 3 – SQL agent settings
Schedule settings
Picture 4 – Schedule settings

After setting property for Report Execution Cache new record is added to execution cache. You can see in database that there is time when cache will expire. After this time the record disappear from the database. (not immediately but after few seconds minutes asynchronous mechanism delete it ).

Cache from database
Picture 5 – Cache from database
SELECT * 
FROM ReportServer.dbo.[Catalog] c
JOIN [ReportServerTempDB].dbo.ExecutionCache ec ON ec.ReportID=c.ItemID

Change report parameter.

Change parameter
Picture 6 – Change parameter

You can see that you record what inserted into Report Execution cache.

Execution cache
Picture 7 – Execution cache

SSRS Report PowerShell

I would like to follow my last post regarding PowerShell interaction with Reporting Services. I prepared few posts, which demonstrate PowerShell manipulation with Reporting Services. I will publish them continuously. In near future you can be looking forward to:

  • Download reports
  • Upload reports
  • Copying reports
  • Report serialization/de-serialization to XML
  • Create linking report
  • Create subscription