none
How to get the project name in a Reporting Services Report in a Project Site? RRS feed

  • Question

  • Hi,

    I developed a report using Reporting Services where I need to choose a project to see the report. I want to include it in a Project Site template using a SSRS Web part. As the report will be in a project web site, I would like the report appears automatically showing the project of the site without the need of choose of the project.

    Can it be done without developing a complex code?

    PS: Project Server 2013 and SQL Reporting Services 2012.

    Thank you.


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner


    • Edited by R.Segawa Friday, January 17, 2014 9:29 PM
    Friday, January 17, 2014 9:26 PM

Answers

  • Unfortunately there are a number of ways to navigate to a project site. (From Project Center is another) - Covering them all is not an option.

    Like I said before there are two options, using a server-side web part - or using client-side code like the following: (just put this in a content editor on the page of your report and it will ensure there is a ProjUid on the query string)

        <script type="text/javascript">

            //(c) 2014 James Boman - IPMO.

            //License granted under creative commons CC BY-SA

            //http://creativecommons.org/licenses/by-sa/3.0/au/

            var PDP;

            var web;

            var ProjectUID;

            var PWAURL;

            ExecuteOrDelayUntilScriptLoaded(CheckProjectUid, "sp.js");

            function CheckProjectUid() {

                if (!urlHasProjectUid()) {

                    getAppWeb(function () {

                        getWebProperties(function () {

                            NavigateMe()

                        });

                    });

                }

            }

            function urlHasProjectUid() {

                strDest = document.URL

                if (strDest.split('?').length > 1) {

                    return (getQueryStringParameter('ProjUid') != null);

                } else {

                    return false;

                }

            }

            function NavigateMe() {

                var strDest = document.URL;

                if (strDest.split('?').length > 1) {

                    strDest += '&';

                } else {

                    strDest += '?'

                }

                strDest += 'ProjUid=' + ProjectUID;

                window.location = strDest;

            }

            function getAppWeb(functionToExecuteOnReady) {

                var context = SP.ClientContext.get_current();

                web = context.get_web();

                context.load(web);

                context.executeQueryAsync(functionToExecuteOnReady, onFailure);

            }

            function getWebProperties(functionToExecuteOnReady) {

                webProperties = web.get_allProperties();

                var context = SP.ClientContext.get_current();

                context.load(webProperties);

                context.executeQueryAsync(function () {

                    var allProperties = webProperties.get_fieldValues();

                    PWAURL = allProperties['PWAURL'];

                    ProjectUID = allProperties['MSPWAPROJUID'];

                    functionToExecuteOnReady();

                }

                    , onFailure);

            }

            function onFailure(sender, args) {

                alert('CSOM Error: ' + args.get_message());

            }

            function getQueryStringParameter(urlParameterKey) {

                var params = document.URL.split('?')[1].split('&');

                for (var i = 0; i < params.length; i = i + 1) {

                    var singleParam = params[i].split('=');

                    if (singleParam[0] == urlParameterKey)

                        return decodeURIComponent(singleParam[1]);

                }

            }

        </script>


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au


    • Edited by James Boman Monday, January 20, 2014 5:54 AM
    • Marked as answer by R.Segawa Wednesday, January 22, 2014 1:08 AM
    Monday, January 20, 2014 5:43 AM
  • Hi,

    Agree with James in case if you want this report on Project Site only, then you have two options left. Either CLient Side as explained by James or Server Side using. net explaining below.

    Report Webpart

    1) Create a visual web part solution.

    2) Add a grid view control there in webpart page.

    3) Read Current URL from browser.

    4) Using string function like len and substring fetch ProjectName from current URL.

    5) Make SQL Connectoin to reporting database and fetch all Project details for the Project fetched in step 4.

    6) Populate data in GridView control.

    7) Build and deploy the webpart.

    8) Add that web part in PWA site template(for new site) and all existing project sites.

    That's all you have report in form of VisualWebPart in Project Site based on Project Site Name.

    Thanks and let me know in case have any doubts.


    Sachin Vashishth MCTS

    • Marked as answer by R.Segawa Wednesday, January 22, 2014 1:09 AM
    Monday, January 20, 2014 11:32 AM

