Execute SSRS Subscription on Demand

There could be many ways to fire SSRS subscription but here i will explain how to fire SSRS subscription on demand using RS.EXE utility.

First step is to create your report Subscription through Report Manager using option "New Subscription" or "New Data-driven Subscription" by navigating <Your Report> => Properties => Subscription . Inside your create Subscription, there will be "Select Schedule" button. Click on this button and choose "Once" as option and give any past time under "Start Time" and complete required details for your subscription and click "Ok" button to create your report subscription.

Second step is to create a .RSS file that will be used to trigger newly created subscription in first step. Open Notepad and put below script and save this Notepad file as "..\<Your Folder Path>\Subscription.rss". You can choose any file name with extension .rss

you can put multiple rs.FireEvent statement inside Sub Main block to fire multiple report's subscriptions at the same time.

Sub Main() 

rs.FireEvent("TimedSubscription", "<SubscriptionID>") 

End Sub

Now, we need to get SubscriptionID of newly created subscription in our first step to use it above block (highlighted as yellow). For that, you need to login to Sql Server Management Studio and execute below query on your ReportServer Database.

select s.SubscriptionID,sD.name subscriptionname,c.Name as reportname from Subscriptions s
inner join ReportSchedule RS on RS.SubscriptionID=s.SubscriptionID
inner join Catalog c on c.ItemID=RS.ReportID
inner join Schedule SD on SD.ScheduleID=RS.ScheduleID
where c.Name='<Your Report Name>'

<Your Report Name> will be name your report for that you have created subscription in first step.

Third step to execute .rss file that we created in second step. Again open a new notepad and put below code and save your file as "..\<Your Folder Path>\RunSubscription.bat"

<Full Path of RS.EXE file>\rs.exe -i "..\<Your Folder Path>\Subscription.rss" -s http://<Your Report Server instance name>/reportserver

We are done. you can run your RunSubscription.bat by double click on .bat file.

Last ran Query in Sql Navigator

There are two ways to find past ran queries in Sql Navigator:

1) Click (see image-1) on SQL History or press (CTRL+ALT+R) 

 

Image-1

2)Find the path …\SQL Navigator 6.2.1\Unified Editor\history in your system.

SharePoint Items Security using SharePoint Powershell

We can use following SharePoint PowerShell script to pull all SharePoint items security whether an item is using unique permission or inherited permission. The output will be written in a .csv file “SharepointSitesOutput.csv”

Just copy and paste following code in .ps1 file and execute that file on SharePoint PowerShell commad using commad &<filename.ps1>

 

get-spsite -Limit All|get-spweb -Limit All|Select URL,Title, hasUniquePerm |Export-csv SharepointSitesOutput.csv –NoTypeInformation

List SSRS items Permissions using PoweShell

If we pull SSRS Items security using ReportServer database using following query then we get stale information. It includes those users as well that has been deleted/deactivated in Active directory.

select C.UserName, D.RoleName, D.Description, E.Path, E.Name

from dbo.PolicyUserRole A

   inner join dbo.Policies B on A.PolicyID = B.PolicyID

   inner join dbo.Users C on A.UserID = C.UserID

   inner join dbo.Roles D on A.RoleID = D.RoleID

   inner join dbo.Catalog E on A.PolicyID = E.PolicyID

order by C.UserName

So instead of using query at ReportServer Database, we can use reportservice2005.asmx GetPolicies method. Following is the Powershell Script that writes the SSRS Folders permissions in SSRSSecurityOutput.csv. Just copy and paste the following code in a .ps1 file like SSRSPermissions.ps1.

.Ps1 is file extension for poweshell script.

$ReportServerUri = 'http://<ReportServer>/ReportServer/ReportService2005.asmx'

$InheritParent = $true

$SourceFolderPath = '/'

$outSSRSSecurity=@()

$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential

$items = $Proxy.ListChildren($sourceFolderPath, $true)|Select-Object Type, Path, Name|Where-Object {$_.type -eq "Folder"};

foreach($item in $items)

{

Add-Member -InputObject $item -MemberType NoteProperty -Name UserName -Value '';

foreach($policy in $Proxy.GetPolicies($item.path, [ref]$InheritParent))

{

                $objtemp=$item.PsObject.Copy();

                $objtemp.UserName=$policy.GroupUserName;

                $outSSRSSecurity += $objtemp;

                $objtemp.reset;

}

}

$outSSRSSecurity|Export-csv SSRSSecurityOutput.csv -NoTypeInformation;

Create SSRS Report using SSAS as a Data source

I am assuming that you are familiar with creating SSRS report using T-SQL as Data source. In order to create a report using SSAS as a data source you have to perform the following steps.

     1.  Create a Data source using SSAS  

     2.  Just follow the same steps as you use to create the TSQL DataSource , But while selecting source select  “Microsoft SQL Analysis Services “ See image below .Rest of the steps are same as you use to perform in T-SQL report DataSource

