Grouping Data in Matrix like Table control

All,

Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:

Region Country City $ales

North America

USA

Chicago 4 MM

LA 10 MM

NYK 6 MM

Canada

Toronto 4 MM

while the matrix would display as:

North America USA Chicago 4MM

LA

NYK

Canada Toronto ...

Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?

Any one?

On a related note, How can I add the "heading" to the dynamic rows in the matrix similar to table:

Region Country City Sales

North America 10 MM

USA 8 MM

Chicago 2 MM

LA 2 MM

NYK 4 MM

Canada 2 MM

Toronto 2 MM

Can a matrix be formatted as above, it can be in a table but I am not sure if this can be achieved with Matrix?

|||This issue is now resolved. The SubTotal property of each Row/Column grouping in a Matrix could be set to "Before/After" the details cells to achieve the desired format. Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively. It saved me from having to write a Cross Tab query in dynamic SQL.|||

Were you able to get the headings on the columns? That's what I've been fighting with, but I haven't been able to figure it out.

Thanks,

Marianne

|||

DotNet_Guy wrote:

Matrix is an amazing tool created by the SSRS team but it does require you to fiddle with it a bit before being used effectively.

SSRS 2008 will include a Tablix (cross between a table and a matrix) due to grouping issues and the like that couldn't previously be accomplished easily.

GROUP BY and SUBQUERY for sum

hello everyone
i hope you could help me in this query.

I have one tables: tblA

tblA columns and data are as follows:

Prod Prodchid Req Process

Banana bread ingredientC 1 Y

ingredientC ingredientA 2 Y

ingredientC ingredientB 1 Y

ingredientA white sugar 2 N

ingredientA flour 1 N

ingredientB banana 1 N

ingredientB flour 1 N

I can to query the sum of Req group by Prodchid

SELECT Prodchid,sum(Req) as Qty

FROM tblA

GROUP BY Prodchid

the result is

Prodchid Qty

white sugar 2

flour 2

banana 1

How can I query by multiply require ingredientA.

How can I use SUBQUERY to get anwser below:

Prodchid Qty

white sugar 2x2=4

flour 1X2+1x1=3

banana 1x1=1

Try

SELECT Prodchid,sum(case Prodchid when 'ingredientA' then 2*Req else Req) as Qty

FROM tblA

GROUP BY Prodchid

or

SELECT Prod, Prodchid,sum(case Prod when 'ingredientA' then 2*Req else Req) as Qty

FROM tblA

GROUP BY Prod,Prodchid

|||

Anyway the ideea is to use case...when... in select command|||

"sum(case Prodchid when 'ingredientA' then 2*Req else Req) as Qty"

This only can apply for 'ingredientA' only, how to replace the '2' by subquery result.

|||

pps1 wrote:

How can I query by multiply require ingredientA.

Prodchid Qty

white sugar 2x2=4

flour 1X2+1x1=3

banana 1x1=1

Why do you have to multiply with 2, cause of line

ingredientA white sugar 2 N

or what ?|||

Here it is,

Code Snippet

Create Table #data (

[Prod] Varchar(100) ,

[Prodchid] Varchar(100) ,

[Req] int ,

[Process] char

);

Insert Into #data Values('Banana bread','ingredientC','1','Y');

Insert Into #data Values('ingredientC','ingredientA','2','Y');

Insert Into #data Values('ingredientC','ingredientB','1','Y');

Insert Into #data Values('ingredientA','white sugar','2','N');

Insert Into #data Values('ingredientA','flour','1','N');

Insert Into #data Values('ingredientB','banana','1','N');

Insert Into #data Values('ingredientB','flour','1','N');

Code Snippet

Select

data.[Prodchid],

Sum(data.[Req] * ingredient.[Req]) Qty

From

#data as data

Join

(

Select

[Prodchid],

[Req]

From

#data

Where

[Process] = 'Y'

) as ingredient

On ingredient.[Prodchid] = data.[Prod]

Where

data.[Process] = 'N'

Group By

data.[Prodchid]

|||

Dear Manivannan,

Thanks, That is what I needed.

In this case, I only can multiply one Level of 'Process', But anywhere it is OK.

Dear ggciubuc,

Multiply with 2, because ingredientA require 2.

All material in ingredientA should multiply with 2.

Thanks

Gridview Search

I tried doing a text box search within Gridview. My code are as follows. However, when I clicked on the search button, nothing shown.

Any help would be appreciated. I'm using an ODBC connection to MySql database. Could it be due to the parameters not accepted in MySql?

