locked
Select Project SSRS Parameter Issue RRS feed

  • Question

  • Hi,

    I have been building a SSRS report, which is pulling data from Project Sites and putting it into a nice report. This element is working nicely, and I have different tables for each list (risks, issues etc).

    This element uses the reporting database and also another XML data source which links to the project select parameter.

    What I now want to do, is take some more data out the project reporting database for some custom fields. This obviously isn't project site data, but I want the 'Select Project' parameter box to bring back data for these custom fields as well.

    Currently, it brings back all of the workspace data. How can I get the parameter to refine this data as well? It obviously comes from the reporting database as well.

    Its all pretty new to me, so any more information I need to give I can provide. Hope this makes sense.......!


    Rob Hardy
    Monday, November 21, 2011 4:44 PM

Answers

  • Hi Rob. One concept i am thinking of perhaps would be to leave parameter 2 as the main parameter. Create a new dataset query which is filtered by the returned value of parameter 2. For example, create a new data set called "SelectedProject".

    SELECT
        p.ProjectWorkspaceInternalHref
    FROM MSP_EPMProject_UserView AS p
    WHERE p.ProjectUID = @Parameter2

    replace @Parameter2 with the name as your current parameter 2 name. Remeber to prefix with an "@" character to represent it as a parameter.

    Now this works on the assumption that you are only returning a single project other wise you may have to get a little more fancy. If it's safe to assume a single project will always be returned then you will be able to reference the first value returned by this dataset in any area of your SSRS report. Then try pass this returned field to your second query which requires a Project site URL. If not possible, you could try creating a hidden parameter and set its default value from the query above which would be a place holder for the project site, then use the hidden parameter to filter your query. Hope this makes sense but its tricky stuff. Have a go and let us know how you get on Rob.


    Regards, Piet Remen
    • Marked as answer by EPM Tester Friday, November 25, 2011 11:53 AM
    Friday, November 25, 2011 12:11 AM

