SSRS : How to Combine Data from 2 different DataSets

In Some Cases we need to show the Data from 2 different Datasets . For Example if we take a Telecome example

 If we want to see the [Number Calls] and [Total duration] of those  calls  to Diifferent countries  On montly basis and Yearly Basis side by side .

To Achieve This we need to Use LookUp function .





For the Above example we can create 2 different data sets one for Montly Result based on country code and another Yearly Result based on country code .

We can join 2 data sets with the Help of LookUp Function . See the below sample


=Lookup(Fields!CountryCode.Value,Fields!CountryCode.Value,Fields!Number_of_calls.Value,"YearDataSet")


MonthDataset is the Primary Dataset for the table so to get the yearly data we need to use Lookup function on CountryCode column .

Comments

Post a Comment

Popular posts from this blog

SSIS :Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

when we try to use table name as parameter is Execute sql task in SSIS Then we got this error [Execute SQL Task] Error: Executing the query "delete ? where [group] ='HTTP' " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. After many trails we came to know that it i snot possible to pass table name as input parameter for the Execute Sql Task . Work Around to solve this issue : Create a Variable to build dynamic query . In the Expression of the Neqvarible pass the Tablename variable . "Delete "+ @[User::DestRateTableName]+ "  where [Group] ='HTTP'" In Execute Sql task select the SQLSourceType as Variable and  select the new variable in Sourcevariable co...
Read more

SSAS : A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). + Solution

Problem : have installed Analysis service on my server machine through remote login from my service account. I am able to open Analysis services from there. When I open SSMS from the same service account on my local machine, I am not able to connect to that Analysis service. It is giving me error by saying : "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond 10.245.192.111:2382(System)." Solution : Based on my research, the issue was caused by the Browser Service account was changed to the domain account. In your scenario, you can try to connect to the Analysis Service using <Servername>:2383 instead of instance name. Or you can change the Browser Service account to "Local System"....
Read more

SSIS Error : Object is not an ADODB.RecordSet or an ADODB.Record + Solution

Problem : I have two script tasks in my SSIS package. The first one saves a string array as a package variable: // In 1st script task: String[] astrCustNames = new String[cust_count]; /* ... * Some code to add strings to array ... */ // Add array to a package variable Dts.Variables["CustomerNames"].Value = astrCustNames; The second task should then extract the strings from the variable. In the past, I've done this with a table variable. The same approach won't work for the string array: // In 2nd script task: OleDbDataAdapter ole_da = new OleDbDataAdapter(); // Fill the customer names data table DataTable dtCustNames = new DataTable(); ole_da.Fill(dtCustNames, Dts.Variables["User::CustomerNames"].Value); The call that fills the data adapter will result in the error "Object is not an ADODB.RecordSet or an ADODB.Record." The .dtsx package lists the data type as DTS:DataType="13" The data type of the variable as it is defined in...
Read more