Protected

Sub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectCommand =

"SELECT * FROM carrier_list WHERE carrierName LIKE '%' + @.carrierName + '%'"

EndSub

Sub doSearch(ByVal SourceAsObject,ByVal EAs EventArgs)

GridViewCarrierList.DataSourceID ="SqlDataSource1"

GridViewCarrierList.DataBind()

EndSub

HTML CODES (Snippet)<asp:ButtonID="btnSearchCarrier"runat="server"onclick="doSearch"Text="Search"/>

' Gridview
<asp:GridViewID="GridViewCarrierList"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

<

asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"SelectCommand="SELECT * FROM carrier_list"></asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"><SelectParameters><asp:ControlParameterControlID="txtSearchCarrier"Name="carrierName"PropertyName="Text"Type="String"></asp:ControlParameter></SelectParameters>

</asp:SqlDataSource>

It's a syntax error on your SQL statement.

Try this:

SqlDataSource1.SelectCommand = "SELECT *FROM carrier_listWHERE carrierNameLIKE'%" + @.carrierName + "%'"

You had an extra ' after first '% and before last %'

Hope this helps and let me know if it worked.

Jae.

|||

It reverted with a :

"Character is not valid." error.

Line 33: SqlDataSource1.SelectCommand = "SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'"

|||

check what your server is passing to sqldatasource by doing following:

response.write("SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'")

it print out:

SELECT * FROM carrier_list WHERE carrierName LIKE %yourvalue%

Also, note that your value (carrierName) should not contain any single quote or double quote.

Hope this helps.

Jae.

|||

check what your server is passing to sqldatasource by doing following:

response.write("SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'")

it print out:

SELECT * FROM carrier_list WHERE carrierName LIKE '%yourvalue%'

Also, note that your value (carrierName) should not contain any single quote or double quote.

Hope this helps.

Jae.

|||

Hi it still prompts the same error:

response.write(

"SELECT * FROM carrier_list WHERE carrierName'%" + @.carrierName + "%'")|||

i just looked at your code from beginning again.

1. it's VB, so why should you use +? instead of &? (sorry i thought of C#)
2. you're mising LIKE on above statement.
3. you can't LITERALLY pass @.carrierName as value. Your value is txtSearchCarrier.text
4. I don't understand why you have 2 sqldatasource (Delete sqldatasource2 - this will show same effect, read on)

So, let's write it again and clean up a bit:

SqlDataSource.SelectCommand = "select * from carrier_list where carriername like '%" & txtSearchCarrier.text & "%'"

Also, you don't need <controlparameter> tag within "select parameter", try this approach:

HTML CODES (Snippet)

<asp:Textbox id="txtSearchCarrier" runat="server"/>
<asp:Button ID="btnSearchCarrier" runat="server" onclick="doSearch" Text="Search" />

' Gridview
<asp:GridView ID="GridViewCarrierList" runat="server" DataSourceID="SqlDataSource1" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" />

That's it.

This will show ALL carriers like you had with sqldatasource2, but with only one sqldatasource1 (because it will pass like '%%' whichi will return all)
*** Also, when carrier name is typed into txtSearchCarrier (obviously a textbox), it will return result sets with characters displayed in textbox.
*** Also, you should have your LOAD_PAGE empty.

If you don't want to diplay anything at first, don't bind DataSourceID = "sqldatasource1", but rather do it on btnSearchCarrier_OnClick handler.
Something like this:

sub btnSeachCarrier_OnClick (....)
.... you other code ...
gridViewCarrierList.datasourceid = "sqlDataSouce1"
gridViewCarrierList.databind()
end sub

This way, you only retrieve data when you click "searchcarrier" button.

Hope this helps and if it doesn't send me the aspx page and I will help you with it. (send it to my email,jae.lee@.jaeleeandco.com)

Jae.

|||

just in case, you HAVE to do following:

1. delete PAGE_LOAD

2. add select command to on_click look at below:

sub btnSeachCarrier_OnClick (....)
.... you other code ...
SqlDataSource.SelectCommand = "select * from carrier_list where carriername like '%" & txtSearchCarrier.text & "%'"
gridViewCarrierList.datasourceid = "sqlDataSouce1"
gridViewCarrierList.databind()
end sub

|||

Thanks Jae,

Actually I used

SqlDataSource1.SelectCommand ="SELECT * FROM carrier_list WHERE carrierName LIKE ? '%' ORDER BY carrierName ASC"

instead and it works.

Will try your suggestion too! Thanks!