found some useful sql tips

·      Insert multiple rows

insert into tablename (col1, col2)

select ‘this1’, ‘that1’

union all

select ‘this2’, ‘that2’

union all

select ‘this3’, ‘that3’

union all

select ‘this4’, ‘that4’

From Personal Notes of Pinal Dave

·         Conditional Where Clause

select *

from   dbo.tblConditionalWhereClause

where  ((Businessdate = @BusinessDate) and (_NODEID_ = @nodeID))

              or     ((@BusinessDate is null) and (_NODEID_ = @nodeID))

              or     ((@nodeID is null) and (Businessdate = @BusinessDate))

              or  ((@BusinessDate is null) and (@nodeID is null))

logical where clause (Not A) or B means if A then B

create a child package and pass variables with this Execute Package Task

Now we need to create a child package.

1) create a new package and create new package variables

2) right click on the design screen and click Package Configurations

3) On the pop up window, enable package configurations and click Add...

4) click next and change Configuration type to Parent package variable and type a Parent variable

5) on the next screen, expand Variables and variable name(this time "logFileName") and expand its properties and click Value and click Next

6) then click next and you will see a new configuration on the Package Configurations Organizer window

7) repeat this if you have more than one variable to pass from parent to child package.
(In my project, I had to pass 3 variables - LogFileName, TxtFileName, and LogSource)

8) then build this package.

9) go back to parent package and modify Execute Package Task 

10) Location can be SQL server or file system depending on where you built your child package. 

SSIS 2008 - flat file source look up other flat file with foreach loop

I don't know what this title should be but here's simplified objectives


1. Pull from a specified directory all files matching a format of “007C0*.log
2. From each error log, you must retrieve the line number from the contents of the error log and the error message
3. Using the file name, pull from  another directory the file that has the same base name, but with a “txt” extension
4. Using the Line Number from the error log, pull the line data from the text file.
5. Use both the information from the error log and the upload file to populate the table

log file format (007C0*.log)

text file format (logfilename_datetimestamp.txt)

Objective 1 and 2 can be done in one foreach loop.
For objective 3, I needed one more foreach loop since text file consists of full log file name and datetimestamp.
For objective 4, I had to put a data into somewhere but I was asked not to put in our database so I had to use cache transform. That means, I had to create two packages - parent and child since cache transform cannot be placed in a for loop(connection problem).

So lets start!

1) create five variables.

2) put a foreach loop container and double click to open up an editor.

click Collection and click + sign to expand Expressions and click ... to put new values

3) Pick Directory and FileSpec  from Property column drop down menu.

and Click ... to add expression.

(In this example, FileSpec is a filename and  Directory is the logfilesource.)

4) click Variable Mappings tab and pick [User::LogFileName] and close an editor.

5) add a Script Task inside Foreach Loop Container. (need to get a log file name without a path and file extension)

Click ReadWriteVariables row and add [User::LogFileName] and click Edit Script

5.1) simple code

         public void Main()

        {   

            String str = Dts.Variables["User::LogFileName"].Value.ToString();            

            String logfilename = str.Substring(3, str.IndexOf(".")-3);

            Dts.Variables["User::LogFileName"].Value = logfilename;            

            Dts.TaskResult = (int)ScriptResults.Success;

        }

* the reason why I used "3"s in str.substring is that the path is "C:\". If your path(directory) is like "C:\Users\asung\Documents\Visual Studio 2008\Projects\Integration Services Project3" then you need to use a different number. 

6) add a new Foreach Loop Container inside and set it up like the one before

But this time FileSpec Expression should be @[User::LogFileName] + "*.txt"

7) click Variable Mappings tab and pick [User::TxtFileName] and close an editor.

8) add a Script Task inside an inner Foreach Loop Container and repeat step 5 with [User::TxtFileName]

8.1) just like 5.1

        public void Main()

        {

            String str = Dts.Variables["User::TxtFileName"].Value.ToString();

            Dts.Variables["User::TxtFileName"].Value = str.Substring(11);

            Dts.TaskResult = (int)ScriptResults.Success;

        }

