Creating Named Set for Current Month, Last 6 Months, and Parallel Period Months

Assume you have a date attributes in the following format:

[Date].[Calendar Date].[Year].&[1999].&[1].&[1].&[01/16/1999]

First create a named set [Current month], with below script. Then followed by  Last 6 Months and parallel period.

Current Month:
StrToMember('[Date].[Calendar Date].[Date].&[' + FORMAT(Now(), "MM/dd/yyyy") + ']').Parent

 Last 6 Months:
TAIL({NULL: [Current Month].ITEM(0)}, 6)

 Last 6 Months - Previous Year
TAIL({NULL: PARALLELPERIOD([Date].[Calendar Date].[Year], 1, [Current Month].ITEM(0))}, 6)

SSRS - How to Split a Single Column to Multiple Columns

Let us assume we have dataset with below fields:
 

StudentName	Score
A	2,90,100,8
B	1,80,100,7
C	1,90,100,2

Score can be defined as (attemptno, scored, total, rank)
Now, I want to split this score column to four columns as 'Attempts', 'Scored', 'Total', and 'Rank'. To add this column we need to add below expression to text boxes.

=(Split(Fields!Score.Value,",")).GetValue(0)
=(Split(Fields!Score.Value,",")).GetValue(1)
=(Split(Fields!Score.Value,",")).GetValue(2)

=(Split(Fields!Score.Value,",")).GetValue(3) 

 

Passing Config File Variables in Other Python Files

Assume you have a config file (ETLConfig.cfg) with below texts (variables):

[ETL]
etldbname = *****
etllogin = ******
etlpassword = ****
etlport = ****
etlserver = *****

[FPATH]
filepath = D:/ETLFile/

Now we need to use these variable in other python files.

Create a python file (test.py) with below script:

1. Read variables from ETLConfig.cfg using configparser and write those variables to connection strings in your file:

import configparser
import sys
sys.path.append('C:\Python34\Lib\site-packages')
import pprint
import time
import datetime
import os
import csv
import sqlite3
from datetime import date, timedelta

import mysql.connector
from mysql.connector import errorcode

config = configparser.RawConfigParser()
config.read('ETLConfig.cfg')

#GET ETL CONFIG
m1servername = config.get('ETL', 'etlserver')
m1dbname = config.get('ETL', 'etldbname')
m1login = config.get('ETL', 'etllogin')
m1password = config.get('ETL', 'etlpassword')
m1port = config.get('ETL', 'etlport')

cnx = {
  'user': m1login,
  'password': m1password,
  'host': m1servername,
  'database': m1dbname,
  'raise_on_warnings': True,
  'use_pure': False
  }

melcnx=mysql.connector.connect(**cnx)
print(melcnx)

#GET ETL FILEPATH CONFIG
etlfilepath = config.get('FPATH', 'filepath')
print(etlfilepath )

2. On executing the python file (test.py), you can see the printed connection strings having same values available in CFG file.

How to Create Configuration File (*.cfg) Using Python?

The below code help you to create a sample config file using python script. The below code is tested with python 3.4 version:


Step 1:  Check you have python 3.* installed in your machine and also you have python configparser package installed.

Step 2: Create a python file with below scripts, say the python file is saved as CreateConfig.py.

import configparser

config = configparser.RawConfigParser()

#MYSQL_MEL
config.add_section('ETL')
config.set('ETL', 'etldbname', '*****')
config.set('ETL', 'etllogin', '******')
config.set('ETL', 'etlpassword', '****')
config.set('ETL', 'etlport', '****')
config.set('ETL', 'etlserver', '*****')

#FilePath
config.add_section('FPATH')
config.set('FPATH', 'filepath', 'D:/ETLFile/')


# Writing our configuration file to 'ETLConfig.cfg'
with open('ETLConfig.cfg', 'wt') as configfile:
    config.write(configfile)

Step 3: On executing CreateConfig.py file, you can see in your working directory, a new CFG file named ETLConfig.cfg is created with below texts.

[ETL]
etldbname = *****
etllogin = ******
etlpassword = ****
etlport = ****
etlserver = *****

[FPATH]
filepath = D:/ETLFile/

This ETLConfig file can be called in other python files, and the variables can be used as Global Variables in all packages.