All replies

  • Ricardo,

        Here at IPMO we have a custom webpart that we use that connects with the reporting services webpart that passes it the ProjectUID.  Not complicated to write, but it is custom code.

    If you want to avoid custom code, then you could vary your approach slightly and put the Reporting services webpart on a PDP, use a querystring filter webpart (out of the box) to pass the ProjectUID from the querystring to the Reporting Services webpart as a parameter.

    Cheers,

       James.


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au

    Friday, January 17, 2014 11:56 PM
  • Agree with James, I think this what you are asking for "http://www.epmpartners.com.au/blog/connect-query-string-web-part-to-ssrs-report-viewer-web-part/".

    Thanks and let me know in case have any doubts.


    Sachin Vashishth MCTS

    Saturday, January 18, 2014 6:40 AM
  • Hi Borman and Vashishth,

    Thank you for your answers.

    Borman for confirming one of my hypothesis about this issue and Vashishth for provide a solution for PDP.

    Just a comment about the article in the link supplied about PDP, the connection field should be ProjUID and not ProjectUID and it is not by example. It is mandatory due the URL address.

    I am still looking for a solution for the Project Site, because I need to insert this report in the home page. Tha absense of coding is because the final users will need to make eventual maintenance on the page.

    Vashishth, have you tried to use other web part filters to extract the project name from the url of the Project Site, without using coding?

    Thank you for everyone.


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Sunday, January 19, 2014 8:58 PM
  • Ricardo,

        The advantage of placing the SSRS webpart on the PDP is that "projuid" will always appear in the query string when navigating to a project from Project Center.

    On the project site, the Project UID is still available, however it is stored in the Property Bag of the SPWeb, and called 'MSPWAPROJUID'.  You can get at this property using the server-side object model, or client-side via javascript, but both ways require some specialised effort. 

    Regards,

       James.


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au

    Sunday, January 19, 2014 10:12 PM
  • Hi James,

    Sorry for mispelling your surname.

    Is there any other way to add at the end of the project site url the string "?ProjUID=<projUID>" when the site is created in Project Server?

    I did it manually in the Server Configuration page in PWA and it worked, but I needed to add the same string in the Home Page link at the quick start panel in the project site.

    Thank you.


    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Monday, January 20, 2014 4:52 AM
  • Unfortunately there are a number of ways to navigate to a project site. (From Project Center is another) - Covering them all is not an option.

    Like I said before there are two options, using a server-side web part - or using client-side code like the following: (just put this in a content editor on the page of your report and it will ensure there is a ProjUid on the query string)

        <script type="text/javascript">

            //(c) 2014 James Boman - IPMO.

            //License granted under creative commons CC BY-SA

            //http://creativecommons.org/licenses/by-sa/3.0/au/

            var PDP;

            var web;

            var ProjectUID;

            var PWAURL;

            ExecuteOrDelayUntilScriptLoaded(CheckProjectUid, "sp.js");

            function CheckProjectUid() {

                if (!urlHasProjectUid()) {

                    getAppWeb(function () {

                        getWebProperties(function () {

                            NavigateMe()

                        });

                    });

                }

            }

            function urlHasProjectUid() {

                strDest = document.URL

                if (strDest.split('?').length > 1) {

                    return (getQueryStringParameter('ProjUid') != null);

                } else {

                    return false;

                }

            }

            function NavigateMe() {

                var strDest = document.URL;

                if (strDest.split('?').length > 1) {

                    strDest += '&';

                } else {

                    strDest += '?'

                }

                strDest += 'ProjUid=' + ProjectUID;

                window.location = strDest;

            }

            function getAppWeb(functionToExecuteOnReady) {

                var context = SP.ClientContext.get_current();

                web = context.get_web();

                context.load(web);

                context.executeQueryAsync(functionToExecuteOnReady, onFailure);

            }

            function getWebProperties(functionToExecuteOnReady) {

                webProperties = web.get_allProperties();

                var context = SP.ClientContext.get_current();

                context.load(webProperties);

                context.executeQueryAsync(function () {

                    var allProperties = webProperties.get_fieldValues();

                    PWAURL = allProperties['PWAURL'];

                    ProjectUID = allProperties['MSPWAPROJUID'];

                    functionToExecuteOnReady();

                }

                    , onFailure);

            }

            function onFailure(sender, args) {

                alert('CSOM Error: ' + args.get_message());

            }

            function getQueryStringParameter(urlParameterKey) {

                var params = document.URL.split('?')[1].split('&');

                for (var i = 0; i < params.length; i = i + 1) {

                    var singleParam = params[i].split('=');

                    if (singleParam[0] == urlParameterKey)

                        return decodeURIComponent(singleParam[1]);

                }

            }

        </script>


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au


    • Edited by James Boman Monday, January 20, 2014 5:54 AM
    • Marked as answer by R.Segawa Wednesday, January 22, 2014 1:08 AM
    Monday, January 20, 2014 5:43 AM
  • Hi,

    Agree with James in case if you want this report on Project Site only, then you have two options left. Either CLient Side as explained by James or Server Side using. net explaining below.

    Report Webpart

    1) Create a visual web part solution.

    2) Add a grid view control there in webpart page.

    3) Read Current URL from browser.

    4) Using string function like len and substring fetch ProjectName from current URL.

    5) Make SQL Connectoin to reporting database and fetch all Project details for the Project fetched in step 4.

    6) Populate data in GridView control.

    7) Build and deploy the webpart.

    8) Add that web part in PWA site template(for new site) and all existing project sites.

    That's all you have report in form of VisualWebPart in Project Site based on Project Site Name.

    Thanks and let me know in case have any doubts.


    Sachin Vashishth MCTS

    • Marked as answer by R.Segawa Wednesday, January 22, 2014 1:09 AM
    Monday, January 20, 2014 11:32 AM
  • Hi James,

    Thank you for showing this piece of code.

    It is really simple but for those who knows how.



    Best regards, Ricardo Segawa - Segawas Projetos / Microsoft Partner

    Tuesday, January 21, 2014 3:18 PM
  • Ricardo,

        Sure - no problems.  Please mark as answer if it helped you out.

    Cheers,

       James.


    James Boman - http://www.boman.biz Software Consultant for IPMO - http://www.ipmo.com.au

    Tuesday, January 21, 2014 9:52 PM