Call SRS subscription from SQL

Hi,

The other an IT guy at the customer asked me if it were possible to somehow trigger an ss report creation in SQL. After some digging, here's the way I found to achieve just that:

1. Create a data driven subscription

2. Choose Scheduled, run once, set the date any time before today so it doesn’t execute

3. Find the scheduleid with:

select * from reportserver..reportschedule

4. Call subscription with:

exec msdb.dbo.sp_start_job 'YourScheduleId'

go

As simple as that!

How to embed a report in an associated view - Crm 4.0

Hi everyone,

Some time ago, I decided to do just that: embed a report in an associated view.

This, of course, is totally and completely unsupported, and might break with the next rollup.

So, in Crm there is the entity campaign, that everyone here knows well. We have created a entity called crm_member, to record the participants to a marketing campaign. There is a one to many relationship between campaign and crm_entity. We decided to do that because heavy customizations were required, which are not possible while using only marketing lists.

So, with this new entity ready, there is now in each campaign the link to the members associated view of that campaign.

I decided to put a button in the associated view, to replace the grid with a report allowing users to have some business intelligence on these members. Indeed, there may be a fow thousand members participating to a campaign.

Here we go.

1. Set the button in the isv.config, in the entity crm_member.

<Entity name="crm_member">
  <grid>
    <MenuBar>
      <Buttons>
        <Button Icon="http://server/isv/icons/icon.gif" JavaScript="">
          <Titles>
            <Title LCID="1033" Text="" />
          </Titles>
          <ToolTips>
            <ToolTip LCID="1033" Text="Campaign Participants" />
          </ToolTips>
        </Button>
      </Buttons>
    </MenuBar>
  </Grid>
</Entity>

2. In the JavaScript="" part, set the function. Here's what I came up with to get this to work:

if (window.parent.document.all.crmForm.ObjectTypeCode!=4400)
{
  alert('You can only get detail from within an action');
}
else
{
  if (document.all.crmGrid.tagName == 'IFRAME')
  {
    document.all.crmGrid.outerHTML = outerHTML;
    document.all.crmGrid.Refresh();
  }
  else
  {
    var oId = document.all.crmGrid.GetParameter('oId');
    var reportURL = "myReportUrl";
    var frame = String.fromCharCode(60)+'iframe id='+String.fromCharCode(39)+'crmGrid'+String.fromCharCode(39)+' style='+String.fromCharCode(39)+'width:100%;height:100%;' +String.fromCharCode(39)+' src=' +String.fromCharCode(39)+reportURL+String.fromCharCode(39)+String.fromCharCode(62) + String.fromCharCode(60) + String.fromCharCode(47) + 'iframe' + String.fromCharCode(62);
    var outerHTML = document.all.crmGrid.outerHTML;
    document.all.crmGrid.outerHTML = frame;
  }
}

Explanation:

1. Get campaign id in the associated view
In this post from the excellent Customer Effective blog, we learn that this code:
document.all.crmGrid.GetParameter('oId')
is used in a grid to "get object id of main record when grid is an associated view".
So we have the campaign id.

2. We create an iframe with the report url as source.

3. Save the current html code displayed in the grid:
var outerHTML = document.all.crmGrid.outerHTML;
This code will allow us to redisplay the associated view.

4. Display our report:
document.all.crmGrid.outerHTML = frame;
So here, the html code of the crmGrid is replaced by our code, when the button is pushed.

5. Redisplay the associated view

if (document.all.crmGrid.tagName == 'IFRAME')
{
  document.all.crmGrid.outerHTML = outerHTML;
  document.all.crmGrid.Refresh();
}
When the same button is clicked again, if the crmGrid contains an iframe (our iframe), then replace the html by the previously saved html, corresponding to the associated view-grid.
Refresh the records displayed.

That's it!

A simple workaround to the SetFocus non sticking issue (Crm 4.0)

The Sdk is pretty clear: the SetFocus function "Sets the focus, changes tabs, and scrolls the window as necessary to show the specified field."

crmForm.all.crm_myfield.SetFocus();

At our customer, what this does is: when the form opens, the focus is changed only for like one milisecond or so and then switches back to default focus, first tab, first field I think. This bug is supposed to be fixed since Update Rollup 1 for Crm 4.0. There is a specific hotfix included in that rollup. And for the record, there was the exact same bug in Crm 3.0, here is the kb. Problem, the issue still happens at the customer, so I have to fix it.

Not so easy to solve at first sight. But after some good thinking, here's the solution I came up with :

