SSRS: Creating a "Shared Reports" folder in Reporting Services

This blog post covers step-by step instructions on how to create a folder that can be shared between multiple users without being publicly findable (i.e. users will have to share a URL with people who they want to be able to access it).

The location of this folder is under the "My Reports" folder for the individual user.

Start "Internet Explorer" with an account which has administrative permissions for the particular SQL Server Reporting Services (SSRS) instance you are wanting to work with and navigate to your report server URL (for this know let's assume I'm using a server called "reportserver" which is setup on the standard ports);

http://reportserver/Reports/Pages/Folder.aspx

SSRS Home Page - Highlighting "Users Folder"

Click on the “Users Folders” folder;

SSRS Contents of the Users Folder

Find the users folder (look for their username) and click on it. The format of the folders is DOMAINUSERNAME;

Highlighting the "My Reports" Folder Under An Individual User

(under the users folder) Click on “My Reports”;

Highlighting "Folder Settings" In A Users "My Reports" Folder

Click on “Folder Settings” in the title bar;

Highlighting "Security" Under Folder Settings

Click on “Security”;

Highlighting "New Role Assignment"

Click on “New Role Assignment”;

Highlighting the "Select all" click-box

Enter the administrator or administrator domain group and use the checkbox to select all the roles. This will allow the administrator to directly administer the folder going forward. Scroll to the bottom and click “OK”.

Highlighting the Users' User name

Click on the username at the top left of the web page;

Highlighting The Users "My reports" Folder

Click on “My Reports” ;

Highlighting the "New Folder" Menu Button

Click on “New Folder”;

"New Folder" Detail Page

Enter the name “Shared Reports “ and copy/ paste the following text (in italics) into the description;

To share a report modify the following URL http://reportserver/Reports/Pages/Report.aspx?ItemPath=%2fUsers+Folders%2f%YOURDOMAIN%+%USERNAME%%2fMy+Reports%2fShared+Reports%

Replacing %YOURDOMAIN% with your domain name, %USERNAME% with your username, and with the name of the report you wish to share.

For example to share the report “Supplier Spend Summary” for user DDX7 from domain GB1 you would need to use the URL;

http://reportserver/Reports/Pages/Report.aspx?ItemPath=%2fUsers+Folders%2fGB1+ddx7%2fMy+Reports%2fShared+Reports%Supplier Spend Summary

NOTE: Replace %USERNAME% with the lowercase version of the users windows username and make sure you don’t get carried away removing %’s!!

Click "OK";

Highlighting the "Shared Reports" Folder

Click on the newly created “Shared Reports” folder;

Highlighting "Folder Settings"

Click on the “Folder Settings” button the titlebar;

Highlighting "Security" Under Folder Settings

Click on “Security” on the left;

"Edit Item Security" Highlighted

Click on “Edit Item Security”;

Breaking Item Inheritance Warning Message

Click “OK”;

"New Role Assignment" Highlighted

Click on “New Role Assignment”;

"New Role Assignment" Role Selection Dialog

You will either to enter individual users (one at a time, repeating the steps above) or enter a group (for example “Domain Users” to include everyone on your domain) and click on the Browser role. Click “OK”;

Final Settings

The setup is now complete. All the users to whom you have assigned the browse role will now be able to access reports in the users "Shared Folder" (once they have the URL).

Oracle EBS: Linking GL Lines to AP Invoice Distributions in R12

This blog post gives some guidance as to how to link a GL Journal line in Oracle R12 back to its associated AP Invoice Distribution.

NOTE: That this applies ONLY to R12 data - you will not be able to link old (pre-migration) data using this method you will need to have one linking rule for old records and one for new. Just another reason to do a reimplementation!

As always the easiest way to look at this is with a picture;

The image is click-able for a larger view.

The linking goes as follows;

GL_JE_Lines > XLA_AE_Lines > XLA_Distribution_Links > AP_Invoice_Distributions_All

In our Oracle implementation this didn't end up with a 1:1 relationship (which we had in R11) mainly due to the changes in the way AP invoices are stored (R11: Invoice + Distributions, R12: Invoice + Invoice Lines + Distributions).

Looking at a specific example from our R12 test system for journal “4673291 Purchase Invoices GBP”. This journal contains 5 GL records;

Looking at records in XLA_AE_Lines we also have 5 records;

But when we move on to the next table (XLA_DISTRIBTUION_LINKS);

