Multi Data Blocks in Columns but different rollups

Good afternoon!

I am currently designing a DataView report layout that shows Customer payment history with conditional formatting illustrating where we collected 1 of 3 options; If we collected payment that month, if we collected more than 1 payment that month, or if we collected no payment at all.

Since we have posted payment counts parsed out by month in columns, can we add singular entity or cubed(I have loaded in both scenarios) columns that display their initial sign up date and account end date? Also, in the case of a month where there was no payment collected, is there a way to cross reference with the Account End Month so we can quickly determine if either there was no payment posted or if the account cancelled altogether?

I’ve included a series of screen shots illustrating what we’ve built thus far as well as data sets that we have available for utilization.

Please let us know if you have additional clarifying questions or concerns.

Any assistance with this is greatly appreciated.

Thanks!

Ford Reid

Find more posts tagged with

Accepted answers

Brendan Broughton

Hi Ford Reid, going from your excel sample I'm assuming you want one cube/block to be displayed across multiple columns (the one displaying '1's), 1 for each month, and then the other (displaying the dates) to only show a single column?

If I have this correct it should be easily possible.

First remove 'month' from the by column axes. This would display one column for each block.

Go to the 'References' tab on the block you wish to split out by month and select month under 'Detail By'. This should then detail (ie. display) that block by month, which will put a column for each month.

you set this here..

Leaving the other blocks as is (ie. no detail by) will leave them with a single column, and the 'detailed by' block will show multiple columns.

Hope this helps and is what you were after.

Cheers

All comments

unknown

Hi Ford Reid,

It sounds you are able to reload data on a regular basis from some kind of source billing system. If that's the case, I imagine you have a process with datareaders to do so. I would recommend adding additional cubes and populating them in that same load process to simplify the dataview. All the calculations can be done as part of a dataflow step in the load process. Each time the load runs, each cube will be updated.

In particular, here are some cubes I suspect would help support this analysis.

Payment Count (Month, BA Customer #)
- This can be calculated in a dataflow step by counting the payments in a given period
Status (Month, BA Customer #)
- This can be calculated in a dataflow step with a column algorithm comparing the current period with the start date and end date

Brendan Broughton

If I have this correct it should be easily possible.

First remove 'month' from the by column axes. This would display one column for each block.

you set this here..

Leaving the other blocks as is (ie. no detail by) will leave them with a single column, and the 'detailed by' block will show multiple columns.

Hope this helps and is what you were after.

Cheers

Previous Member

Hello Brendan Broughton!

Thanks for the quick tip! I think we're getting very close. We were able to add in the single columns as well as detailed columns by month. However, the singular columns are not displaying any values. I've extracted these date entities and reloaded them into dummy ones as an additional test but still no luck.

Attached are some screen shots showing the latest configuration.

Thanks again Brendan!

Ford Reid