Crystal Reports – Using Shared Variable in version 8.5

February 28, 2014

This example show how to use Shared Variables in CR 8.5

I know it is an old version but the principles can be used in later versions as well.

Objective was to transfer a value from a sub-report into the main report for further calculations.

1. First create the sub-report, and link it to the Main report using the Insert Menu

image

2. When the link has been setup, insert a new Formula Field

image

In the new formula field you add a Shared Variable.

WhilePrintingRecords;
Shared NumberVar myTotal :=Sum ({Your.Field})

3. Next you go the Main Report and add as well a new Formula field.

image

In the new formula field you add reference to the previous  Shared Variable, of the sub-report.

WhilePrintingRecords;
Shared NumberVar myTotal;
myTotal;

4. Add the new Formula field in the main report

image

Keep in mind that you need to add the field in a SECTION BELOW the detailed section of the main report. In order for the variable to grab the values correct.

5. Making calculations using Shared Variables.

You can use the this just like any other field value to make your calculations.

WhilePrintingRecords;
Shared NumberVar myTotal;
If {Your.Field} <> 0 then
myTotal / {Your.Field};

Tips:

To get the sub-report field properly formatted in your report it’s a bit tricky.

Remove the BORDERS and if needed uncheck the Keep Objects Together

image

 

image

Enjoy !!

Advertisements

SharePoint – Deciding Which API to Use

February 26, 2014

When deciding to start developing in SharePoint you first need to know which API’s / Tools and Technologies are available.

Deciding Which SharePoint 2010 API to Use

There are 2 types the Server-Side and the Client Side ones.

Server-side Object Model

Client Object Model

Ok now we know both API categories, how can we use it in our Scripting / Programming experience.

First we need to decide which language we want to use to access the API’s/

Powershell
JavaScript / Jquery

.NET c# or VB.NET
ASP.NET

Which Tools and Technologies do you need to master.

MS SharePoint Designer
XSL
HTML
CSS3
XML

MS Silverlight

Mobile Apps
HTML5

SharePoint 2010 Web Services
SOAP / REST
SharePoint Query String (URL)

SharePoint Designer Features for Developers
Workflows
Business Data Connectivity

Database Management
MS SQL DB & Admin
MS SSRS (integrated mode)

MS Report Builder
Query language
CAML query syntax
LINQ query syntax

MS Windows Server 2008 or Higher
Active Directory
MS IIS
SharePoint Security Model

Maybe I forgot some, but these are the one I got my feet wet in.

So if you decide to take a dive into SharePoint development. You run into an elephant Winking smile

This is a good comprehensive overview as well : SharePoint Architecture.

Here you can find some examples on how to access the different Interfaces

 


SharePoint – XSL Error Hyperlink already used by another control

February 21, 2014

Ever got this error coming up ?

image

I did ?

This custom hyperlink control I added to a list and got the error while debugging ?

image

Seemed obvious afterwards

The Hyperlink had to be appearing in a Grid multiple times.

Solution :

Just add {$ID}{generate-id()} to the ID

image

Solved !

Enjoy Smile


SharePoint – List add distinct Parameter values

February 17, 2014

Unfortunately the CAML syntax does not support the DISTINCT keyword. I needed this this fill in a parameter for filtering purposes.

When searching the net the only solution was to add some CODE to the report Surprised smile

Luckily that guys had the code snippet posted. So here we go.

You need 2 datasets. 1 main dataset you will display on the report. And a second one that you will use to retrieve the Parameter Field from.

image

In the Projects dataset are all the NON DISTINCT BU names.

1. So we need to create a normal Parameter to get these values from the list.

General settings, check Allow Multiple Values and make it Hidden

clip_image002[4]

Available values is picked up from the Query.

clip_image004

Don’t forget to fill in the Default Values

clip_image006

2. Next add the piece of code I found in the net. It will REMOVE all DUPLICATES from the list values.

clip_image008

 
Public Shared Function RemoveDups(ByVal items As String) As String 

Dim noDups As New System.Collections.ArrayList() 
Dim SpStr 

SpStr = Split(items ,",") 
For i As Integer=0 To Ubound(Spstr) 

 If Not noDups.Contains(SpStr(i).Trim()) Then 

  noDups.Add(SpStr(i).Trim()) 

 End If 
Next 

Dim uniqueItems As String() = New String(noDups.Count-1){} 
  noDups.CopyTo(uniqueItems) 

    Return String.Join(",", uniqueItems) 

End Function 

3. Next add a second Parameter that will retrieve the DISTINCT Parameter Values.

clip_image010

This will be the Parameter that is Visible in the report.

clip_image012

The Available Values will be a FORMULA EXPRESSION

clip_image014

=Split(Code.RemoveDups(JOIN(Parameters!BU.Value, “,”)), “,”)

clip_image016

For the DEFAULT VALUES you add the same Expression.

clip_image018

4. Add the FILTER to the TABLIX in the report and you are ready to go.

clip_image020

clip_image022

Thanks for the guys who supplied the code snippet.


SSRS – Report Builder Parameter Error when using a SharePoint List

February 16, 2014

Some time back I was struggling to get a Parameter working for a SharePoint list.

The report had 2 datasets, and the parameter had to come from the second dataset. Which was a Calculated Feld using a LOOKUP FIELD by the way.

image

So the BU parameter was a lookup value from the second dataset PROJECTS. So I though just as straightforward a calculated field to the primary dataset and that’s it.

But apparently it a parameter does not accept a calculated field that hold a lookup function Sad smile

image

Solution :

Remove the calculated field from the primary dataset.

Go to the TABLIX Properties and go to FILTERS, and add the Lookup Field expression there to filter the returned items in the list.

image

It is working well now.

image

Done !


SharePoint – hresult: 0x80131904 Error !

February 6, 2014

Got an urgent call from users not being able to ADD new Records in a certain SharePoint Collection ?

The famous correlation error appeared ?

image

Which does not tell a lot Sad smile So I had to dig a bit deeper in the ULS logs.

There error was visible immediately !

image

Database full error on SQL Server instance ‘MY_SERVER’ in database ‘WSS_Content’. Additional error information from SQL Server is included below.  Could not allocate space for object ‘dbo.EventLog’.’EventLog_Id’ in database ‘WSS_Content’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The last bit gave the hint that we might have run out of disk space ! Smile

And yes indeed that was it. So I move some files around and we where back in business.


Exchange 2010 – Client Connection Issues

February 4, 2014

A while back I got a lot of complaints coming in from all over the place, regarding client connection issues. Where clients who logged in early in the morning where able to log in and others where not ? Sad smile

Interesting challenge. But since we are servicing a multi Timezone user base across the world. The heat was rising.

So first I thought it would be a client / internet connectivity related issue. But I was wrong.

After Googling a bit I found out that our parameters on the server where not set optimal when added more and more users and email domains to Exchange 2010.

So here is the solution :

Open the EMC and go to the server properties. Next to the LIMIT tab.

image

Change the Maximum Concurrent outbound Settings (and per Domain) to a more relevant number. Default it is 1000 and 20.

You will need to restart the Exchange Transport service when finished modifying these settings.

Also check the HUB Transport Global Settings – Max number of Recipients

image

More info you can find using Powershell to get hold of the ThrottlingPolicy Settings

CLS

if(!(Get-PSSnapin | 
    Where-Object { `

    $_.name -eq "Microsoft.Exchange.Management.PowerShell.E2010"})) {
      ADD-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
    }
 
 Get-ThrottlingPolicy

Hope it can help solving your mysteries Winking smile