We now have 6 lines (line 2 having been split into two).

We solved this problem but automatically populating a field in the GL with direct 1:1 linking information (hopefully I can add this as another Knoll).

SSRS: Sharing Report Builder 3 Reports Between Users

This blog post provides a fairly quick-and-easy guide to simply sharing a single report between two users.

NOTE: If you're sharing a report in your "My Reports" directory you will need to do some fiddly changes with the URL in order to get someone else to actually access the report (they won't simply be able to browse to it).


Sharing a Folder
By far the easiest way to share reports is actually to set the permissions on folders so that multiple users can see the same folder. Access your reporting server and then browse to the folder you want to share;

When you're in the folder (in this case a folder called "Procurement" select "Folder Settings" (the fourth button from the left on the toolbar). Don't see the button? Then you don't have permission to share folders and you either need to contact someone who does or share the individual reports;

This screen is showing you the basic properties for the folder. Click on the "Security" tab (beneath Properties) on the very left of the page;

Security in SSRS is based on roles. You have five basic roles;

• Browser - Someone with this role can run reports
• Content Manger - This role can create folders, move reports around, etc.
• My Reports - This role grants access to a "My Reports" folder for that individual user
• Publisher - This role can create reports
• Report Builder - This role grants access to the "Report Builder" plugin to create new reports

Clicking on "Edit Item Security" will probably bring up the popup below;

This is simply letting you know that if you make any changes the permissions on this item will then be different to the permissions on the parent item - as this is exactly what we're trying to achieve click "OK" to continue.

To add a new user to the folder (or group of users) click on "New Role Assignment" (the middle button above the section on the right);

Enter the Active Directory user (or group) that you wish to give permissions to access the folder and select the role wyou want that group to have. Click "OK" and you're done.

Sharing A Single Report
Navigate to the directory which contains the report you wish to share;

For the purposes of this example I'm going to share the "Lookup Example - For Everyone"-report. Hover over it and select "Security" from the drop down when it appears;

The current permissions for the item are displayed;

Click "Edit Item Security" (in the menu bar);

Click "OK" to break the inherritance and give this report specific permisisons.

Click on "New Role Assignment";

Enter the username (or AD groupname) that you wish to define permisisons for on this report (Browser if you just want them to be able to run it) and click "OK". The user will now have the permissions you defined for the report.

NOTE: If you as sharing a report in the "My Reports" folder you MUST provide a URL to those you have shared the report with that does not reference "My Reports". For example;

https://xxx/Reports/Pages/Folder.aspx?ItemPath=%2fUsers+Folders%2f%2fMy+Reports&ViewMode=List

Would work as it goes through "Users Folders > > My Reports" whereas;

https://xxx/Reports/Pages/Folder.aspx?ItemPath=%2fMy+Reports&ViewMode=List

Could not be shared as each user will have a different "My Reports" folder. Looking at the two URL's above you should be able to quickly see how to convert one to another when sharing.

Noetix: Adding a Flexfield to a View

This blog post covers a step-by-step guide to adding a Flexfield into a NoetixView. As is the case with all Noetix changes if you do now have NCM (Noetix Customisation Mantenance) these changes will not be supported by Noetix.

In order to add a flexfield you need to identify the table and columns used to store the data. Flexfield data will typically be stored in the ATTRIBUTE1..x columns of the table you specify, but this isn't always the case! You only need the "general" information, not down the the segment level.

Once you have the table/column combinations you need to create a script called from xu2 that contains the following insert statement;

INSERT INTO n_view_column_templates
  (view_label, query_position, column_label, table_alias, column_expression, column_position, column_type, description, group_by_flag, product_version, ref_lookup_column_name, created_by, creation_date, last_updated_by, last_update_date)
VALUES
  ('**1**' -- view_label
  ,**4** -- query_position
  ,'**2**' -- column_label
  ,'**2**' -- table_alias
  ,'**2**' -- column_expression
  ,(&max_col_position + 1) -- column_position
  ,'ATTR' -- column_type
  ,'Descriptive flexfield' -- description
  ,'N' -- group_by_flag
  ,'%' -- product_version
  ,'**5**' -- ref_lookup_column_name
  ,'**3**' -- created_by
  ,to_date('07-SEP-2011') -- creation_date
  ,'**3**' -- last_updated_by
  ,to_date('07-SEP-2011')) -- last_update_date
;