Now just go to the report and create a Dataset same you use to create in T-SQL report

In function window paste your query like below image

SELECT                {

                                            [Measures].[Internet Order Quantity],

                                            [Measures].[Internet Tax Amount]

                                } on COLUMNS ,

NON EMPTY ([Product].[Product].[Product] ) on 1 from

[Adventure Works]

Now Click on “Field” TAB check your filed data source see below image

At the above stage, you need to manually change the Field Names and give appropriate names. So that it would be easy for us to use these names at report coding. Refer following details.

 Copy Field source XMLA and paste it somewhere like below

<?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product].[Product]" />

See unique name according to your unique name provide the name of your field see image above

Note: If type =”level” it means this your dimension/level field and if it is type =”Measure" then it is Measure

After performing all the steps your dataset is ready with fields 

Now you can Preview report.

Display ALL when (Select All) in Multi-value parameter selection

Let’s say we are using a Multi-value parameter and requirement is to show selected values in report output. Then simply we can use following expression

=Join(Parameters!YourMultivalueParameterName.value,”,”)

But if list of available values are too big and requirement is to show ALL in report output instead of huge list in case of (Select All) 

 then we can use following expression

=IIF(Parameters! YourMultivalueParameterName.Count=Count(Fields!DataSetFieldName.Value, "Name OF Your DataSet"),"ALL",Join(Parameters! YourMultivalueParameterName.Value,","))

Errors in OLAP Storage Engine “The Attribute Key cannot be found when Processing”

1. If you are facing this error, Try first to Full Process all your Dimensions and then reprocess your cube.

2. When the Key value is present in the fact table but not present in the Dimension table (The keys on which Dim and Fact tables are linked See key relation in Dimension Usage tab.)

Dim Table				Fact Table
ProductKey	Product Name	Product Status		ProductKey	Sale Price	Qty.
-1	Unknown	unknown		1	200	20
1	Shirt	A		2	300	15
2	cap	A		3	20	100
3	Pen	A		4	20	100
				5	10	15

Workaround: 

• If you face this issue select distinct key values from Dim table and Select distinct Key values from fact table , compare and check which key value that is present in Fact table and not present in Dim table . Investigate and plan your next course of action.
• If above point is true in your scenarios, you can insert missing Key value in Dim table that is present in fact table but not present in Dim table.
• You can restrict Fact table records for the key value that is not present in the dim table.

(This is just an example you have to plan your course of action what to do according your requirement. But I would like to suggest you investigate in your fact table and dim table populating logic why this key value is present in fact table and not present in Dim table.) 

3. When there is a null value present in your fact table key column value.

Some times while populating the fact table from different sources, we use many joins (left, right etc). Due to lack of data in any source tables, Key value can be null in fact table. Then you can face this issue.

Dim Table				Fact Table
ProductKey	Product Name	Product Status		ProductKey	Sale Price	Qty.
-1	Unknown	unknown		1	200	20
1	Shirt	A		2	300	15
2	cap	A		3	20	100
3	Pen	A		null	10	15

See above fact table, there is null value in Key column  and there is no matching Key value is in Dim table .In this Case, you might experience error “Errors in OLAP Storage Engine The attribute key cannot be found when processing.”

Workaround:

      While designing the dim table you should insert one record with -1 for key value.

Let say you have product table for dimension with columns [Product ID], [Product Name],[Product status]  you should populate it as

Product  ID	Product  Name	Product Status
-1	unknown	unknown

While populating the dim table your SQL should be like below

SELECT

               -1  as ProductID,

               ‘unknown’ As ProductName,

               ‘unknown’  AS ProductStatus

 Union ALL

Select

                ProductID,

                ProductName,

                ProductStatus

From your <Source Dimension tables>

Use following logic to populate Fact Table key columns

Select ISNULL( ProductID  ,-1) AS ProductID

This practice you have to follow for other key column values also. Doing so, when due to any reason if key value column have null from source tables then it will convert to -1 key column value in fact table .Now you have -1 as key value in both Fact table and Dim table. In this case there will be no chance of key mismatches and all the record will be aggregated under unknown or -1 and your cube will not fail during processing.

Now see you have populated -1 for the null key value column of fact table. It will match with -1 key value of Dim table.

Dim Table				Fact Table
ProductKey	Product Name	Product Status		ProductKey	Sale Price	Qty.
-1	Unknown	Unknown		1	200	20
1	Shirt	A		2	300	15
2	cap	A		3	20	100
3	Pen	A		-1	10	15

 
4. Sometimes keys are present in the fact and Dim table but still you get this error.

 

Workaround:

·         In this case it might be the reason that you have used Named Query or View to build your dimension in cube where you have made some restriction/Filter etc.

·         If your Dim table and fact table key values are of Varchar/String data type then there may be chance of blank spaces. Then you should take care of blank spaces using Ltrim/RTrim etc