var oField = crmForm.all.crm_myfield;
oField.onblur = function() {
  oField.SetFocus();
  oField.onblur = null;
}
oField.SetFocus();

Word of explanation: the onblur event is triggered when an element loses the focus.
So, crmForm loads, I call SetFocus, the field receives the focus and after one milisec loses it. So the bug has been executed. Then, onblur is called, and the field gets the focus again. To allow the user to click on other field, remove the onblur event. This is a quite simple and effective workaround.

Untested on Crm 3.0, but probably works.

That's it!

Set Focus on a field when opening record from srs report

Hi everyone,

With Crm 4.0, a customer recently asked me to develop a new report with Sql Server Reporting Services, that would display different records from an entity, with the fields belonging mainly to that entity. Each field should be a link to the corresponding record.

Here's the interesting part: when clicking on a particular field, the record should open with the focus set on that field.

Remember that if you have multiple tabs, simply setting the focus on the field you want will have the correct tab displayed.

So, 4 steps here :

1. First, the links in the report.
2. How to say to a crmForm which field to set focus on?
3. Retrieve the value in crmForm
4. Set the focus on that field

Let's go!

1. First, the links in the report.

As a small reminder, here's an exemple:

="http://crm/myorg/userdefined/edit.aspx?id={" + Fields!entityid.Value.ToString() + "}&etn=crm_myentity"

Notice the use of etn (Entity Type Name), much preferable to etc (Entity Type Code), which is a number and changes between crm implementations, thus from dev to prod for example.

2. How to say to a crmForm which field to set focus on?

As explained in the sdk, when you open a new form, using the URL address you can include arguments in the query string to set field values. The following requirements must be met:

• The names of the query string arguments must match or include the names of attributes for the entity.
• Lookup and Customer lookup fields should use the suffix 'name' with the attribute name to set the text to display.
• Customer lookup fields must use the suffix 'type' with the attribute name to set whether the reference is to an account or a contact.
• The values passed must be valid.
• The value cannot be a script.

Note: This does not work with activities.

So, now we know it is possible to pass values to a crmForm, the only condition being that the parameter name must be the name of an existing attribute.
Thus, create a new varchar attribute, with enough chars for your needs.
Example: crm_focus. No need to put the attribute on the form!

Now, we can change the url in the report to this:

="http://crm/myorg/userdefined/edit.aspx?id={" + Fields!entityid.Value.ToString() + "}&etn=crm_myentity&crm_focus=cm_attribute_to_focus"

Note: when you try to pass a parameter name that is not the name of an attribute, crm will just open the error form.

3. Retrieve the value in crmForm

For this, 2 possible solutions that I know of.

A. Use gup (Get Url Parameter) function, that you find about anywhere on the net, here for example.

function gup( name )
{
  name = name.replace(/[\[]/,"\\\[").replace(/[\]]/,"\\\]");
  var regexS = "[\\?&]"+name+"=([^&#]*)";
  var regex = new RegExp( regexS );
  var results = regex.exec( window.location.href );
  if( results == null )
    return "";
  else
    return results[1];
}

So in the onload of your crmForm, just use :

var focus = gup('crm_focus');

B. See this page on StunnWare, where Crm Mvp Michael Höhne, among other things, describes a nice ParseQueryString() function, which results in this:

var QueryString = ParseQueryString();
var template = QueryString["template"];

On that page, Michael gives other nice and interesting JavaScript tricks, worth a read.

4. Set the focus on that field

So, here we go. In my focus variable, I have the name of the field. So, I try to use the crm function SetFocus, defined in the Sdk :

var oField = document.getElementById(focus);
oField.SetFocus();

Horror! When the form opens, the focus is changed only for like one milisecond or so and then switches back to default focus, first tab, first field I think.
This bug is supposed to fixed since Update Rollup 1 for Crm 4.0. There is a specific hotfix included in that rollup. And for the record, there was the exact same bug in Crm 3.0, here is the kb. Problem, the issue still happens at the customer, so I have to fix it.

Not so easy to solve at first sight. But after some good thinking, here's the solution I came up with :

var focus = gup('crm_focus');
var oField = document.getElementById(focus);
oField.onblur = function() {
  oField.SetFocus();
  oField.onblur = null;
}
oField.SetFocus();

Word of explanation: the onblur event is triggered when an element loses the focus.
So, crmForm loads, I call SetFocus, the field receives the focus and after one milisec loses it. So the bug has been executed. Then, onblur is called, and the field gets the focus again. To allow the user to click on other field, remove the onblur event. This is a quite simple and effective workaround.

That's it!!