**1** = The view label, from n_view_templates, i.e. GME_Batch_Headers
**2** = The table alias, from n_view_table_templates, that you identified as being used to store the flexfield
**3** = Your details!
**4** = The query position in use in the target view. Look in n_view_column_templates to get the active values. This will usually only be one value, if not you will need to replicate the insert statement for each query position.
**5** = The columns where the flex field is stored followed by a percentage (usually ATTRIBUTE%)

NOTE: Product version might also have to change if you have two environments you regenerate into (i.e. 11.5.1.0 and 12.1) when the table aliases between the versions are different.

If you follow the standard script template for xu2 then the @max_col_position variable will already be populated.

Oracle PL/SQL: Working with Materialized Views in a 24/7 Reporting Environment

This blog post gives an outline for a process that will provide users with the Last Update, Next Update, and a "nice message" during a materialized view refresh.

The purpose of this blog post is to provide your report writers with a way of identifying whether or not the view is currently being refreshed so they can "try again later". Of course for business-critical up-to-the-second reporting you might not want to be using materialized views at all.


Setting Up The Demonstration System
In order to show this in action we need to setup the following items in a database;

create table MAT_VIEW_TEST
(
  ID          number not null,
  SHORTCODE   varchar2(30) not null,
  DESCRIPTION varchar2(255),
  STARTDATE   date not null,
  ENDDATE     date
)
;

alter table MAT_VIEW_TEST
  add constraint MAT_VIEW_TEST_PK primary key (ID);

insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (1, 'IT', 'IT Department', to_date('01-05-2010', 'dd-mm-yyyy'), null);
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (2, 'IT', 'Business Systems Department', to_date('01-01-1980', 'dd-mm-yyyy'), to_date('30-04-2010', 'dd-mm-yyyy'));
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (3, 'FIN', 'Finance', to_date('01-01-1980', 'dd-mm-yyyy'), null);
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (4, 'PRC', 'Procurement', to_date('01-01-1980', 'dd-mm-yyyy'), null);
insert into MAT_VIEW_TEST (ID, SHORTCODE, DESCRIPTION, STARTDATE, ENDDATE)
values (5, 'LEG', 'Legal', to_date('01-01-1980', 'dd-mm-yyyy'), null);
commit;

 create materialized view mat_view_test_mv
  refresh complete 
   start with sysdate 
   next trunc(sysdate)+1 + 5/24 as
  select *
    from mat_view_test
 ;

The materialized view we're creating will refresh immediately and will then schedule itself to refresh at 5am each day going forward.

Aims and Expected Results
The whole point of this Knol is to manage the users expectations. If you provide them a report which is refreshed at 5am and they make a change to the data and then run the report you want them to know why they're not going to see their new record so they don't call your helpdesk and start saying the report is "broken".

The result we're expecting to see (from a simple SELECT * ...) would be;

(1) - This is the date the view was last refreshed and the date the view will next be refreshed.
(2) - As all the records are being returned from the main table the message always reads "OK".

We would expect to get this result at any time other than when the view was being refreshed. When the view is actively being refreshed then the result would normally be blank but what we'd like to see would be a single row returned with the message "REFRESHING".

Building Our View of the Materialized View
Now the values we need (the last and next refresh dates) are stored in SYS.ALL_MVIEWS and SYS.DBA_JOBS respectively. It's a fairly simple process to join these objects together and get the result;

select am.LAST_REFRESH_DATE,
       dj.next_date next_refresh_date,
       'OK' as message,
       mvtv.*
  from MAT_VIEW_TEST_MV mvtv
  left outer join sys.dba_jobs dj
    on dj.what =
       'dbms_refresh.refresh(''"' || USER || '"."MAT_VIEW_TEST_MV"'');'
  join sys.all_mviews am
    on am.owner = user
   and am.mview_name = 'MAT_VIEW_TEST_MV'

We can use a default for the message so that we can change it to something more meaningful in the reporting tool.

Now that we've got our main result we now need to handle the special case when MAT_VIEW_TEST_MV contains no records. The easiest way to do this is to use the DUAL system object to return a single row and do a check in MAT_VIEW_TEST_MV for records. For example;

select null, null, 'REFRESHING', mvtv.*
  from sys.dual
  left join MAT_VIEW_TEST_MV mvtv
    on 3 = 1
 where not exists (select 'x' from MAT_VIEW_TEST_MV)

