SSRS - How to show a tablix inside a tablix

1.     Background

This article describes how we can show a tablix inside a tablix. Cascade tablix report is very useful in such cases where we need to show summary and detail data within the same report. One tablix can be used for summary data and another tablix can be used for detail data. The advantage of this kind of report is that we don’t need to create sub report to show detail data.

Let’s consider the below data for the article. Below table having information about Country, city and its population with respect to male and female:

2.     Steps to create cascade tablix report

                    I.            Create a data source that points to required server and database.

                  II.            Create a dataset having the below SQL query:

    SELECT * FROM Population

 Dataset output is same as above mentioned table.

Create a simple tabular report having two columns Country Name and Total Population:

               III.            The above report is simple report does not having any groupings. Now we will add a group for Country Name. For Grouping, Go to grouping paneè Row Grouping èright click on the Details groupè Group propertiesèGeneral tabè Group on should be “Country Name”è click.

 

                IV.            Now go to report layout and add sum function to numerical value(Population field):

                  V.            Preview the report. We can see all country names with total population. So this report is showing summary data for country and its population.

                VI.            Now create one more tablix using the same dataset that above report is using for showing detailed data:

              VII.            Preview the report. We can see first tablix showing summary data and second tablix showing detail data.

            VIII.            Go to Report layout è first tablixè right click on left most of the row è Insert rowè Insert Group below:

 It will add a new row to tablix within the same grouping:

                IX.            Merge the cells of newly created row:

                  X.            Right click on the second tablix, copy and paste on newly created row of first tablix. We can remove country column as it is already available in first tablix.



                      Preview the report:

                XI.            For better visibility, we can add drill down also. For adding drilldown,  right click on the row where second tablix is pastedèRow VisibilityèChange the display option – When the report is initially run to “Hide” and display can be toggled by Country Name textbox and click ok:

              XII.            Now preview the report. We can see the drilldown in the report. When initially report runs, it will show summary data:

            XIII.            For seeing the detail data, we can click on (+) sign :

3.     Conclusion

 We can show a tablix inside a tablix by inserting the tablix in any group of the parent tablix.

SSRS - show or hide tablix in Sql server Reporting

Formula: =IIf(Parameters!CASETYPE_PARAM.Value="Government",True,False)

IBM DB2 - Using CASE in Select Query

Example:1

SELECT  CASE WHEN CM.CASE_TYPE='G' THEN 'Government' ELSE 'Non-Financial' END As CASE_TYPE_NAME ,
    CM.CASE_SEQUENCE_NUM,CS.DESC_EN,COUNT(*) AS TOTAL   
FROM
     FIU.CASES_MASTER CM,FIU.FI_SUSPECT_CASE_INFO SC,FIU.CASE_STATUS CS
WHERE    CM.CASE_SEQUENCE_NUM!=0 AND CM.CASE_SEQUENCE_NUM=SC.CASE_SEQUENCE_NUM AND CM.STATUS=CS.CODE
GROUP BY    CM.CASE_SEQUENCE_NUM,CS.DESC_EN,CASE WHEN CM.CASE_TYPE='G' THEN 'Government' ELSE 'Non-Financial' END


Example:2

SELECT DISTINCT CASE WHEN CM.CASE_TYPE='G' THEN 'Government'
        WHEN CM.CASE_TYPE='F' THEN 'Financial' 
          WHEN CM.CASE_TYPE='N' THEN 'Non-Financial' 
          WHEN CM.CASE_TYPE='S' THEN 'Sector' 
          WHEN CM.CASE_TYPE='I' THEN 'Individual' END As CASE_TYPE_NAME       
FROM
     FIU.CASES_MASTER CM

MySQL - Create Table with Foreign Key

Example:

Create table tblPatientPrescription(
PrescriptionId int auto_increment primary key, PatientId int, DoctorID int,
INDEX inx_tblPatientPrescription_pid(PatientId),
INDEX inx_tblPatientPrescription_did(DoctorID),
Description1 varchar(100),
Description2 varchar(100),
foreign key(PatientId) references tblpatientinfo(patientid) ON DELETE CASCADE,
foreign key(DoctorID) references tblDoctorInfo(DoctorID)ON DELETE CASCADE)

SQL Server - BCP

