Calling an SSRS Report from a CRM form's ribbon button with a guid

• j-1064772

  SSCertifiable

  Points: 5316

  More actions

  April 8, 2014 at 11:48 am

  #292072

  First, a long-winded description of the situation.

  Pretty much basic stuff, really.

  A report developed in Visual Studio 2K8 w/BIDS extension is imported into CRM 2011 as a linked report which is rendered in a web page after pressing a button in a CRM form's ribbon. That was the easy part.

  Let's say we are talking about a summary data sheet for an account. The report developed in VS uses two embedded datasets. The first dataset uses an SQL statement to get the specific information of an account by passing it the accountId parameter value. Such as

  [font="Comic Sans MS"]And NO, I will not use FetchXML because when I learned it a few months ago it provided absolutely no support for a simple, elementary construct like [/font][font="Courier New"]select Name, count(orders) from Account group by Name[/font].

  [font="Courier New"]SELECT * FROM Account WHERE accountId = @pu_AccountId[/font]

  The second dataset (ds_AccountsList) is used to provide a list of accounts to select from in a combo control on the report.

  [font="Courier New"]SELECT accountId, Name FROM Account[/font]

  A parameter of the same name is created in the SSRS report and dragged onto the body and marked as visible. Its properties are set as follows:

  [font="Courier New"]Data type: Text

  Available values: Get values from a query

  Dataset: ds_AccountsList

  Value: accountId

  Label: Name[/font]

  When the report is run in Visual Studio, the user is presented with a combo control and its drop down window is the list of account Names. The user selects an account and presses the [font="Courier New"]View Report[/font] in the [font="Courier New"]Preview tab[/font] of the VS design window pane.

  The CRM Account form's ribbon button has been properly set to return the accountId (guid) within the url of the report's rendering page:

  [font="Courier New"]http://myCRMserver/Reports/Pages/Report.aspx?ItemPath=%2fCRM-Reports%2fAccount Datasheet%29&id=%7b1CCE0A94-AA90-E211-B216-000C2965EDF5%7d&orglcid=1033&orgname=myOrg&type=1&typename=account&userlcid=1033

  1CCE0A94-AA90-E211-B216-000C2965EDF5 [/font] is the accountId returned by the CRM.

  Again, the user is shown the parameter combo from which to select an account for which the datasheet will be generated.

  I can either call a report from CRM by three different routes:

  1) [font="Courier New"]Worplace -> Reports[/font] a non contextual environment where the user must chose the account from the combo

  2) [font="Courier New"]Sales -> Account[/font] which provides a matrix (table) of the accounts present in the CRM database.

  3) double-clicking one the table records in 2) above to open the Account form in CRM.

  The two latter access points ARE context environment, i.e. CRM calls up the report with the accountId in the URL. The first access point returns no id.

  And now, for the matter at hand:

  What needs to be done to the report so that when its web page is produced - when called up by the latter two access points in CRM as described above - that it be able to "catch" the accountId in the url and set AUTOMATICALLY the combo to the account corresponding to the accountId in the url ?

  Also, when the two context-based access points are used, force the visibility state of the parameter combo to false ?

  Is this done by a BASIC script within the report rdl file itself or is this something done by some JavaScript to be added to the CRM Account form ?

  Or a combination of both ?