Now you're probably already spotted that the columns returned by both queries are identical which makes unioning them a lot easier giving you the final piece of SQL;

create view mat_view_test_v as
select am.LAST_REFRESH_DATE,
       dj.next_date next_refresh_date,
       'OK' as message,
       mvtv.*
  from MAT_VIEW_TEST_MV mvtv
  left outer join sys.dba_jobs dj
    on dj.what =
       'dbms_refresh.refresh(''"' || USER || '"."MAT_VIEW_TEST_MV"'');'
  join sys.all_mviews am
    on am.owner = user
   and am.mview_name = 'MAT_VIEW_TEST_MV'
union
select null, null, 'REFRESHING', mvtv.*
  from sys.dual
  left join MAT_VIEW_TEST_MV mvtv
    on 3 = 1
 where not exists (select 'x' from MAT_VIEW_TEST_MV)

You now have a view which will return the contents of the materialized view if it has any or a single record you can check for if it doesn't.

Oracle PL/SQL: When are Materialized Views refreshing?

This blog post provides a pieces of SQL which will look for Jobs in the SYS.DBA_JOBS view that appear to be Materialized View refreshes and display the timing information as well as the interval.


Execute the following SQL;

 SELECT dj.job,
       SUBSTR(SUBSTR(dj.what, 24, 255),
              1,
              INSTR(SUBSTR(dj.what, 25, 255), '"')) VIEW_OWNER,
       REPLACE(SUBSTR(SUBSTR(dj.what, 24, 255),
                      INSTR(SUBSTR(dj.what, 25, 255), '.') + 3,
                      255),
               '"'');',
               '') VIEW_NAME,
       dj.last_date,
       dj.last_sec,
       dj.NEXT_DATE,
       dj.broken,
       dj.interval
  FROM sys.dba_jobs dj
 WHERE dj.what like 'dbms_refresh.refresh(''"%"."%"'');'

This will return something similar to;

This gives enough information to allow new views to be scheduled in the gaps around the refreshes of old views and I have always found very useful.

Oracle PL/SQL: Removing an Existing Materialized View

This blog post provides a piece of SQL that you can easily customize to check-for and remove a materialized view if it is present.


The SQL is;

declare
  v_MVName varchar2(30) := 'XXX';
begin
  for v_Row in (select 'x' from all_mviews am where am.owner = user and am.mview_name = v_MVName) loop
  execute immediate
    'drop materialized view ' || v_MVName;
  end loop;
end;

In order to target a specific view just replace XXX in the second line with the name of your view.

SSRS: Problems Linking To SharePoint Lists When Running in Sharepoint-Integrated Mode

This blog post documents a problem linking to List Items in SharePoint from a SSRS Report Running on a SharePoint integrated-mode server.

NOTE: There is currently no solution to this problem. However there is a quick and relatively painless work-around (see below)

Prerequisites;

• SQL Server Reporting Services 2008R2 with SP1 server running in Integrated Mode (with SharePoint 2007)
• A SharePoint website containing at least one list to use as a source for reporting
• Report Builder 3.0 for creating/ testing the report

Duplicating The Issue
Create a new report (using the "Blank report" option in the Wizard);

Report Builder 3: New Report (using Wizard)

Click on the "Click to add title" box and enter the text TEST. Right-click the text box and select "Text Box Properties" and then go down to "Action" in the list on the left;

Report Builder 3: Text Box Properties

Select "Go to URL" and then click on the Expression (fx) elipse;

Report Builder 3: Expression Dialog

Enter the URL to directly link to an item in the list adding in the current value of ID from the report as the parameter. For the example list I'm using this is;

="http://xxx/Reports%20List/DispForm.aspx?ID=4"

This will mean when you click on the link it will take you directly to the corresponding item. Click "OK" twice to get back to the main report and then click "Run" on the ribbon;

Report Builder 3: Report Preview

Click on "TEST" and it will open the URL in Internet Explorer (for example My Tasks);

Internet Explorer: Working Link

The important point to note is the URL which is;

Internet Explorer URL Bar

http://xxx/Reports%20List/DispForm.aspx?ID=4

The "4" is the ID for the "My Tasks" item (shown above).

This shows the report is working correctly when run from within Report Builder.

Save the Report to the Integrated-Mode Reporting Services server and re-run it;

Test Reporting Running from SharePoint Server

I've highlighted the same item we clicked on previously, click on it again;

Incorrect Data Displayed

