Solved

Need to populate a Dundas Chart from Clicking a DataList Item

Posted on 2008-10-30
66
1,925 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:djshohit
  • 40
  • 26
66 Comments
 
LVL 1

Expert Comment

by:clintfield
ID: 22844330
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?
0
 

Author Comment

by:djshohit
ID: 22844470
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.
<%@ 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>

Open in new window

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22844663
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?
0
 

Author Comment

by:djshohit
ID: 22844685
Spot on! I can do a postback or anything that works.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22848911
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.



<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> 

Open in new window

0
 

Author Comment

by:djshohit
ID: 22849554
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>

Open in new window

0
 

Author Comment

by:djshohit
ID: 22849560
Also I dont have a sproc to return all the table neames yet. I think I would need help with that :(
0
 

Author Comment

by:djshohit
ID: 22849586
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> 

Open in new window

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22849767
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)
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22849898
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" SelectCommandType="StoredProcedure"
                </asp:SqlDataSource>
0
 

Author Comment

by:djshohit
ID: 22849952
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

Open in new window

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22849960
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>

Open in new window

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22849983
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.  
0
 

Author Comment

by:djshohit
ID: 22850014
Nope doesnt work. Attaching a screenshot
procTables.bmp
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22850088
Ahhh...try this
CREATE PROCEDURE GetTables
AS
select name from sysobjects where xtype = 'U' order by name
GO  
The "AS" was missing...
 
0
 

Author Comment

by:djshohit
ID: 22850271
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?


---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>

Open in new window

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22850428
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.
0
 

Author Comment

by:djshohit
ID: 22850568
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
0
 

Author Comment

by:djshohit
ID: 22850614
The thing is that some countries dont have certain reports associated with it. So, the datalist has to change based on the users selection.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22850739
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?
 
0
 

Author Comment

by:djshohit
ID: 22850810
"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.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22850965
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?
 
 

    <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>

Open in new window

0
 

Author Comment

by:djshohit
ID: 22850979
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?
0
 

Author Comment

by:djshohit
ID: 22851010
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 :)
0
 

Author Comment

by:djshohit
ID: 22851036
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
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22851774
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_MonsterEmploymentIndex_YoY_prod).
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.  
0
 

Author Comment

by:djshohit
ID: 22852175
Booya pretty much it. I'll post the diagram in a minute.
0
 

Author Comment

by:djshohit
ID: 22852245
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
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22852492
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?
0
 

Author Comment

by:djshohit
ID: 22852557
Wow I didnt think of that. Yea that is much much easier. Give me a minute I'll do that right now.
0
 

Author Comment

by:djshohit
ID: 22852612
Done. Here is what it looks like
update.JPG
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22852668
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 = @Country

Open in new window

0
 

Author Comment

by:djshohit
ID: 22852695
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 :)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:clintfield
ID: 22853091
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.

<!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>

Open in new window

0
 

Author Comment

by:djshohit
ID: 22853105
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.
0
 

Author Comment

by:djshohit
ID: 22853127
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.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22853157
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...
0
 

Author Comment

by:djshohit
ID: 22853179
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.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22853275
You would check for the querystring variable in the postback.
if(Request.QueryString["reportName"] != null){
//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.
0
 

Author Comment

by:djshohit
ID: 22866958
Hey Clintfield,
Hopefully you had a good weekend. I tried playing around with what you mentioned but couldnt get it done. :(

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22867062
So everything works, but you couldnt get the code for dynamically binding the querystring variable to dundas?
0
 

Author Comment

by:djshohit
ID: 22867072
Yup thats it. Everything till now works just fine.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22867266
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.

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();
 
 

        }

    }

Open in new window

0
 

Author Comment

by:djshohit
ID: 22867427
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.
asp:HyperLink ID="lnkTable" runat="server" NavigateUrl='<%# "~/tables.aspx?table=" + DataBinder.Eval(Container.DataItem, "ReportName") %>'>

        <%# DataBinder.Eval(Container.DataItem,"ReportName") %>

        

        </asp:HyperLink>

Open in new window

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22867477
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?
0
 

Author Comment

by:djshohit
ID: 22867590
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?
0
 

Author Comment

by:djshohit
ID: 22867616
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?
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22867659
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.
0
 

Author Comment

by:djshohit
ID: 22867783
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.
0
 

Author Comment

by:djshohit
ID: 22868793
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
0
 

Author Comment

by:djshohit
ID: 22868982
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.

0
 
LVL 1

Accepted Solution

by:
clintfield earned 500 total points
ID: 22869635
It looks like you  are on the right track.  All you really have to do is use the datatable to grab the tablename and its x and y values.  Use this to bind to your chart.  
First add the parameters to your querystring using something similar to the first part of the snippet.
Then use the Request.QueryString for the x and y vals like I did in the second part of the snippet.
I didnt test the code, but that is basically how it should look.
Looking back at the original question you posted...I think we have sufficiently answered Question 1 and 2.  Can you award points?  We can still follow up, but Im not sure we are posting threads relevant to the original question.
Thanks.
 
//in your datalist hyperlink add the querystring

<asp:HyperLink ID="lnkTable" 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>
 
 
 
 

//-------------------------------------------

    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();

 

            Chart1.Series.Add("Totals");

            Chart1.Series["Totals"].ValueMemberX = Request.QueryString["xval1"].ToString();

            Chart1.Series["Totals"].ValueMembersY = Request.QueryString["yval1"].ToString();
 

        }

    }

Open in new window

0
 

Author Comment

by:djshohit
ID: 22869809
Sure. I'm gonna award you the points for this. Thank you so much for your help so far.
0
 

Author Comment

by:djshohit
ID: 22870170
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?
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22870264
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.DataItem, "xaxis1") should correspond to your XAxis1 col in your Reportcountry table.  This is the table that is binding to your datalist.  The result HTML will look something like this:
<a href="default.aspx?reportname=monsteremployement&xaxis1=MEI&yaxis1=month">National</a>
0
 

Author Comment

by:djshohit
ID: 22870847
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'.
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22870926
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.
0
 

Author Comment

by:djshohit
ID: 22871466
All columns returning. Still the error:

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>

Open in new window

error.JPG
procresult.JPG
0
 

Author Comment

by:djshohit
ID: 22871613
Let me give it a shot before you help me out. I think this is a stupid mistake I have done.
0
 

Author Comment

by:djshohit
ID: 22871791
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
0
 

Author Comment

by:djshohit
ID: 22872127
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.

0
 
LVL 1

Expert Comment

by:clintfield
ID: 22875234
Glad its going well.  Let me know when you post that 2nd graph question.
0
 

Author Comment

by:djshohit
ID: 22876187
0
 

Author Comment

by:djshohit
ID: 22878189
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 :)
0
 
LVL 1

Expert Comment

by:clintfield
ID: 22878337
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?
0
 

Author Comment

by:djshohit
ID: 22889435
Zooming would be fine. I know a way to take care of that. i'm gonna modify the question to remove zooming.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now