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,","))