none
Binding images in cxml for pivot viewer

    Question

  • Hi All,

    I have an excel with duplicated data in few rows. So, I created my cxml file from my code. Now, how can I bind the images for this cxml file in oder to bind this cxml file to pivot viewer?

    Sunday, December 26, 2010 11:08 PM

Answers

  • If you already have a database and queries to get the data then there is not a lot of database work required. The sample JIT server has a LINQ to SQL connection to the AdventureWorks database. You may be surprised by how little is involved.

    If all your data is in one table, simple. If not a view joining a few tables. Basically you are aiming to return a 'grid-like' set of data that maps almost 1-for-1 with the collection id, description, links and facet values.

    The most work usually involves just connecting to the database ;) Are you using SQL server, Access or other?

    Thursday, December 30, 2010 1:16 PM

All replies

  • Let me know if I understood you correctly, you have an excel file with some duplicate rows. And you read this excel file from c# into a datatable and then converted this datatable into a collection using the Pivot JIT Server, is this correct??

    If not, can you please explain to me, what exactly have you done?

    Monday, December 27, 2010 9:09 AM
  • Not exactly. I have an excel with duplicated rows (all rows have an unique ID column. What I mean duplicate rows is, I have two/more rows with same unique ID and different data in the rest of the columns). So, I read this excel in c# and created an cxml (as a regular xml file) file from the code itself. I am not sure whether it is the right way to do. But I started off here. If there is a better way to do this, please let me know.

    Monday, December 27, 2010 3:48 PM
  • The way you are generating the cxml file from the code is fine but I have few more questions:

    1. Do you want the cxml to be generated dynamically? This is useful only in the situation where you fetch the data from a database which gets updated every now or then!

    2. After you generated the cxml, when you say "Binding images in cxml for pivot viewer", what exactly is the problem here? This is because if you have generated the cxml properly the images come automatically. But another thing, that is to be noticed here is, if you are trying to use the cxml (that you generated from the code) as static cxml, this may not work as desired.

    3. If you dont need the cxml to be dynamic (since your data may not change), and you are using code only bcoz you have duplicate IDs, then you can just rely on the excel i.e. you'll have to use the PivotViewer Collection Tool for Microsoft Office Excel (this is an add-in for excel). Download this tool and use it for creating your collection. As far as your rows with same ID values are concerned, a simple excel formula can be used to only get the first row with particular id value.

    So, let me know what is your situation here and we can proceed accordingly!

    Monday, December 27, 2010 10:12 PM
  • OK. Sorry for all the mess I created. Let me start from the beginning and explain my scenario. 

    I have a tool which gives me an excel sheet(with duplicate rows, as I mentioned before) as an output. This excel sheet is generated by querying a database with specific parameters. This excel sheet doesn't have any images. I need to visualize this excel report to the user in a pivot viewer which I tried with a simple excel (having pivot extensions for excel and adding images from here). This is so simple. Now the problem is, I want to automate this process. User will use the excel as an input file (this will be changing from time to time and user to user) for my web app and I need to visualize the report in the pivot viwer. 

    For this, I started at generating a plain xml (in the cxml format, without having any 'ImgBase' and 'Img="#0"' properties for the xml nodes) file from the excel. I am stuck here and don't know how to proceed further.

    Let me know if I am not clear and  I apologize for not being clear on my question.

    Tuesday, December 28, 2010 2:39 AM
  • Ok, I understood now quite clearly still I'll explain myself just to clarify.

    1. You have a tool that gives an excel file as output (which can have duplicate value in the ID column).
    2. You create a new excel file (where u use the pivotviewer excel add-in). You copy all the data from the first excel file and add images there and make your collection.
    3. And this is the process you want to automate.

    My questions:

    1. The images that you want to use with each item in the first excel file (that your user will upload), can they be found dynamically??

    2. Is it still required to handle the duplicate ID values?

     

    Tuesday, December 28, 2010 3:24 AM
  • You understanding is perfect except that, I am not generating another excel from the tool given excel. I am generating the cxml from the original excel itself. I used the pivot excel add-in just for my understanding of cxml structure, which will help me in generating the cxml as a we do for xml files in c#.

    Images are not uploaded by the user. Depending on the ID column, images can be found dynamically (I am not sure of this process). But still, I need to handle the duplicate IDs. Otherwise, the filter in pivot viewer is getting screwed up. For example, lets say I got the following table in excel from the tool.

    Name      Place

    Steve      USA

    Harry       USA

    Shyam     USA

    Shyam     India


    If I use the same table for generating my collection, the image of Shyam will show up twice in the pivot viewer which is not good. So, I am adding the duplicate values in the facets which makes it easy for filtering.

    I can get the same data from the database without relying on the tool. But I do not know how to deal with databases along with pivot viewer. So, I choose this hard way of altering my excel, converting it to cxml from code, which is not turning out to be a solution. I am completely new to this pivot and silverlight. Pardon my ignorance. 

    Wednesday, December 29, 2010 7:17 PM
  • It could be the right time for you to learn a little bit of database and use the JIT server example. As your data will be changing the JIT server will handle it with no effort later.

    You already have a database and presumably it is not too difficult to locate the queries already being used. What database software are you using?

     

    Thursday, December 30, 2010 5:20 AM
  • Ok, I understood your situation. If you really don’t want to use your database you can do the same using Excel, although it’s not recommended. I guess, here you are taking the Excel file that you originally have and generating the cxml using C# (by probably using StringBuilder or XDocument or XmlDocument). Now if you wish to get rid of the rows with duplicate ids, you have 2 ways to do it.

    1. You can use the office’s very functionality (I know this is available in Office 2007 and 2010 but not sure about the previous versions). To be able to do this you have to follow the below process:
      1. Convert your data into a table. You can do this by selecting the complete region of the data, go to the Insert Tab and click on Table.
      2. Now when you are on the table, you will a new table appearing in the tabs “Table tools”. Click on this Tab and then in the Tab Groups, in second one you’ll see there’s a button “Remove Duplicates”, click on it and it’ll will ask you the column on which the duplicates are to be removed, select “ID” in it and click on OK. And you are done.
    2. The second way is through C#. Here you can read all the data in excel in a DataTable object. See http://lordakshaya.wordpress.com/2010/12/28/excel-operations-%e2%80%93-open-xml-unleashed/ for more details. And once you have all the data in this object then you can use the below code to remove the duplicate ID rows.

    dt = dt.DefaultView.ToTable(true, "Id");

    And then your rows with duplicate IDs will go away.

     

    But the problem here is even after your duplicate ids are gone from excel or from the DataTable still you can’t generate the CXML like other xml files that will help in attaching images. Instead, you WILL HAVE to use the JIT server to generate the collection for you. The sample code is available under the section “Sample Code for Creating Just in Time PivotViewer Collections” from here http://www.silverlight.net/learn/pivotviewer/collection-tools/ and the API is available from here http://i2.silverlight.net/content/pivotviewer/developer-info/api/. Here you have a Collection class in which you can add items, give the image information in the parameters like below:

    var collection = new Collection {Name = "Items"};

    var facetArr = new Facet[count]; (where count is the no. of facets)

    facetArr[0] = new Facet(“FacetName”, “FacetValue”);

    .

    .

    .

    var image = new ItemImage(new Uri(imageUrl, UriKind.RelativeOrAbsolute));

     

    where imageUrl is the url through which the image is accessible, please note if your images are present in your hard disk, you can use the following instead:

    var image = new ItemImage("Image File Path");

     and then:

    collection.AddItem(fileName, postUrl, string.Empty, image, facetArr);

    return collection;

     

    So, just traverse through your rows in the DataTable and create the items and add it to your collection. The best part here is:

    You are not prone to make the errors while generating the cxml.
    The cxml can get updated automatically using the updated data (this is if you query from the database to get the data).
     Let me know if you want more help!

    Thursday, December 30, 2010 8:12 AM
  • I think, lots of study required to generate a cxml from a SQL Server database using JIT server. I'll go throught the links provided and give it a try. Mean while, please provide me any other links to tutorials which teaches me about using JIT server for SQL server/


    Thanks.

    Thursday, December 30, 2010 12:46 PM
  • If you already have a database and queries to get the data then there is not a lot of database work required. The sample JIT server has a LINQ to SQL connection to the AdventureWorks database. You may be surprised by how little is involved.

    If all your data is in one table, simple. If not a view joining a few tables. Basically you are aiming to return a 'grid-like' set of data that maps almost 1-for-1 with the collection id, description, links and facet values.

    The most work usually involves just connecting to the database ;) Are you using SQL server, Access or other?

    Thursday, December 30, 2010 1:16 PM
  • you need to be familiarized with linq to sql.

    Additionally, you don't need to worry about how JitServer Works, just extends the abstract CollectionFactoryBase and ImageBase classes.

    In order to handle the duplicate IDs, u can separate it into two views, and consume it with Linq to sql, Linq automatically creates classes to hold the data has collections

    Tuesday, January 04, 2011 3:33 PM
  • you need to be familiarized with linq to sql.

    Dude, dont scare the guy! Linq To Sql can be used for the purpose but its not mandatory!! Plain Sql querying will do the job Smile

    And also, its just about getting the data, this can be taken from any data source - be it sql, oracle, access or just excel (as in the case of this guy).

    Wednesday, January 05, 2011 1:06 AM
  • Wink like I said the hardest part is usually connecting to the database. I mentioned LINQ to SQL just beause the AdventureWorks sample in the JIT server uses it. Basically you connect to the database and choose tables/views to use. Then the code looks like this:

    var products = from p in m_dataContext.Products
    where p.ListPrice > 0
    select p;
    foreach (var product in products.Take(maxItems_c)) {
    ...
     
    With VS intellisense you are off doing collection.AddItem for each record of you data. It is easy to clone to get a simple collection generated from data in a single table or view in your own database.
     
    Wednesday, January 05, 2011 4:12 AM
  • You are right, in terms of using the power of VS intellisense and ease of usage as class objects! Its a good way to kickstart for a user to create his first collection. Then again, it also depends on the comfort level of the user in Linq to Sql.

    I would like to add some more information, which is a bit orthogonal to this context, but still I would like to share it:

    In a global picture Linq to Sql has its own disadvantages. For e.g., it should be used if and only if the table definition is not going to change in the future, otherwise, as you would know, the user will have re-create the dbml file everytime there's a change in the schema doesnt matter how small it is. Then there could be performance issues with too many calls to sql being fired and it can work with SQL only Laughing.

    But, if all is planned well in advance, there wont be any issue with Linq to Sql apart from the performance when many calls are being fired, which can be reduced big time by creating Stored Procedures and calling them directly.

     

    Wednesday, January 05, 2011 5:11 AM
  • Hi:d, he is very scared for now :D, .

    He ask for some guidelines in order to understand the JIT sample, he says also that will need to handle the duplicated ids, thought, the easiest way to do it in the JIT server is using Linq.

    Thursday, January 06, 2011 5:23 PM
  • Hi,

    This thread helping me  a lot.. still got struck while extending:(

    For Extending JitServer i have implemented CollectionFactoryBase into my class

    below is the code for that one.

    after running this code i am getting an error exception at FactoryClassFinder class line 65

       at System.Reflection.RuntimeModule.GetTypes(RuntimeModule module)
    at System.Reflection.RuntimeModule.GetTypes()
    at System.Reflection.Assembly.GetTypes()
    at PivotServerTools.Internal.FactoryClassFinder.<EnumerateTypesInAssemblies>d__9.MoveNext() in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\Internal\FactoryClassFinder.cs:line 65
    at PivotServerTools.Internal.FactoryClassFinder.Find(String folderPath) in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\Internal\FactoryClassFinder.cs:line 27
    at PivotServerTools.Internal.CollectionFactories.AddFromFolder(String folderPath) in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\Internal\CollectionFactories.cs:line 43
    at PivotServerTools.PivotHttpHandlersImpl.AddFactoriesFromFolder(String folderPath) in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\PivotHttpHandlers.cs:line 169
    at PivotServerTools.PivotHttpHandlersImpl.AddDefaultFactoryLocationIfNone() in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\PivotHttpHandlers.cs:line 363
    at PivotServerTools.PivotHttpHandlersImpl.CollectionInfoHtml() in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\PivotHttpHandlers.cs:line 178
    at PivotServerTools.PivotHttpHandlers.CollectionInfoHtml() in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServerTools\PivotHttpHandlers.cs:line 58
    at PivotServer._default.HtmlFragmentListPivotCollectionFactories() in D:\MS CRM Practice\TestProjects\PivotJitServer\PivotServer\default.aspx.cs:line 14
    at ASP.default_aspx.__Renderform1(HtmlTextWriter __w, Control parameterContainer) in d:\MS CRM Practice\TestProjects\PivotJitServer\PivotServer\default.aspx:line 17
    at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
    at System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer)
    at System.Web.UI.HtmlControls.HtmlContainerControl.Render(HtmlTextWriter writer)
    at System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output)
    at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
    at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
    at System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer)
    at System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children)
    at System.Web.UI.Control.RenderChildren(HtmlTextWriter writer)
    at System.Web.UI.Page.Render(HtmlTextWriter writer)
    at System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter)
    at System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter)
    at System.Web.UI.Control.RenderControl(HtmlTextWriter writer)
    at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

    //Loader Exception

    {"Could not load file or assembly 'System.Windows.Controls.Layout.Toolkit, Version=4.0.5.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.":"System.Windows.Controls.Layout.Toolkit, Version=4.0.5.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"}

    namespace CollectionFactories
    {
        class TelecomPoc : CollectionFactoryBase
        {
            public TelecomPoc()
            {
                //this.Name = "Telecom POC";
                //this.SampleQueries = "";
                //this.Summary = "TelecomPoc is use to get the customers and its images from Database";
            }
    
            public override Collection MakeCollection(CollectionRequestContext context)
            {
                const int maxItems_c = 150;
    
                try
                {
                    var customers = from c in Telecom_Context.Customers
                                   where c.ConnectionType>1
                                   select c;
    
                    Collection collection = new Collection();
                    collection.Name = "Telecom Works database";
    
                    foreach (var customer in customers.Take(maxItems_c))
                    {
                        //TODO: An exercise for the reader:
                        // Load the image data from the ProductPhotos database table and use it to draw the item.
                        ItemImage image = null;
                        
                        collection.AddItem(customer.Name, null, null, image
                            , new Facet("Name", customer.Name)                        
                            , new Facet("CreditLimit",customer.CreditLimit)
                            );
                    }
                    //collection.SetFacetFormat("CreditLimit", "$#,0.00");
                    return collection;
                     
                }
                catch (Exception ex)
                {
                    return ErrorCollection.FromException(ex);
                }
            }
    
    
            // Private Fields
            //======================================================================
    
            TelecomLinqDataContext Telecom_Context = new TelecomLinqDataContext();
        }
    }

    Tuesday, November 29, 2011 7:35 AM
  • Your code looks fine. Are you sure you have built and deployed the new dll's and that your http request matches the class name: TelecomPoc.cxml (as it your requested class is not being found in the assembly)

    That should be all that is required from your error trace...

    Tuesday, November 29, 2011 8:37 AM
  • Looks like you are missing the toolkit from your SL project (which the PivotViewer needs to have).  Make sure you have System.Windows.Controls.Layout.Toolkit added to your SL project.

     

    Tuesday, November 29, 2011 9:10 AM
  • Hi David,

    Thanks for your reply..

    yes i have built an application and i am running that via VS itself..

    is there any documentation for deploying this Silverlight app on to IIS 7?

    Tuesday, November 29, 2011 12:01 PM
  • Hi Tony,

    I checked SL project directory, there exists System.Windows.Controls.Layout.Toolkit.dll 

    Thanks & Regards

    Raziq

    Tuesday, November 29, 2011 12:08 PM
  • You possibly have more than 1 problem left to sort out. Do double check that you test web page (via SL object parameters) and/or SL code that causes the load CXML is correcting referenciing: TelecomPoc.cxml as the first error is saying that it cannot find your custom implementation in the assembly (spelling, case-sensitive, etc)

    Possibly the XAP is out of date and not being overwritten by a new build too if you think the Toolkit dll ref is there. Similarily, the JIT dll's may not be up to date hence it can't find TelecomPoc.

    Have a further nosey around and see if you can spot something along these lines.

    Tuesday, November 29, 2011 12:59 PM
  • Hi!  Experts!  I wish i can get help for my problem posted at http://social.msdn.microsoft.com/Forums/en-US/silverlightpivotviewer/thread/4636b0c1-7e12-450a-bbc8-87fd8f40c9ca/.  In that thread, I report that the failure of the dynamic generation of images (content-type = text/html rather than image/jpeg).  What is the possible approach to fix this problem?  Thanks. 

    Thursday, April 19, 2012 11:08 AM