9) add an Execute Package Task

Next is to create a child package and pass variables with this Execute Package Task.

simple tip - passing values from Infopath 2010 to sharepoint (easy way)

1. click File tab and click Publish and then click SharePoint Server Button

2. Enter the location of Sharepoint

3. click Next unless you want to use advanced settings

4. click Update the form template in an existing form library and select the name of your target list

5. click Add button and pick a field from the list

6. click Next and click Publish

It's done

I removed a phase column from the column view using site's modify view(under Library tools)

Infopath 2010 multiple views, repeating tables, and external database

This was my first infopath/sharepoint project last year.
It was straight forward and I was very glad that I had a chance to learn about infopath and sharepoint.
However, it was never published.. The reason? idk. :(

Here's the screenshot for an infopath form.

It has six different views for ItemAdded, ItemChanged, and ItemRemoved and three approval views.

Example of ItemAddedReview view



Each button(ItemAdded, ItemChanged, ItemRemoved) has only one rule. - Switch to view:viewname

Let's take a look at an ItemAdded view table.
I created a section and put a repeating table and buttons for other views.

Here are some rules for textboxes.

ItemCount_ItemAdded field is for counting how many row were added.
CatLvl1Changes field has one action rule to set Catlvl2_ItemAdded, Catlvl3_ItemAdded, Catlvl4_ItemAdded to blank when the field changes
CatLvl2Changes field has one action rule to set Catlvl3_ItemAdded, Catlvl4_ItemAdded to blank when the field changes
CatLvl3Changes field has one action rule to set Catlvl4_ItemAdded to blank when the field changes

here's drop-down box properties for CatLvl1 and CatLvl2

There are two ways of getting data for multiple drop-down box data. Maybe more....
First is that you create a full database table with all information  like this

so all drop-down box don't have to compare its value with other drop-down box values.

Another way of doing this is comparing values for two drop-down boxes.
For instance, you get the first drop-down box value, then you can populate second drop-down box menu by using a formula. value[this = that]

I used the simple one for this example since it was much better for me to create a one big database table connection then many small database tables.

Here's my data connections

POS Update Form Testers : to get people's information manually. (on my tesing environment, I can't use GetUserProfileByName nor User Information List)
GetUserProfileByName : to get people's information (check Itay Shakury's blog )
User Information List : just like GetUserProfileByName (at least on my production environment it works like GetUserProfileByName)
ItemMenuList : external database
ItemCategories  : external database
CategoryMAP : external database
CategoryMST : external database
Main submit : testing
SharePoint Library Submit : sharepoint submit

and again, this will help you to have a better form for your workflow.

My Cheat Sheet for SSRS

From various websites with my try-catch-fail method :P

Thank you all who posted valuable information on the web

· Format numbers with leading zeros

=format(Fields!name.Value,"00000000")

=strdup(10-InStr(Fields!CHECK_AMT.Value, "."), "*") & Fields!name.Value & "*" è “****1111.11*”

· to int to string

=cstr(Fields!name1.Value & format(Fields!name2.Value,"00000000"))

· next line/new line

vbCrLf

· Grouping

To use 2 or more inner groups

1. Use ‘add group’ to add parent group

2. Delete the column(delete the column only)

3. Do it one more time

4. Insert a row outside group X 2 times

5. Do it all over again

