If I understood correctly, the dashboards first loads all the data in memory and then applies the filters on that data, no new queries in DB.
I have the following problems:
I have a big table with a detail of about 200.000 records, which is a big load for the dashboard, which are composed by ARTICLE, CATEGORY, YEAR, MONTH, VALUE.
I want to get averages and totals for year and month, so I set up 2 checklists to select the years and the months I want to show, so I can't group the records before, because the averages and totals must be shown for the selected year and month.
I set up the relations so Article.YEAR is linked to years.YEAR and the same for the month.
When I select the years they get filtered as I expect
If I select the month I get the month of all the years, like if it's ignoring the YEAR filter.
Same if I filter for category, it ignores the year.
I also have a separate query for an istogram chart to show the totals by year and month, there I don't have the category column. How could I set a filter on an article or a category in that query without extracting all the 200k rows? A variable?
I would know what is the correct way to set up a dashboard for this quantity of data.
Thank you
EDIT:
If I use the relations to filter the 200k rows dataset I get this error when I launch the dashboard
Code: Select all
[OutOfMemoryException: Generata eccezione di tipo 'System.OutOfMemoryException'.]
System.Data.Common.DecimalStorage.SetCapacity(Int32 capacity) +22
System.Data.RecordManager.set_RecordCapacity(Int32 value) +83
System.Data.RecordManager.GrowRecordCapacity() +73
System.Data.RecordManager.NewRecordBase() +46
System.Data.DataTable.NewRecordFromArray(Object[] value) +73
System.Data.DataRowCollection.Add(Object[] values) +16
System.Data.DataTable.UpdatingAdd(Object[] values) +323
System.Data.DataTable.LoadDataRow(Object[] values, Boolean fAcceptChanges) +425
Stimulsoft.Data.Engine.StiDataRowJoiner.LeftJoinRowsV2(StiDataLink link) +627
Stimulsoft.Data.Engine.StiDataRowJoiner.Join(StiDataJoinType type, StiDataLink link) +46
Stimulsoft.Data.Engine.StiDataJoiner.Join(IEnumerable`1 tables, List`1 links, IStiApp app) +757
Stimulsoft.Data.Engine.StiDataAnalyzer.Analyze(IStiQueryObject query, String group, IEnumerable`1 meters, StiDataRequestOption option, IEnumerable`1 userSorts, IEnumerable`1 userFilters, IEnumerable`1 dataFilters, IEnumerable`1 dataActions, IEnumerable`1 transformSorts, IEnumerable`1 transformFilters, IEnumerable`1 transformActions, IEnumerable`1 drillDownFilters) +1107
Stimulsoft.Report.Dashboard.StiElementDataCache.Create(IStiElement element, StiDataRequestOption option) +285
Stimulsoft.Report.Dashboard.StiElementDataCache.GetOrCreate(IStiElement element, StiDataRequestOption option) +111
Stimulsoft.Report.Web.Helpers.Dashboards.StiListBoxElementViewHelper.GetElementItems(IStiListBoxElement listBoxElement) +42
Stimulsoft.Report.Web.Helpers.Dashboards.StiDashboardElementViewHelper.GetElementContentAttributes(IStiElement element, Double scaleX, Double scaleY, StiRequestParams requestParams) +67
Stimulsoft.Report.Web.StiReportHelper.GetDashboardPage(StiReport report, Int32 pageIndex, StiRequestParams requestParams) +3794
Stimulsoft.Report.Web.StiReportHelper.GetPagesArray(StiReport report, StiRequestParams requestParams) +411
Stimulsoft.Report.Web.StiReportHelper.ViewerResult(StiRequestParams requestParams, StiReport report) +761
Stimulsoft.Report.Web.StiWebViewer.ProcessRequest(StiRequestParams requestParams) +357
Stimulsoft.Report.Web.StiWebViewer.OnInit(EventArgs e) +48
System.Web.UI.Control.InitRecursive(Control namingContainer) +139
System.Web.UI.Control.InitRecursive(Control namingContainer) +312
System.Web.UI.Control.InitRecursive(Control namingContainer) +312
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +408
[code]
These are the relations I set up
[attachment=0]filters_dashboard.PNG[/attachment]