All replies

  • In my reporting db queries I generally use the Views, rather than the direct tables. This is because the views do some of hte leg work for you around joining up to get the custom field data.

    The table called "MSP_EPMProject" does not have any custom field data, but the View called "MSP_EpmProject_Userview" has all the data of the table but ALSO has the custom field data (scroll down to the bottom and your project level custom fields are all there.

    this query is a sample one that could accept a project name parameter and then join over to WSS stuff as well as giving you your custom fields. (IT Project Type and Marketing Project Type are both project custom fields held in the view.)

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_WssDeliverable_OlapView.Title AS Del_Title, MSP_WssIssue_OlapView.Title AS Issue_Title, 
                          MSP_WssRisk_OlapView.Title AS Risk_Title, MSP_EpmProject_UserView.[IT Project Type], MSP_EpmProject_UserView.[Marketing Project Types]
    FROM         MSP_EpmProject_UserView INNER JOIN
                          MSP_WssIssue_OlapView ON MSP_EpmProject_UserView.ProjectUID = MSP_WssIssue_OlapView.ProjectUID INNER JOIN
                          MSP_WssDeliverable_OlapView ON MSP_EpmProject_UserView.ProjectUID = MSP_WssDeliverable_OlapView.ProjectUID INNER JOIN
                          MSP_WssRisk_OlapView ON MSP_EpmProject_UserView.ProjectUID = MSP_WssRisk_OlapView.ProjectUID
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Monday, November 21, 2011 6:31 PM
  • Hi Rob,

    What is you "Select Project" parameter passing to the report, the ProjectUID? If so, just use the EPMProject_Userview as Brian sugests but add a where clause such as "Where MSP_EPMProject_Userview.ProjectUID = @ProjectUID" - change for the name of the parameter used in the report.

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Monday, November 21, 2011 7:48 PM
  • Hi Rod,

    Some months ago I developed something similar as you. To get the ProjectUID and then pass to the SQL query, I used that code snippet:

    private Guid? GetCurrentProjectUid()
            {
                SPWeb currentWeb = SPContext.Current.Web;
                if ((currentWeb.WebTemplateId >= WSS.WssPWSTemplateNumericIdMinLimit) &&
                    (currentWeb.WebTemplateId <= WSS.WssPWSTemplateNumericIdMaxLimit))
                {
                    string webPropProjUid = currentWeb.AllProperties["MSPWAPROJUID"] as string;
                    if (!string.IsNullOrEmpty(webPropProjUid))
                    {
                        return new Guid(webPropProjUid);
                    }
                }
                return null;
            }

    Hope that helps you!

    Kind Regards


    Miguel Soler
    Monday, November 21, 2011 8:48 PM
  • Hi,

    Thanks for the tips.

    So I started the report again, this time I get the parameters okay, however when I view the report no values show up in the boxes (nor any errors).

    I have setup the following (zkey_MS_KMS* are custom fields)

    Dataset 1 (Uses the Reporting Database):

    SELECT
    MSP_EpmProject_UserView.ProjectName,
    MSP_WssDeliverable_OlapView.Title AS Deliverable_Title,
    MSP_WssIssue_OlapView.Title AS Issue_Title,
    MSP_WssRisk_OlapView.Title AS Risk_Title,
    MSP_EpmProject_UserView.[zKey MS KMS1],
    MSP_EpmProject_UserView.[zKey MS KMS2],
    MSP_EpmProject_UserView.[zKey MS KMS3],
    MSP_EpmProject_UserView.[zKey MS KMS4],
    MSP_EpmProject_UserView.[zKey MS KMS5],
    MSP_EpmProject_UserView.[zKey MS KMS6],
    MSP_EpmProject_UserView.[zKey MS KMS7],
    MSP_EpmProject_UserView.[zKey MS KMS8],
    MSP_EpmProject_UserView.[zKey MS KMS9]

    FROM
    MSP_EpmProject_UserView

    INNER JOIN
    MSP_WssIssue_OlapView ON MSP_EpmProject_UserView.ProjectUID = MSP_WssIssue_OlapView.ProjectUID
    INNER JOIN
    MSP_WssDeliverable_OlapView ON MSP_EpmProject_UserView.ProjectUID = MSP_WssDeliverable_OlapView.ProjectUID
    INNER JOIN
    MSP_WssRisk_OlapView ON MSP_EpmProject_UserView.ProjectUID = MSP_WssRisk_OlapView.ProjectUID

    Where MSP_EPMProject_Userview.ProjectUID = @ProjectName

    Dataset 2 (Also uses the Reporting Database):

    SELECT ProjectUID, ProjectName FROM MSP_EpmProject_UserView

    ProjectName (Only Parameter):

    Uses 'Dataset 2', with 'ProjectUID' as value field and 'ProjectName' as Label Field

    Any ideas? I have obviously missed something, maybe something simple.


    Rob Hardy
    Monday, November 21, 2011 11:39 PM
  • the Where clause in your first dataset seems to pass your project name parameter into the ProjectUID.

    I think it should be: Where MSP_EpmProject_UserView.ProjectName = @ProjectName


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Tuesday, November 22, 2011 2:00 AM
  • Hi Rod again!

    As Brian pointed, you have to make a little change in the Where clause, but I would do it in this way:

    Where MSP_EPMProject_Userview.ProjectUID = @ProjectUID.

    So, as you said too, you are missing something. Im gonna provide you some steps to attain your aim.

    First thing, you cannot get the projectUID just with SQL (SQL datasets dont know in which workspace you are working). You have to develop a webpart that implements a ReportViewer item.

    The solution is not so easy as you want, and not so difficult as you can imagine. I post you some code, supposing your Integration SSRS with Sharepoint is in Native Mode:

    reportViewer1 = new ReportViewer();
    reportViewer1.Width = System.Web.UI.WebControls.Unit.Pixel(600);
    reportViewer1.ProcessingMode = ProcessingMode.Remote;
    reportViewer1.ServerReport.ReportServerUrl = new Uri(ReportServer);
    reportViewer1.ServerReport.ReportPath =  ReportPath;                reportViewer1.ServerReport.ReportServerCredentials = new CustomReportCredentials("user", "password", "domain");
    List<ReportParameter> paramList = new List<ReportParameter>();

    //This is the important thing, the parameter has to have the same name as you put as parameter in your datasets!! And the GetProjectId() is the function I provided you above
    paramList.Add(new ReportParameter("ProjectUID", GetProjectId(), false));
    reportViewer1.ServerReport.SetParameters(paramList);
    this.Controls.Add(reportViewer1);

    Hope that helps you, and more people!!

    Kind regards!

     


    Miguel Soler
    Tuesday, November 22, 2011 8:21 AM
  • Hi

    So I still hadn't hit the nail on the head with the above yet, but I now have a report with two parameters both using the same dataset which points at the MSP_EpmProject_UserView view in the Reporting DB;

    Parameter 1 brings back the project site data (Value field = ProjectWorkspaceInternalHRef, Label = ProjectName)

    Parameter 2 brings back the project custom field data (Value Field = ProjectUID, Label = ProjectName)

    This is working nicely, however both drop downs (parameters) ideally I would like to merge into one ProjectName drop down. Any ideas on the easiest approach?


    Rob Hardy
    Wednesday, November 23, 2011 1:29 PM
  • As I understand, you want to show for each workspace its project Information (also the workspace information). That is to say, you want that the report understand in which workspace and show its information, no??

    Miguel Soler
    Wednesday, November 23, 2011 1:55 PM
  • If I am understanding your request right you would like to have a single parameter in the report which displays both the project name and the project site URL but the value which is retained for that selection would be the Proect GUID. In that scenario, use the following:

    SELECT
        ProjectUID AS 'ParameterValueField'
        ,ProjectName + ' - ' + ProjectWorkspaceInternalHref AS 'ParameterLabelField'
    FROM MSP_EPMProject_UserView

     


    Regards, Piet Remen
    Wednesday, November 23, 2011 3:32 PM
  • Hi,

    So my objective is to have one parameter which presents a Project Name.

    So my report as it stands, has two parameter boxes which both present Project Names for selection. If I select the project name in both boxes, the correct data is shown in the report.

    • Parameter 1 brings back the project site data (Value field = ProjectWorkspaceInternalHRef, Label = ProjectName)
    • Parameter 2 brings back the project custom field data (Value Field = ProjectUID, Label = ProjectName)

    What I want though, is to just have one parameter box, since the ProjectName field is coming from the same 'MSP_EpmProject_UserView' in the reporting db anyway.......

    Its just I had to put an additional XML datasource in for the workspace list data as well;

    =Parameters!ProjectSelect.Value & "/_vti_bin/lists.asmx"

    The custom field data obvisouly doesnt leverage this data source and just directly comes from the table. And hence I cannot figure out how to just have one parameter (its all new to me)

    Hope that makes more sence - really appreciate the help.


    Rob Hardy

    • Edited by EPM Tester Wednesday, November 23, 2011 4:16 PM
    Wednesday, November 23, 2011 4:11 PM
  • Hi,

    Has anybody got any idea how I can have just one parameter based on the above post I made?


    Rob Hardy
    Thursday, November 24, 2011 4:50 PM
  • Hi Rob. One concept i am thinking of perhaps would be to leave parameter 2 as the main parameter. Create a new dataset query which is filtered by the returned value of parameter 2. For example, create a new data set called "SelectedProject".

    SELECT
        p.ProjectWorkspaceInternalHref
    FROM MSP_EPMProject_UserView AS p
    WHERE p.ProjectUID = @Parameter2

    replace @Parameter2 with the name as your current parameter 2 name. Remeber to prefix with an "@" character to represent it as a parameter.

    Now this works on the assumption that you are only returning a single project other wise you may have to get a little more fancy. If it's safe to assume a single project will always be returned then you will be able to reference the first value returned by this dataset in any area of your SSRS report. Then try pass this returned field to your second query which requires a Project site URL. If not possible, you could try creating a hidden parameter and set its default value from the query above which would be a place holder for the project site, then use the hidden parameter to filter your query. Hope this makes sense but its tricky stuff. Have a go and let us know how you get on Rob.


    Regards, Piet Remen
    • Marked as answer by EPM Tester Friday, November 25, 2011 11:53 AM
    Friday, November 25, 2011 12:11 AM
  • Thanks Piet, thats great, that pointed me in the right direction.

    The solution was a combination of the above tips from people, so thanks.


    Rob Hardy
    Friday, November 25, 2011 11:53 AM