Current filter:
                                You should refresh the page.
                                  • Dear Support Team,

                                    About 15 months ago a collegue created a new mvc application.
                                    This application serves as a separate module for our main mvc application.
                                    Due to growth of our company we had to put further development on hold.

                                    This new mvc application is now being reviewed by me.
                                    Main parts of this new mvc application are:
                                    - End User Reporting
                                    - End User Dashboard

                                    For the End User Reporting I found out that a few different methods are used to connect to the database.
                                    I spend today to investigate all the different ways these methods are used.

                                    I have 1 question at this moment.
                                    When one creates a report, you have assigned a datasource to it.
                                    And the datasource can hace multiple data members.

                                    I tested how the data is retrieved from our sql servers with SQL Server Profiler, and saw some 'strange' behaviour.
                                    I have a datasource with 8 data members.
                                    And when I use just 1 field of 1 of these data members, there are 8 queries firing on the server.
                                    I expected just 1 query with the selection of the 1 field I used.

                                    Then I tested the use of parameters.
                                    The report showed correctly just 1 record, but according to SQL Server Profiler the same queries were executed.
                                    So again all the data is retrieved.

                                    Is this how it is supposed to work?

                                    Best regards,
                                    John van de Pol

                                1 Solution

                                Creation Date Importance Sort by

                                Hi John,
                                The behavior you observe on your side is a known issue that we have already fixed in the latest version of our components. See the "SqlDataSource - Execute Required Queries" entry on the What's New in v19.1 page.
                                If you have any questions or concerns regarding updating your application, or ifyou'd like to know about possible solutions in the version you're using, please let us know. I'll be happy to follow up with you.

                                Regards,
                                Yaroslav

                                Show all comments
                                • John van de Pol 09.20.2019

                                  Hi Yaroslav,

                                  We're stuck on version 18.1.6. because of issue:
                                  https://www.devexpress.com/Support/Center/Question/Details/T725402/aspxrichedit-it-s-possible-to-drag-a-floating-image-s-anchor-inside-a-merge-field

                                  So if there's any workaround for this problem, I would like to here it.

                                  Best Regards,
                                  John van de Pol

                                • Yaroslav (DevExpress Support) 09.20.2019

                                  Hi John,
                                  In the version you're using, all queries defined at the SqlDataSource level (SqlDataSource.Queries) will be filled at once, regardless of whether you use a single query or all of them. Therefore, the only solution I can suggest here is define only the queries you use. For example, if you have a look-up parameter bound to Query1 and the actual report bound to Query2, define two separate SqlDataSources, each with a dedicated query.

                                  Regarding the thread you mentioned, I just have a word with our R&D team regarding it. No specific promises were made, but we may be able to provide you with a workaround in one of next releases. I suggest that you keep an eye on that thread for further updates in order to not miss anything.

                                  Regards,
                                  Yaroslav

                                • John van de Pol 09.20.2019

                                  Hi Yaroslav,

                                  Working day is almost finished here :-).
                                  So I will look at your solution next monday.

                                  I guess besides this issue, I will have a few other questions about End User Reporting.
                                  Those questions will be about using datasets and about RegisterDataSourceWizardDBSchemaProviderExFactory.
                                  Is it desirable to commit separate tickets for this?

                                  Best regards,
                                  John

                                • Yaroslav (DevExpress Support) 09.20.2019

                                  No worries, John. Please keep me informed of your results.

                                  Regarding your question:
                                  Yes, it's better to create a separate ticket for each encountered issue as, otherwise, it will become quite difficult to keep track on the conversation and overall progress.
                                  Thank you in advance and have a nice weekend.

                                  Regards,
                                  Yaroslav

                                • John van de Pol 09.24.2019

                                  Hi Yaroslav,

                                  Because our 'management' module is a separate Project in our Solution, we were able to only update this Project to version 19.1.6.
                                  I tested with SQL Server Profiler again and found out that only the queries were used wich actually are in use.
                                  So that's great!

                                  But....
                                  We have a few 'large' companies as client with huge amounts of data in some of the database tables.
                                  All the records of 1 query are retrieved immediately even if you use a parameter as filter.

                                  I can imagine that a report shows 10 records while all the records are retrieved.
                                  Some tables have over a million records.....
                                  So this is not ideal.

                                  Do you have any solution for this problem?

                                  Best regards,
                                  John

                                • Yaroslav (DevExpress Support) 09.24.2019

                                  Hi John,
                                  I'm happy to hear that things are working well after the update. As for your follow-up inquiry;
                                  >>All the records of 1 query are retrieved immediately even if you use a parameter as filter
                                  Apparently, you set the filter at the report level (XtraReport.FilterString). This filter is data agnostic, meaning it works with the already loaded in-memory collection of objects. If you'd like to avoid loading extra data by the client, parameterize the actual query instead. The "Query Builder" dialog provides all the necessary functionality for this task, so you don't have to write any extra code: Query Parameters. Note that query parameters can be linked to report parameters through an expression: you won't lose the capability of letting the user to choose which record(s) to display (contrary to just hardcoding some value).

                                  Should you have other questions, let us know.

                                  Regards,
                                  Yaroslav

                                • John van de Pol 09.26.2019

                                  Hi Yaroslav,

                                  I've looked at the "Query Builder" dialog,
                                  Is it not possible to use lookup settings there?

                                  Best regards,
                                  John

                                • Yaroslav (DevExpress Support) 09.26.2019

                                  I confirm that query parameters do not provide any look-up options: their single responsibility is to pass the value down to the executed query (the value is escaped to prevent any malicious code injection). What you can do, however, is to map a query parameter to a report parameter through a binding Expression.

                                  Report parameters is what you see upon opening any report in the viewer: they are rendered in a separate "Parameters" panel. Unlike query parameters, each report parameter has a specific editor associated with it. This can be a plain text editor, a date editor, or a drop-down editor with the list of possible options: Look-Up Parameter Editors.

                                  I hope it helps. Let me know if you face any issues with configuring your parameters.

                                  Regards,
                                  Yaroslav

                                • John van de Pol 09.27.2019

                                  Yaroslav,

                                  I tried to create a report with a query parameter and a report parameter.
                                  It's unclear to me where exactly in the report I can make this mapping.

                                  Best regards,
                                  John

                                • Yaroslav (DevExpress Support) 09.27.2019

                                  John,
                                  I've attached a screencast to illustrate how the required mapping can be achieved, both in the End-User Web Report Designer and its desktop counterpart.

                                  Let me know if it helps.

                                  Regards,
                                  Yaroslav

                                • John van de Pol 09.27.2019

                                  Yaroslav,

                                  Thank you again, this was very helpful!

                                  John