Different data. Incorrect data. If you check the URL it has become;

http://xxx/Reports%20List/DispForm.aspx?ID=4&ID=4[1]

You'll notice that the ID variable on the end of the URL has been duplicated. This is no-where in the code that causes the link not to work. If you add the URL we are building as a displayed field you will find that it is displayed correctly (with a single ID) - something in SSRS seems to be causing this duplication to be introduced in the renderer/viewer rather than in the code of the report.

Workaround
If, rather than building the URL as described above, you use the expression;

="javascript:void(window.open('http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID=" & CStr(Fields!ID.Value) & "', '_self'))"

This solves the problem by "hiding" the URL in the expression so it doesn't get incorrectly updated.

(Thanks to Klaus Sobel from Microsoft Support for providing me with this work-around!)

References

1. The error seems very specific to SharePoint URL's. For example if you change the URL to "http://www.google.co.uk/?ID=" then the problem (duplication of parameters) does not appear. Equally if you were to remove the ?ID= then you don't get the problem. If you then add in the “?ID=” a bit at a time you get; Adding "?" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?4. Adding "?ID" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID4. Adding "?ID=" gives http://xxx/Reports%20LIVE/Forms/EditForm.aspx?ID=83&ID=4. This suggests some amount of parsing is going on somewhere, and the parsing is incorrect.

SSRS: Using &ReportName In SharePoint Mode vs Native Mode

This blog highlights the different values (for the same report) returned in the two modes in which SSRS can be run; Integrated into a SharePoint Farm, and in Native (or stand-alone) Mode.

For the purposes of testing I am running Report Builder 3 against 2008R2 SP1 reporting servers. The version of SharePoint I'm using to integrate with is 2007 (with SP2). To browse the website I'm using Internet Explorer 9.

UPDATE (from Microsoft): The lowercasing behaviour (documented below) is a "known bug" (108596) which will be resolved in a "future release". Appending the ".rdl" extension is a feature of SharePoint and as such is "as intended" behaviour. Bugs are fixed based on the number of users affected so if you want it fixed you need to raise it with Microsoft.

Writing The Test Report
Let's start with a fairly simple report;

Report Builder 3: New Report (Using Wizard)

This report is basically the "Blank Report" as created by the wizard when you first start Report Builder 3 (without specifying a starting report).

Right-click on the "Click to add title" at the top of the screen;

Report Builder 3: Expression Dialog (using Globals!ReportName)

Choose "Globals!ReportName" as your expression (under the "Built-in Fields" category on the left). Click OK;

Report Builder 3: Showing &ReportName as Report Title

You'll see that the title of the report is now set to the Report Name. Click on "Run" (left on the ribbon);

Report Builder 3: Showing Report GUID

This is your reports GUID. As you haven't saved the report yet it doesn't have a formal name so the expression evaluates to the GUID.

Saving The Report To Reporting ServersSave the Report to a native (stand-alone) server using the name "ReportName Test Report" and click refresh;

Report Builder 3: Native-Mode Report Title

Save the Report to SharePoint-Integrated server using the same name and click refresh;

Report Builder 3: SharePoint Integrated-Mode Report Title

You notice that ".rdl" (the file extension) has been appended in SharePoint integrated mode.

Both of these tests were running the report in "Report Builder" - this is usually a pretty good indicator of how the report will look in live but it's important to test the report in a web browser as well.

Looking at the native mode report in Internet Explorer (I'm using IE9) gives you;

Native-Mode Report in IE9

Looking at the same report in SharePoint integrated mode;

SharePoint Integrated-Mode Report in IE9

Exactly what you'd expect. Now close Report Builder, open the native-mode report and then save it to the SharePoint server (adding a 1 suffix so we can tell it's different). Close down Report Builder (so we can be sure we're not carrying across any settings) and re-edit the "ReportName Test Report 1" file.

Preview the report in Report Builder;

Report Builder 3: Migrated Report

Exactly what we'd expect. Now view the report in IE;

Migrated Report in IE

The ReportName has now been converted to lowercase.

Result Summary for ReportName "ReportName Test Report";
Non-Integrated Mode

• &ReportName remains "ReportName Test Report" in all circumstances

Integrated (SharePoint) Mode

• &ReportName has ".rdl" added as a suffix in all circumstances
• If the report has been migrated from Non-Integrated Mode @ReportName is converted to lowercase when viewing in a browser only (Report Builder is unaffected)