each record in multiple page in ssrs

Sinario: Need to Show each Record in Multiple Pages.

for this first take the list item from toolbox
place it in designer place.

need to give the details in the list :

first Group the filed.

here i grouped : new_name.

and give the expression: =Ceiling((RowNumber(Nothing))/1)
after that go to RowGroup by right clicking on that.
remove the sorting filed.
give the page breaker.

then run the report.

Fillter or Act based on User role in Dynamic CRM 2011

situation: if we want filter optional set based on user roles or disable enable a field based on user role
for this we need to write a javascript code as below.


function currentuserroles()
{

   var xml = "" +
  "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
  "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
  Xrm.Page.context.getAuthenticationHeader() +
  " <soap:Body>" +
  " <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
   " <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
   " <q1:EntityName>role</q1:EntityName>" +
  " <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
  " <q1:Attributes>" +
  " <q1:Attribute>name</q1:Attribute>" +
  " </q1:Attributes>" +
  " </q1:ColumnSet>" +
  " <q1:Distinct>false</q1:Distinct>" +
  " <q1:LinkEntities>" +
  " <q1:LinkEntity>" +
  " <q1:LinkFromAttributeName>roleid</q1:LinkFromAttributeName>" +
  " <q1:LinkFromEntityName>role</q1:LinkFromEntityName>" +
  " <q1:LinkToEntityName>systemuserroles</q1:LinkToEntityName>" +
  " <q1:LinkToAttributeName>roleid</q1:LinkToAttributeName>" +
  " <q1:JoinOperator>Inner</q1:JoinOperator>" +
  " <q1:LinkEntities>" +
  " <q1:LinkEntity>" +
  " <q1:LinkFromAttributeName>systemuserid</q1:LinkFromAttributeName>" +
  " <q1:LinkFromEntityName>systemuserroles</q1:LinkFromEntityName>" +
  " <q1:LinkToEntityName>systemuser</q1:LinkToEntityName>" +
  " <q1:LinkToAttributeName>systemuserid</q1:LinkToAttributeName>" +
  " <q1:JoinOperator>Inner</q1:JoinOperator>" +
  " <q1:LinkCriteria>" +
  " <q1:FilterOperator>And</q1:FilterOperator>" +
  " <q1:Conditions>" +
  " <q1:Condition>" +
  " <q1:AttributeName>systemuserid</q1:AttributeName>" +
  " <q1:Operator>EqualUserId</q1:Operator>" +
  " </q1:Condition>" +
  " </q1:Conditions>" +
  " </q1:LinkCriteria>" +
  " </q1:LinkEntity>" +
  " </q1:LinkEntities>" +
  " </q1:LinkEntity>" +
  " </q1:LinkEntities>" +
  " </query>" +
  " </RetrieveMultiple>" +
  " </soap:Body>" +
  "</soap:Envelope>" +
  "";

  var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

  xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
  xmlHttpRequest.setRequestHeader("SOAPAction"," http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
  xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
  xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
  xmlHttpRequest.send(xml);

  var resultXml = xmlHttpRequest.responseXML;
  //return(resultXml);

//alert(resultXml);

    //get Current User Roles, oXml is an object
    if (resultXml != null) {
        //select the node text
        var roles = resultXml.selectNodes("//BusinessEntity/q1:name");
//alert(roles);
//alert(roles.length);
        if (roles != null) {
            for (i = 0; i < roles.length; i++) {
           var rolename=roles[i].text;
//alert(rolename);
     
       if((rolename=="System Customizer")||(rolename=="System Administrator"))
     {
//Xrm.Page.getControl("tra_typeofcustomer").removeOption(167490004);
Xrm.Page.getControl("statuscode").removeOption(2);
Xrm.Page.getControl("statuscode").removeOption(3);
Xrm.Page.getControl("statuscode").removeOption(4);
     }


      }

        }
    }

}

here we need to change the roles accordingly.  but copy and paste full length of the code.

in bottom we have the options to custamize out code.

Some information in SSRS

Good tutorial:

http://www.w3schools.com/sql/sql_func_now.asp

http://www.xrm.sk/blog/2009/05/crm-reporting-tips-and-hints-ii-crmaf-binding-not-working/


To Get only Date value :

if you are getting the Date and Time then this is the method to diffentiate the date.

=FormatDateTime(Fields!scheduledend.Value,DateFormat.ShortDate)
ex: 05/15/2012.

for multiple dataset:

=FormatDateTime(Fields!scheduledend.Value,DateFormat.ShortDate)

=First(Fields!createdon.Value, "withsupplierandstore")

=FormatDateTime(First(Fields!createdon.Value, "withsupplierandstore"),DateFormat.ShortDate)


http://www.dotnetspider.com/resources/42874-SSRS-SQL-Server-Reporting-Services.aspx

use full expressions:
http://technet.microsoft.com/en-us/library/ms157328.aspx

Counting the Rows:

http://www.techonthenet.com/sql/count.php

if we want to give the no.of calls made a person then:


SELECT     createdbyname, COUNT(DISTINCT activityid) AS calls
FROM         FilteredAppointment
GROUP BY createdbyname

here we will get the result with name and count of activities.

Owner	Appointment
Akilan s	2
Damodaram Karnam	3

Another Method:

Give the Select count(*) from (  select * from entity where condition )

ex:

SELECT     COUNT(*)
FROM         (SELECT     createdbyname, createdon, new_name, new_regionofname, new_stockavilability, new_stockavilabilityname, new_stockavilabledate,
                                              new_stockavilabledateutc, owneridname
                       FROM          Filterednew_cooldrink)


with where condition:


SELECT     createdbyname, COUNT(DISTINCT activityid) AS calls
FROM         FilteredAppointment
WHERE     (CONVERT(varchar, createdon, 103) = CONVERT(varchar, GETDATE(), 103))
GROUP BY createdbyname



Formating time : Removing Seconds from the time:

(SELECT CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 101) + ' ' + RIGHT(CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 100), 7) )
                      AS scheduledstart


