Just another WordPress.com site

MS SSRS Tips

User Roles and Permissions in SSRS

Very good article explaining how to setup user roles and grant permissions.

http://bhushan.extreme-advice.com/user-roles-and-permissions-in-ssrs/

Advertisements

Entire Steps of How To Create a SSRS Report

Implementation Steps:
1. Create the reports in SSRS with the shared data source to the database

2. Configure the project properties:

3. Go to http://servername/Reports, locate the new data source that just created in the Data Sources folder, configure as below:

4. In SQL management Studio, grant the report access account db_datareader permission to the database.

5. If users still cannot access the reports, open the report as the administrator, in folder’s Security, add a new account “Market\Domain Users” and grant with the “Browser” privilege.

If need to send out email automatically,
6. Go to StartAll ProgramsMicrosoft SQL ServerConfiguration ToolsReporting Services Management Tools
In E-mail Settings page, type in necessary information, note in “Sender Address”, type in an email address which you want to show up as the sender, this email address does not need to be a real existing email account.

7. Go to the report webpage, in Subscriptions page, on the Subscriptions page create a new subscription by clicking on “New Subscription” button. Create an email group for the report recipients, fill as the recipient in the “To” line.


‘ Domain\User’ does not have required permissions.

When browse the SSRS webpage http://SSRSServer/Reports/Pages/Folder.aspx, page gives error message: User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
But the user has already been granted with “System Administrator” right and content manager right to the reports.

Solution:

Run web browser with Administrator’s role, then go to SSRS report site home page, in Folder Settings, as the Domain User “Domain\User’” as a Content Manager which has the full privileges to the reports and folders.

Reference Article:
http://bhushan.extreme-advice.com/user-domainuser-does-not-have-required-permissions/


Add spaces and changes lines in text(notes)

Add spaces: “string1” & space(6) &“string2”

Change lines:”string1″+ VBCRLF +”string2″


How to set an angle for x-axis in Dundas chart

In Dundas chart properties window, click on Advanced tab, select X AxisàLabelStyleàFontAngle, then manually type the angle degrees there.

Notes: set the LabelsAutoFit to False, otherwise the Label will display at the setting for LabelsAutoFit.

After apply, the report looks as below:


How to create a search page for a report?

 Scenario

We want to create a search page, if user types any keyword, the system will provide with all the options that match the keyword with links to each report page.

  1. In SSRS, create a report parameter @Search.
  2. In the dataset, write down as in below script, it will enable the system to search in each every level for the keyword

select distinct Entity, null Site,null Program ,null SubProgram,null NursingUnit, 1 Sequence,

http://TestServer/ReportServer/Pages/ReportViewer.aspx?%2fFactReport&rs%3aComand=Render’+

isnull(‘&pEntity=’+Entity,”)+’&pNU=All%20Nursing%20Units’ URL

from dbo.ReportFactData

where Entity like ‘%’+@Search+’%’ 

union

select distinct Entity, Site,null Program, null SubProgram,null NursingUnit, 2 Sequence,

http://TestServer/ReportServer/Pages/ReportViewer.aspx?%2fFactReport&rs%3aCommand=Render’+

isnull(‘&pEntity=’+Entity,”)+isnull(‘&pSite=’+Site,”)+’&pNU=All%20Nursing%20Units’ URL

from dbo.ReportFactData

where Site like ‘%’+@Search+’%’ 

union

select distinct Entity, Site, Program, null SubProgram,null NursingUnit, 3 Sequence,

http://TestServer/ReportServer/Pages/ReportViewer.aspx?%2fFactReport&rs%3aCommand=Render’+

isnull(‘&pEntity=’+Entity,”)+isnull(‘&pSite=’+Site,”)+isnull(‘&pProgram=’+Program,”)+’&pNU=All%20Nursing%20Units’ URL

from dbo.ReportFactData

where Program like ‘%’+@Search+’%’

3. In the report layout page, create a table to show all the options that matches the searching criteria, in the properties of Link field, Action section, select “Jump to URL”, and choose Fields!URL.Value for the content of the URL.