In this blog post, I’m going to walk through the basics of BCP (bulk copy program). BCP is a utility that installs with SQL Server and can assist with large data transfers.

Let’s see what parameter options are available to use. From the command line on a machine with SQL Server installed, type “bcp” and press Enter.

You can find out more information on BCP parameters on Books Online:http://msdn.microsoft.com/en-us/library/ms162802.aspx

For now, we’re going to examine just the basics. The simplest syntax of a BCP command is:

bcp
databaseName.Schema.TableName *or* “Query”
in, out, *or* queryout
-S ServerName\instanceName
-U userName -P password *or* -T
-c *or* -n *or* specify storage information for each column

Let’s look at these options in a little more detail:

databaseName.Schema.TableName *or* Query
You can specify either an entire table to copy or a query. The query should be surrounded in quotations and must also include the fully qualified table name.

in, out, *or* queryout
in = import, out = full table export, queryout = query to select data for export

-U userName -P password *or* -T
You can either specify a specific account to access SQL Server, or use -T to indicate Trusted Connection (i.e. Windows Authentication)

-c *or* -n *or* specify storage information for each column
-c indicates character data type, -n indicates native data type; if neither one is specified, by default you will be prompted for the data type for each column.

Now let’s put this together and run some BCP commands. All of these examples will use the AdventureWorks 2008 sample database.

First, let’s export an entire table. To do this, we’ll use the “out” parameter.

bcp AdventureWorks.Sales.SalesOrderDetail out
C:\bcp_outputTable.txt -SYourServerName -T -c

I don’t normally export an entire table… or at least, not in one process. So let’s walk through what it would look like to export the same table using a query. This will use the “queryout” parameter.

bcp "Select SalesOrderID, SalesOrderDetailID, OrderQty, ProductID
From AdventureWorks.Sales.SalesOrderDetail" queryout
C:\bcp_outputQuery.txt -SYourServerName -T -c

You’ll notice that the total duration for the query was shorter than for the full-table export. This is because we’re only exporting a few of the columns. This is important to keep in mind when bcp’ing data: you’ll get better performance if you only export the data elements that you actually need.

Now that we’ve exported some data, let’s walk through the process of importing this data. First, let’s create a table with a constraint that will result in some errors.

Create Table dbo.testBCPLoad
(
      SalesOrderID          int      Not Null
    , SalesOrderDetailID    int      Not Null
    , OrderQty              smallint Null
    , ProductID             int      Null
 
    Constraint PK_testBCPLoad
        Primary Key Clustered
        (SalesOrderID)
);

Now execute the BCP import command:

bcp sandbox.dbo.testBCPLoad in
C:\bcp_outputQuery.txt -SYourServername -T -c

You should receive a Primary Key error. When you check your results in SQL Server, you should find no results loaded into the table. This is BCP’s default behavior.

Let’s change our constraint and try the same BCP command again:

Alter Table dbo.testBCPLoad
    Drop Constraint PK_testBCPLoad;
 
Alter Table dbo.testBCPLoad
    Add Constraint PK_testBCPLoad
    Primary Key Clustered
        (SalesOrderID, SalesOrderDetailID);

bcp sandbox.dbo.testBCPLoad in
C:\bcp_outputQuery.txt -SYourServername -T -c

You should now have the data loaded into your SQL Server destination table:

So there you have it, the basics of BCP! 

A few BCP tips:

• BCP commands are case-sensitive!
• If you’re accessing the data across a WAN, perhaps via a VPN connection, try to remote desktop (mstsc) to the actual SQL Server to perform the BCP. If possible, keep the operation on the same local drive or even local network as the server; the less distance data needs to travel across a network, the faster BCP will perform.
• If you need to copy large amounts of data (i.e. >100mm rows), try breaking the data into smaller chunks. This will help if you have an error during BCP (i.e. a PK error can rollback the entire import operation by default, although there are options that can change this behavior). When working with partitioned tables, I find it very efficient to segregate the data imported/exported by partition.
• If you’re BCP’ing data into a new table, you can minimize impact on the server by waiting to create your indexes after all the data is loaded.
• I like to construct my queries in SSMS, then copy them to BCP. Since the command-line utility does not support copy and pasting, I create a text file with my BCP command in NotePad, then save the command as a .cmd. To execute, just call the .cmd file.