Reporting Service Configuration to Sending Mail

The photo is not match about the story I will talking about today, I knew that but I just want all of you to be happy and smile ^_^ and I likes her hips even I am a girl and straight.

OKEY! today I would like present how to configuration mail on SQL Reporting Service to sending mail to any concerns.

STEP-1
Go to "SQL Server Reporting Service Configuration Manager"

STEP-2
You will the popup as image below, This details in window it for configuration of SQL Reporting Service Only

STEP-3Then you have to fill mail server detail as image below

Read More

What is TM1 Cognos? What is good and bad side?

TM1 is the one product of IBM, The full name of TM1 is Table Manager One, Yes the concept of TM1 is keep all data on memory, Build cube, Build dimension that very easy and it create for supporting financial and budgeting planning.

For the concept to keep all data in memory, that helpful, you do not need to process cube if you want generate any report, very simple and fast then other product, They said ^_^.

There are many ways to enter to TM1 for many reason
1.TM1 Architect 
This work space is comfortable for programming, and some super user to get inside for key in data, budget, you can create cube, dimension, create formula, codding in this product.

2.TM1 Perspective
It still look the same like TM1 Architect but the only one is different it because you can export your job from here to excel or slice your job to excel with formula then you can work on excel, if you selected to slice that mean every formula will export to excel also, whatever you have change from excel, it'll make effect to TM1 Architect automatically, So why IBM have to create this product?.

3.TM1 Application
It's a web application and it the way to get inside to project. You can create work flow on this product but you can not create cube or build any dimension on here, user also can key in data on this space.

4.TM1 Web
I'm still not sure why they need to separate this section from TM1 application product because every thing seem like the same, but the different one is you can not create work flow on here, that it's!?

5.TM1 Insight
Yes if you want to work in off line, you have to install TM1 insight on your computer but that not helpful, U can not sick your job to server after you have done automatic, it need special way to do, every thing feel the same TM1 application, but you just can create or import cube and dimension from here then I still not get why IBM have to create many window to inside work space that too trouble.


Let's see compare between TM1 and SQL Server Analysis Service

List	TM1	SQL server analysis services
Brand	IBM	Microsoft
Build Cube	Yes	Yes
Process Cube	No	Yes
Make data relation table	Yes	Yes
User Friendly	Poor	Nice
Programmer Friendly	Poor	Nice
In memory	Yes	No
Fast	Depend on data	Depend on data
Database	No	SQL Server
Work Flow	Yes	No
Off Line Mode	Yes	No
Recouse	Height Speed	Heidht Speed

Read More

SSIS Parameters in Different Type of Data in Package

How can I image that parameters in SSIS that quite different when you have to use it in different type of data flow. Yes I know that at first we have to create parameters in same level but when you have to select data by use different type of database you have to change the syntax for query, OKEY! let's see what is different.

Set Parameter for Execute SQL Task Step 1

STEP-1
You have to create parameters as below by click on menu  SSIS -> "Variables"

STEP-2
Define parameters as you want for example, year, month etc..

STEP-3

After you created and define all parameters as you want you have to set parameter for "Execute SQL Task", you have to set type of parameter as your data config on table of database

STEP-4

Then click on query of Task and you have to replace the value of parameter by use =? instead

STEP-5

But if you have to put parameter on "ADO Net Source" object, you have to replace it by =@parameter_name

Set Parameter for Execute SQL Task Step 2

STEP-6

Any if you have to use "Execute SQL Task" in same package and need to set parameter, you have to add parameter in that task again

STEP-7

After you set parameter on this step, you have to replace parameter by use "=?" in "Execute SQL Task" it the same as step 1

STEP-8

To use parameter for query on OLE DB you have to click on "Parameters" button on the right page of window popup

STEP-9

And now you have to replace parameter on OLE DB for query data from SQL Server as
"=?"

STEP-10
It's perfect your job package will look like this image, click on "Start" for running package

Notice :
Define parameter for a package you have to set it just one time but for use parameter in different type of object, you have to replace in different symbol.

Read More

Import File System to SQL Integration Service Catalogs for SQL Sever Agenda

Before you will set schedule on SQL Server Agent, you have to import file package from SSIS to Integration Service first. OKEY! it's time to go.

STEP-1

Go to  SQL Server Studio 2014 -> Server type : Integration Services -> fill "Server Name or IP" Server

STEP-2

Click on "Store Package" you can create new folder by click right and "New Folder" for collect your project jobs

STEP-3

After you created folder to keep your package, you have to import package from SSIS to folder by click right on folder you have just created then select "Import Package"

STEP-4

Select package as your job path then click on "OK" to finished

Next Step to create job schedule on SQL Server Agent
http://www.mymemoboxs.com/2015/02/create-agenda-on-sql-server-agent-for.html

Notice :

If you changed any thing on your SSIS package, you have to reimport that package again, otherwise after you create job schedule and click on run, you may see many error.

GOOD LUCK 

Read More

Create Agenda on SQL Server Agent for Run Script on SSIS

After you create all SSIS to transfer data from any platform to SQL server, now you have to set up schedule for run script as automatic by use SQL Server Agent .

At first you have to import your package to Integration Services as details here
http://www.mymemoboxs.com/2015/02/import-file-system-to-sql-integration.html

STEP-1
Open SQL Server, click on right "Jobs" at SQL Server Agent then hit on "New Job"

STEP-2

Dialogue will appear as image blow, you have to set the on "General" section
Name : "Run Company Profile" or any name as you wanted
Owner : select administrator permission by click on browse
Category : pick up on "Data collector"

STEP-3
To select source file click on "Steps"
Step name : set the process name as you wanted
Type : select SQL Server Integration Service Package
Run as : SQL Server Agent Service Account will automatic set
Package sourve : SSIS Package Store
Server : localhost or IP Server
Package : Browse for your package job

STEP-4
Click on "Execution Options" and click on "Use 32 runtime" then click Ok to finished create source package

STEP-5
To set the time to running script project by click on "Schedules" on the left menu

STEP-6To run process, click right on job schedule and select "Start job as Step"

Read More

Create Parameters On SSRS for Query Data (Advance) #3

http://www.mymemoboxs.com/2015/01/create-report-on-ssrs-for-share-point.html
http://www.mymemoboxs.com/2015/01/create-report-on-ssrs-publish-on-share.html

We are moving to SSRS in advance, Now it's time to create some parameters to selection from database as we wanted. Let's go to step by step

For this concept, we will select Year and Property from database as we have on hand to be a selection when we wanted to run report.

STEP-1
Query data for value to create drop down on parameters, Click right on "Datasets" on Report Data area, if your could not see this area, go to "View" menu on top then select "Report Data" as below

STEP-2
Select Data Set from data source in same report

STEP-3Query data form table to get year value

Example Query for Year Parameter
SELECT distinct (year) FROM customer ORDER BY year DESC

STEP-4 

Now you will see Data Year as image below. Next step is create parameters by click right on "Parameters" menu

STEP-5 parameter name and label, select data type as "Allow multiple values" and "Allow blank value ("")"

Set



STEP-6 
Set value for Year parameter by click on "Available Values" then
Select from one of the following options : Get values from a query
Dataset: "dataset name" select dataset as we created
Value field: "Field year name"
Label field: "Field year name"

STEP-7
After finished create parameter "Year" you have to set parameter on your query as
"SELECT * FROM TABLE WHERE (pl_year in (@Year))"

STEP-8
Every thing have done now, you have to preview and select parameter as image

Read More