Tips:
  1. In a report web page which you need to select value for parameters, you can assign values in the weblink such as pagelink&parameter1=value&parameter2=value&parameter3=value
  2. In the parameter value, if there are spaces, we can replace with %20 in the weblink
  3. It is unnecessary to assign values to all parameters if they have been assigned with default values in the parameter properties in SSRS

Eg: Below link will show a report with Entity=Vancouver, Site=HSP, Program=ALL, NursingUnit=All Nursing Units

http://TestServer/ReportServer/Pages/ReportViewer.aspx?%2fFactReport&rs%3aCommand=Render&pEntity=Vancouver&pSite=HSP&pProgram=ALL&pNU=All%20Nursing%20Units


How to cascade parameters in a report

Scenario

We need a report which contains a couple of parameters at different levels (eg: Entity, Site, Program, SubProgram, NursingUnit). We need the report can cascade these parameters, so that when a high level parameter is given a value, only options pertain to that value will show in the dropdown for other parameters.

Solution:

1. In SSRS, create report parameters for each level.

  

2. In the report, create dataset for each parameter data pool:

Eg: script to create dataset for NursingUnit,

select distinct NursingUnitCode, NursingUnit, Site from dbo.FactData

where

   isnull(SubProgram, ‘All’)=case isnull(@pSubProgram, ‘All’) when ‘All’ then isnull(SubProgram, ‘All’) else @pSubProgram end

   and isnull(Program,’All’)=case isnull(@pProgram, ‘All’) when ‘All’ then  isnull(Program,’All’) else @pProgram end

   and isnull(Site,’All Sites’)=case isnull(@pSite, ‘All Sites’) when ‘All Sites’ then  isnull(Site,’All Sites’) else @pSite end

   and isnull(Entity,’All Entities’)=case isnull(@pEntity, ‘All Entities’) when ‘All Entities’ then  isnull(Entity,’All Entities’) else @pEntity end

union

select ‘ALL’, ‘All Nursing Units’,null

order by NursingUnit

3. For the fact dataset, a stored procedure will call all parameters.

Exec dbo.GeteportFactData @Entity, @Site, @Program, @SubProgram, @NursingUnit

4. In the stroed procedure,

ALTER PROCEDURE [dbo].[GetPneumoniaReportFactData] –null,null,null,null,’HSP – Ward1 (HSP-Ward1)’

(     @Entity  varchar(100),

      @site varchar(100),

      @Program varchar(100),

      @SubProgram varchar(100),

      @NursingUnit  varchar(100)

)

AS 

if @NursingUnit=’All Nursing Units’ begin set @NursingUnit=null end

if @SubProgram=’ALL’ begin set @SubProgram=null end

if @Program=’ALL’ begin set @Program=null end

if @Site=’All Sites’ begin set @Site=null end

if @Entity=’All Entities’ begin set @Entity=null end 

select case when isnull(@NursingUnit,”)<>” then NursingUnit

                  when isnull(@SubProgram,”)<>” then SubProgram

                  when isnull(@Program,”)<>” then Program

                  when isnull(@Site,”)<>” then Site

                  when isnull(@Entity,”)<>” then Entity

                  else HA end as ReportArea,

                  FiscalYear,

                  FiscalPeriod,

                  sum(case when Flag = ‘Y’ then 1 else 0 end) AcctCount,

                  convert(decimal(5,1),(sum(case when Flag = ‘Y’ then 1 else 0 end)*100.0/(case Count(*) when 0 then 1 else Count(*) end))) Rate,

                  Count(*) TotalRecords

from dbo.FactData

where isnull(NursingUnit,”)  = isnull(isnull(@NursingUnit, NursingUnit),”)

      and isnull(SubProgram,”) = isnull(isnull(@SubProgram,SubProgram),”)

      and isnull(Program,”) = isnull(isnull(@Program,Program),”)

      and isnull(Site,”) = isnull(isnull(@Site,Site),”)

      and isnull(Entity,”) = isnull(isnull(@Entity,Entity),”)

      –and SubEntityID<6

      group by case when isnull(@NursingUnit,”)<>” then NursingUnit

                  when isnull(@SubProgram,”)<>” then SubProgram

                  when isnull(@Program,”)<>” then Program

                  when isnull(@Site,”)<>” then Site

                  when isnull(@Entity,”)<>” then Entity

                  Else HA end, FiscalYear,

                  FiscalPeriod