Retrieving url parameters

I have a 2 reports - Product Master and Product Details. I have the ProductID parameter being passed properly from the Master via the URL to the Details Report, but the Details report doesn't seem to be picking up the paramater from the URL and keeps prompting for the ProductID.

What settings should I be configuring in my Details report to say - "Get the parameter from the URL"?

I've tried multiple configs within the Report Parameters dialog box but I seem to be missing something...

?

In the first report, on the navigation tab for the textbox:

Choose the Report action, choose a report or enter a path to the report and then click the parameters button. Provide the values you want. you can use an expression here which is something like Parameters!ParameterName.Value which will pass to the drill-through report the same value as was passed to the original report.

Hope that helps,

-Lukasz

Retrieving Hierarchical Data from a single table

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.

Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure.
Example data:

ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level
-------------------------------------------
1 Custom Furniture 0 0
2 Boxes 0 0
3 Toys 0 0
4 Bedroom 1 Custom Furniture 1
5 Dining 1 Custom Furniture 1
6 Accessories 1 Custom Furniture 1
7 Picture Frames 6 Accessories 2
8 Serving Trays 6 Accessories 2
9 Entertainment 1 Custom Furniture 1
10 Planes 3 Toys 1
11 Trains 3 Toys 1
12 Boats 3 Toys 1
13 Automobiles 3 Toys 1
14 Jewelry 2 Boxes 1
15 Keepsake 2 Boxes 1
16 Specialty 2 Boxes 1

Desired output:

Custom Furniture
Accessories
Picture Frames
Serving Trays
Bedroom
Dining
Entertainment
Boxes
Jewelry
Keepsake
Specialty
Toys
Automobiles
Boats
Planes
Trains

Hello, if I get you right, here is a very short article that might get you started:http://www.mmkit.com/article.php?sid=345&lang=en_GB

HTH. -LV

|||The answer very much depends on whether you are using Sql Server 2005 or an older version. Assuming 2005 then have a read ofHeirarchical Queries in Sql Server 2005.|||Thanks much. This was exactly what I needed. I don't think I could have come up with this solution on my on at this point.|||

--Copy the code to run on your machine. I named your table as TreeSource with columns needed.

IF(SELECTOBJECT_ID('TreeSource','U'))ISNOTNULL

BEGIN

DROPTABLE TreeSource

END

GO

CREATETABLE [dbo].[TreeSource](

[ProductCategoryID] [int]NULL,

[CatDescription] [nvarchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentProductCategoryID] [int]NULL

)ON [PRIMARY]

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(1,'Custom Furniture',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(2,'Boxes',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(3,'Toys',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(4,'Bedroom',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(5,'Dining',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(6,'Accessories',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(7,'Picture Frames',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(8,'Serving Trays',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(9,'Entertainment',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(10,'Planes',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(11,'Trains',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(12,'Boats',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(13,'Automobiles',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(14,'Jewelry',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(15,'Keepsake',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(16,'Specialty',2)

--step 0

CREATETABLE #Tree(

NodeintNOTNULLIDENTITY(100, 1),

ParentNodeint,

ProductCategoryIDintNOTNULL,

Depthtinyint,

Lineagevarchar(50))

--step 1

INSERTINTO #Tree(ProductCategoryID)SELECT ProductCategoryIDFROM TreeSourceORDERBY ParentProductCategoryID,CatDescription

Go

UPDATE TSET T.ParentNode=P.Node

FROM dbo.#Tree T

INNERJOIN TreeSource EON T.ProductCategoryID=E.ProductCategoryID

INNERJOIN TreeSource BON E.ParentProductCategoryID=B.ProductCategoryID

INNERJOIN dbo.#Tree PON B.ProductCategoryID=P.ProductCategoryID

GO

--step 3

UPDATE #TreeSET Lineage='.', Depth=0WHERE ParentNodeIsNull

--step 4

WHILEEXISTS(SELECT*FROM #TreeWHERE DepthIsNull)

UPDATE TSET T.depth= P.Depth+ 1,

T.Lineage= P.Lineage+Ltrim(Str(T.ParentNode,4,0))+'.'

FROM #TreeAS T

INNERJOIN #TreeAS PON(T.ParentNode=P.Node)

WHERE P.Depth>=0

AND P.LineageIsNotNull

AND T.DepthIsNull

--step 5 final

SELECTSpace(T.Depth*4)+ E.CatDescriptionASName

FROM TreeSource E

INNERJOIN #Tree TON E.ProductCategoryID=T.ProductCategoryID

ORDERBY T.Lineage+Ltrim(Str(T.Node,6,0))

--SELECT * FROM #Tree

DROPTable #Tree

--read more http://www.sqlteam.com/item.asp?ItemID=8866

RETRIEVE VALUE AFTER ADD FROM SQLDATASOURCE

I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC

Thanks

Steve

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){int id = Convert.ToInt32(e.Command.Parameters["@.ProductId"].Value);}}

Sure, you just need to add a Parameter of the type "Return" as in this example:

ASPX

<asp:formview id="FormView1" runat="server" datasourceid="SqlDataSource1" defaultmode="Insert"><insertitemtemplate>Name:<asp:textbox id="NameTextBox" runat="server" text='<%# Bind("Name")%>' /><br /><asp:linkbutton id="InsertButton" runat="server" causesvalidation="True" commandname="Insert"text="Insert" /><asp:linkbutton id="InsertCancelButton" runat="server" causesvalidation="False" commandname="Cancel"text="Cancel" /></insertitemtemplate><itemtemplate>Id:<asp:label id="IdLabel" runat="server" text='<%# Eval("Id")%>' /><br />Name:<asp:label id="NameLabel" runat="server" text='<%# Bind("Name")%>' /><br /><asp:linkbutton id="NewButton" runat="server" causesvalidation="False" commandname="New"text="New" /></itemtemplate></asp:formview>

CODE-BEHIND

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){int id = Convert.ToInt32(e.Command.Parameters["@.ProductId"].Value);}} 

retrieve the value that occurs the most

Hi,

I am trying to retrieve the value that occurs the most at a dimension level. For example, I have to analyze the product type mostly used in a specific target segment by number of products.

The mathematical function normally used is MODE. However, in AS2005 there is not such function. I tried to use TopCount but it is not as easy as I thought.

Any suggestion?

Thanks!

As far as I know, TopCount should work.

This is a sample from Adventure Works:

with

set [TopCustomers] as

TOPCOUNT(

[Dim Customer].[Dim Customer].[Dim Customer].members,

1,

[Measures].[Sales Amount]

)

select {[Measures].[Sales Amount]} on 0,

[TopCustomers] on 1

from [Adventure Works DW]

Hope this helps,

Santi

|||I think TopCount would only get you a mode if you were to use it against a count measure. I am guessing that you want to find out which member from the product dimension has the most transactions against it for a given set of criteria. Depending on the granularity of your dimensions you may have difficulty doing this without having some sort of count measure.