Here’s the thing. In case you don’t use filters in your Tablix, you can easily run a count on the dataset. Like for example:
=IIF(CountRows("Dataset1") > 0, False, True)
But, in my case my Tablix has a couple of filters and this changes things.
In order to count the number of rows which the Tablix is actually using/displaying, you may want to use this expression for the Hide-property:
=IIF(CountRows() > 0, False, True)
Depends on whether your company works on a Saturday, but one of these SSRS Expressions will do the trick:
Basically WEEKDAY() will return the day number of the week, which will return:
– Sunday = 1
– Monday = 2
– etc.
(Doesn’t matter which Language (e.g. en-GB, etc.) you’ve set in the report)
So if you would like to select the previous workday (including Saturdays):
=IIF(WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY())),TODAY()),
DATEADD("d",-1,TODAY())
)
‘In case it’s Monday, select Today’s date minus 2 days. Otherwise return yesterday’
If you would like to exclude the entire weekend:
=IIF(WEEKDAY(TODAY())=1 OR WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY()))-1,TODAY()),
DATEADD("d",-1,TODAY())
)
Here’s a simple trick to filter a dataset in SSRS based on multiple values (which can also be used to limit the number of parameter values).
The formula:
=Split("2013,2014",",")
I’ve created a report that shows a little more information than the SQL Agent Activity Monitor and it can be scheduled via SSRS:
Download:
Click here to download the files:
SSRSSQLAgentJobsReport.zip
Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Schedule Timeline report
Here’s how to accomplish this:
The results:
The code:
WITH MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Dim Period].[Id].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{
[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT ( [Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)
This might seem to be a simple approach, but for some it takes a while to figure out that such tricks are even possible in SSRS.
For example to calculate the difference/variance between Actual and Budget values in a report:
=ReportItems!Textbox50.Value - ReportItems!Textbox171.Value
Simply lookup the textbox’ name in the Properties-window:
This approach also works for ReportItems outside of the table/matrix (across the report even).
In some cases you’ll need to combine two KPI values from different matrixes/tables in the report and for example run another formula to come up with a definite rating/value.
Instead of ‘Fields!‘ use ‘ReportItems!‘ to refer to actual items on the report canvas (this can be on the header, footer or body of the report).
In my experience you may need to use a temporary hidden textbox to store the value temporary in order to use it in the header or footer. So in case you run into some sort of constraint, try this one out first.
Today I just looked at it I guess, SSRS is actually complaining a lot:
‘Dataset: (Warning: Possible performance impact)‘
Every report @parameter that refers to a cube-related dataset somehow warns you every time…but why? Anyone an idea?
Even Google couldn’t give me an answer on that…
The cube’s performance is great, especially when just selecting a subset of values from a very small dimension (< 15 records).
Any idea why SSRS does that??
Leave a comment!
By default, Reporting Server (SSRS) keeps report execution information in the execution log (‘[dbo].[ExecutionLogStorage]‘). After that, you guessed it…it will be deleted. In case you would like to increase the retention period on this table, there’s a server property named “ExecutionLogDaysKept” for Reporting Services. This can help you to increase the retention period, for more information:
http://technet.microsoft.com/en-us/library/bb934303%28v=sql.105%29.aspx
ExecutionLogDaysKept
The number of days to keep report execution information in the execution log. Valid values for this property include 0 through 2,147,483,647. If the value is 0 entries are not deleted from the Execution Log table. The default value is 60.
How to increase the number of days
You can modify the settings by connecting to your SSRS database and executing the SetConfigurationInfo stored procedure:
EXEC SetConfigurationInfo @Name=N'ExecutionLogDaysKept',@Value=N'90'
Or connect to your Reporting Services instance with SSMS and open the Server Properties of SSRS and go to ‘Logging’:
Amit Banerjee blogged a very usefull article about how to analyze the performance and usage of your Reporting Server (SSRS) with Power Pivot and Power View:
http://troubleshootingsql.com/tag/executionlogstorage/
I recently needed to query report execution statistics for a SQL Server Reporting Services instance where the number of rows were quite large. So I decided to use Excel 2013 and PowerPivot to analyze the data. This would allow me to directly pull all of the data into an Excel sheet and then perform analysis on the extracted data without having to query the Report Server database repeatedly.
Leverage PowerView’s interactive capabilities to view the Report execution statistics, the report processed status (failure or success) and the processing, rendering and data retrieval times for each report.
More info: http://troubleshootingsql.com/tag/executionlogstorage/
Drillthrough, Drilldown, Subreports, and Nested Data Regions
You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drilldown action. You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix. You can display the data in a subreport that is completely contained within a main report. Or, you can put the detail data in drillthrough reports, separate reports that are displayed when a user clicks a link.
A. Drillthrough report
B. Subreport
C. Nested data regions
D. Drilldown actionAll of these have commonalities, but they serve different purposes and have different features. Two of them, drillthrough reports and subreports, are actually separate reports. Nesting is a means of putting one data region inside another data region. Drilldown is an action you can apply to any report item to hide and show other report items. They all are ways that you can organize and display data to help your users understand your report better.
Source: http://technet.microsoft.com/en-gb/library/dd207141.aspx
Clint Huijbers' Blog 