6. If you can remove “[=” it would be easier so look for it

· Page break by grouping (group properties)

=Fix((RowNumber("tablename")-1) / rownumbers)

· To keep header and footer on the same page this should be used in a list

????? where did it go?

Display multi-valued parameter

=join(Parameters!processLevel.Value,",")

· Two datasets from different servers

If two tables have 1:1 relationship

=Lookup(p.key from dataset1, p.key from dataset2, column that I need, “dataset name”)

If two tables have 1:many relationship

=join(LookupsetLookup(p.key from dataset1, p.key from dataset2, column that I need, “dataset name”))

If more than one condition is needed in order to filter out or show/hide

=>break it down to one condition at a time(it is sequential)

· To Keep headers on each page

<TablixRowHierarchy>

<TablixMembers>

<TablixMember>

<RepeatOnNewPage>true</RepeatOnNewPage> ADD this Line!!

<KeepTogether>true</KeepTogether>

OR

Change row/column groups to advanced mode

open the first static property and change FixedData, KeepTogether, KeepWithGroup, RepeatOnNewPage to True as you need

· Suppressing Null or Zero Values at Run Time

=IIF(Field!B.Value=0, 0, Field!A.Value / IIF(Field!B.Value =0, 1, Field!B.Value))

*** to avoid Attempted to divide by zero Error, use =iif(A=0, 0, B / iif(A=0, 1, A))

· Multiple value parameters

Dataset Properties => Parameters => add a parameter that has multiple values

Parameter Value => =split(join(Parameters!Name.Value,","),",")

And within the where clause, add columnName in (@Name)

· First day of week(Monday)

=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

So It can be changed any day

DateAdd("d", -(WeekDay(Parameters!Parameter1.Value,2))+1, Parameters!Parameter1.Value)

--next monday:

=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())

--First day of month:

=DateAdd("d",1-DatePart("d",Today()),Today())

--First day of next month:

=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))

--First day of year:

=DateSerial(YEAR(Today()),1,1)

--First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

================More===================

Set First Day of previous week (Monday)

=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

Set First Day of Current Week (Monday)

=DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())

Set Last Day of previous week (Sunday)

=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))

Set First Date of last month

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

*format decimals with expression

=Format(Fields!Price.Value, "C")

*to remove time from getdate()

convert(varchar(8),dateadd(dd,-1,GETDATE()),112)

*Page number by groups

*need to find a way to do it on BI

Open up with reporting builder and change the value in Group properties => Group => PageBreak

BreakLocation to Between and ResetPageNumber to True

SQL Report Service with Visual Studio 2008 - date (my cheat sheet)

There are many ways to calculate dates.

Here are some useful date calculations.

Thank you all who created and posted each one of these on their websites or blogs.

--Monday (current week):

=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

--Next Monday:

=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())

--First day of month:

=DateAdd("d",1-DatePart("d",Today()),Today())

--First day of next month:

=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))

--First day of year:

=DateSerial(YEAR(Today()),1,1)

So based on these expressions we can change Today()'s value and numbers to calculate other days.

For instance,

--using a parameter value

=DateAdd("d", -(WeekDay(Parameters!Parameter1.Value,2))+1, Parameters!Parameter1.Value)

--First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

--Last Day of previous week(Sunday)

=DateAdd("d", 1-Weekday(today),Today)

and so on....

 

Infopath 2010 multiple approvals

Infopath 2010 Sections with numbers(hiding certain parts) for a multiple approval form

This was a part of my project for last 6 weeks to bulid a multiple approval workflow with a loop.
It took more than what I thought but I think it's ok :P.
Once again, planning is very important.

here's the first part of the form.

This part of the form will initiate the workflow.
and here's some rules on this section.

and here are more rules (I got this idea from Laura Rogers from sharepoint911)

and here's the rules for submit button

Count field is the key. It will keep track of each section and pass the value to workflow to process.
Steps field can be ignored.
Initiator field holds who created this form. In our workflow some phases need to go back to a certain person or phase.
Phase field indicates whether it was submitted or approved.

here's the approval section after submitting the reqeust

now section1 is locked and the approver receives an email and open this form.

There are common rules for each section and controls.
1. if nextperson != currentuser
2. if count != 2(section number, in this example it is 2)

and here are some rules for PhaseApproval button

and submit button rules

again you can ignore steps field.
I set NextPerson = Initiator since in my workflow, it has to go back to the creator for the phase 0, 1, and 2.
Phase field is equal to one since this is an approval. (zero for submission)

that's it. now I can have a multiple approval form.
By using publishing wizard, I can now add columns in SharePoint Site that I need for workflow.