djshohit
asked on
Need to populate a Dundas Chart from Clicking a DataList Item
I have a Dundas Chart that needs to be populated once a Datalist Item is clicked. The thing is that the Datalist items represent Table Names. So each time the item is clicked, I need to send in a new table with new information to the Dundas chart.
Question1 - How do I make the Datalist items clickable? Is there something else I can use which would be easier to send information to the Dundas charts (a datagrid maybe)?
Question2 - How do I make the Dundas charts read through a new table each time someone clicks a value in the dataList. Some stored procedure/function combination perhaps?
Sorry a novice here
Question1 - How do I make the Datalist items clickable? Is there something else I can use which would be easier to send information to the Dundas charts (a datagrid maybe)?
Question2 - How do I make the Dundas charts read through a new table each time someone clicks a value in the dataList. Some stored procedure/function combination perhaps?
Sorry a novice here
clintfield
Hello. Can you provide some of your html code from the datalist? Im assuming you want to create some item templates that display the data (table names) as hyperlinks? Should these hyperlinks open new windows or change some section of the current page?
ASKER
I havent customized the charts yet. I am still trying to see if GridView or DataList is a better option. But here you go.
It is suppose to change a certain section of the page (not open a new page). The certain section of the page is the Dundas graphs.
It is suppose to change a certain section of the page (not open a new page). The certain section of the page is the Dundas graphs.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register TagPrefix="dcwc" Namespace="Dundas.Charting.WebControl" Assembly="DundasWebChart" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<style type="text/css">
.style1
{
width: 100%;
height: 600px;
}
.style2
{
width: 309px;
}
.style3
{
width: 309px;
height: 122px;
}
.style4
{
height: 122px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table class="style1">
<tr>
<td class="style3">
Country<br />
<asp:DropDownList ID="CountryListDropDown" runat="server" AutoPostBack="True"
DataSourceID="CountryList" DataTextField="Country" DataValueField="Country">
</asp:DropDownList>
<asp:SqlDataSource ID="CountryList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="SELECT DISTINCT Country FROM KPI_MEI_MonsterEmploymentIndex_YoY_prod ORDER BY Country">
</asp:SqlDataSource>
<br />
Area<br />
<asp:DropDownList ID="AreaListDropDown" runat="server" AutoPostBack="True"
DataSourceID="AreaList" DataTextField="Area" DataValueField="Area"
onselectedindexchanged="AreaListDropDown_SelectedIndexChanged">
</asp:DropDownList>
<asp:SqlDataSource ID="AreaList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="SELECT DISTINCT Area FROM KPI_MEI_MonsterEmploymentIndex_YoY_prod WHERE (Country = @Country)">
<SelectParameters>
<asp:ControlParameter ControlID="CountryListDropDown" Name="Country"
PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</td>
<td class="style4">
<br />
<asp:Label ID="ReportName" runat="server" Font-Bold="True"></asp:Label>
<br />
<br />
<br />
<asp:Label ID="City" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td class="style2">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="ReportList">
<Columns>
<asp:HyperLinkField DataNavigateUrlFields="http://www.yahoo.com"
DataTextField="Report" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="ReportList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="SELECT [Report] FROM [KM_ReportCountry] WHERE ([KM_ReportCountry] = @KM_ReportCountry)">
<SelectParameters>
<asp:ControlParameter ControlID="CountryListDropDown" Name="KM_ReportCountry"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<br />
</td>
<td>
<dcwc:Chart ID="Chart1" runat="server" BackColor="#D3DFF0"
BackGradientEndColor="White" BackGradientType="TopBottom"
BorderLineColor="26, 59, 105" BorderLineStyle="Solid" BorderLineWidth="2"
Height="526px" ImageUrl="~/tempImages/ChartPic_#SEQ(300,3)" Palette="Dundas"
Width="851px">
<borderskin skinstyle="Emboss" />
<series>
<dcwc:Series BorderColor="180, 26, 59, 105" ChartArea="Stock" ChartType="Stock"
Color="224, 64, 10" Name="Input" ShadowColor="64, 0, 0, 0"
XValueType="DateTime" YValuesPerPoint="4">
</dcwc:Series>
<dcwc:Series BorderColor="180, 26, 59, 105" BorderWidth="0" ChartArea="Volume"
Color="65, 140, 240" CustomAttributes="MinPixelPointWidth=2, PointWidth=0.6"
MarkerStep="10" Name="Volume" ToolTip="#VAL{C}" XValueType="DateTime">
</dcwc:Series>
</series>
<MapAreas>
<dcwc:MapArea Coordinates="74.40108,61.04045,82.9852,86.75476"
ToolTip="$62.00" />
<dcwc:MapArea Coordinates="60.09422,76.80083,68.67833,86.75476"
ToolTip="$24.00" />
<dcwc:MapArea Coordinates="45.78735,76.38609,54.37147,86.75476"
ToolTip="$25.00" />
<dcwc:MapArea Coordinates="31.48049,71.82387,40.06461,86.75476"
ToolTip="$36.00" />
<dcwc:MapArea Coordinates="17.17363,56.89297,25.75775,86.75476"
ToolTip="$72.00" />
</MapAreas>
<chartareas>
<dcwc:ChartArea BackColor="64, 165, 191, 228" BackGradientEndColor="White"
BackGradientType="TopBottom" BorderColor="64, 64, 64, 64" Name="Stock"
ShadowColor="Transparent">
<axisx labelsautofit="False" linecolor="64, 64, 64, 64">
<ScrollBar Enabled="False" LineColor="64, 64, 64" />
<LabelStyle Enabled="False" Font="Trebuchet MS, 8.25pt, style=Bold"
Format="MMM dd" Interval="Auto" IntervalOffset="Auto" IntervalOffsetType="Auto"
IntervalType="Auto" />
<majortickmark enabled="False" interval="Auto" intervaloffset="Auto"
intervaloffsettype="Auto" intervaltype="Auto" />
<majorgrid interval="Auto" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" linecolor="64, 64, 64, 64" />
<View MinSize="7" MinSizeType="Days" />
</axisx>
<Position Height="40" Width="90" X="3" Y="7" />
<axisy labelsautofit="False" linecolor="64, 64, 64, 64" startfromzero="False">
<ScrollBar LineColor="64, 64, 64" />
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" Interval="Auto"
IntervalOffset="Auto" IntervalOffsetType="Auto" IntervalType="Auto" />
<majorgrid interval="Auto" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" linecolor="64, 64, 64, 64" />
<majortickmark interval="Auto" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" />
</axisy>
</dcwc:ChartArea>
<dcwc:ChartArea AlignWithChartArea="Default" BackColor="64, 165, 191, 228"
BackGradientEndColor="White" BackGradientType="TopBottom"
BorderColor="64, 64, 64, 64" Name="Volume" ShadowColor="Transparent">
<axisx labelsautofit="False" linecolor="64, 64, 64, 64">
<ScrollBar BackColor="LightSteelBlue" ButtonColor="Control"
LineColor="64, 64, 64" />
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" Format="MMM dd"
Interval="Auto" IntervalOffset="Auto" IntervalOffsetType="Auto"
IntervalType="Auto" ShowEndLabels="False" />
<majorgrid interval="Auto" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" linecolor="64, 64, 64, 64" />
<majortickmark interval="Auto" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" />
<View MinSize="7" MinSizeType="Days" />
</axisx>
<Position Height="40" Width="90" X="3" Y="52" />
<axisy interval="40" labelsautofit="False" linecolor="64, 64, 64, 64">
<ScrollBar LineColor="64, 64, 64" />
<LabelStyle Font="Trebuchet MS, 8.25pt, style=Bold" Interval="40"
IntervalOffset="Auto" IntervalOffsetType="Auto" IntervalType="Auto" />
<majorgrid interval="40" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" linecolor="64, 64, 64, 64" />
<majortickmark interval="40" intervaloffset="Auto" intervaloffsettype="Auto"
intervaltype="Auto" />
</axisy>
</dcwc:ChartArea>
</chartareas>
<legends>
<dcwc:Legend AutoFitText="False" BackColor="Transparent" Enabled="False"
Font="Trebuchet MS, 8.25pt, style=Bold" Name="Default"
TitleFont="Microsoft Sans Serif, 8pt, style=Bold">
</dcwc:Legend>
</legends>
</dcwc:Chart>
<asp:SqlDataSource ID="MEIData" runat="server"></asp:SqlDataSource>
</td>
</tr>
</table>
</form>
</body>
</html>
I dont think it matters if you use datalist or gridview (maybe someone will disagree) but a datalist is probably more light weight.
It looks like you need your items in the datalist to update the dundas chart on the current page. Can there be a postback?
It looks like you need your items in the datalist to update the dundas chart on the current page. Can there be a postback?
ASKER
Spot on! I can do a postback or anything that works.
Ok. I think this should be easy enough then. You could also use a repeater for this, which would be even less generated HTML, but lets assume the datalist will allow you more flexibility if you need more functionality down the road. Lets try this. (This works in 2003 and 2005, and should work in 2008, but let me know if you have an issue.)
First create your datalist like you see in the code snippet. Im assuming you have sprocs and methods that will return all the table names? The datalist just uses an objectsource and then creates an html table with hyperlinks that call the same page, but with a querystring variable named "table." This will hold the table name that you will use to bind to your Dundas. Does this work? If so, then we can proceed to binding to Dundas next.
First create your datalist like you see in the code snippet. Im assuming you have sprocs and methods that will return all the table names? The datalist just uses an objectsource and then creates an html table with hyperlinks that call the same page, but with a querystring variable named "table." This will hold the table name that you will use to bind to your Dundas. Does this work? If so, then we can proceed to binding to Dundas next.
<asp:DataList ID="DataList1" runat="server" DataSourceID="ObjectDataSource1">
<ItemTemplate>
<asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/tables.aspx?table=" + DataBinder.Eval(Container.DataItem, "Table") %>'>
<%# DataBinder.Eval(Container.DataItem,"Table") %>
</asp:HyperLink>
</ItemTemplate>
</asp:DataList><asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetTables"
TypeName="employees"></asp:ObjectDataSource> ASKER
I have a SqlDataSource as follows. Couldyou help me with where to put that code?
<asp:SqlDataSource ID="ReportList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="SELECT [Report] FROM [KM_ReportCountry] WHERE ([KM_ReportCountry] = @KM_ReportCountry)">
<SelectParameters>
<asp:ControlParameter ControlID="CountryListDropDown" Name="KM_ReportCountry"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ASKER
Also I dont have a sproc to return all the table neames yet. I think I would need help with that :(
ASKER
I ran this code : After binding my datasource to the table. Gives me an error
asp:DataList ID="DataList1" runat="server" DataSourceID="ReportList">
<ItemTemplate>
<asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/tables.aspx?table=" + DataBinder.Eval(Container.DataItem, "KM_ReportCountry") %>'>
<%# DataBinder.Eval(Container.DataItem,"KM_ReportCountry") %>
</asp:HyperLink>
</ItemTemplate>
Couple of questions.
1. Do you know how to create an objectdatasource?
2. Is the name of your aspx page "tables?" (If not, change the navigate url value to your table name)
1. Do you know how to create an objectdatasource?
2. Is the name of your aspx page "tables?" (If not, change the navigate url value to your table name)
Scratch the objectdatasource question. Lets go with what you know...which is an sqldatasource.
1. Create a sproc named procTables. Use this as the query for the sproc:
select name from sysobjects where xtype = 'U' order by name
2. Create your sqldatasource and point to this sproc. It should look something like this:
<asp:SqlDataSource ID="TableList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="procTables"
</asp:SqlDataSource>
1. Create a sproc named procTables. Use this as the query for the sproc:
select name from sysobjects where xtype = 'U' order by name
2. Create your sqldatasource and point to this sproc. It should look something like this:
<asp:SqlDataSource ID="TableList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="procTables"
</asp:SqlDataSource>
ASKER
Could you help me out with the sproc? I have never created a stored procedure with the query you have given to me so maybe its something I am doing wrong. I tried the code written below - and it isnt working.
CREATE PROCEDURE GetTables
select name from sysobjects where xtype = 'U' order by name
GO
Sorry for the multiple posts...but here is the aspx that you can try. I tested it and it runs the sproc and displays all the database tables in a datalist as hyperlinks. Does it work if you try this?
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Tables.aspx.cs" Inherits="Tables" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/tables.aspx?table=" + DataBinder.Eval(Container.DataItem, "name") %>'>
<%# DataBinder.Eval(Container.DataItem,"name") %>
</asp:HyperLink>
</ItemTemplate>
</asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="procTables" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
CREATE PROCEDURE GetTables select name from sysobjects where xtype = 'U' order by name GO
That should work. Is it not? Also, you may want to name it procTables just so we can easily talk about the same sproc without translating names.
That should work. Is it not? Also, you may want to name it procTables just so we can easily talk about the same sproc without translating names.
ASKER
Nope doesnt work. Attaching a screenshot
procTables.bmp
procTables.bmp
Ahhh...try this
CREATE PROCEDURE GetTables
AS
select name from sysobjects where xtype = 'U' order by name
GO
The "AS" was missing...
CREATE PROCEDURE GetTables
AS
select name from sysobjects where xtype = 'U' order by name
GO
The "AS" was missing...
ASKER
It works. Ok here's the tricky part. This datatable, gets populated based on a click from 2 drop down menu's.
Basically, we have two drop down menu's that say choose a country - it does an autopostback and changes the values of the city drop down menu accordingly. When I click on city, it does an auto postback, and it is suppose to show me the tables available only for that city.
For this purpose, I created a table called KM_Report Country, which basically had the Country and the associated reports available for them. Thats why I had the following code earlier for the sqldatasource attached. Can you give me an idea how to do this - maybe in the stored procedure or the query itself?
Basically, we have two drop down menu's that say choose a country - it does an autopostback and changes the values of the city drop down menu accordingly. When I click on city, it does an auto postback, and it is suppose to show me the tables available only for that city.
For this purpose, I created a table called KM_Report Country, which basically had the Country and the associated reports available for them. Thats why I had the following code earlier for the sqldatasource attached. Can you give me an idea how to do this - maybe in the stored procedure or the query itself?
---The query in the Sql Data Source
SELECT [Report] FROM [KM_ReportCountry] WHERE ([KM_ReportCountry] = @KM_ReportCountry
---The SqlDataSource itself
<asp:SqlDataSource ID="ReportList" runat="server"
ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="SELECT [Report] FROM [KM_ReportCountry] WHERE ([KM_ReportCountry] = @KM_ReportCountry)">
<SelectParameters>
<asp:ControlParameter ControlID="CountryListDropDown" Name="KM_ReportCountry"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
You want the sqldatasource listed above to call a sproc/query and pass the currently selected dropdown value? This value is a city that should have table names associated with it in a table in the db? Can you post your schema or the sql for your db tables? Maybe you can post a pic of your ERD or database diagram showing the tables and their cols? That would be helpful.
ASKER
Here are the details you asked with the details associated with it. Hope this makes sense.
DropDown-Gets-City-and-Area.JPG
appscreen.JPG
Reports.JPG
DropDown-Gets-City-and-Area.JPG
appscreen.JPG
Reports.JPG
ASKER
The thing is that some countries dont have certain reports associated with it. So, the datalist has to change based on the users selection.
Ok...so first dropdown selects the distinct countries. Then the user selects a country which in turn shows the areas for that country in the second dropdown. So selecting Belgium in the first dropdown would show Brussels and Flemish Region in the second dropdown. Correct?
Im a little confused as Im not sure why you are showing the areas, since it looks like the datalist should show all the reports for a country, not an area. So when they select a region like Brussels, you want the datalist to show Monster Employment Index, Consumer Confidence Index and Inflation for possible reports that they can then click on to generate the graph?
Im a little confused as Im not sure why you are showing the areas, since it looks like the datalist should show all the reports for a country, not an area. So when they select a region like Brussels, you want the datalist to show Monster Employment Index, Consumer Confidence Index and Inflation for possible reports that they can then click on to generate the graph?
ASKER
"So selecting Belgium in the first dropdown would show Brussels and Flemish Region in the second dropdown. Correct? " ---------- Yes
Areas are there, thanks to the clients request :). Basically in the near future, there would be a need to generate reports because different cities would have different reports. For now, we are only using country, since that is the only thing effecting the database. I'm going to bug them with this question today. But for now, country should suffice as the value to generate Reports.
Areas are there, thanks to the clients request :). Basically in the near future, there would be a need to generate reports because different cities would have different reports. For now, we are only using country, since that is the only thing effecting the database. I'm going to bug them with this question today. But for now, country should suffice as the value to generate Reports.
Ok. So now we need to generate the graph based on the selected country. So they choose Belgium and you want the datalist to show the reports from the report col with a link that posts back to the page and a querystring with the report name. Im not sure why you needed the names of the tables in the db, but it looks like your datalist templates should look more like the code snippet. The sqldatasource for this needs to look like the 2nd posting in the code snippet.
Based on this...I think you need a new sproc to get the reports from that table based on your dropdown parameter, correct?
Based on this...I think you need a new sproc to get the reports from that table based on your dropdown parameter, correct?
<ItemTemplate>
<asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/Default.aspx?reportCountry=" + DataBinder.Eval(Container.DataItem, "report") %>'>
<%# DataBinder.Eval(Container.DataItem,"report") %>
</asp:HyperLink>
</ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>
SelectCommand="procGetReports" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="szCountry" QueryStringField="reportCountry" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ASKER
Actually we do have the data - shoot I need to put it in the DB! Ok so this changes things. I need to rework the Table which I showed you in Reports.JPG above.
I think I would need to add a column for cities/areas and make it such so that when the user selects any US value from the from the first drop down, it updates the Area drop down, and after selecting a value from there, it generates the datalist of reports.
The wierd thing is this, and maybe your expertise will give me a good idea about this. Here is what the client has told me:
""
Data available for each of the following countries: Belgium, EU (overall), France, Germany, Italy, Netherlands, Spain, Sweden, UK:
" Monster Employment Index
" Consumer Confidence Index
" Inflation
" Unemployment Rate
" GDP
Data available for US (national):
" Monster Employment Index
" Help Wanted Index
" Consumer Confidence Index
" Inflation
" Turnover Rate
" Job Openings Rate
" Unemployment Rate
" Employment
" GDP
Data available for US markets (top 28): Atlanta, Baltimore, Boston, Chicago, Cincinnati, Cleveland, Dallas, Denver, Detroit, Houston, Indianapolis, Kansas City, Los Angeles, Miami, Minneapolis, New York City, Orlando, Philadelphia, Phoenix, Pittsburgh, Portland, Sacramento, San Diego, Seattle, San Francisco, St. Louis, Tampa, Washington, DC
" Monster Employment Index
" Help Wanted Index
" Unemployment Rate
" Employment
""
Hence, only the united states city specific data changes the reports. The other places in Europe dont make a difference. Aaah, any chance that would be doable in a stored procedure?
I think I would need to add a column for cities/areas and make it such so that when the user selects any US value from the from the first drop down, it updates the Area drop down, and after selecting a value from there, it generates the datalist of reports.
The wierd thing is this, and maybe your expertise will give me a good idea about this. Here is what the client has told me:
""
Data available for each of the following countries: Belgium, EU (overall), France, Germany, Italy, Netherlands, Spain, Sweden, UK:
" Monster Employment Index
" Consumer Confidence Index
" Inflation
" Unemployment Rate
" GDP
Data available for US (national):
" Monster Employment Index
" Help Wanted Index
" Consumer Confidence Index
" Inflation
" Turnover Rate
" Job Openings Rate
" Unemployment Rate
" Employment
" GDP
Data available for US markets (top 28): Atlanta, Baltimore, Boston, Chicago, Cincinnati, Cleveland, Dallas, Denver, Detroit, Houston, Indianapolis, Kansas City, Los Angeles, Miami, Minneapolis, New York City, Orlando, Philadelphia, Phoenix, Pittsburgh, Portland, Sacramento, San Diego, Seattle, San Francisco, St. Louis, Tampa, Washington, DC
" Monster Employment Index
" Help Wanted Index
" Unemployment Rate
" Employment
""
Hence, only the united states city specific data changes the reports. The other places in Europe dont make a difference. Aaah, any chance that would be doable in a stored procedure?
ASKER
And oh, I really appreciate your help thus far. I am in need with this project and I am stuck on this. Thank you so much for all your help :)
ASKER
I needed the table name for the stored procedure because each table, is essentially a table. I am attaching a picture to give you an idea. Sorry for so many replies. I'll try to incorporate that in one in the future.
tables.JPG
tables.JPG
Ok...one more time to be sure Ive got everything.
(Lets just focus on non-US first):
1. If the country selected in the first dropdown is Belgium, we then show the areas in the second dropdown.
2. The datalist shows a set of hyperlinks that shows the names of the reports for Belgium (i.e. Monster Employment) with a querystring that contains the name of the table (KPI_MEI_MonsterEmployment
3. When you click on the hyperlink, it post back and passes the table name to Dundas control for graphing.
Also, can you create an ERD for me and show it? Basically just create a new DB diagram and add all the relevant tables, then arrange them neatly so its easy to determine what relates to what.
(Lets just focus on non-US first):
1. If the country selected in the first dropdown is Belgium, we then show the areas in the second dropdown.
2. The datalist shows a set of hyperlinks that shows the names of the reports for Belgium (i.e. Monster Employment) with a querystring that contains the name of the table (KPI_MEI_MonsterEmployment
3. When you click on the hyperlink, it post back and passes the table name to Dundas control for graphing.
Also, can you create an ERD for me and show it? Basically just create a new DB diagram and add all the relevant tables, then arrange them neatly so its easy to determine what relates to what.
ASKER
Booya pretty much it. I'll post the diagram in a minute.
ASKER
Actually there isnt any relation between the tables. No foreign keys, no references, nothing. They are tables that each have a Country and Area. Now that I think of it, I think I need a seperate table with the list of countries and Areas and compare it to it. But lets leave that for now.
God I hope no one from work see's this :-P
Schema.JPG
God I hope no one from work see's this :-P
Schema.JPG
I dont see where you have a table that shows the Report table name. Your KM_ReportCountry shows the country/report relationship, but how do you know which table that is? I know you named your tables to coincide with the report, but for the sake of scaleability, I think you should also add a col to your KM_ReportCountry table that is called tablename. That is easier than taking the report name for the country then adding KPI_MEI as a prefix and YoY at the end. Does that make sense? Then we could write a query that does this.
select report, tablename from KM_ReportCountry where KM_ReportCountry = @Country
Report would be the datalist text and tablename would be the querystring var that we pass to dundas.
How does that sound?
select report, tablename from KM_ReportCountry where KM_ReportCountry = @Country
Report would be the datalist text and tablename would be the querystring var that we pass to dundas.
How does that sound?
ASKER
Wow I didnt think of that. Yea that is much much easier. Give me a minute I'll do that right now.
ASKER
Done. Here is what it looks like
update.JPG
update.JPG
Me likey. That should make things much easier. Now try the code in the snippet to create the sproc.
CREATE PROCEDURE procGetReports @Country varchar(50)
AS
SELECT
report,
tablename
FROM
KM_ReportCountry
WHERE
KM_ReportCountry.KM_ReportCountry = @CountryASKER
Done! I think thats where I was really feeling hopeless about passing in tables into stored procedure (which I found out you cant do). This is much better :)
Now we need your datalist showing the reports as hyperlinks with querystrings containing the reportname. Try the code snippet.
The snippet is basically an aspx which has a dropdown that is populated with countries and a datalist that populates based on the selected country. When a person chooses a country it will refresh the page and run the query to get the reports based on the dropdown selection, which will in turn fill the datalist with the hyperlinks.
Let me know if this works.
The snippet is basically an aspx which has a dropdown that is populated with countries and a datalist that populates based on the selected country. When a person chooses a country it will refresh the page and run the query to get the reports based on the dropdown selection, which will in turn fill the datalist with the hyperlinks.
Let me know if this works.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
DataTextField="KM_ReportCountry" DataValueField="KM_ReportCountry">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="procGetCountries" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
<asp:DataList ID="DataList1" runat="server" DataKeyField="KM_ReportCountry" DataSourceID="SqlDataSource3">
<ItemTemplate>
<asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/Default.aspx?reportName=" + DataBinder.Eval(Container.DataItem, "reportname") %>'>
<%# DataBinder.Eval(Container.DataItem,"report") %>
</asp:HyperLink>
</ItemTemplate>
</asp:DataList><asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="procGetReports" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:FormParameter FormField="DropDownlist1" Name="KM_ReportCountry" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
ASKER
I was able to bind everything (2 drop downs and the datalist) together. Having trouble capturing the click event in the datalist to pass in value.
ASKER
And yes, that works fine. Ignore my previous question. That is something else and I'll post that as a new question in a bit.
So you now need to take the reportname in the querystring and bind it to dundas on the postback, right? Sounds like we are almos there. By the way, Im leaving for the day in about 10 minutes...not sure if I will be back on until Monday...
ASKER
Ok one last question for now, so I can handle this on the weekend. How would I bind the Dundas chart on the current aspx page with a new table, everytime someone clicks it? I am used to working with the wizard.
Any small code snippet would help. I'll play around with it till Monday if need be.
Any small code snippet would help. I'll play around with it till Monday if need be.
You would check for the querystring variable in the postback.
if(Request.QueryString["re
//bind dundas chart
}
The dundas samples that are installed are usually full of code to do this sort of thing. I gotta run for now, but I will see how you progressed Monday.
if(Request.QueryString["re
//bind dundas chart
}
The dundas samples that are installed are usually full of code to do this sort of thing. I gotta run for now, but I will see how you progressed Monday.
ASKER
Hey Clintfield,
Hopefully you had a good weekend. I tried playing around with what you mentioned but couldnt get it done. :(
Hopefully you had a good weekend. I tried playing around with what you mentioned but couldnt get it done. :(
So everything works, but you couldnt get the code for dynamically binding the querystring variable to dundas?
ASKER
Yup thats it. Everything till now works just fine.
Im not using Dundas 2008, but I would think that it wont make much of a difference.
The first thing we need to do is create a datatable or dataset based on the querystring which contains the name of the table we want to retrieve data from. Then we bind that datatable to your dundas chart.
Try the code snippet. You might want to put a break point after the datatable is filled just to be sure it has data.
The first thing we need to do is create a datatable or dataset based on the querystring which contains the name of the table we want to retrieve data from. Then we bind that datatable to your dundas chart.
Try the code snippet. You might want to put a break point after the datatable is filled just to be sure it has data.
using System.Configuration; //add this to your usings list
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["reportName"] != null)
{
//create the datatable
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["Global KPIConnectionString"]);
connection.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT * FROM " + Request.QueryString["reportName"].ToString(), connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
//bind to dundas
Chart1.DataSource = dt;
Chart1.DataBind();
}
}ASKER
Ok before I try that, I believe we have the following code to click on the DataList to populate the Dundas chart.
Any advise how to change the code so it passes in a new table, along with some parameters, to the chart, everytime someone clicks on the DataList.
Any advise how to change the code so it passes in a new table, along with some parameters, to the chart, everytime someone clicks on the DataList.
asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/tables.aspx?table=" + DataBinder.Eval(Container.DataItem, "ReportName") %>'>
<%# DataBinder.Eval(Container.DataItem,"ReportName") %>
</asp:HyperLink>
Right now its set up to put the table name in the querystring so that everytime someone click on one of the reports, it postback and passes the name of the table. Each link has a different table name.
Im not sure what you mean by changing the code to pass in a new table with parameters to the chart. Are you talking about adding parameters to the querystring in addition to the tablename?
Im not sure what you mean by changing the code to pass in a new table with parameters to the chart. Are you talking about adding parameters to the querystring in addition to the tablename?
ASKER
yea actually. But I guess that could be worked out. For now, when I click on the report name in the DataList. For now I get a 404 error when I click on the list saying that the resouce could not be found.
Dashboard/tables.aspx is the link its looking at. I dont think I have a tables.aspx page. I am assuming it would be the Default.aspx which seems to work, except since I guess we havent defined any X or Y values, we wont get a graph. Am I correct?
Dashboard/tables.aspx is the link its looking at. I dont think I have a tables.aspx page. I am assuming it would be the Default.aspx which seems to work, except since I guess we havent defined any X or Y values, we wont get a graph. Am I correct?
ASKER
So here's the thing. For some reports, we need sort of two charts (one top of the other) and for some report we need just one. Should I be looking at some sort of a case statement for this, or anything you can think of which would be a better way of doing this?
Here is the tricky part. Youve got different tables with different X and Y, right? It also sounds like you want multiple charts based on the table being passed in?
This design could get complex. I would think about expanding your report table to include a few extra fields of metadata. For example, you may want to add an "X" col and a "Y" col. You could then use this create your x and y for the chart using the datable col values. That is easy, and allows you flexibility to adapt and change over time.
As far as multiple charts for one report table, I would have to know more to determine what fields we add to the report table.
This design could get complex. I would think about expanding your report table to include a few extra fields of metadata. For example, you may want to add an "X" col and a "Y" col. You could then use this create your x and y for the chart using the datable col values. That is easy, and allows you flexibility to adapt and change over time.
As far as multiple charts for one report table, I would have to know more to determine what fields we add to the report table.
ASKER
Well I think if we deal with the most complicated one, we would be able to adjust the code accordingly.
I think I understand your viewpoint about adding data into the Reports table, to help us design the tables.
I'll get back to you on that. Let me add some more data and get back to you.
I think I understand your viewpoint about adding data into the Reports table, to help us design the tables.
I'll get back to you on that. Let me add some more data and get back to you.
ASKER
Ok for now this would do. I have created a Table which basically has a 6 more columns. The first graph name, the x value of the first graph, the y value of the first graph, the name of the second graph (if required), the x value of the 2nd graph (if required) and the y value of the 2nd graph (if required). For the time being, I only have data for monster employment index so that is the only thing that will show so far. If editing is required, I'll change things accordingly.
So how do we go from here?
NewTable.bmp
So how do we go from here?
NewTable.bmp
ASKER
The x and y values correspond to actual field names of the tables where the values are being pulled from. I messed up the X and Y values a bit...Month should be an X value option. I'll fix it, but otherwise you get the jist.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sure. I'm gonna award you the points for this. Thank you so much for your help so far.
ASKER
Hey,
Ok so I have no idea how the Databinder.Eval works. I cant figure out which values correspond to the column names in the table, and which ones are alias values.
Could you help me with that real quick pls? Also, i dont think there is anything in the asp.net code for any Graph2 values. Do you think I should start a different question for that?
Ok so I have no idea how the Databinder.Eval works. I cant figure out which values correspond to the column names in the table, and which ones are alias values.
Could you help me with that real quick pls? Also, i dont think there is anything in the asp.net code for any Graph2 values. Do you think I should start a different question for that?
I would start another question for graph2. Post back here the question id if you can. I may try to follow up on that question.
Databinder.Eval just reads the data coming in from the datalist binding.
So DataBinder.Eval(Container.
<a href="default.aspx?reportn
Databinder.Eval just reads the data coming in from the datalist binding.
So DataBinder.Eval(Container.
<a href="default.aspx?reportn
ASKER
Ok I tried a lot of things but things didnt work out. I finally went back to your code to see if I can get the basics working. I get the following error:
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xaxis1'.
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xaxis1'.
Does your sproc return all the values from that table? I think we had it just returning the reportname. Make sure all the cols are returned.
ASKER
All columns returning. Still the error:
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xaxis1'.
procresult.JPG
DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'xaxis1'.
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "~/Default.aspx?reportName=" + DataBinder.Eval(Container.DataItem, "reportname") + "&xval1=" + DataBinder.Eval(Container.DataItem, "xaxis1") + "&yval1=" + DataBinder.Eval(Container.DataItem, "yaxis1") %>'>
<%# DataBinder.Eval(Container.DataItem,"report") %>
</asp:HyperLink>
%>'
</ItemTemplate>
</asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Global KPIConnectionString %>"
SelectCommand="SELECT [ReportName] FROM [MEI_ReportCountry] WHERE ([KM_ReportCountry] = @KM_ReportCountry)">
<SelectParameters>
<asp:ControlParameter ControlID="CountryListDropDown" Name="KM_ReportCountry"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>procresult.JPG
ASKER
Let me give it a shot before you help me out. I think this is a stupid mistake I have done.
ASKER
Ok there were a whole bunch of things I got wrong. i got the page to load up but when I click on the link in the datalist, I get the following error. I tested the storprocs and my database is up. So, any ideas regarding this problem?
connection.JPG
connection.JPG
ASKER
Got that fixed too. Thanks so much for ALL your help. I want to delete the question because all the information out here. But lets leave it for now.
Thank you for all the help.
Thank you for all the help.
Glad its going well. Let me know when you post that 2nd graph question.
ASKER
ASKER
Hey Clintfield,
Do you think you can pickup my question? I'm sure you must be busy with your stuff. But since you are aware of the situation than anyone else, it would be great ifyou could pickup this particular question.
Thank you, once again, for all your help :)
Do you think you can pickup my question? I'm sure you must be busy with your stuff. But since you are aware of the situation than anyone else, it would be great ifyou could pickup this particular question.
Thank you, once again, for all your help :)
You had something about a zoom on the dundas and Ive never done that before. Modify the question and take that out so I can help you. Then you may want to post another question about how to zoom on dundas. Sound good?
ASKER
Zooming would be fine. I know a way to take care of that. i'm gonna modify the question to remove zooming.