this is the logic.

where we need to place it.

in query builder. Query looks like.
select new_name, (SELECT CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 101) + ' ' + RIGHT(CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 100), 7) )
                      AS scheduledstart from FilteredAppointment
WHERE     (CONVERT(varchar, createdon, 103) >= CONVERT(varchar, GETDATE() - 5, 103))

then we will get the week data.

Getting Every Record in Different Page:

=Ceiling((RowNumber(Nothing))/1)
check the below link
http://sqlserverrider.wordpress.com/2011/06/20/page-break-at-nth-row-in-ssrs-report/


Giving distinct values in parameter:

For this first we need to create DataSet2 with the distinct condition

SELECT DISTINCT  statuscode, statuscodename from FilteredAccount.
then create a parameter, it will show the distinct values.

Getting the user permission to deploy the report:
http://sanganakauthority.blogspot.in/2012/01/verify-that-sufficient-permissions-have.html

first go to the start and select the Internet Explorer.
Run as Administrator.

give the link like : http://servername/reports
you will see the foldersettings
click on that and add the new role assign.

Optional set Lable in SSRS

generally we are using views to do SSRS.

in views we have FILTERED VIEWS.
if we use Filtered Views then we will get the Optional set names.

like if we have one entity new_cooldrink
use the filtered view : Filterednew_cooldrink  in the query designer.

we will get this Filtered views in VIEWS tab.

by using this we will get the attribute like
optionalsetName attribute
by using this in report we will get the solution.

Email Report Delivery Option in SSRS

By Default I didn't get the Email option for Report Delivery option in Scheduling a SSRS.

for this : Run the configuration tool as a Administration ( right click then we will get).

go to Email-setting,

give the details like this 

ex:

  sender : damu@domaim.com

using : SMTP;

SMTP server name : mail.domain.com

for the SMTP server name:

go to outlook

click on File

click on Account settings

here u will get the name and type text box

double click on that

u will get the server information.

Filter Data Based on Today date in SSRS

For this,
we need to write a query in Query designer.

Ex:


SELECT     CreatedByName, OwnerIdName, ActivityTypeCode, CreatedOn, Subject, ServiceIdName, ActivityId
FROM         ActivityPointer
WHERE     (CONVERT(varchar, CreatedOn, 103) = CONVERT(varchar, GETDATE(), 103))

here : 


Today report


 WHERE (CONVERT(varchar, CreatedOn, 103) = CONVERT(varchar, GETDATE(), 103))
is the logic for getting records from current date.

Yesterday report


WHERE     (CONVERT(varchar, createdon, 103) = CONVERT(varchar, GETDATE() - 1, 103))

for Weekly Report:

WHERE  (DATEPART(week, createdon) = DATEPART(week, GETDATE()) - 1)

for monthly report

WHERE     (DATEPART(month, createdon) = DATEPART(month, GETDATE())-1)

Removing Seconds from Date and Time:


SELECT CONVERT(VARCHAR,scheduledstart,101)  +
RIGHT (CONVERT(VARCHAR,scheduledstart , 100 ) ,7)

this will give the result: mm/dd/yy hh:mm am/pm


Getting only Date:


=FormatDateTime(Fields!scheduledend.Value,DateFormat.ShortDate)

Formating time : Removing Seconds from the time:



(SELECT CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 101) + ' ' + RIGHT(CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 100), 7) AS Expr1)
                      AS scheduledstart


this is the logic.

where we need to place it.

Example:


in query builder. Query looks like.
select new_name, (SELECT CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 101) + ' ' + RIGHT(CONVERT(VARCHAR, FilteredAppointment.scheduledstart, 100), 7) AS Expr1)
                      AS scheduledstart from FilteredAppointment
WHERE     (CONVERT(varchar, createdon, 103) >= CONVERT(varchar, GETDATE() - 5, 103))

then we will get the week data.

scheduling a SSRS

For this first create a report,
follow the link:
http://snippetsandhelp.blogspot.in/2011/02/ssrs-scheduling-reports-using-sql.html

for this give link like:  http://servername/Reports/Pages/Folder.aspx
here we will get the folders of the reports.

choose which report need to schedule.

choose create Linked Report option:

give the name like : damu.

click ok and click on DataSource tab:

then choose the Subscription option:
we will see the options:


then choose the method of delivery



here need to give a valid path

PATH is : right click on perticular folder and go to properities,
 take the sharing tab.
there we will get the path.

it is network path:

THEN do the schedule.

hide a section in dynamic crm 2011

In single line we can hide a section with the java script:

Xrm.Page.ui.tabs.get("tab_name").sections.get("section_name").setVisible(true);

on some conditions:

function RefundHide()
{

var cat = Xrm.Page.getAttribute("new_city").getValue();
var subcat = Xrm.Page.getAttribute("new_state").getValue();
if(cat ==  852700002 && subcat == 852700014)
{
Xrm.Page.ui.tabs.get("general").sections.get("refund").setVisible(true);
}
}


function hide()
{
var type = Xrm.Page.getAttribute("new_city").getValue();
if (type == null)
{
 Xrm.Page.ui.controls.get("new_state").setDisabled(true);
}
}



function SendSMS()
{
Xrm.Page.data.entity.save("save");
}


function PicklistOneOnchange()
{

var optionsetControl = Xrm.Page.ui.controls.get("new_state");
var options = optionsetControl.getAttribute().getOptions();
var type = Xrm.Page.getAttribute("new_city").getValue();
if (type == null)
{
 Xrm.Page.ui.controls.get(" new_state ").setDisabled(true);
}
optionsetControl.clearOptions();

if (type == 852700000)
{
Xrm.Page.ui.controls.get(" new_state ").setDisabled(false);
for (var i = 1; i <7; i++)
 optionsetControl.addOption(options[i]);
}
if (type == 852700001)
{
Xrm.Page.ui.controls.get(" new_state ").setDisabled(false);
for (var i = 7; i < 10; i++)
  optionsetControl.addOption(options[i]);
}

if (type == 852700002)
{
Xrm.Page.ui.controls.get(" new_state ").setDisabled(false);
for (var i = 10; i < 16; i++)
optionsetControl.addOption(options[i]);
}

if (type == 852700003)
{
Xrm.Page.ui.controls.get(" new_state ").setDisabled(false);
for (var i = 16; i < 19; i++)
optionsetControl.addOption(options[i]);
}
}

for some reference:

http://www.powerobjects.com/blog/2011/01/14/crm-2011-useful-javascript-tidbits/

http://crmbusiness.wordpress.com/2011/02/17/crm-2011-javascript-xrm-page-basics/

Setting Field Required (require)

function resolutionstatus()
{
var type = Xrm.Page.getAttribute("new_resolutionstatus").getValue();
if (type == 852700005)
{
Xrm.Page.ui.controls.get("new_rejectreason").setVisible(true);
Xrm.Page.getAttribute("new_rejectreason").setRequiredLevel("required");

}

else
{
Xrm.Page.getAttribute("new_rejectreason").setRequiredLevel("none");
Xrm.Page.ui.controls.get("new_rejectreason").setVisible(false);
}

}



Disable a field:


function hide()
{
var type = Xrm.Page.getAttribute("casetypecode").getValue();
if (type == null)
{
 Xrm.Page.ui.controls.get("new_subcategory").setDisabled(true);
}
}

calculating two fields:

function calculate()

   {

       var val1 = Xrm.Page.entity.attributes.get['new_val1'].getValue();

      var val2 = Xrm.Page.entity.attributes.get['new_val2'].getValue

();

      if(val1==null)return;

     if(val2==null)return;

      var result = val1 * val2;

     Xrm.Page.entity.attributes.get['new_result'].setValue(result);

 }

http://community.dynamics.com/product/crm/crmtechnical/b/mshelp/archive/2011/03/14/creating-a-calculated-field-in-dynamics-crm2011.aspx